Labs ICT
⭐ Pro Login

Exercises

Time to test yourself. These exercises start easy and get harder. Try to solve them before looking at the hints. The best way to learn SQL is to write SQL.

Setup

Run this to create your practice data: 

CREATE TABLE employees (
  emp_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT,
  salary REAL,
  hire_date TEXT
);

INSERT INTO employees VALUES
  (1, 'Amina', 'Engineering', 120000, '2023-01-15'),
  (2, 'Musa', 'Marketing', 95000, '2023-03-20'),
  (3, 'Zainab', 'Engineering', 130000, '2022-11-01'),
  (4, 'John', 'Sales', 85000, '2024-06-10'),
  (5, 'Fatima', 'Marketing', 105000, '2023-08-05'),
  (6, 'Ibrahim', 'Sales', 90000, '2024-01-22'),
  (7, 'Grace', 'Engineering', 140000, '2022-06-15'),
  (8, 'Samuel', 'HR', 60000, '2025-02-01');
Try it Yourself →

Exercise 1: Easy

Write a query to show the name and salary of all employees in Engineering.

Show Solution 
SELECT name, salary FROM employees WHERE department = 'Engineering';

Exercise 2: Easy

List all employees sorted by salary from highest to lowest.

Show Solution 
SELECT * FROM employees ORDER BY salary DESC;

Exercise 3: Medium

Find the average salary per department.

Show Solution 
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Exercise 4: Medium

Find departments where the average salary is above 100,000.

Show Solution 
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

Exercise 5: Hard

Find the employee(s) with the highest salary in each department.

Show Solution 
SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary = (
  SELECT MAX(e2.salary)
  FROM employees e2
  WHERE e2.department = e1.department
);

Exercise 6: Hard

Find employees hired in 2023 or earlier, earning more than the average salary of all employees.

Show Solution 
SELECT name, salary, hire_date
FROM employees
WHERE hire_date <= '2023-12-31'
  AND salary > (SELECT AVG(salary) FROM employees);

Exercise 7: Challenge

For each department, show the salary difference between the highest-paid and lowest-paid employee.

Show Solution 
SELECT department,
  MAX(salary) - MIN(salary) AS salary_gap
FROM employees
GROUP BY department;

Your Turn

The best exercise is to come up with your own questions and try to answer them with SQL. Here are some ideas:

  • How many employees are in each department?
  • Who was the first person hired? The most recent?
  • What is the total salary cost per department?
  • Which department has the most employees?
  • Find employees whose salary is below the average in their department.

Try writing these queries yourself. If you get stuck, review the relevant lesson or ask someone. The more you practice, the more natural SQL will feel.

← Examples