SQL Query Description Language — Database & SQL Exercises

1 / 5

A developer narrates a query during a code review. Which description is the most professional and technically precise?

SELECT o.order_id, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'DE';

Professional SQL narration — vocabulary guide:

Option B uses the precise technical vocabulary expected in code reviews and architecture documents.

Informal phrasing	Professional phrasing
"grabs"	"retrieves" / "returns" / "projects"
"customer is from Germany"	"filters the result set WHERE country = 'DE'"
"JOIN orders to customers"	"performs an INNER JOIN between orders and customers ON the foreign key"
"returning order details"	"projects the order_id and total columns"

Key SQL narration vocabulary:

Project — select specific columns from the result set (from the SELECT clause)
Filter — apply a WHERE predicate to reduce the number of rows
Join condition — the ON clause that defines how rows between tables are matched
Result set — the table of rows returned by the entire query
Foreign key — the column in the child table referencing the parent table's primary key

2 / 5

A senior engineer explains a window function in a sprint review. Which description correctly and completely explains what this expression computes?

RANK() OVER (PARTITION BY department ORDER BY salary DESC)

Window function vocabulary — RANK() vs. related functions:

Option A is correct. PARTITION BY resets the rank counter for each department; ORDER BY salary DESC makes rank 1 the highest earner; and RANK() (unlike DENSE_RANK) introduces gaps after ties.

Function	Tied rows behaviour	Gaps in sequence?	Example output
`RANK()`	Same rank for ties	Yes	1, 1, 3
`DENSE_RANK()`	Same rank for ties	No	1, 1, 2
`ROW_NUMBER()`	Arbitrary tie-breaking	No	1, 2, 3

Window function key vocabulary:

PARTITION BY — divides the result set into independent groups for separate computation
OVER clause — defines the window: the partition and sort order for the function
Peer rows — rows with equal ORDER BY values that receive the same RANK
Frame — the subset of rows within the partition considered by aggregate window functions (not applicable to RANK)

3 / 5

A developer describes an aggregation query to a non-technical product manager. Which description uses correct and complete English vocabulary?

SELECT category, COUNT(*) AS order_count, SUM(revenue) AS total_revenue
FROM orders
GROUP BY category;

Aggregate vocabulary — narrating GROUP BY queries:

Option C is correct because it names all structural elements: the grouping column, the per-group scope, the aggregate functions, and the output aliases. This precision matters in async reviews where the reader cannot ask follow-up questions.

SQL element	Professional narration phrase
GROUP BY column	"groups the result set by [col]" / "partitioned by [col]"
COUNT(*)	"the count of rows per group"
SUM(col)	"the sum of [col] for each group"
AS alias	"aliased as [name]" / "exposed as [name]"

Key aggregate function vocabulary:

Aggregate function — COUNT, SUM, AVG, MIN, MAX — reduce many rows to one value per group
Scalar result — one value returned per group by the aggregate
HAVING — filters on aggregate results after grouping (vs. WHERE which filters rows before aggregation)
GROUP BY key — the column(s) that define group membership

4 / 5

An engineer writes a design document explaining this query. Which narration is technically accurate about how the subquery executes?

SELECT name, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Correlated subquery vocabulary:

Option D is the only accurate description. The critical distinction is the keyword correlated — the inner SELECT references e.department from the outer query, so it re-executes once per outer row, not once globally. Options A, B, and C all describe different queries.

Subquery type	References outer query?	Execution frequency
Correlated subquery	Yes — references outer alias	Once per outer row
Uncorrelated (scalar) subquery	No	Once for the whole query
Derived table (subquery in FROM)	No	Once; result used as a table

Performance note for code reviews: Correlated subqueries on large tables can be expensive because they execute once per outer row. In a review, suggest rewriting as a window function (AVG(salary) OVER (PARTITION BY department)) or as a derived table with GROUP BY, which the planner can optimise more effectively.

5 / 5

A developer writes in a PR: "The LEFT JOIN here ensures we include customers even if they have no orders." Which statement most precisely describes what a LEFT JOIN guarantees about its result set?

JOIN type vocabulary reference:

Option B is the correct and precise definition. A LEFT JOIN (also written LEFT OUTER JOIN) preserves every row from the left table — the table named before the JOIN keyword — regardless of whether a match exists in the right table.

JOIN type	Rows included	NULL behaviour for unmatched rows
INNER JOIN	Only matched rows from both tables	No NULLs from the join itself
LEFT JOIN	All rows from left table	Right-table columns → NULL for unmatched rows
RIGHT JOIN	All rows from right table	Left-table columns → NULL for unmatched rows
FULL OUTER JOIN	All rows from both tables	NULLs on either side for unmatched rows

Practical narration tip: Always name the driving table explicitly: "Since customers is the left table, every customer appears in the output — orders columns will be NULL for customers who have placed no orders." This removes ambiguity for readers who may not track which table appears on which side of the JOIN keyword.