Skip to main content

Relational Algebra to SQL Workshop

Retrieval Prompts​

  1. State, in one sentence each, what these operators do: selection σ, projection π, natural join ⋈, union ∪, difference −, rename ρ.
  2. State the difference between a relation (Codd) and an SQL table (multiset with ordered columns).
  3. State the logical evaluation order of an SQL query with FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY.
  4. State the semantic difference between putting a predicate in ON versus WHERE for a LEFT OUTER JOIN.
  5. Explain in one sentence why SELECT cannot reference an alias defined in the same SELECT list inside WHERE.

Compare and Distinguish​

Separate these pairs clearly:

  • superkey vs candidate key vs primary key vs alternate key
  • natural join ⋈ vs inner join with ON vs CROSS JOIN + WHERE
  • UNION vs UNION ALL
  • correlated subquery vs derived table vs CTE
  • WHERE vs HAVING
  • INNER JOIN vs LEFT JOIN ... IS NULL (anti-join)

Common Mistake Check​

Identify the bug or incorrect claim in each:

  1. SELECT name, AVG(salary) FROM employee GROUP BY dept_id;
  2. "LEFT JOIN followed by WHERE b.col = 1 returns the same rows as putting b.col = 1 in the ON clause."
  3. SELECT dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employee WHERE rank = 1;
  4. "SELECT * from a natural join of employee and dept on dept_id returns every column from both tables, including dept_id twice."
  5. "UNION is faster than UNION ALL because it removes duplicates."

Mini Application​

Translate each English description into (a) relational algebra in the σ / π / ⋈ / ∪ / − notation, then (b) equivalent PostgreSQL SQL.

Schema:

customer(id, name, region)
orders(id, customer_id, total_cents, placed_at)
order_item(order_id, product_id, qty, unit_cents)
product(id, name, category)
  1. Names of customers in 'EU' who have placed at least one order.
  2. For each region, the total revenue (sum of total_cents) in 2025.
  3. Products (id, name) that have never been ordered.
  4. Customers who ordered every product in category 'books' (set-difference flavor -- hint: "there is no book such that the customer has not ordered it").
  5. The top-3 highest-spending customers per region (use window functions in SQL; describe in prose for algebra).

Schema Sketch​

Write CREATE TABLE statements for the schema above with:

  • appropriate PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK constraints
  • ON DELETE actions that reflect reasonable business behavior for each FK

Evidence Check​

This page is complete only if, for any English description of a query over this schema, you can:

  • decompose it into relational algebra operators and justify each one
  • write a PostgreSQL query that returns the same rows and columns
  • explain the evaluation order the database will use
  • defend the choice of inner vs outer vs semi vs anti join in one sentence