Quiz: SQL SELECT Statement: Querying Data from a Table
Study the lesson first: 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?
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:
WHEREafterGROUP BYis a syntax error. - The column list may contain expressions:
SELECT product, lbs * price FROM sales;. Arithmetic withNULLgivesNULL.FROMcan 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 largeTEXT), and code that reads aResultSetby 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?
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;givesERROR 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), becauseWHEREruns beforeSELECT. It always works inORDER BY, and inGROUP BYandHAVINGas a MySQL extension (not in standard SQL or PostgreSQL).
3. Which operators are used in WHERE: comparison, AND, OR, NOT, IN, BETWEEN and LIKE?
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 creads asa 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 aDATETIMEcolumn 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 aPreparedStatement, 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?
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
NULLare in neitherprice < 3norprice >= 3. If you need them, say so explicitly:price < 3 OR price IS NULL. NOT INwith a list or subquery that containsNULLalways returns an empty result — filter outNULLor useNOT EXISTS.- The MySQL operator
<=>is NULL-safe equality:NULL <=> NULLis true. - For
DISTINCT, allNULLs count as one value:SELECT DISTINCT price FROM sales;returns seven rows — six prices and oneNULL. - In ascending order MySQL puts
NULLs first (PostgreSQL and Oracle put them last); to move them to the end, writeORDER 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?
How do DISTINCT, ORDER BY and LIMIT with OFFSET work in MySQL, and how do you paginate results correctly?
- DISTINCT goes right after
SELECTand removes duplicate rows across all listed columns:SELECT DISTINCT productgives 4 names, whileSELECT DISTINCT shop, productreturns all 8 rows because no pair repeats.DISTINCTdoes not guarantee row order. - ORDER BY sorts in ascending order (
ASC) by default;DESCis 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 theSELECTlist and by an alias.ORDER BY 3works but silently changes meaning when the column list changes. WithoutORDER BYthe 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, 20means 20 rows starting from the 11th. There is noSELECT TOPin 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?
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
SELECTstep, so it is not visible inWHEREyet (ERROR 1054: Unknown column ... in 'where clause'), but it is available inORDER BY. - There are no groups at the
WHEREstep, so aggregate functions are not allowed there (ERROR 1111); conditions on them go intoHAVING. LIMITis 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.