0%
DQDigital Qasas Courses/SQL for Professionals
Sign inAll courses
  • What SQL is, and why professionals learn it
  • Learn faster: pair this course with a free AI assistant
  • SELECT and FROM: your first real query
  • WHERE: keep only the rows you want
  • Combine conditions: AND, OR, IN, BETWEEN, LIKE, NULL
  • ORDER BY, LIMIT, and DISTINCT
  • COUNT, SUM, AVG, MIN, MAX
  • GROUP BY: one number per group
  • HAVING: filter the groups
  • Why tables relate: keys and joins
  • INNER JOIN: matching rows from two tables
  • LEFT JOIN: keep everything, find what is missing
  • Subqueries: a query inside a query
  • CASE: if-and-then inside a query
  • Window functions: a first look
  • Putting it together, and a cheat sheet
  • Practice challenges
Wrap up

Putting it together, and a cheat sheet

Last updated 14 June 2026

A real analytics query usually stacks several clauses. Here is one that joins three tables, groups, filters the groups, and sorts. Read it clause by clause and it is just the pieces you already know.

Question: which product categories have brought in more than RM 500 of paid revenue, highest first?

Paid revenue per category, only categories above RM 500.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT,
  signup_date TEXT
);
INSERT INTO customers VALUES
  (1, 'Aisyah Rahman', 'Kuala Lumpur', '2023-01-12'),
  (2, 'Lim Wei Jie', 'Penang', '2023-02-03'),
  (3, 'Arjun Pillai', 'Johor Bahru', '2023-02-20'),
  (4, 'Nurul Huda', 'Kuala Lumpur', '2023-03-15'),
  (5, 'Tan Mei Ling', 'Ipoh', '2023-05-01'),
  (6, 'Faiz Hassan', 'Penang', '2023-06-10');

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT,
  category TEXT,
  price REAL
);
INSERT INTO products VALUES
  (1, 'Standard License', 'Software', 199.0),
  (2, 'Pro License', 'Software', 499.0),
  (3, 'Onboarding Workshop', 'Service', 1200.0),
  (4, 'Support Plan', 'Service', 300.0),
  (5, 'Data Pack', 'Add-on', 89.0);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  order_date TEXT,
  status TEXT
);
INSERT INTO orders VALUES
  (1001, 1, '2023-03-01', 'paid'),
  (1002, 1, '2023-04-12', 'paid'),
  (1003, 2, '2023-04-15', 'paid'),
  (1004, 3, '2023-05-02', 'refunded'),
  (1005, 4, '2023-05-20', 'paid'),
  (1006, 2, '2023-06-01', 'pending'),
  (1007, 5, '2023-06-18', 'paid');

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER
);
INSERT INTO order_items VALUES
  (1001, 1, 2), (1001, 5, 1),
  (1002, 2, 1),
  (1003, 1, 1), (1003, 4, 1),
  (1004, 3, 1),
  (1005, 2, 2), (1005, 5, 3),
  (1006, 1, 1),
  (1007, 3, 1), (1007, 4, 2);
SELECT p.category, SUM(oi.quantity * p.price) AS revenue
FROM order_items AS oi
JOIN products AS p ON oi.product_id = p.id
JOIN orders AS o ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY p.category
HAVING SUM(oi.quantity * p.price) > 500
ORDER BY revenue DESC;

The order SQL runs in

You write the clauses in one order, but the database runs them in another. Knowing the run order explains why WHERE cannot see aggregates and why AS names you create are usable in ORDER BY but not in WHERE.

  • FROM and JOIN:assemble the rows
  • WHERE:filter rows
  • GROUP BY:fold rows into groups
  • HAVING:filter the groups
  • SELECT:pick and compute the output columns
  • ORDER BY:sort
  • LIMIT:trim to N rows

Cheat sheet

  • Pick columns: SELECT a, b · rename with AS
  • Filter rows: WHERE with =, IN, BETWEEN, LIKE, IS NULL
  • Aggregate: COUNT, SUM, AVG, MIN, MAX
  • Per group: GROUP BY, then HAVING to filter groups
  • Combine tables: JOIN ... ON, and LEFT JOIN to keep unmatched rows
  • Shape output: ORDER BY, LIMIT, DISTINCT

Why can ORDER BY use a column alias created in SELECT, but WHERE cannot?

  • WHERE runs before SELECT, so the alias does not exist yet; ORDER BY runs after
  • It is a quirk with no reason
  • WHERE can use aliases too, the lesson is wrong
Run order is the key. WHERE happens before SELECT builds the output columns, so an alias is not available yet. ORDER BY happens after, so it can use the alias.
← PreviousWindow functions: a first lookNext →Practice challenges