Reading SELECT Queries
SELECT, FROM, WHERE, JOIN, ORDER BY, DISTINCT, aliases, and subqueries
SELECT query vocabulary
- SELECT col FROM tbl WHERE cond ORDER BY col ASC/DESC — the core pattern
- INNER JOIN — only matched rows; LEFT JOIN — all left + matched right (NULL if no match)
- DISTINCT — deduplicate: returns unique rows only
- AS alias — temporary name for a table or column in this query only
- Subquery (SELECT ...) in WHERE — runs first; outer query uses its result as a value
Question 0 of 5
Read this SQL query and describe what it does in one sentence:SELECT first_name, last_name, email FROM users WHERE active = true ORDER BY last_name ASC;
Retrieves first name, last name, and email of all active users, sorted A–Z by last name. SELECT statement vocabulary:
- SELECT columns — which fields to include in the result (vs SELECT * for all columns)
- FROM table — which table to query
- WHERE condition — filter rows; only rows where the condition is true are returned
- ORDER BY column ASC/DESC — sort the result set; ASC = ascending (A-Z, 0-9); DESC = descending
- LIMIT n — restrict to the first n rows
What does this JOIN do?SELECT orders.id, users.email, orders.total FROM orders INNER JOIN users ON orders.user_id = users.id WHERE orders.total > 100;
Returns matched order+user rows where order total exceeds 100. JOIN vocabulary:
- INNER JOIN — only rows that have a match in both tables; unmatched rows are excluded
- LEFT JOIN — all rows from the left table, plus matched rows from the right (unmatched right side = NULL)
- RIGHT JOIN — all rows from the right table, matched from the left
- FULL OUTER JOIN — all rows from both tables, NULLs for non-matches
- ON condition — the join condition that links rows between tables
Describe the result of this query:SELECT DISTINCT status FROM orders;
Returns each unique status value exactly once. DISTINCT vocabulary:
- DISTINCT — removes duplicate rows from the result set; only unique combinations of selected columns are returned
- Without DISTINCT:
SELECT status FROM orders→ might return ["pending", "pending", "shipped", "delivered", "pending"] - With DISTINCT: → returns ["pending", "shipped", "delivered"]
What does the alias do in this query?SELECT u.id, u.email, o.total AS order_total FROM users AS u LEFT JOIN orders AS o ON u.id = o.user_id;
AS creates a temporary alias for this query only — tables and columns keep their original names in the database. Alias vocabulary:
- table alias —
users AS uor justusers u; shorter name to reference in SELECT and ON clauses - column alias —
o.total AS order_total; the column appears asorder_totalin the result set - Aliases make queries more readable, especially with long table names or when using aggregate functions:
COUNT(*) AS total_orders
What does this subquery return?SELECT name FROM products WHERE price > (SELECT AVG(price) FROM products);
Product names priced above the overall average. Subquery vocabulary:
- The inner query
(SELECT AVG(price) FROM products)runs first and returns a single value (the average price) - The outer query uses that value as a filter condition
- Scalar subquery — returns a single value; used in WHERE or SELECT
- Correlated subquery — references the outer query; runs once per outer row
- Subquery in FROM — also called a derived table:
FROM (SELECT ...) AS sub