SQL Coding Lab

Database Setup

Run this SQL first — it creates the tables and sample data you'll query throughout the lab.

-- Paste this entire block into sqliteonline.com and click Run

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    department TEXT,
    salary     REAL,
    hire_date  TEXT,
    manager_id INTEGER
);

CREATE TABLE departments (
    id       INTEGER PRIMARY KEY,
    name     TEXT,
    location TEXT,
    budget   REAL
);

CREATE TABLE projects (
    id          INTEGER PRIMARY KEY,
    name        TEXT,
    department  TEXT,
    status      TEXT,
    budget      REAL,
    start_date  TEXT
);

CREATE TABLE assignments (
    employee_id INTEGER,
    project_id  INTEGER,
    role        TEXT,
    hours       INTEGER
);

INSERT INTO departments VALUES
(1,'Engineering','London',500000),
(2,'Marketing','Manchester',200000),
(3,'Sales','Birmingham',350000),
(4,'HR','London',150000);

INSERT INTO employees VALUES
(1,'Alice Johnson','Engineering',75000,'2020-03-15',NULL),
(2,'Bob Smith','Marketing',55000,'2019-07-22',NULL),
(3,'Charlie Brown','Engineering',68000,'2021-01-10',1),
(4,'Diana Prince','Sales',62000,'2018-11-30',NULL),
(5,'Eve Wilson','Engineering',82000,'2017-05-20',1),
(6,'Frank Miller','HR',48000,'2022-02-14',NULL),
(7,'Grace Lee','Marketing',59000,'2020-09-01',2),
(8,'Henry Ford','Sales',71000,'2016-04-08',4),
(9,'Iris Chen','Engineering',90000,'2015-12-01',1),
(10,'Jack Davis','Sales',53000,'2023-06-15',4);

INSERT INTO projects VALUES
(1,'Website Redesign','Engineering','Active',80000,'2024-01-01'),
(2,'Social Campaign','Marketing','Active',30000,'2024-02-15'),
(3,'CRM Migration','Engineering','Completed',120000,'2023-06-01'),
(4,'Sales Training','HR','Active',15000,'2024-03-01'),
(5,'Mobile App','Engineering','Active',200000,'2024-01-15'),
(6,'Brand Refresh','Marketing','Paused',45000,'2024-04-01');

INSERT INTO assignments VALUES
(1,1,'Lead',120),(1,5,'Reviewer',40),
(3,1,'Developer',200),(3,3,'Developer',80),
(5,3,'Lead',150),(5,5,'Lead',300),
(9,1,'Architect',60),(9,5,'Architect',100),
(2,2,'Manager',180),(7,2,'Designer',140),
(4,4,'Trainer',80),(6,4,'Coordinator',60),
(8,4,'Attendee',20),(10,4,'Attendee',20);

Exercises 1–5: SELECT Basics

Exercise 1

Select all columns from the employees table.

SELECT * FROM employees;

Exercise 2

Select only the name and salary columns from employees, and rename them in the output as "Employee Name" and "Annual Salary".

SELECT name AS "Employee Name", salary AS "Annual Salary"
FROM employees;

Exercise 3

Show all unique department names from the employees table (no duplicates).

SELECT DISTINCT department FROM employees;

Exercise 4

List all employees ordered by salary from highest to lowest. Show name and salary only.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Exercise 5

Show the top 3 highest-paid employees (name, department, salary).

SELECT name, department, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Exercises 6–10: Filtering with WHERE

Exercise 6

Find all employees in the Engineering department.

SELECT * FROM employees WHERE department = 'Engineering';

Exercise 7

Find all employees earning more than £65,000 who are in Engineering or Sales. Show name, department, and salary.

SELECT name, department, salary
FROM employees
WHERE salary > 65000
AND department IN ('Engineering', 'Sales')
ORDER BY salary DESC;

Exercise 8

Find all employees hired between 2019 and 2021 (inclusive). Show name, hire_date, department.

SELECT name, hire_date, department
FROM employees
WHERE hire_date BETWEEN '2019-01-01' AND '2021-12-31'
ORDER BY hire_date;

Exercise 9

Find all employees whose name starts with a vowel (A, E, I, O, U).

SELECT name FROM employees
WHERE name LIKE 'A%'
   OR name LIKE 'E%'
   OR name LIKE 'I%'
   OR name LIKE 'O%'
   OR name LIKE 'U%';

Exercise 10

Find all employees who do NOT have a manager (manager_id is NULL).

SELECT name, department FROM employees
WHERE manager_id IS NULL;

Exercises 11–15: Aggregation & Grouping

Exercise 11

Count the total number of employees and find the average, minimum, and maximum salary.

SELECT
    COUNT(*)         AS total_employees,
    ROUND(AVG(salary), 2) AS avg_salary,
    MIN(salary)      AS lowest_salary,
    MAX(salary)      AS highest_salary
FROM employees;

Exercise 12

Show the number of employees and average salary per department. Order by average salary descending.

SELECT
    department,
    COUNT(*) AS headcount,
    ROUND(AVG(salary), 2) AS avg_salary,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Exercise 13

Find departments where the total payroll (sum of salaries) exceeds £200,000.

SELECT department, SUM(salary) AS total_payroll
FROM employees
GROUP BY department
HAVING total_payroll > 200000
ORDER BY total_payroll DESC;

Exercise 14

Show the total hours worked per project (from the assignments table). Show project_id and total hours, ordered by hours descending.

SELECT project_id, SUM(hours) AS total_hours
FROM assignments
GROUP BY project_id
ORDER BY total_hours DESC;

Exercise 15

Find how many projects are in each status (Active, Completed, Paused).

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

Exercises 16–20: JOINs

Exercise 16

Show each employee's name along with the projects they are assigned to (project name, not just ID). Use a JOIN.

SELECT e.name AS employee, p.name AS project, a.role
FROM employees e
JOIN assignments a ON e.id = a.employee_id
JOIN projects   p ON p.id = a.project_id
ORDER BY e.name;

Exercise 17

Show all Engineering employees and how many projects each one is assigned to (including 0 if none). Use LEFT JOIN.

SELECT e.name, COUNT(a.project_id) AS projects_count
FROM employees e
LEFT JOIN assignments a ON e.id = a.employee_id
WHERE e.department = 'Engineering'
GROUP BY e.id
ORDER BY projects_count DESC;

Exercise 18

Find the total hours worked by each employee across all their projects. Show name, department, total hours. Include employees with no hours (0).

SELECT e.name, e.department, COALESCE(SUM(a.hours), 0) AS total_hours
FROM employees e
LEFT JOIN assignments a ON e.id = a.employee_id
GROUP BY e.id
ORDER BY total_hours DESC;

Exercise 19

List all active projects with their department budget (from the departments table) and how many employees are assigned to each project.

SELECT
    p.name AS project,
    p.status,
    d.budget AS dept_budget,
    p.budget AS project_budget,
    COUNT(a.employee_id) AS team_size
FROM projects p
JOIN departments d ON d.name = p.department
LEFT JOIN assignments a ON a.project_id = p.id
WHERE p.status = 'Active'
GROUP BY p.id
ORDER BY team_size DESC;

Exercise 20 — Boss Level

Find the highest-paid employee in each department, along with their total hours worked across all projects and number of projects. Sort by salary descending.

SELECT
    e.name,
    e.department,
    e.salary,
    COALESCE(SUM(a.hours), 0)       AS total_hours,
    COUNT(DISTINCT a.project_id)     AS project_count
FROM employees e
LEFT JOIN assignments a ON e.id = a.employee_id
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
)
GROUP BY e.id
ORDER BY e.salary DESC;