SQL CASE Statements

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 à manipuler les données via les CASE Statements :

SQL CASE Statement
Multiple conditions to a CASE Statement
CASE Statements Aggregate Functions
CASE Statements and with Aggregate Functions and Joins
Transposing Data using CASE clause

Objectifs de l'analyse

Utiliser les instructions CASE pour effectuer des évaluations conditionnelles et manipuler les données pour les besoins de l’analyse.

CASE STATEMENTS

Change M to Male and F to Female in the employees table

				
					SELECT  emp_no,
		CASE gender
			WHEN 'M' THEN 'MALE'
			WHEN 'F' THEN 'FEMALE'
			ELSE 'No Gender'
		END AS gender
FROM employees
LIMIT 10;

emp_no	gender
10001	MALE
10002	FEMALE
10003	MALE
10004	MALE
10005	MALE
10006	FEMALE
10007	FEMALE
10008	MALE
10009	FEMALE
10010	FEMALE

Multiple Conditions to a CASE Statement

Create a column called age_category that returs young for ages less than 30, aged for ages greater than 60, and middle aged otherwise

				
					SELECT  customerid,
		age,
		CASE
			WHEN age < 30 THEN 'Young'
			WHEN age > 60 THEN 'Aged'
			ELSE 'Middle Aged'
		END AS age_category
FROM customers
LIMIT 10;

customerid	age	age_category
CG-12520	67	Aged
DV-13045	31	Middle Aged
SO-20335	65	Aged
BH-11710	20	Young
AA-10480	50	Middle Aged
IM-15070	66	Aged
HP-14815	20	Young
PK-19075	46	Middle Aged
AG-10270	18	Young
ZD-21925	66	Aged

Retrieve a list of the number of employees that were employed before 1990, between 1990 and 1995, and after 1995

				
					WITH new_hire AS (
    SELECT 
        CASE
            WHEN EXTRACT(YEAR FROM hire_date) < '1990' THEN 'Before 1990'
            WHEN EXTRACT(YEAR FROM hire_date) BETWEEN '1990' AND '1995' THEN 'Between 1990 and 1995'
            ELSE 'After 1995'
        END AS hire_category,
        emp_no
    FROM employees
)
SELECT hire_category, COUNT(emp_no) AS number_of_employees
FROM new_hire
GROUP BY hire_category
ORDER BY 
    CASE
        WHEN hire_category = 'Before 1990' THEN 1
        WHEN hire_category = 'Between 1990 and 1995' THEN 2
        ELSE 3
    END;

hire_category	number_of_employees
Before 1990	6428
Between 1990 and 1995	4533
After 1995	836

CASE Statement and Aggregate Functions

Retrieve the average salary of all employees

				
					SELECT emp_no, ROUND(AVG(salary)) AS average_salary
FROM salaries
GROUP BY emp_no
ORDER BY AVG(salary) DESC
LIMIT 10;

emp_no	average_salary
18006	128619
18997	128594
20004	128212
21587	127106
14523	126436
14487	125685
11486	124462
12149	123657
15039	123643
21413	121109

Retrieve a list of the average salary of employees, if the average is more than 64k and less than 80k then return 'Paid Well', if the average is less than 64k then return 'Underpaid' else return 'Manager'

				
					SELECT  emp_no,
        ROUND(AVG(salary)) AS average_salary,
		CASE
			WHEN AVG(salary) BETWEEN 64000 AND 80000 THEN 'Paid Well'
			WHEN AVG(salary)<64000 THEN 'Underpaid'
			ELSE 'Manager'
		END AS salary_category
FROM salaries
GROUP BY emp_no
LIMIT 10;

emp_no	average_salary	salary_category
10001	75389	Paid Well
10002	68855	Paid Well
10003	43030	Underpaid
10004	56512	Underpaid
10005	87276	Manager
10006	50515	Underpaid
10007	70827	Paid Well
10008	49308	Underpaid
10009	78285	Paid Well
10010	76723	Paid Well

Count the number of employees in each salary category

				
					WITH emp_per_cat AS (
SELECT	CASE
			WHEN AVG(salary) BETWEEN 64000 AND 80000 THEN 'Paid Well'
			WHEN AVG(salary)<64000 THEN 'Underpaid'
			ELSE 'Manager'
			END AS salary_category,
		emp_no
FROM salaries
GROUP BY emp_no)
SELECT salary_category, COUNT(emp_no) AS count_emp
FROM emp_per_cat
GROUP BY salary_category

salary_category	count_emp
Manager	1617
Underpaid	7438
Paid Well	2742

CASE Statement together with SQL Joins and Aggregate Functions

Retrieve a list of all salaries earned by an employee

				
					SELECT e.emp_no, s.salary
FROM employees e
JOIN salaries s
ON e.emp_no = s.emp_no
GROUP BY e.emp_no, s.salary
LIMIT 10;

emp_no	salary
13245	65477
19139	53089
13951	90603
11474	70084
17891	64534
17044	54244
13439	50723
11130	49148
16386	52187
20398	71624

Retrieve a list of employee number, first and last name, add a column named salary difference which is the difference between the employees max and min salary, a column called salary_increase which returns salary was raised by more than 30k if the difference is >30k, salary was raised by more than 20k if between 20k and 30k, and salary was raised by less than 20k if the difference is <20k

				
					SELECT  e.emp_no,
        e.first_name,
        e.last_name, MAX(s.salary)-MIN(s.salary) AS salary_difference,
	    CASE
	    	WHEN MAX(s.salary)-MIN(s.salary) >=30000 THEN 'Salary was raised by more than 30k$'
	    	WHEN MAX(s.salary)-MIN(s.salary) >= 20000 AND MAX(s.salary)-MIN(s.salary) <30000 THEN 'Salary was raised by more than 20k$'
	    	ELSE 'Salary was raised by less than 20k$'
		END AS salary_increase
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
GROUP BY e.emp_no
LIMIT 10;

emp_no	first_name	last_name	salary_difference	salary_increase
10001	Georgi	Facello	28841	Salary was raised by more than 20k$
10002	Bezalel	Simmel	6699	Salary was raised by less than 20k$
10003	Parto	Bamford	3693	Salary was raised by less than 20k$
10004	Chirstian	Koblick	34003	Salary was raised by more than 30k$
10005	Kyoichi	Maliniak	16464	Salary was raised by less than 20k$
10006	Anneke	Preusig	20098	Salary was raised by more than 20k$
10007	Tzvetan	Zielinski	31346	Salary was raised by more than 30k$
10008	Saniya	Kalloufi	5997	Salary was raised by less than 20k$
10009	Sumant	Peac	33514	Salary was raised by more than 30k$
10010	Duangkaew	Piveteau	7836	Salary was raised by less than 20k$

Extract the employee number, first and last namle of the first 100 employees, and add a fourth column called current_employee saying is still employed if the employee is still working in the company, or not an employee anymore if the employee is not working at the company anymore

				
					SELECT  e.emp_no,
        e.first_name,
        e.last_name,
    	CASE
	    	WHEN de.to_date IS NULL THEN 'Still Employed'
	    	ELSE 'Not an employee anymore'
		END AS current_employee
FROM employees e
JOIN dept_emp de
ON e.emp_no=de.emp_no
ORDER BY e.emp_no
LIMIT 100;

emp_no	first_name	last_name	current_employee
10001	Georgi	Facello	Still Employed
10002	Bezalel	Simmel	Still Employed
10003	Parto	Bamford	Still Employed
10004	Chirstian	Koblick	Still Employed
10005	Kyoichi	Maliniak	Still Employed
10006	Anneke	Preusig	Still Employed
10007	Tzvetan	Zielinski	Still Employed
10008	Saniya	Kalloufi	Not an employee anymore
10009	Sumant	Peac	Still Employed
10010	Duangkaew	Piveteau	Not an employee anymore
10010	Duangkaew	Piveteau	Still Employed
10011	Mary	Sluis	Not an employee anymore
10012	Patricio	Bridgland	Still Employed
10013	Eberhardt	Terkki	Still Employed
10014	Berni	Genin	Still Employed
10015	Guoxiang	Nooteboom	Not an employee anymore
10016	Kazuhito	Cappelletti	Still Employed
10017	Cristinel	Bouloucos	Still Employed
10018	Kazuhide	Peha	Still Employed
10018	Kazuhide	Peha	Not an employee anymore
10019	Lillian	Haddadi	Still Employed
10020	Mayuko	Warwick	Still Employed
10021	Ramzi	Erde	Not an employee anymore
10022	Shahaf	Famili	Still Employed
10023	Bojan	Montemayor	Still Employed
10024	Suzette	Pettey	Still Employed
10025	Prasadram	Heyers	Not an employee anymore
10026	Yongqiao	Berztiss	Still Employed
10027	Divier	Reistad	Still Employed
10028	Domenick	Tempesti	Not an employee anymore
10029	Otmar	Herbst	Not an employee anymore
10029	Otmar	Herbst	Still Employed
10030	Elvis	Demeyer	Still Employed
10031	Karsten	Joslin	Still Employed
10032	Jeong	Reistad	Still Employed
10033	Arif	Merlo	Not an employee anymore
10034	Bader	Swan	Not an employee anymore
10035	Alain	Chappelet	Still Employed
10036	Adamantios	Portugali	Still Employed
10037	Pradeep	Makrucki	Still Employed
10038	Huan	Lortz	Still Employed
10039	Alejandro	Brender	Still Employed
10040	Weiyi	Meriste	Not an employee anymore
10040	Weiyi	Meriste	Still Employed
10041	Uri	Lenart	Still Employed
10042	Magy	Stamatiou	Not an employee anymore
10043	Yishay	Tzvieli	Still Employed
10044	Mingsen	Casley	Still Employed
10045	Moss	Shanbhogue	Still Employed
10046	Lucien	Rosenbaum	Still Employed
10047	Zvonko	Nyanchama	Still Employed
10048	Florian	Syrotiuk	Not an employee anymore
10049	Basil	Tramer	Still Employed
10050	Yinghua	Dredge	Not an employee anymore
10050	Yinghua	Dredge	Still Employed
10051	Hidefumi	Caine	Still Employed
10052	Heping	Nitsch	Still Employed
10053	Sanjiv	Zschoche	Still Employed
10054	Mayumi	Schueller	Still Employed
10055	Georgy	Dredge	Not an employee anymore
10056	Brendon	Bernini	Still Employed
10057	Ebbe	Callaway	Still Employed
10058	Berhard	McFarlin	Still Employed
10059	Alejandro	McAlpine	Still Employed
10060	Breannda	Billingsley	Not an employee anymore
10060	Breannda	Billingsley	Still Employed
10061	Tse	Herber	Still Employed
10062	Anoosh	Peyn	Still Employed
10063	Gino	Leonhardt	Still Employed
10064	Udi	Jansch	Not an employee anymore
10065	Satosi	Awdeh	Still Employed
10066	Kwee	Schusler	Still Employed
10067	Claudi	Stavenow	Still Employed
10068	Charlene	Brattka	Still Employed
10069	Margareta	Bierman	Still Employed
10070	Reuven	Garigliano	Not an employee anymore
10070	Reuven	Garigliano	Still Employed
10071	Hisao	Lipner	Still Employed
10072	Hironoby	Sidou	Still Employed
10073	Shir	McClurg	Not an employee anymore
10074	Mokhtar	Bernatsky	Still Employed
10075	Gao	Dolinsky	Not an employee anymore
10076	Erez	Ritzmann	Still Employed
10077	Mona	Azuma	Still Employed
10078	Danel	Mondadori	Still Employed
10079	Kshitij	Gils	Still Employed
10080	Premal	Baek	Not an employee anymore
10080	Premal	Baek	Still Employed
10081	Zhongwei	Rosen	Still Employed
10082	Parviz	Lortz	Not an employee anymore
10083	Vishv	Zockler	Still Employed
10084	Tuval	Kalloufi	Still Employed
10085	Kenroku	Malabarba	Still Employed
10086	Somnath	Foote	Still Employed
10087	Xinglin	Eugenio	Not an employee anymore
10088	Jungsoon	Syrzycki	Not an employee anymore
10088	Jungsoon	Syrzycki	Still Employed
10089	Sudharsan	Flasterstein	Still Employed
10090	Kendra	Hofting	Not an employee anymore
10091	Amabile	Gomatam	Still Employed

Transposing Data using the CASE Clause

Retrieve the count of the different profit category from the sales table

				
					WITH pr_cat AS
(SELECT  order_line,
        profit,
		CASE
			WHEN profit<=0 THEN 'No profit'
			WHEN profit>0 AND profit<=500 THEN 'Low profit'
			WHEN profit>500 AND profit<=1500 THEN 'Good profit'
			ELSE 'High profit'
		END AS profit_category
FROM sales)
SELECT profit_category, COUNT(order_line)
FROM pr_cat
GROUP BY profit_category

profit_category	count
Good profit	97
No profit	1936
Low profit	7941
High profit	20

Transpose the output above

				
					SELECT  SUM(CASE WHEN profit<=0 THEN 1 ELSE 0 END) AS no_profit,
		SUM(CASE WHEN profit>0 AND profit<=500 THEN 1 ELSE 0 END) AS low_profit,
		SUM(CASE WHEN profit>500 AND profit<=1500 THEN 1 ELSE 0 END) AS good_profit,
		SUM(CASE WHEN profit>1500 THEN 1 ELSE 0 END) AS high_profit
FROM sales

no_profit	low_profit	good_profit	high_profit
1936	7941	97	20

Retrieve the number of employees in the first 4 departments in the dept_emp table and the departments names

				
					SELECT  de.dept_no,
        COUNT(de.emp_no),
        d.dept_name
FROM dept_emp de
JOIN departments d
ON de.dept_no=d.dept_no
WHERE de.dept_no IN ('d001','d002','d003','d004')
GROUP BY de.dept_no, d.dept_name

dept_no	count	dept_name
d003	699	Human Resources
d001	753	Marketing
d004	2965	Production
d002	712	Finance

Transpose the output above

				
					SELECT SUM(CASE WHEN dept_no='d001' THEN 1 ELSE 0 END) AS Marketing,
		SUM(CASE WHEN dept_no='d002' THEN 1 ELSE 0 END) AS Finance,
		SUM(CASE WHEN dept_no='d003' THEN 1 ELSE 0 END) AS Human_Resources,
		SUM(CASE WHEN dept_no='d004' THEN 1 ELSE 0 END) AS Production
FROM dept_emp

marketing	finance	human_resources	production
753	712	699	2965

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

SQL CASE Statements

Liens

Compétences & Outils

Description du projet

Objectifs de l'analyse

Home

Excel Projects

Tableau Projects

SQL Projects

Python Projects