Difficulty: Intermediate | Prerequisites: Basic SQL syntax (SELECT, FROM, WHERE), familiarity with relational database concepts (tables, rows, columns, primary keys, foreign keys). If you have not yet worked through introductory SQL, review that first.
CSE 512 (Distributed Database Systems) at ASU uses the TPC-H benchmark dataset to teach practical SQL querying and database performance concepts. TPC-H models a realistic wholesale-supplier business with eight interrelated tables covering customers, orders, line items, parts, suppliers, nations and regions. This set of notes covers how to query that schema using core SQL operations: selection, projection, joins, set operations, aggregates, sorting, subqueries and NULL handling. If you can write these queries fluently against TPC-H, you have the foundation for the partitioning and optimisation work that follows later in the course.
The TPC-H schema has eight tables linked by foreign keys. You query them with selection/projection (WHERE, column lists), joins (explicit JOIN, NATURAL JOIN, cross product), set operations (UNION, INTERSECT, EXCEPT), aggregates (COUNT, SUM, AVG, MIN, MAX with GROUP BY and HAVING), ordering (ORDER BY with LIMIT), NULL checks and nested subqueries (IN, EXISTS). Mastering these operations is what the exam tests and what the later partitioning labs build on.
TPC-H
A standard benchmark for decision-support (analytical) database workloads, maintained by the Transaction Processing Performance Council. It defines eight tables and 22 queries that simulate a wholesale-supplier business.
Think of it as a universally agreed-upon fake company database that lets everyone compare query performance on the same data.
Selection
The relational operation that filters rows from a table based on a predicate (condition). In SQL this is the WHERE clause.
In simple terms, this means picking only the rows you care about, like "give me orders placed after January 2024."
Projection
The relational operation that selects specific columns from a table. In SQL this is the column list in SELECT.
In simple terms, this means choosing which columns to display rather than returning everything with SELECT *.
Cross product (Cartesian product)
The operation that pairs every row of one table with every row of another. Written in SQL as FROM table_a, table_b with no join condition.
Think of it as "every possible combination." If table A has 100 rows and table B has 50, the result has 5,000 rows. Rarely useful on its own, but it is the conceptual foundation of joins.
JOIN (explicit join / inner join)
A cross product filtered by a meaningful relationship between the two tables, specified with the ON clause. Returns only matching row pairs.
In simple terms, a join is a cross product that keeps only the rows where the keys line up.
NATURAL JOIN
A join that automatically matches tables on all columns that share the same name. No ON clause needed.
Think of it as a shorthand: PostgreSQL looks at both tables, finds columns with identical names and joins on those.
UNION
A set operation that combines the result sets of two queries and removes duplicates.
In simple terms, stack the rows from both queries and de-duplicate.
INTERSECT
A set operation that returns only the rows common to both query results.
Think of it as the overlap between two sets.
EXCEPT (set difference)
A set operation that returns rows from the first query that do not appear in the second.
In simple terms, "give me everything in A that is not in B."
Aggregate functions (COUNT, SUM, AVG, MIN, MAX)
Functions that compute a single summary value from a group of rows.
Think of them as the SQL equivalent of spreadsheet totals: count, sum, average, smallest, largest.
GROUP BY
A clause that partitions rows into groups sharing the same value(s) in one or more columns, so that aggregate functions operate per group rather than over the whole table.
In simple terms, "split the data into buckets and summarise each bucket."
HAVING
A filter applied after GROUP BY and aggregation, used to keep or discard entire groups based on an aggregate condition.
Think of it as a WHERE clause for groups. WHERE filters individual rows before grouping; HAVING filters groups after.
ORDER BY
A clause that sorts the result set by one or more columns, ascending (ASC) or descending (DESC).
In simple terms, it puts the output in the order you want to read it.
IS NULL / IS NOT NULL
Predicates that test whether a column value is missing (NULL) or present. You cannot use = NULL because NULL is not equal to anything, including itself.
Think of NULL as "unknown" rather than "zero" or "empty string."
Nested query (subquery)
A SELECT statement embedded inside another query, usually in the WHERE clause. The inner query runs first and feeds its result to the outer query.
In simple terms, a query inside a query. You use one query to compute a set of values and the outer query to filter by that set.
IN subquery
A nested query pattern where the outer WHERE tests membership in a list produced by the inner query: WHERE column IN (SELECT ...).
Think of it as asking "is this value in the list that the subquery returned?"
EXISTS subquery
A nested query pattern that returns true if the inner query produces at least one row. Typically uses a correlated subquery referencing the outer table.
In simple terms, EXISTS asks "does any matching row exist?" and stops as soon as it finds one.
Selection filters rows; projection chooses columns. Together they form the most basic query pattern.
Filtering by equality: SELECT * FROM orders WHERE o_orderdate = '1996-12-01'; returns only orders placed on that date.
Filtering by range: SELECT * FROM part WHERE p_retailprice > 1000; returns parts priced above 1,000.
Pattern matching: SELECT * FROM customer WHERE c_name LIKE 'A%'; returns customers whose name starts with 'A'. The % wildcard matches any sequence of characters.
Combining with LIMIT: SELECT * FROM supplier LIMIT 10; returns just the first ten rows, useful for quick inspection of large tables.
Projecting specific columns: SELECT c_name, c_acctbal FROM customer; returns only names and account balances rather than every column. This is projection in action.
Mixing projection and filtering: SELECT o_orderdate, o_totalprice FROM orders; projects two columns from the orders table. Add a WHERE clause and you have selection and projection together.
The key point: in relational algebra, selection is about rows and projection is about columns. SQL's WHERE handles selection; the column list after SELECT handles projection.
Listing two tables in FROM with no join condition produces every possible row combination.
SELECT * FROM customer, orders LIMIT 10; pairs each customer with each order. With 150,000 customers and 1.5 million orders, the full result would be 225 billion rows, which is why you almost always add LIMIT or a join condition.
Cross products are the conceptual starting point for understanding joins: a join is a cross product with a filter.
An explicit JOIN uses an ON clause to specify the matching condition, keeping only the row pairs where the keys match.
Multi-table join (customer to region via nation):
SELECT c.c_name, r.r_name
FROM customer c
JOIN nation n ON c.c_nationkey = n.n_nationkey
JOIN region r ON n.n_regionkey = r.r_regionkey
LIMIT 10;This chains two joins: customer to nation, then nation to region. The aliases c, n, r keep the query readable.
Two-table join (supplier to nation):
SELECT s.s_name, n.n_name
FROM supplier s
JOIN nation n ON s.s_nationkey = n.n_nationkey
LIMIT 10;NATURAL JOIN matches tables on all columns that share the same name, so you write no ON clause.
SELECT c_name, o_orderdate, o_totalprice FROM customer NATURAL JOIN orders LIMIT 10; joins on c_custkey because both tables have that column.
Three-table natural join: SELECT p_name, s_name, ps_supplycost FROM part NATURAL JOIN partsupp NATURAL JOIN supplier LIMIT 10; chains through common columns p_partkey and s_suppkey.
NATURAL JOIN is concise but fragile: if a schema change adds a same-named column you did not intend to join on, the query silently produces wrong results. Explicit JOIN ... ON is safer for production code.
Set operations combine the results of two SELECT statements. Both queries must return the same number of columns with compatible types.
Combines two result sets and removes duplicates.
SELECT c_name FROM customer UNION SELECT s_name FROM supplier; produces a single list of all unique names across both tables.
Use UNION ALL if you want to keep duplicates (and skip the deduplication cost).
Returns only the rows that appear in both result sets.
SELECT c_name FROM customer INTERSECT SELECT s_name FROM supplier; returns names that exist in both the customer and supplier tables.
Practical example: find European nations that also have suppliers:
SELECT n_name FROM nation WHERE n_regionkey = 2
INTERSECT
SELECT n_name FROM nation JOIN supplier ON nation.n_nationkey = supplier.s_nationkey;Returns rows from the first query that are absent from the second.
SELECT c_nationkey FROM customer EXCEPT SELECT n_nationkey FROM nation WHERE n_name = 'UNITED STATES'; returns all customer nation keys except the one belonging to the United States.
You can also use NOT IN to achieve the same result: WHERE c_nationkey NOT IN (SELECT n_nationkey FROM nation WHERE n_name = 'UNITED STATES'). EXCEPT operates on entire result sets; NOT IN operates row by row in the WHERE clause. Both give the same answer here, but EXCEPT automatically deduplicates while NOT IN does not.
All three set operations require the column count and types to match between the two SELECTs. UNION and INTERSECT are commutative (A UNION B = B UNION A). EXCEPT is not (A EXCEPT B is different from B EXCEPT A).
Aggregate functions collapse many rows into a single summary value.
COUNT: SELECT COUNT(*) FROM orders WHERE o_orderstatus = 'F'; counts filled orders.
SUM: SELECT SUM(o_totalprice) FROM orders; totals the price of every order.
AVG: SELECT AVG(o_totalprice) FROM orders; computes the mean order price.
MIN: SELECT MIN(p_retailprice) FROM part; finds the cheapest part.
MAX: SELECT MAX(l_quantity) FROM lineitem; finds the largest quantity ever ordered in a single line item.
Without GROUP BY, an aggregate returns one row for the entire table. With GROUP BY, it returns one row per group.
ORDER BY sorts the output. ASC (ascending) is the default; DESC reverses it.
Single column sort: SELECT c_name FROM customer ORDER BY c_name ASC;
Descending sort: SELECT o_orderkey, o_totalprice FROM orders ORDER BY o_totalprice DESC;
Multi-column sort: SELECT p_partkey, p_name, p_retailprice FROM part ORDER BY p_retailprice ASC, p_name DESC; sorts first by price (low to high), breaking ties by name (Z to A).
ORDER BY with LIMIT: SELECT o_orderkey, o_totalprice FROM orders ORDER BY o_totalprice DESC LIMIT 10; gives the ten most expensive orders. This is one of the most common patterns in analytical queries.
ORDER BY operates after all filtering, joining and grouping. It is the last clause evaluated before LIMIT.
HAVING filters groups after aggregation. WHERE filters individual rows before grouping; HAVING filters the grouped results.
Customers with more than 10 orders:
SELECT o_custkey, COUNT(o_orderkey) AS order_count
FROM orders
GROUP BY o_custkey
HAVING COUNT(o_orderkey) > 10;Suppliers earning more than 10,000 in revenue:
SELECT l_suppkey, SUM(l_extendedprice * (1 - l_discount)) AS total_revenue
FROM lineitem
GROUP BY l_suppkey
HAVING SUM(l_extendedprice * (1 - l_discount)) > 10000;Parts with average discount above 5%:
SELECT p_partkey, AVG(l_discount) AS avg_discount
FROM part
JOIN lineitem ON p_partkey = l_partkey
GROUP BY p_partkey
HAVING AVG(l_discount) > 0.05;Notice that the aggregate expression in HAVING must match what appears in the SELECT or be a valid aggregate over the grouped columns.
NULL represents an unknown or missing value. You test for it with IS NULL or IS NOT NULL, never with = NULL.
SELECT c_custkey, c_name FROM customer WHERE c_phone IS NULL; finds customers with no phone number on file.
SELECT c_custkey, c_name FROM customer WHERE c_phone IS NOT NULL; finds customers who do have a phone number.
NULLs propagate through expressions: any arithmetic involving NULL yields NULL. Aggregates like COUNT(*) count all rows, but COUNT(column) skips NULLs in that column.
IN tests whether a value belongs to the set returned by an inner query.
Customers who ordered but never received a shipment:
SELECT DISTINCT c_name
FROM customer
WHERE c_custkey IN (
SELECT o_custkey FROM orders
EXCEPT
SELECT o_custkey FROM orders
JOIN lineitem ON o_orderkey = l_orderkey
);The inner EXCEPT finds customer keys with orders but no matching line items (shipments). The outer query fetches those customers' names.
Parts never ordered:
SELECT DISTINCT p_name
FROM part
WHERE p_partkey IN (
SELECT p_partkey FROM part
EXCEPT
SELECT l_partkey FROM lineitem
);Parts sharing the same container as part 99:
SELECT p_name, p_partkey, p_container
FROM part
WHERE p_container IN (
SELECT p_container FROM part WHERE p_partkey = 99
);When the inner query returns exactly one value, you can replace IN with =. IN is the safer default because it handles the case where the subquery returns more than one row.
EXISTS returns true if the inner query produces at least one row. It is typically correlated, meaning it references a column from the outer query.
Customers who placed at least one order:
SELECT c_name
FROM customer c
WHERE EXISTS (
SELECT * FROM orders o
WHERE o.o_custkey = c.c_custkey
);The inner query is correlated: it checks each customer individually. PostgreSQL can optimise this into a semi-join.
Parts supplied by more than one supplier:
SELECT p_name
FROM part p
WHERE EXISTS (
SELECT 1 FROM partsupp ps
WHERE ps.ps_partkey = p.p_partkey
GROUP BY ps.ps_partkey
HAVING COUNT(DISTINCT ps.ps_suppkey) > 1
);The GROUP BY and HAVING inside the EXISTS subquery is a useful pattern: it lets you filter on aggregated properties of related rows without pulling the aggregate into the outer query.
Students often think WHERE column = NULL will find missing values. It will not. NULL is not equal to anything, including itself. Always use IS NULL.
Students confuse WHERE and HAVING. WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Putting an aggregate condition in WHERE (e.g., WHERE COUNT(*) > 5) is a syntax error.
Students assume NATURAL JOIN is the same as explicit JOIN. It matches on all same-named columns, which can silently break your query if two tables share a column name you did not intend to join on.
Students forget that EXCEPT is not commutative. A EXCEPT B gives rows in A missing from B; B EXCEPT A gives the opposite. UNION and INTERSECT are commutative.
⚠️ The TPC-H schema's table sizes vary enormously. lineitem and orders have millions of rows; nation has 25 and region has 5. Expect exam questions about which SELECT * queries run slowly (answer: the large tables).
⚠️ Know the logical order of SQL clause evaluation: FROM and JOINs first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. This explains why you cannot use a column alias defined in SELECT inside a WHERE clause.
⚠️ Be prepared to write queries combining JOIN, GROUP BY, HAVING and ORDER BY in a single statement. The TPC-H benchmark queries (Q1, Q3) do exactly this.
⚠️ Understand the difference between IN and EXISTS for nested queries. EXISTS with a correlated subquery can be more efficient for large datasets because it stops scanning as soon as it finds one match.
True or false: SELECT * FROM customer WHERE c_acctbal = NULL; will find customers with no account balance. (False. Use IS NULL.)
Fill in the blank: The SQL clause that filters groups after aggregation is called ______. (HAVING)
True or false: NATURAL JOIN and explicit JOIN always produce the same result. (False. NATURAL JOIN matches on all same-named columns, which may include unintended ones.)
Fill in the blank: A cross product of a table with 100 rows and a table with 200 rows produces ______ rows. (20,000)
True or false: EXCEPT is commutative (A EXCEPT B = B EXCEPT A). (False.)
Q: Write a query to find the top 5 customers by total order value. Which clauses do you need?
A: You need JOIN (customer to orders), SUM as the aggregate, GROUP BY on the customer key, ORDER BY the sum descending, and LIMIT 5.
Q: What is the difference between WHERE and HAVING?
A: WHERE filters individual rows before any grouping or aggregation. HAVING filters entire groups after GROUP BY and aggregation have run.
Q: Why does SELECT * FROM customer, orders; produce an enormous result set?
A: It computes the cross product (Cartesian product): every customer row paired with every order row. With 150K customers and 1.5M orders, that is 225 billion rows.
Q: Write a query using EXISTS to find all suppliers located in a nation that has at least one customer.
A: SELECT s_name FROM supplier s WHERE EXISTS (SELECT 1 FROM customer c JOIN nation n ON c.c_nationkey = n.n_nationkey WHERE n.n_nationkey = s.s_nationkey);
Q: Explain why NATURAL JOIN can be dangerous in production code.
A: NATURAL JOIN implicitly matches on every column with the same name across both tables. If a schema migration adds a new column that shares a name, the join condition changes silently and can return wrong results. Explicit JOIN ... ON avoids this because you spell out exactly which columns to match.
This material connects directly to the partitioning and query optimisation labs later in CSE 512. The TPC-H Q1 (pricing summary) and Q3 (shipping priority) queries combine nearly every operation covered here: joins, aggregates, GROUP BY, HAVING, ORDER BY and LIMIT. Understanding how PostgreSQL executes these queries (via EXPLAIN ANALYZE) builds the bridge to the partitioning unit, where you will compare unpartitioned versus partitioned table performance.
The set operations (UNION, INTERSECT, EXCEPT) also map to relational algebra operations you may encounter in a database theory course, and the nested-query patterns (IN, EXISTS) reappear in distributed query processing where subqueries may execute on different nodes.
TPC-H, CSE 512, distributed database systems, ASU, SQL querying, PostgreSQL, pgAdmin, selection, projection, WHERE clause, column list, cross product, Cartesian product, INNER JOIN, explicit join, NATURAL JOIN, UNION, UNION ALL, INTERSECT, EXCEPT, set difference, aggregate functions, COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, ORDER BY, ASC, DESC, LIMIT, IS NULL, IS NOT NULL, nested query, subquery, IN subquery, EXISTS subquery, correlated subquery, semi-join, TPC-H schema, lineitem, orders, customer, supplier, part, partsupp, nation, region, relational algebra, query optimisation