Aggregating Data and JOINs Demo

Overview

In this demo, we will be using a 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 1 lecture. The database has been modified slightly to be consistent with the naming conventions we have been using so far in this course. You can find the SQL Sample Database here with downloads for a variety of database systems.

If you are interested in working with a larger employee database, check out this resource here. There are MySQL, PostgreSQL, and SQLite versions of varying sizes.

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.

Aggregate Functions¶

Question 1¶

Write a query that returns the total number of employees for the company.

Include only calculated column employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_cnt
40

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

Question 2¶

Write a query that returns the minimum, maximum, and average salary for all employees in the company.

Include calculated columns salary_min, salary_max, salary_avg.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

salary_min	salary_max	salary_avg
2500.0	24000.0	8060.0

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

Grouping Data¶

Question 3¶

Write a query that returns the minimum, maximum, and average salary in each department (department_id) and order the results by average salary in descending order. Round the average salary to 2 decimal places.

Include columns department_id as well as calculated columns dept_min_salary, dept_max_salary, dept_avg_salary, employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

department_id	dept_min_salary	dept_max_salary	dept_avg_salary	employee_cnt
9	17000.0	24000.0	19333.33	3
11	8300.0	12000.0	10150.0	2
7	10000.0	10000.0	10000.0	1
8	6200.0	14000.0	9616.67	6
2	6000.0	13000.0	9500.0	2
10	6900.0	12000.0	8600.0	6
4	6500.0	6500.0	6500.0	1
5	2700.0	8200.0	5885.71	7
6	4200.0	9000.0	5760.0	5
1	4400.0	4400.0	4400.0	1

Truncated to displaylimit of 10.

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

JOINs¶

Question 4¶

Write a query that returns the employee with the Programmer title that has the lowest salary.

Include columns employee_id, first_name, last_name, salary, job_title.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_id	first_name	last_name	salary	job_title
107	Diana	Lorentz	4200.0	Programmer

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

Question 5¶

Write a query that returns the minimum, maximum, and average salary in each department (department_name) and order the results by average salary in descending order. Round the average salary to 2 decimal places.

Include columns department_name as well as calculated columns dept_min_salary, dept_max_salary, dept_avg_salary, employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

department_name	dept_min_salary	dept_max_salary	dept_avg_salary	employee_cnt
Executive	17000.0	24000.0	19333.33	3
Accounting	8300.0	12000.0	10150.0	2
Public Relations	10000.0	10000.0	10000.0	1
Sales	6200.0	14000.0	9616.67	6
Marketing	6000.0	13000.0	9500.0	2
Finance	6900.0	12000.0	8600.0	6
Human Resources	6500.0	6500.0	6500.0	1
Shipping	2700.0	8200.0	5885.71	7
IT	4200.0	9000.0	5760.0	5
Administration	4400.0	4400.0	4400.0	1

Truncated to displaylimit of 10.

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

Question 6¶

Write a query that returns the minimum, maximum, and average salary in each department with at least 5 employees and order the results by average salary in descending order. Round the average salary to 2 decimal places.

Include columns department_name as well as calculated columns dept_min_salary, dept_max_salary, dept_avg_salary, employee_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

department_name	dept_min_salary	dept_max_salary	dept_avg_salary	employee_cnt
Sales	6200.0	14000.0	9616.67	6
Finance	6900.0	12000.0	8600.0	6
Shipping	2700.0	8200.0	5885.71	7
IT	4200.0	9000.0	5760.0	5
Purchasing	2500.0	11000.0	4150.0	6

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

Question 7¶

Write a query that returns all employees that have no dependents.

Include columns employee_id, first_name, last_name, department_name.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_id	first_name	last_name	department_name
120	Matthew	Weiss	Shipping
121	Adam	Fripp	Shipping
122	Payam	Kaufling	Shipping
123	Shanta	Vollman	Shipping
126	Irene	Mikkilineni	Shipping
177	Jack	Livingston	Sales
178	Kimberely	Grant	Sales
179	Charles	Johnson	Sales
192	Sarah	Bell	Shipping
193	Britney	Everett	Shipping

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 number of dependents per employee in the Sales and Executive departments. Employees that have no dependents should be zero and order the results by the number of dependents in descending order and then by employee id in ascending order.

Include columns employee_id, first_name, last_name, department_name as well as calculated columns dependent_cnt.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_id	first_name	last_name	department_name	dependent_cnt
100	Steven	King	Executive	1
101	Neena	Kochhar	Executive	1
102	Lex	De Haan	Executive	1
145	John	Russell	Sales	1
146	Karen	Partners	Sales	1
176	Jonathon	Taylor	Sales	1
177	Jack	Livingston	Sales	0
178	Kimberely	Grant	Sales	0
179	Charles	Johnson	Sales	0

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

Question 9¶

Write a query that returns all employees that make less than 50% of their max salary for the job title and order the results by their salary in ascending order and then by employee id in ascending order.

Include columns employee_id, first_name, last_name, salary, job_title as well as alias columns job_max_salary. Use max_salary from job instead of calculating the max salary for all employees.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_id	first_name	last_name	salary	job_title	job_max_salary
119	Karen	Colmenares	2500.0	Purchasing Clerk	5500.0
118	Guy	Himuro	2600.0	Purchasing Clerk	5500.0
107	Diana	Lorentz	4200.0	Programmer	10000.0
105	David	Austin	4800.0	Programmer	10000.0
106	Valli	Pataballa	4800.0	Programmer	10000.0

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

Question 10¶

Write a query that returns all employees that have a manager that is not in the same department. Employees that do not have a manager should be included in the results. Order the results by their salary in descending order and then by employee id in ascending order.

Include columns employee_id, first_name, last_name, salary, department_name as well as alias columns manager_first_name, manager_last_name, manager_department_name.

Write Your Code Below:

In [ ]:

Expected Output:

In [ ]:

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

Out[ ]:

employee_id	first_name	last_name	salary	department_name	manager_first_name	manager_last_name	manager_department_name
100	Steven	King	24000.0	Executive	None	None	None
145	John	Russell	14000.0	Sales	Steven	King	Executive
146	Karen	Partners	13500.0	Sales	Steven	King	Executive
201	Michael	Hartstein	13000.0	Marketing	Steven	King	Executive
108	Nancy	Greenberg	12000.0	Finance	Neena	Kochhar	Executive
205	Shelley	Higgins	12000.0	Accounting	Neena	Kochhar	Executive
114	Den	Raphaely	11000.0	Purchasing	Steven	King	Executive
204	Hermann	Baer	10000.0	Public Relations	Neena	Kochhar	Executive
103	Alexander	Hunold	9000.0	IT	Lex	De Haan	Executive
176	Jonathon	Taylor	8600.0	Sales	Steven	King	Executive

Truncated to displaylimit of 10.

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

In [ ]: