MySQL Employees Sample Database exercise problems + solutions.

MySQL practice problems using the Employees Sample Database along with my solutions. See here for database installation details.

Problem 1

Find the number of Male (M) and Female (F) employees in the database and order the counts in descending order.

SELECT gender, COUNT(*) AS total_count
FROM employees 
GROUP BY gender
ORDER BY total_count DESC;

Problem 2

Find the average salary by employee title, round to 2 decimal places and order by descending order.

SELECT title, ROUND(AVG(salary), 2) as avg_salary
FROM titles t JOIN salaries s ON s.emp_no = t.emp_no
GROUP BY title
ORDER BY avg_salary DESC;

Problem 3

Find all the employees that have worked in at least 2 departments. Show their first name, last_name and the number of departments they work in. Display all results in ascending order.

SELECT CONCAT(e.first_name, ' ' , e.last_name) AS name, COUNT(*) AS number_of_departments
FROM employees e JOIN dept_emp d ON e.emp_no = d.emp_no
GROUP BY d.emp_no 
HAVING COUNT(*) > 1
ORDER BY name ASC;

Problem 4

Display the first name, last name, and salary of the highest payed employee.

SELECT CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name, salaries.salary 
FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE salaries.salary = (SELECT MAX(salaries.salary) FROM salaries);

Problem 5

Display the first name, last name, and salary of the second highest payed employee.

SELECT CONCAT(employees.first_name, ' ', employees.last_name) AS employee_name, salaries.salary 
FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE salaries.salary < (SELECT MAX(salaries.salary) FROM salaries)
ORDER BY salaries.salary DESC
LIMIT 1;

Problem 6

Display the month and total hires for the month with the most hires.

SELECT DATE_FORMAT(hire_date, '%M') AS month, COUNT(*) AS total_hires
FROM employees
GROUP BY month
ORDER BY total_hires DESC
LIMIT 1;

Problem 7

Display each department and the age of the youngest employee at hire date.

SELECT dept.dept_name, 
       MIN(TIMESTAMPDIFF(YEAR, e.birth_date, e.hire_date)) AS age_hire_date
FROM employees e 
	JOIN dept_emp d_emp ON e.emp_no = d_emp.emp_no 
	JOIN departments dept ON d_emp.dept_no = dept.dept_no
GROUP BY dept.dept_name

Problem 8

Find all the employees that do not contain vowels in their first name and display the department they work in.

SELECT e.first_name, dep.dept_name
FROM employees e JOIN dept_emp de ON e.emp_no = de.emp_no 
		 JOIN departments dep ON de.dept_no = dep.dept_no
WHERE e.first_name NOT LIKE '%a%' 
  AND e.first_name NOT LIKE '%e%'  
  AND e.first_name NOT LIKE '%i%' 
  AND e.first_name NOT LIKE '%o%'
  AND e.first_name NOT LIKE '%u%'

Hello,

First of all, I found your problems really good and helpful during my sql practise session.

While solving the 2nd one, I think that you miss something in the solution:
In the sample database, in the salaries table we also see salary entries that are not valid since they are salaries that employees had in the past. I think that in this table, to find the active salary for each employee we need to filter using the to_date field.

So at the end I would recommend also adding the following condition:
WHERE s.to_date=DATE("9999-01-01")

SELECT title, ROUND(AVG(salary), 2) as avg_salary
FROM titles t JOIN salaries s ON s.emp_no = t.emp_no
WHERE s.to_date=DATE("9999-01-01")
GROUP BY title
ORDER BY avg_salary DESC;

wgopar/MySQL_practice_problems.md