Data analysis - Window Functions

Peer instructions

If I want to fetch the sum of all salary across departments combined; What is the correct query?

A)

SELECT department_name, SUM(salary)
FROM employees
INNER JOIN departments
ON employees.department_number = departments.department_number
GROUP BY department_name 

B)

SELECT department_name, SUM(salary)
FROM employees, departments
ON employees.department_number = departments.department_number
GROUP BY department_name 

C)

SELECT department_name, SUM(salary)
FROM employees
INNER JOIN departments
ON employees.department_number = departments.department_number
GROUP BY salary

D)

None

If I want to fetch the average salary for each department ordered by average salary with the highest at the top; what is the correct query?

A)

SELECT AVG(salary)
FROM employees
INNER JOIN departments
GROUP BY department_name
ORDER BY DESC

B)

SELECT *
FROM employees
INNER JOIN departments
ON employees.department_number = departments.department_number
GROUP BY department_name 
ORDER BY AVG(salary) DESC

C)

SELECT department_name, AVG(salary)
FROM employees
INNER JOIN departments
ON employees.department_number = departments.department_number
GROUP BY department_name DESC
ORDER BY AVG(salary)

D)

None

If I want to fetch the highest salary in each department along with the employee name; what is the correct query?

A)

SELECT department_name, MAX(salary), employee_name
FROM employees
INNER JOIN departments
ON employees.department_number = departments.department_number
GROUP BY department_name;

B)

SELECT d.department_name, e.employee_name, e.salary
FROM employees e
INNER JOIN departments d
ON e.department_number = d.department_number
WHERE e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_number = e.department_number
);

C)

SELECT department_name, employee_name, MAX(salary)
FROM employees
GROUP BY department_name, employee_name;

D) None

Exercise 1 Project Sakila - Groups

Data: https://downloads.mysql.com/docs/sakila-db.zip

Run schema file first data file second

Explore the dataset.

• What is the average running time of all movies?

  • 115.2720

• What is the average running time of movies by category?

  • From most to least: Sports: 128,2027 ... Sci-Fi 108,1967

• What actor has starred in most movies?

  • Dina Degeneres in 42 movies

• What store carries the most inventory?

  • Store id 2 with 2311 inventory elements

• Which customer rented the most movies?

  • Eleanor Hunt with 46 rentals

• What film has generated the most revenue?

  • Telegraph Voyage with 231,73 dollars of revenue

• Which film category has the highest total revenue?

  • Sports with 5314,21

• What is the name of the best selling person in staff?

Exercises 2 (Pairs) - Groups

A) Describe and write down: What are the difference between Aggregate functions and window functions?

B) Describe and write down: What does the "Group By"- clause obtain?

• How does it affect the resulting response

C) What happens if you do not use a "On"- clause in a join statement?

D) Describe the usage of a sub query and provide and example

Exercises 3 - Groups

In the pokemon dataset

A) Write a query to calculate the running total of HP for each Pokemon grouped by its primary type. Order the results by the Pokemon's pokedex_number within each primary_type.

B) Write a query to rank Pokemon by their speed stat within each primary_type. Display the primary_type, pokedex_number, name, speed, and the rank.

C) Write a query to find the top 3 Pokemon by special_attack within each primary_type. Display the primary_type, pokedex_number, name, special_attack, and the rank.

D) (Advanced optional) Write a query to identify Pokemon whose speed stat is above the average speed of their primary_type. Display the primary_type, pokedex_number, name, speed, and the average speed of the primary_type.

In the Employees & Departments dataset

A) Write a query to calculate the cumulative salary of employees within each department. Display the department_number, employee_name, salary, and the cumulative sum of salary ordered by hiredate.

B) Write a query to identify employees whose salary is above the average salary of their department. Display the department_number, employee_name, salary, and the average salary of the department.

C) Write a query to calculate the year of employment (hiredate year) for each employee and rank them within their department based on their hire date. Display the department_number, employee_name, hiredate, employment_year, and the rank.

D) Write a query to find the top 3 highest earners in each department. Display the department_number, employee_name, salary, and their rank within the department.

Advanced

E) Write a query to calculate the difference in salary between an employee and the employee hired immediately before them within each department, ordered by hiredate.

• Hint: Use the LAG() function: https://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/