Quiz: SQL SELECT Statement: Querying Data from a Table

Total: 6 questions

1. 

What does the SQL SELECT statement do, in what order are its clauses written, and why is SELECT * avoided in application code?

SELECT reads data from one or more tables and returns the result as a new table (a result set of rows); the stored data does not change. By the SQL standard it is a DML statement, but it is often put in a separate group, DQL — the data query language.

Only the column list is required; the other clauses are optional but must appear in exactly this order:

SELECT [DISTINCT] columns_or_expressions
FROM table
[WHERE row_condition]
[GROUP BY columns]
[HAVING group_condition]
[ORDER BY columns [ASC | DESC]]
[LIMIT row_count [OFFSET offset]];
  • Clauses cannot be reordered: WHERE after GROUP BY is a syntax error.
  • The column list may contain expressions: SELECT product, lbs * price FROM sales;. Arithmetic with NULL gives NULL. FROM can be dropped entirely: SELECT 2 + 3; returns 5.
  • SELECT * returns all columns in declaration order — handy in the console. In an application, list the columns explicitly: the server does not send data you do not need (such as a large TEXT), and code that reads a ResultSet by column index (getString(2)) does not break when a column is added.
2. 

What are column and table aliases (AS) in SELECT, and in which parts of a query can you use them?

An alias is a temporary name for a column or a table that is valid only inside one query. A column alias becomes the result header, and a table alias shortens the table name in the rest of the query. The keyword AS is optional: sales AS s and sales s mean the same thing.

SELECT s.product, s.lbs * s.price AS revenue
FROM sales AS s
WHERE s.shop = 'Delta';
  • Once a table has an alias, its original name no longer works in that query: SELECT sales.shop FROM sales AS s; gives ERROR 1054: Unknown column 'sales.shop'.
  • A table alias is required when the same table appears in a query twice.
  • An alias with a space goes in backticks: AS `revenue, USD`.
  • A column alias is not visible in WHERE (ERROR 1054), because WHERE runs before SELECT. It always works in ORDER BY, and in GROUP BY and HAVING as a MySQL extension (not in standard SQL or PostgreSQL).
3. 

Which operators are used in WHERE: comparison, AND, OR, NOT, IN, BETWEEN and LIKE?

WHERE keeps only the rows for which the condition is true; rows where it is false or NULL are discarded.

  • Comparison: =, <> (standard) or !=, >, <, >=, <=. Strings go in single quotes, numbers without quotes. In MySQL 8 string comparison is case-insensitive by default (utf8mb4_0900_ai_ci): shop = 'acme' finds “Acme”; in PostgreSQL it does not.
  • AND, OR, NOT: the precedence is NOT, then AND, then OR. a OR b AND c reads as a OR (b AND c), so whenever AND and OR are mixed, add parentheses.
  • IN checks membership in a list and replaces a chain of OR conditions: product IN ('pears', 'lemons').
  • BETWEEN a AND b includes both bounds (same as >= a AND <= b); the lower bound goes first, otherwise the result is empty. For a DATETIME column a monthly range is safer as a half-open interval: >= '2026-11-01' AND < '2026-12-01'.
  • LIKE is pattern matching: % matches any number of any characters (including none), _ matches exactly one character. In MySQL LIKE is case-insensitive by default and works even with numbers; a pattern that starts with % cannot use an index. With a PreparedStatement, add % to the parameter value: ps.setString(1, prefix + "%").

Negations: NOT IN, NOT BETWEEN, NOT LIKE.

4. 

How do you find rows with NULL in SELECT, and why does WHERE price = NULL return nothing?

NULL means a missing or unknown value. Comparing it with =, <>, < or > gives neither true nor false but NULL, so rows with NULL are found only with IS NULL and IS NOT NULL.

SELECT id, product FROM sales WHERE price = NULL;   -- Empty set, no error and no warning
SELECT id, product FROM sales WHERE price IS NULL;  -- rows 3 and 6
  • Rows with NULL are in neither price < 3 nor price >= 3. If you need them, say so explicitly: price < 3 OR price IS NULL.
  • NOT IN with a list or subquery that contains NULL always returns an empty result — filter out NULL or use NOT EXISTS.
  • The MySQL operator <=> is NULL-safe equality: NULL <=> NULL is true.
  • For DISTINCT, all NULLs count as one value: SELECT DISTINCT price FROM sales; returns seven rows — six prices and one NULL.
  • In ascending order MySQL puts NULLs first (PostgreSQL and Oracle put them last); to move them to the end, write ORDER BY price IS NULL, price.
5. 

How do DISTINCT, ORDER BY and LIMIT with OFFSET work in MySQL, and how do you paginate results correctly?

  • DISTINCT goes right after SELECT and removes duplicate rows across all listed columns: SELECT DISTINCT product gives 4 names, while SELECT DISTINCT shop, product returns all 8 rows because no pair repeats. DISTINCT does not guarantee row order.
  • ORDER BY sorts in ascending order (ASC) by default; DESC is set per column, and the next column only orders rows with equal values in the previous one. You can sort by a column that is not in the SELECT list and by an alias. ORDER BY 3 works but silently changes meaning when the column list changes. Without ORDER BY the row order is undefined.
  • LIMIT n OFFSET m skips m rows and returns at most n. The MySQL short form is LIMIT m, n, where the first number is the offset: LIMIT 10, 20 means 20 rows starting from the 11th. There is no SELECT TOP in MySQL.
SELECT shop, product, lbs
FROM sales
ORDER BY lbs DESC, id   -- id makes the order unambiguous
LIMIT 3 OFFSET 3;       -- the second page of three rows

For pagination, use LIMIT only with an ORDER BY that ends with a unique column; otherwise rows with equal values can appear on two pages or on none. A large OFFSET is slow (the skipped rows are still read); for long lists use keyset pagination: WHERE id > 1200 ORDER BY id LIMIT 20.

6. 

In what order are the clauses of a SELECT query executed, and what follows from it?

Logically, the clauses are processed not in the order they are written but in this order:

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

  • A column alias is assigned at the SELECT step, so it is not visible in WHERE yet (ERROR 1054: Unknown column ... in 'where clause'), but it is available in ORDER BY.
  • There are no groups at the WHERE step, so aggregate functions are not allowed there (ERROR 1111); conditions on them go into HAVING.
  • LIMIT is applied last — to the already sorted result.

This is the logical order, which decides which names are visible in each clause. The optimizer may execute the query physically in a different way (for example, read rows already sorted through an index), but the result always matches the logical order. MySQL relaxes the standard by allowing SELECT aliases in GROUP BY and HAVING; in WHERE, aliases do not work in any database.

Page 1 of 1