Thinking in Sets

Declarative queries over relations

SQL is declarative: you describe which rows and columns you want, and the database's planner decides how to get them (which indexes, which join algorithms, which order). Tables are sets of rows with no inherent order: without ORDER BY, row order is not guaranteed. Think in whole sets ("all paid orders joined to their customers, grouped by city") rather than row-by-row loops, and know the grain of each table (one row per order, per order item, per day).

Sets, order of evaluation, NULLs

SQL describes the result you want; understanding how a query is evaluated explains most surprises.

The sample schema used in this course

Six small tables: departments, employees (with a manager hierarchy), customers, orders, order_items and logins.

CREATE TABLE departments (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE employees (
  id int PRIMARY KEY, name text NOT NULL, dept_id int REFERENCES departments(id),
  manager_id int REFERENCES employees(id), salary int NOT NULL, hired date NOT NULL);
CREATE TABLE customers (id int PRIMARY KEY, name text NOT NULL, city text);
CREATE TABLE orders (
  id int PRIMARY KEY, customer_id int REFERENCES customers(id),
  ordered_on date NOT NULL, status text NOT NULL, amount numeric(10,2) NOT NULL);
CREATE TABLE order_items (order_id int REFERENCES orders(id), product text, qty int, price numeric(10,2));
CREATE TABLE logins (user_id int, day date);

INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'Support'),(4,'Legal');
INSERT INTO employees VALUES
 (1,'Asha',1,NULL,250000,'2019-03-01'), (2,'Ravi',1,1,180000,'2020-06-15'),
 (3,'Meera',1,1,180000,'2021-01-10'),  (4,'John',1,2,120000,'2023-05-01'),
 (5,'Sara',2,1,150000,'2020-02-01'),   (6,'Ken',2,5,90000,'2022-08-20'),
 (7,'Li',2,5,95000,'2024-01-05'),      (8,'Omar',3,1,70000,'2021-11-11'),
 (9,'Nina',NULL,1,60000,'2025-02-01');
INSERT INTO customers VALUES (1,'Asha','Pune'),(2,'Ravi','Delhi'),(3,'Meera','Pune'),(4,'John',NULL);
INSERT INTO orders VALUES
 (101,1,'2026-01-03','paid',1200),(102,1,'2026-01-20','paid',300),(103,2,'2026-01-21','refunded',800),
 (104,2,'2026-02-02','paid',450),(105,3,'2026-02-14','paid',2000),(106,1,'2026-02-28','paid',700),
 (107,3,'2026-03-01','cancelled',150);
INSERT INTO order_items VALUES
 (101,'lamp',1,1000),(101,'pen',10,20),(102,'mug',2,150),(103,'chair',1,800),
 (104,'pen',5,20),(104,'mug',1,350),(105,'desk',1,2000),(106,'lamp',1,700),(107,'pen',5,30);
INSERT INTO logins VALUES
 (1,'2026-03-01'),(1,'2026-03-02'),(1,'2026-03-03'),(1,'2026-03-05'),(1,'2026-03-06'),
 (2,'2026-03-02'),(2,'2026-03-04'),(2,'2026-03-05'),(2,'2026-03-06'),(2,'2026-03-07');

Name the grain before writing joins

Saying "orders is one row per order, order_items one row per line" prevents most double-counting bugs.

Quick check: What order do rows come back in without ORDER BY?

• Primary key order
• Insertion order
• No guaranteed order
• Alphabetical order