Mekki MOURADI

Data Analyst

Rabat, Maroc

Mekki MOURADI
mekkimouradi@gmail.com

À Propos

Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.

SQL Projects

Performing Data Aggregation using SQL

Liens

Certificat

Compétences & Outils

SQL
Data Aggregation Functions

Description du projet

Apprendre à manipuler les données via les Fonctions d’Aggregation :

  • COUNT
  • SELECT DISTINCT & GROUP BY
  • HAVING
  • SUM
  • MIN & MAX
  • AVERAGE
  • ROUND

Objectifs de l'analyse

  • Utiliser les différentes Fonctions d’Aggregation pour aider l’entreprise à mieux comprendre ses données.
  1. COUNT
  1. How many employees are in the company? 
				
					SELECT COUNT(DISTINCT emp_no)
FROM employees;
number_employees
11797
  1. Is there any employee without a first name 
				
					SELECT COUNT(emp_no)
FROM employees
WHERE first_name IS NULL;
count
0
  1. How many records are in the salaries table? 
				
					SELECT COUNT (emp_no)
FROM salaries;
count
111943
  1. How many annual contracts with a value higher than or equal to 100k$ have been registered in the salaries table? 
				
					SELECT COUNT(*)
FROM salaries
WHERE salary >=100000;
count
3876
  1. How many times have we payed salaries to each employee? 
				
					SELECT  emp_no,
        COUNT(emp_no) AS times_payed
FROM salaries
GROUP BY emp_no
ORDER BY emp_no
LIMIT 10;
emp_notimes_payed
1000117
100026
100037
1000416
1000513
1000612
1000714
100083
1000918
100106
  1. SELECT DISTINCT & GROUP BY
  1. How many different names can be found in the employees table? 
				
					SELECT COUNT(DISTINCT first_name)
FROM employees;
count
1275
  1. Select different names from the employees table 
				
					SELECT DISTINCT(first_name)
FROM employees
LIMIT 10;
first_name
Harjit
Divine
Masaru
Conrado
Magy
Torsten
Mandell
Abdelghani
Steve
Gila
  1. List all the names and how many of them are in the employees table 
				
					SELECT  first_name,
        COUNT(first_name) AS frequency
FROM employees
GROUP BY first_name
ORDER BY first_name
LIMIT 10;
first_namefrequency
Aamer6
Aamod8
Abdelaziz8
Abdelghani5
Abdelkader7
Abdelwaheb12
Abdulah8
Abdulla10
Achilleas12
Adam8
  1. Retrieve a list of how many employees earn over 80k$ and how much they earn. Rename the second column as emp_with_same_salary 
				
					SELECT  salary,
        COUNT(emp_no) AS emp_with_same_salary
FROM salaries
WHERE salary > 80000
GROUP BY salary
ORDER BY 2 DESC
LIMIT 10;
salaryemp_with_same_salary
819497
802527
811866
857906
851526
806066
871795
811755
893565
925725
  1. HAVING
  1. Extract a list of names of employees, where the number of employees is more than 15. Order by first_name 
				
					SELECT  first_name,
		COUNT(first_name) AS frequency
FROM employees
GROUP BY first_name
HAVING COUNT(first_name) > 15
ORDER BY COUNT(first_name) DESC
LIMIT 10;
first_namefrequency
Kish20
Stepehn19
JiYoung19
Morris18
Shawna18
Ramalingam17
Valeri17
Oguz17
Mohammad17
Hisao17
  1. Retrieve a list of employee numbers and the average salary, make sure to return where the average salary is more than 120k$ 
				
					SELECT  emp_no,
        AVG(salary) AS average_salary
FROM salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY AVG(salary) DESC
LIMIT 10;
emp_noaverage_salary
18006128619.200000000000
18997128594.000000000000
20004128211.500000000000
21587127105.692307692308
14523126436.444444444444
14487125684.875000000000
11486124462.111111111111
12149123656.928571428571
15039123642.687500000000
21413121109.000000000000
  1. Extract a list of all names that have encountered less than 200 times. Let the data refer to people hired after 1st of january, 1999 
				
					SELECT  emp_no,
		first_name,
		COUNT(first_name) AS count_names, hire_date
FROM employees
WHERE hire_date > '1999-01-01'
GROUP BY emp_no
HAVING COUNT(first_name) < 200
ORDER BY COUNT(first_name) DESC
LIMIT 10;
emp_nofirst_namecount_nameshire_date
18235Elvia11999-06-16
12550Shawna11999-01-04
15124Adhemar11999-02-20
19854Youssef11999-08-28
16665Zhilian11999-03-04
19056Leaf11999-07-29
16244Parviz11999-02-22
18000Tzvetan11999-05-03
15368Seongbin11999-04-01
16308Yannis11999-05-08
  1. Select the employees numbers of all individuals who have signed more than 1 contract after the 1st january, 2000 
				
					SELECT  emp_no,
		COUNT(emp_no)
FROM dept_emp
WHERE from_date > '2000-01-01'
GROUP BY emp_no
HAVING COUNT(emp_no)>=2
ORDER BY COUNT(emp_no);
emp_nocount
205942
136262
158932
199642
166892
116092
  1. SUM
  1. Retrieve the total amount the company has paid in salary 
				
					SELECT SUM(salary)
FROM salaries;
sum
7162796017
  1. What is the total amount of money spent on salaries for all contracts starting after the 1st of january, 1997 
				
					SELECT SUM(salary)
FROM salaries
WHERE from_date > '1997-01-01';
sum
3712936185
  1. MIN & MAX
  1. Retrieve the MIN and MAX salary paid by the company 
				
					SELECT MIN(salary)
FROM salaries;
min
38812 
				
					SELECT MAX(salary)
FROM salaries;
max
145732
  1. Retrieve the LOWEST and HIGHEST employee number 
				
					SELECT MIN(emp_no)
FROM salaries;
min
10001 
				
					SELECT MAX(emp_no)
FROM salaries;
max
21797
  1. AVERAGE
  1. How much have the company paid on average to employees? 
				
					SELECT AVG(salary)
FROM salaries;
avg
63986.100220648008
  1. What is the average annual salary paid to employees who started after the 1st of january, 1997? 
				
					SELECT AVG(salary)
FROM salaries
WHERE from_date > '1997-01-01';
avg
67775.335140463282
  1. ROUND
  1. Round the average salary to the nearest whole number 
				
					SELECT ROUND(AVG(salary))
FROM salaries;
round
63986
  1. Round the average amount of money spent on salaries for all contracts that started after the 1st of january, 1997 to a precision of cents 
				
					SELECT ROUND(AVG(salary),2)
FROM salaries
WHERE from_date > '1997-01-01';
round
67775.34
  1. Retrieve the range of salary 
				
					SELECT ROUND(MAX(salary)-MIN(salary), 2) AS salary_range
FROM salaries;
salary_range
106920.00
  1. Retrieve the mid-range of salary 
				
					SELECT ROUND((MAX(salary)-MIN(salary))/2, 2) AS salary_mid_range
FROM salaries;
salary_mid_range
53460.00
SQL Projects