Beginner Practice Queries

Before you start

Every exercise runs against the sample schema from The Sample Database Used in This Path. Create it first, then attempt each question before reading the solution.

Selecting and sorting

1. List every employee's first name, last name and salary, highest paid first.

SELECT first_name, last_name, salary
FROM   employees
ORDER BY salary DESC;

2. Show each employee's full name as one column called full_name.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM   employees
ORDER BY last_name;

3. List the distinct cities customers come from.

SELECT DISTINCT city FROM customers ORDER BY city;

4. Show the five most expensive products.

SELECT name, category, price
FROM   products
ORDER BY price DESC, id
LIMIT  5;

The id tie breaker makes the result deterministic. Without it, two products at the same price could swap places between runs.

Filtering

5. Find employees earning more than 80000.

SELECT first_name, salary FROM employees WHERE salary > 80000;

6. Find employees in department 10 or 30.

SELECT first_name, dept_id FROM employees WHERE dept_id IN (10, 30);

7. Find employees hired during 2024.

SELECT first_name, hire_date
FROM   employees
WHERE  hire_date >= '2024-01-01' AND hire_date < '2025-01-01';

Written as a half open range, not YEAR(hire_date) = 2024, so an index on hire_date can be used.

8. Find employees with no email address.

SELECT first_name FROM employees WHERE email IS NULL;

9. Find customers whose name contains "Systems".

SELECT name, city FROM customers WHERE name LIKE '%Systems%';

10. Find active employees earning between 60000 and 90000.

SELECT first_name, salary
FROM   employees
WHERE  status = 'active'
  AND  salary BETWEEN 60000 AND 90000;

11. Find every employee not in department 10, including those with no department.

SELECT first_name, dept_id
FROM   employees
WHERE  dept_id <> 10 OR dept_id IS NULL;

The second condition is essential. dept_id <> 10 alone is UNKNOWN for the NULL row, so that employee would be dropped.

Expressions and functions

12. Show each employee's monthly salary rounded to two decimals.

SELECT first_name, salary, ROUND(salary / 12, 2) AS monthly
FROM   employees;

13. Show each employee's hire year.

SELECT first_name, EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;

14. Show each employee's email, or the text "not provided".

SELECT first_name, COALESCE(email, 'not provided') AS contact FROM employees;

15. Label each employee senior, mid or junior by salary.

SELECT first_name, salary,
       CASE WHEN salary >= 100000 THEN 'senior'
            WHEN salary >=  70000 THEN 'mid'
            ELSE                       'junior'
       END AS band
FROM   employees
ORDER BY salary DESC;

Counting and simple aggregates

16. How many employees are there, and how many have a department?

SELECT COUNT(*) AS total, COUNT(dept_id) AS with_department FROM employees;

17. What is the total, average, minimum and maximum salary?

SELECT SUM(salary)            AS payroll,
       ROUND(AVG(salary), 2)  AS average,
       MIN(salary)            AS lowest,
       MAX(salary)            AS highest
FROM   employees;

18. How many orders are there of each status?

SELECT status, COUNT(*) AS orders
FROM   orders
GROUP BY status
ORDER BY orders DESC;

19. What is the earliest and latest hire date?

SELECT MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire FROM employees;

20. How many distinct departments actually have staff?

SELECT COUNT(DISTINCT dept_id) AS staffed_departments FROM employees;

Check your understanding

1. Why does question 11 need OR dept_id IS NULL?
2. Why does question 16 return different numbers for the two counts?
3. Rewrite question 7 using BETWEEN, and explain what would break if hire_date became a DATETIME.