MySQL Employees Sample Database exercise problems + solutions.

MySQL_practice_problems.md

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%'

I think there is an error in Problem 3.
Should be GROUP BY name

I believe that it is way more correct to do it with emp_no, because it is a primary key and it is unique. But your are not sure if the name is unique. Maybe you get the same result maybe not. But you can not risk it.

True. to get the correct number we should group by emp_no. But In some cases, under a specific configuration, this is an invalid query.
Or other databases, like MS-SQL for example.
Here: https://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by

I did not know that it could be a problem. But when you have a large amount of data. Let's say 1 million in a single table. You gonna groupby with name only or with name and after emp_no?

I think it should be like: GROUP BY d.emp_no, name

Nice. So this is the answer.

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;