CASE and Custom JOINs Demo

Overview

In this demo, we will continue using the small sample employee database courtesy of https://www.sqltutorial.org/ to get some hands-on practice with the concepts we learned in the Intermediate_SQL Part 2 lecture.

Download the resource files for this demo here.

How is the data organized¶

Below is the ERD for employee_small databases.

employee_small_ERD

Load Database¶

NOTE: The employee_small.db file MUST be saved in the same folder location as this notebook on your computer.

In [1]:

Copied!

import sqlite3

%load_ext sql

%sql sqlite:///employee_small.db
import sqlite3

%load_ext sql

%sql sqlite:///employee_small.db

Connecting to 'sqlite:///employee_small.db'

IMPORTANT:
Every query that you write in a Jupyter Notebook cell must have the %%sql command directly above the SELECT statement.

String and Date Functions¶

Question 1¶

Write a query that returns all employees with a 650 area code. You may use any method to filter for area_code in the WHERE clause but you must use a string function to return the area_code in the SELECT clause. Order by employee_id in ascending order.

Include columns employee_id, first_name, last_name, phone_number and calculated column area_code.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

employee_id	first_name	last_name	phone_number	area_code
120	Matthew	Weiss	650.123.1234	650
121	Adam	Fripp	650.123.2234	650
122	Payam	Kaufling	650.123.3234	650
123	Shanta	Vollman	650.123.4234	650
126	Irene	Mikkilineni	650.124.1224	650
192	Sarah	Bell	650.501.1876	650
193	Britney	Everett	650.501.2876	650

Copy and paste the code into the submission.yaml file for Question 1.

Question 2¶

Write a query that returns all employees that were hired in 1998. You may use any method to filter for hire_date in the WHERE clause but you must use a date function to return the hire_year in the SELECT clause. Order by employee_id in ascending order.

Include columns employee_id, first_name, last_name, hire_date and calculated column hire_year.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

employee_id	first_name	last_name	hire_date	hire_year
106	Valli	Pataballa	1998-02-05	1998
112	Jose Manuel	Urman	1998-03-07	1998
118	Guy	Himuro	1998-11-15	1998
126	Irene	Mikkilineni	1998-09-28	1998
176	Jonathon	Taylor	1998-03-24	1998
177	Jack	Livingston	1998-04-23	1998

Copy and paste the code into the submission.yaml file for Question 2.

CASE Statements¶

Question 3¶

Write a query that identifies the number of employees by department that make less than 25% higher than the min_salary for their job. Create a custom calculated field min_salary_pctg_diff_group that groups employees into < 25% and >= 25% categories. Order by department in ascending order and then by min_salary_pctg_diff_group in ascending order.

Include column department_name as well as calculated columns min_salary_pctg_diff_group, employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

department_name	min_salary_pctg_diff_group	employee_cnt
Accounting	>= 25%	2
Administration	>= 25%	1
Executive	< 25%	3
Finance	>= 25%	6
Human Resources	>= 25%	1
IT	< 25%	3
IT	>= 25%	2
Marketing	>= 25%	2
Public Relations	>= 25%	1
Purchasing	< 25%	5

Truncated to displaylimit of 10.

Copy and paste the code into the submission.yaml file for Question 3.

Question 4¶

Building on question 3. Write a query that identifies the number of employees by department that now groups employees into < 25%, > 100%, and 25% - 100% categories. Order by department in ascending order and then by min_salary_pctg_diff_group in ascending order.

Include columns department_name as well as calculated columns min_salary_pctg_diff_group, employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

department_name	min_salary_pctg_diff_group	employee_cnt
Accounting	25% - 100%	2
Administration	25% - 100%	1
Executive	< 25%	3
Finance	25% - 100%	5
Finance	> 100%	1
Human Resources	25% - 100%	1
IT	25% - 100%	1
IT	< 25%	3
IT	> 100%	1
Marketing	25% - 100%	2

Truncated to displaylimit of 10.

Copy and paste the code into the submission.yaml file for Question 4.

Question 5¶

Write a query that returns the % of employees that make less than 25% in each department with at least 4 employees. Order by the less than 25 percentage calculated field in descending order, round the percentage to 2 decimal places and express as a percentage but without the % sign.

Include columns department_name as well as calculated columns less_than_25_pctg.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

department_name	less_than_25_pctg
Purchasing	83.33
IT	60.0
Sales	33.33
Shipping	14.29
Finance	0.0

Copy and paste the code into the submission.yaml file for Question 5.

Question 6¶

Write a query that returns all employees with a salary that is greater than the salary for the IT department manager. You can hard code the managers employee_id in a JOIN or WHERE clause condition. Order by salary in descending order and then hire date in ascending order.

Include columns employee_id, calculated field employee_full_name in the format first name and last name with a space in between names, as well as columns hire_date, job_title, salary.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

employee_id	employee_full_name	hire_date	job_title	salary
100	Steven King	1987-06-17	President	24000.0
101	Neena Kochhar	1989-09-21	Administration Vice President	17000.0
102	Lex De Haan	1993-01-13	Administration Vice President	17000.0
145	John Russell	1996-10-01	Sales Manager	14000.0
146	Karen Partners	1997-01-05	Sales Manager	13500.0
201	Michael Hartstein	1996-02-17	Marketing Manager	13000.0
205	Shelley Higgins	1994-06-07	Accounting Manager	12000.0
108	Nancy Greenberg	1994-08-17	Finance Manager	12000.0
114	Den Raphaely	1994-12-07	Purchasing Manager	11000.0
204	Hermann Baer	1994-06-07	Public Relations Representative	10000.0

Truncated to displaylimit of 10.

Copy and paste the code into the submission.yaml file for Question 6.

Question 7¶

Write a query that returns the number of employees by region and country but rename the region name North America for USA, Canada, and Mexico countries, all other Americas as South America then use region_name for all others. Include all countries even if they have no employees. Order by the number employees in descending order and then by country name in ascending order.

Include updated calculated column region_name, column country_name, and calculated column employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

region	country_name	employee_cnt
North America	United States of America	30
Europe	United Kingdom	7
North America	Canada	2
Europe	Germany	1
South America	Argentina	0
Asia	Australia	0
Europe	Belgium	0
South America	Brazil	0
Asia	China	0
Europe	Denmark	0

Truncated to displaylimit of 10.

Copy and paste the code into the submission.yaml file for Question 7.

Question 8¶

Write a query that returns the % of employees that work in North America. Round the percentage to 2 decimal places and express as a percentage but without the % sign. The count calculated fields should be whole numbers.

Include calculated columns north_america_employee_cnt, total_employee_cnt, north_america_employee_pctg.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

north_america_employee_cnt	total_employee_cnt	north_america_employee_pctg
32	40	80.0

Copy and paste the code into the submission.yaml file for Question 8.

Question 9¶

Write a query that returns all employees with a salary that is greater than the max salary for Programmers. Order by salary in descending order and then hire date in ascending order.

Include columns employee_id, calculated field employee_full_name in the format first name and last name with a space in between names, as well as columns hire_date, job_title, salary.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

employee_id	employee_full_name	hire_date	job_title	salary
100	Steven King	1987-06-17	President	24000.0
101	Neena Kochhar	1989-09-21	Administration Vice President	17000.0
102	Lex De Haan	1993-01-13	Administration Vice President	17000.0
145	John Russell	1996-10-01	Sales Manager	14000.0
146	Karen Partners	1997-01-05	Sales Manager	13500.0
201	Michael Hartstein	1996-02-17	Marketing Manager	13000.0
205	Shelley Higgins	1994-06-07	Accounting Manager	12000.0
108	Nancy Greenberg	1994-08-17	Finance Manager	12000.0
114	Den Raphaely	1994-12-07	Purchasing Manager	11000.0

Copy and paste the code into the submission.yaml file for Question 9.

Question 10¶

Write a query that returns all employees with a salary that is less than 50% of their manager that shares the same job title.

Include columns employee_id, calculated field employee_full_name in the format first name and last name with a space in between names, columns job_title, salary, and calculated column manager_full_name in the same format as the employee.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

Running query in 'sqlite:///employee_small.db'

Out[ ]:

employee_id	employee_full_name	job_title	salary	manager_full_name
107	Diana Lorentz	Programmer	4200.0	Alexander Hunold

Copy and paste the code into the submission.yaml file for Question 10.

In [ ]: