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.
Apprendre à manipuler les données via les Fonctions d’Aggregation :
SELECT COUNT(DISTINCT emp_no)
FROM employees;
number_employees |
---|
11797 |
SELECT COUNT(emp_no)
FROM employees
WHERE first_name IS NULL;
count |
---|
0 |
SELECT COUNT (emp_no)
FROM salaries;
count |
---|
111943 |
SELECT COUNT(*)
FROM salaries
WHERE salary >=100000;
count |
---|
3876 |
SELECT emp_no,
COUNT(emp_no) AS times_payed
FROM salaries
GROUP BY emp_no
ORDER BY emp_no
LIMIT 10;
emp_no | times_payed |
---|---|
10001 | 17 |
10002 | 6 |
10003 | 7 |
10004 | 16 |
10005 | 13 |
10006 | 12 |
10007 | 14 |
10008 | 3 |
10009 | 18 |
10010 | 6 |
SELECT COUNT(DISTINCT first_name)
FROM employees;
count |
---|
1275 |
SELECT DISTINCT(first_name)
FROM employees
LIMIT 10;
first_name |
---|
Harjit |
Divine |
Masaru |
Conrado |
Magy |
Torsten |
Mandell |
Abdelghani |
Steve |
Gila |
SELECT first_name,
COUNT(first_name) AS frequency
FROM employees
GROUP BY first_name
ORDER BY first_name
LIMIT 10;
first_name | frequency |
---|---|
Aamer | 6 |
Aamod | 8 |
Abdelaziz | 8 |
Abdelghani | 5 |
Abdelkader | 7 |
Abdelwaheb | 12 |
Abdulah | 8 |
Abdulla | 10 |
Achilleas | 12 |
Adam | 8 |
SELECT salary,
COUNT(emp_no) AS emp_with_same_salary
FROM salaries
WHERE salary > 80000
GROUP BY salary
ORDER BY 2 DESC
LIMIT 10;
salary | emp_with_same_salary |
---|---|
81949 | 7 |
80252 | 7 |
81186 | 6 |
85790 | 6 |
85152 | 6 |
80606 | 6 |
87179 | 5 |
81175 | 5 |
89356 | 5 |
92572 | 5 |
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_name | frequency |
---|---|
Kish | 20 |
Stepehn | 19 |
JiYoung | 19 |
Morris | 18 |
Shawna | 18 |
Ramalingam | 17 |
Valeri | 17 |
Oguz | 17 |
Mohammad | 17 |
Hisao | 17 |
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_no | average_salary |
---|---|
18006 | 128619.200000000000 |
18997 | 128594.000000000000 |
20004 | 128211.500000000000 |
21587 | 127105.692307692308 |
14523 | 126436.444444444444 |
14487 | 125684.875000000000 |
11486 | 124462.111111111111 |
12149 | 123656.928571428571 |
15039 | 123642.687500000000 |
21413 | 121109.000000000000 |
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_no | first_name | count_names | hire_date |
---|---|---|---|
18235 | Elvia | 1 | 1999-06-16 |
12550 | Shawna | 1 | 1999-01-04 |
15124 | Adhemar | 1 | 1999-02-20 |
19854 | Youssef | 1 | 1999-08-28 |
16665 | Zhilian | 1 | 1999-03-04 |
19056 | Leaf | 1 | 1999-07-29 |
16244 | Parviz | 1 | 1999-02-22 |
18000 | Tzvetan | 1 | 1999-05-03 |
15368 | Seongbin | 1 | 1999-04-01 |
16308 | Yannis | 1 | 1999-05-08 |
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_no | count |
---|---|
20594 | 2 |
13626 | 2 |
15893 | 2 |
19964 | 2 |
16689 | 2 |
11609 | 2 |
SELECT SUM(salary)
FROM salaries;
sum |
---|
7162796017 |
SELECT SUM(salary)
FROM salaries
WHERE from_date > '1997-01-01';
sum |
---|
3712936185 |
SELECT MIN(salary)
FROM salaries;
min |
---|
38812 |
SELECT MAX(salary)
FROM salaries;
max |
---|
145732 |
SELECT MIN(emp_no)
FROM salaries;
min |
---|
10001 |
SELECT MAX(emp_no)
FROM salaries;
max |
---|
21797 |
SELECT AVG(salary)
FROM salaries;
avg |
---|
63986.100220648008 |
SELECT AVG(salary)
FROM salaries
WHERE from_date > '1997-01-01';
avg |
---|
67775.335140463282 |
SELECT ROUND(AVG(salary))
FROM salaries;
round |
---|
63986 |
SELECT ROUND(AVG(salary),2)
FROM salaries
WHERE from_date > '1997-01-01';
round |
---|
67775.34 |
SELECT ROUND(MAX(salary)-MIN(salary), 2) AS salary_range
FROM salaries;
salary_range |
---|
106920.00 |
SELECT ROUND((MAX(salary)-MIN(salary))/2, 2) AS salary_mid_range
FROM salaries;
salary_mid_range |
---|
53460.00 |
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.
© 2024 Mekki Mouradi. Tous droits réservés.