Mastering SQL Joins

Mekki MOURADI

Data Analyst

Rabat, Maroc

À 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.

Liens

Compétences & Outils

Description du projet

Apprendre à combiner des données provenant de deux ou plusieurs tables

INNER JOIN
LEFT and RIGHT JOINS
JOIN feat WHERE
JOIN feat AGGREGATE FUNCTIONS
Join more than 2 tables

Objectifs de l'analyse

Utiliser les Fonctions de Jointure SQL pour combiner des données provenant de deux ou plusieurs tables pour les besoins de l’analyse.

INNER JOIN

Extract all managers employees number, department number, and department name. Order by the manager's department number

				
					SELECT  dm.emp_no,
        dm.dept_no,
        d.dept_name
FROM dept_manager_dup dm
JOIN departments_dup d
ON dm.dept_no=d.dept_no
ORDER BY dm.dept_no;

emp_no	dept_no	dept_name
110039	d001	Marketing
110022	d001	Marketing
110228	d003	Human Resources
110183	d003	Human Resources
110344	d004	Production
110386	d004	Production
110420	d004	Production
110303	d004	Production
110567	d005	Development
110511	d005	Development
110765	d006	Quality Management
110800	d006	Quality Management
110854	d006	Quality Management
110725	d006	Quality Management
111133	d007	Sales
111035	d007	Sales
111534	d008	Research
111400	d008	Research
111784	d009	Customer Service
111877	d009	Customer Service
111939	d009	Customer Service
111692	d009	Customer Service

Extract a list containing information about all managers employees number, first and last name, department number and hire date

				
					SELECT  dm.emp_no,
		e.first_name,
		e.last_name,
		dm.dept_no,
		e.hire_date
FROM employees e
JOIN dept_manager dm
ON e.emp_no=dm.emp_no;

emp_no	first_name	last_name	dept_no	hire_date
110022	Margareta	Markovitch	d001	1985-01-01
110039	Vishwani	Minakawa	d001	1986-04-12
110085	Ebru	Alpin	d002	1985-01-01
110114	Isamu	Legleitner	d002	1985-01-14
110183	Shirish	Ossenbruggen	d003	1985-01-01
110228	Karsten	Sigstam	d003	1985-08-04
110303	Krassimir	Wegerle	d004	1985-01-01
110344	Rosine	Cools	d004	1985-11-22
110386	Shem	Kieras	d004	1988-10-14
110420	Oscar	Ghazalie	d004	1992-02-05
110511	DeForest	Hagimont	d005	1985-01-01
110567	Leon	DasSarma	d005	1986-10-21
110725	Peternela	Onuegbe	d006	1985-01-01
110765	Rutger	Hofmeyr	d006	1989-01-07
110800	Sanjoy	Quadeer	d006	1986-08-12
110854	Dung	Pesch	d006	1989-06-09
111035	Przemyslawa	Kaelbling	d007	1985-01-01
111133	Hauke	Zhang	d007	1986-12-30
111400	Arie	Staelin	d008	1985-01-01
111534	Hilary	Kambil	d008	1988-01-31
111692	Tonny	Butterworth	d009	1985-01-01
111784	Marjo	Giarratana	d009	1988-02-12
111877	Xiaobin	Spinelli	d009	1991-08-17
111939	Yuchang	Weedman	d009	1989-07-10

LEFT and RIGHT JOINS

Extract a subset of all managers employee number, department number, and deparment name. Order by the managers' department number using LEFT JOIN

				
					SELECT  dm.dept_no,
		dm.emp_no,
		dd.dept_name
FROM departments_dup dd
LEFT JOIN dept_manager_dup dm
ON dm.dept_no=dd.dept_no
ORDER BY dm.dept_no

dept_no	emp_no	dept_name
d001	110022	Marketing
d001	110039	Marketing
d003	110183	Human Resources
d003	110228	Human Resources
d004	110344	Production
d004	110386	Production
d004	110420	Production
d004	110303	Production
d005	110511	Development
d005	110567	Development
d006	110854	Quality Management
d006	110725	Quality Management
d006	110765	Quality Management
d006	110800	Quality Management
d007	111133	Sales
d007	111035	Sales
d008	111534	Research
d008	111400	Research
d009	111877	Customer Service
d009	111692	Customer Service
d009	111784	Customer Service
d009	111939	Customer Service
NULL	NULL	Public Relations
NULL	NULL	NULL
NULL	NULL	NULL

Extract a subset of all managers employee number, department number, and deparment name. Order by the managers' department number using RIGHT JOIN

				
					SELECT  dm.dept_no,
		dm.emp_no,
		dd.dept_name
FROM dept_manager_dup dm
RIGHT JOIN departments_dup dd
ON dm.dept_no=dd.dept_no
ORDER BY dm.dept_no

dept_no	emp_no	dept_name
d001	110022	Marketing
d001	110039	Marketing
d003	110183	Human Resources
d003	110228	Human Resources
d004	110344	Production
d004	110386	Production
d004	110420	Production
d004	110303	Production
d005	110511	Development
d005	110567	Development
d006	110854	Quality Management
d006	110725	Quality Management
d006	110765	Quality Management
d006	110800	Quality Management
d007	111133	Sales
d007	111035	Sales
d008	111534	Research
d008	111400	Research
d009	111877	Customer Service
d009	111692	Customer Service
d009	111784	Customer Service
d009	111939	Customer Service
NULL	NULL	Public Relations
NULL	NULL	NULL
NULL	NULL	NULL

JOIN feat WHERE

Extract the employee number, first name, last name, and salary of all employees, who earn more than 145k per year

				
					SELECT  e.emp_no,
		e.first_name,
		e.last_name,
		s.salary
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
WHERE salary>145000;

emp_no	first_name	last_name	salary
11486	Itzchak	Ramaiah	145732
18997	Basim	Tischendorf	145215

Select the first name, last name, the hire date and the salary of all employees whose first name is 'Mario' and last name is 'Straney'

				
					SELECT  e.first_name,
		e.last_name,
		e.hire_date,
		s.salary
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
WHERE e.first_name='Mario' AND e.last_name='Straney';

first_name	last_name	hire_date	salary
Mario	Straney	1997-07-09	64363
Mario	Straney	1997-07-09	64523
Mario	Straney	1997-07-09	64741
Mario	Straney	1997-07-09	65092
Mario	Straney	1997-07-09	68422

Return a subset of all the employees whose last name is 'Markovitch'. See if the output contains a manager with that name

				
					SELECT  e.emp_no,
		e.first_name,
		e.last_name,
		CASE
			WHEN d.emp_no IS NOT NULL THEN 'Yes' ELSE 'No' 
		END AS is_department_manager
FROM employees e
LEFT JOIN dept_manager d
ON e.emp_no = d.emp_no
WHERE e.last_name = 'Markovitch';

emp_no	first_name	last_name	is_department_manager
110022	Margareta	Markovitch	Yes
21545	Boguslaw	Markovitch	No
11817	Niranjan	Markovitch	No
15392	Pradeep	Markovitch	No
10898	Munenori	Markovitch	No
12419	Srinidhi	Markovitch	No
12977	Byong	Markovitch	No

JOINS feat AGGREGATE FUNCTIONS

What is the average salary for the different genders

				
					SELECT  e.gender,
		ROUND(AVG(s.salary),2) AS average_salary
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
GROUP BY gender;

gender	average_salary
M	63865.65
F	64163.84

Retrieve the distribution of managers by gender

				
					SELECT  e.gender,
		COUNT(e.emp_no)
FROM employees e
JOIN dept_manager d
ON e.emp_no=d.emp_no
GROUP BY e.gender;

gender	count
M	11
F	13

Join more than 2 tables in SQL

Extract a list of all managers first and last name, department number, hire date, to_date, and department name

				
					SELECT  e.first_name,
		e.last_name,
		d.dept_no,
		e.hire_date,
		dm.to_date,
		d.dept_name
FROM employees e
JOIN dept_manager dm
ON e.emp_no=dm.emp_no
JOIN departments d
ON dm.dept_no=d.dept_no;

first_name	last_name	dept_no	hire_date	to_date	dept_name
Margareta	Markovitch	d001	1985-01-01	1991-10-01	Marketing
Vishwani	Minakawa	d001	1986-04-12	9999-01-01	Marketing
Ebru	Alpin	d002	1985-01-01	1989-12-17	Finance
Isamu	Legleitner	d002	1985-01-14	9999-01-01	Finance
Shirish	Ossenbruggen	d003	1985-01-01	1992-03-21	Human Resources
Karsten	Sigstam	d003	1985-08-04	9999-01-01	Human Resources
Krassimir	Wegerle	d004	1985-01-01	1988-09-09	Production
Rosine	Cools	d004	1985-11-22	1992-08-02	Production
Shem	Kieras	d004	1988-10-14	1996-08-30	Production
Oscar	Ghazalie	d004	1992-02-05	9999-01-01	Production
DeForest	Hagimont	d005	1985-01-01	1992-04-25	Development
Leon	DasSarma	d005	1986-10-21	9999-01-01	Development
Peternela	Onuegbe	d006	1985-01-01	1989-05-06	Quality Management
Rutger	Hofmeyr	d006	1989-01-07	1991-09-12	Quality Management
Sanjoy	Quadeer	d006	1986-08-12	1994-06-28	Quality Management
Dung	Pesch	d006	1989-06-09	9999-01-01	Quality Management
Przemyslawa	Kaelbling	d007	1985-01-01	1991-03-07	Sales
Hauke	Zhang	d007	1986-12-30	9999-01-01	Sales
Arie	Staelin	d008	1985-01-01	1991-04-08	Research
Hilary	Kambil	d008	1988-01-31	9999-01-01	Research
Tonny	Butterworth	d009	1985-01-01	1988-10-17	Customer Service
Marjo	Giarratana	d009	1988-02-12	1992-09-08	Customer Service
Xiaobin	Spinelli	d009	1991-08-17	1996-01-03	Customer Service
Yuchang	Weedman	d009	1989-07-10	9999-01-01	Customer Service

Retrieve the average salary for the different departments

				
					SELECT  d.dept_name,
		ROUND(AVG(s.salary)) AS average_salary
FROM departments d
JOIN dept_emp de
ON d.dept_no=de.dept_no
JOIN salaries s
ON s.emp_no=de.emp_no
GROUP BY d.dept_name;

dept_name	average_salary
Finance	70621
Customer Service	58754
Quality Management	58119
Sales	80865
Marketing	72452
Development	59620
Production	59806
Research	60313
Human Resources	55941

Retrieve the average salary for the different departments where the average salary is > 60k

				
					SELECT  d.dept_name,
		ROUND(AVG(s.salary)) AS average_salary
FROM departments d
JOIN dept_emp de
ON d.dept_no=de.dept_no
JOIN salaries s
ON s.emp_no=de.emp_no
GROUP BY d.dept_name
HAVING ROUND(AVG(s.salary)) > 60000;

dept_name	average_salary
Finance	70621
Sales	80865
Marketing	72452
Research	60313

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

Mastering SQL Joins

Liens

Compétences & Outils

Description du projet

Objectifs de l'analyse

Home

Excel Projects

Tableau Projects

SQL Projects

Python Projects