3.1SQL Queries (SELECT, JOIN, Subqueries, Set Operations, DML)

This area carries the largest weight in the OSS-DB Silver exam range (S3.1, weight 13). The SELECT statement is not just "retrieve data"—only by combining filtering, ordering, aggregation, and de-duplication clauses in the correct order and role do you get the intended result. In real work spanning multiple tables, picking the wrong JOIN type causes missing or duplicated rows, while subqueries and set operations let you express more complex conditions in a single statement.

3.1.1SELECT statement clauses

• WHERE filters rows (SELECT * FROM orders WHERE amount > 1000); it operates on rows before aggregation. ORDER BY sorts the result (ORDER BY amount DESC; ascending ASC is the default). LIMIT/OFFSET specify a row cap and how many rows to skip (LIMIT 10 OFFSET 20 for pagination).
• GROUP BY groups rows by the values of specified columns, used together with aggregate functions (count/sum, etc.). HAVING filters after grouping, applied to the aggregated result (GROUP BY customer_id HAVING count(*) > 5). The distinction that WHERE acts before aggregation and HAVING after is the most frequently tested point.
• DISTINCT removes duplicate rows from the result (SELECT DISTINCT city FROM customers). The logical execution order of a SELECT is roughly FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT/OFFSET, which differs from the order the clauses are written in.
• A NULL (a missing value, i.e. "unknown") follows three-valued logic: col = NULL is neither true nor false but always unknown, so it returns no rows. Always use IS NULL/IS NOT NULL to test for NULL (WHERE col IS NULL). In aggregation, count(*) counts rows but count(column) excludes NULLs, and sum/avg ignore NULLs too; GROUP BY groups all NULLs into one group. "You cannot use = NULL; use IS NULL" is a top exam trap.

3.1.2JOIN, subqueries, set operations, and DML

• INNER JOIN returns only rows where the join condition matches in both tables (plain JOIN means INNER JOIN). LEFT JOIN keeps every row from the left table, filling with NULL where there is no match on the right. RIGHT JOIN keeps every row from the right table (the mirror of LEFT JOIN). FULL JOIN keeps every row from both sides, filling with NULL wherever only one side has a match. CROSS JOIN generates every combination (Cartesian product) with no join condition.
• A subquery embeds one SELECT inside another. It appears in WHERE (WHERE column IN (SELECT ...)), and also in FROM or SELECT clauses. Set operations: UNION combines two results and removes duplicates (UNION ALL keeps duplicates); INTERSECT returns only rows common to both; EXCEPT returns the rows in one result that are absent from the other. All require matching column count and compatible types.
• INSERT adds a new row (INSERT INTO t (col1,col2) VALUES (1,'a')). UPDATE modifies existing rows (UPDATE t SET col1=2 WHERE id=1—omitting WHERE updates every row). DELETE removes rows (DELETE FROM t WHERE id=1; omitting WHERE deletes every row—distinct from DROP TABLE, which removes the table structure itself).

Suppose there is an orders table orders(id, customer_id, amount, order_date) and a customers table customers(id, name, city). Walk through building a common analytical query: "For Tokyo customers, show only those whose total order amount exceeds 5000, in descending order." This requires joining customers and orders on customer_id. Writing SELECT c.name, sum(o.amount) AS total FROM customers c JOIN orders o ON c.id = o.customer_id WHERE c.city = 'Tokyo' GROUP BY c.name HAVING sum(o.amount) > 5000 ORDER BY total DESC; condenses into one statement the flow: filter to Tokyo customers with WHERE, aggregate, filter the aggregated result with HAVING, then sort with ORDER BY. If the requirement then expands to "also include customers who have never placed an order," an INNER JOIN would drop those customers entirely, so you switch to LEFT JOIN orders o ON ... and handle the resulting NULL in sum(o.amount) with a function like coalesce. For "just the IDs of customers who have ever placed an order over 10000," a simple subquery like SELECT DISTINCT customer_id FROM orders WHERE amount > 10000 may suffice on its own, or it can be embedded as a filtering condition inside another SELECT's WHERE, as in WHERE customer_id IN (SELECT customer_id FROM orders WHERE amount > 10000). Set operations come into play when you need the relationship between two independent query results—such as "customers who are both new this month and dormant since last year" (common to both = INTERSECT, only one side = EXCEPT).

3.1.3Section summary

• Execution order: WHERE (pre-aggregation) -> GROUP BY -> HAVING (post-aggregation) -> ORDER BY -> LIMIT/OFFSET. DISTINCT removes duplicates
• INNER = matches only; LEFT/RIGHT/FULL = NULL-fill while preserving all rows from one or both sides; CROSS = Cartesian product. Subqueries can be embedded in WHERE/FROM/SELECT
• UNION removes duplicates, UNION ALL keeps them, INTERSECT is the common set, EXCEPT is the difference. Omitting WHERE in UPDATE/DELETE affects every row