Examples

Here are some real-world SQL examples that bring together everything you have learned. These are the kinds of queries you will actually write on the job.

Sales Report

Find total sales per product category, sorted by highest revenue:

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  product TEXT NOT NULL,
  category TEXT NOT NULL,
  amount REAL NOT NULL,
  sale_date TEXT
);

INSERT INTO sales VALUES 
  (1, 'Laptop', 'Electronics', 500000, '2026-01-15'),
  (2, 'Phone', 'Electronics', 250000, '2026-01-16'),
  (3, 'Bread', 'Food', 5000, '2026-01-17'),
  (4, 'Rice', 'Food', 15000, '2026-01-18'),
  (5, 'TV', 'Electronics', 300000, '2026-01-19'),
  (6, 'Milk', 'Food', 3000, '2026-01-20');

SELECT 
  category,
  COUNT(*) AS items_sold,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_price,
  MAX(amount) AS most_expensive
FROM sales
GROUP BY category
ORDER BY total_revenue DESC;

Customer Order History

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  product TEXT NOT NULL,
  amount REAL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INSERT INTO customers VALUES (1, 'Bilal', 'Kano');
INSERT INTO customers VALUES (2, 'Fatima', 'Abuja');
INSERT INTO customers VALUES (3, 'Musa', 'Lagos');

INSERT INTO orders VALUES 
  (1, 1, 'Laptop', 450000),
  (2, 1, 'Mouse', 15000),
  (3, 2, 'Phone', 200000),
  (4, 3, 'Tablet', 180000),
  (5, 3, 'Keyboard', 25000);

-- Top customers by total spending
SELECT 
  c.name,
  c.city,
  COUNT(o.id) AS orders_count,
  SUM(o.amount) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY total_spent DESC;

Find Duplicate Records

A common data cleaning task — find duplicate entries:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL
);

INSERT INTO users VALUES 
  (1, 'bilal@email.com'),
  (2, 'fatima@email.com'),
  (3, 'musa@email.com'),
  (4, 'bilal@email.com'),  
  (5, 'zainab@email.com'),
  (6, 'bilal@email.com');  

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Date-Based Analysis

CREATE TABLE daily_sales (
  id INTEGER PRIMARY KEY,
  sale_date TEXT NOT NULL,
  amount REAL NOT NULL
);

INSERT INTO daily_sales VALUES 
  (1, '2026-06-01', 50000),
  (2, '2026-06-01', 75000),
  (3, '2026-06-02', 62000),
  (4, '2026-06-02', 43000),
  (5, '2026-06-03', 88000);

SELECT 
  sale_date,
  COUNT(*) AS transactions,
  SUM(amount) AS daily_total
FROM daily_sales
GROUP BY sale_date
ORDER BY sale_date;