SQL Quiz: Select Queries
10 questions · exam conditions
0:00
Select QueriesQuestion 1 of 10

The employees table contains employee_id, first_name, last_name, and department_id. An export must contain exactly three columns, in this order: the employee's last name labeled family_name, the employee ID labeled employee_key, and the department ID labeled org_unit.

Which query produces the required export?

SELECT last_name AS family_name, department_id AS org_unit, employee_id AS employee_key FROM employees;
SELECT family_name AS last_name, employee_key AS employee_id, org_unit AS department_id FROM employees;
SELECT last_name AS family_name, employee_id AS employee_key, department_id AS org_unit FROM employees;
SELECT employee_id AS employee_key, last_name AS family_name, department_id AS org_unit FROM employees;
← Back to quizzes

SQL Quiz

SQL Quiz: Select Queries

Practice Select Queries in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Select Queries, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

The employees table contains employee_id, first_name, last_name, and department_id. An export must contain exactly three columns, in this order: the employee's last name labeled family_name, the employee ID labeled employee_key, and the department ID labeled org_unit.

Which query produces the required export?

  1. SELECT last_name AS family_name, department_id AS org_unit, employee_id AS employee_key FROM employees;
  2. SELECT family_name AS last_name, employee_key AS employee_id, org_unit AS department_id FROM employees;
  3. SELECT last_name AS family_name, employee_id AS employee_key, department_id AS org_unit FROM employees; (correct answer)
  4. SELECT employee_id AS employee_key, last_name AS family_name, department_id AS org_unit FROM employees;
Explanation: When working with column aliases in SQL, two things matter: using the correct source column names and listing them in the correct order. The AS keyword renames a column in the output, following the pattern source_column AS alias. The alias is what appears in the result set — it does not exist in the actual table. Option C is correct because it selects last_name, employee_id, and department_id — all valid column names from the employees table — and assigns them the required aliases family_name, employee_key, and org_unit, in exactly the specified order: family name first, employee key second, org unit third. Option A selects the columns in the wrong order. It places department_id AS org_unit second and employee_id AS employee_key third, but the requirement says employee ID must come second and department ID third. The aliases are correct, but column order matters for exports. Option B reverses the AS logic entirely. It writes family_name AS last_name, treating the alias as the source column — but family_name doesn't exist in the table. This query would throw an error because you cannot select a column that doesn't exist. Option D lists employee_id first, but the export requires last_name (aliased as family_name) to appear first. The order of columns in the SELECT clause directly determines the order in the output. A reliable tip: always read alias questions by checking (1) are the source column names real table columns, and (2) does the order in SELECT match the required output order?

Question 2

The order_lines table contains quantity, unit_price, and discount_rate, all of which are non-null. Using standard SQL, a query must return exactly two calculated columns: subtotal and net_amount. A select-list alias cannot be referenced by another expression in the same select list.

Which query satisfies the requirement?

  1. SELECT quantity * unit_price AS subtotal, quantity * unit_price * (1 - discount_rate) AS net_amount FROM order_lines; (correct answer)
  2. SELECT quantity * unit_price AS subtotal, subtotal * (1 - discount_rate) AS net_amount FROM order_lines;
  3. SELECT quantity * unit_price AS subtotal, quantity * unit_price * (1 + discount_rate) AS net_amount FROM order_lines;
  4. SELECT quantity, quantity * unit_price AS subtotal, quantity * unit_price * (1 - discount_rate) AS net_amount FROM order_lines;
Explanation: When writing a SELECT list in standard SQL, every expression must reference actual column names from the source table — you cannot use an alias defined earlier in the same SELECT list as input to another expression. This rule catches many students off guard because it feels natural to reuse a calculated value you just named. Option A handles this correctly: it calculates quantity * unit_price twice — once labeled subtotal, and again multiplied by (1 - discount_rate) to produce net_amount. Repeating the base expression is the standard SQL way to work around the alias restriction. The formula \text{net_amount} = \text{quantity} \times \text{unit_price} \times (1 - \text{discount_rate}) correctly applies the discount as a reduction factor. Option B fails because it tries to reference subtotal — an alias — inside the same SELECT list to compute net_amount. Standard SQL does not allow this; the alias isn't yet defined when the SELECT list is evaluated. Option C produces a mathematically wrong result. Multiplying by (1 + discount_rate) increases the price rather than applying a discount, so net_amount would be larger than subtotal. That's the opposite of the intended behavior. Option D returns three columns — quantity, subtotal, and net_amount — but the question explicitly requires exactly two calculated columns. Including raw quantity violates the output specification. A good study habit: whenever you need to reuse a calculated value in the same SELECT, just repeat the full expression. If you want to avoid repetition, wrap it in a subquery or CTE so the alias becomes an actual column in the outer query.

Question 3

The invoice_lines table contains invoice_id, quantity, unit_price, and discount_rate. The discount rate is stored as a decimal fraction and can be null, in which case it must be treated as zero. A result must contain exactly invoice_id and a calculated column named net_total.

Which query returns the required columns with the correct null handling?

  1. SELECT invoice_id, quantity * unit_price * (1 - discount_rate) AS net_total FROM invoice_lines;
  2. SELECT invoice_id, quantity * unit_price * (1 - COALESCE(discount_rate, 1)) AS net_total FROM invoice_lines;
  3. SELECT invoice_id, quantity, unit_price, quantity * unit_price * (1 - COALESCE(discount_rate, 0)) AS net_total FROM invoice_lines;
  4. SELECT invoice_id, quantity * unit_price * (1 - COALESCE(discount_rate, 0)) AS net_total FROM invoice_lines; (correct answer)
Explanation: When a SQL column can contain NULL, any arithmetic operation involving that NULL will itself return NULL — silently breaking your calculations. This question tests whether you can spot that trap and apply the right fix using COALESCE. The key formula here is: \text{net_total} = \text{quantity} \times \text{unit_price} \times (1 - \text{discount_rate}) When discount_rate is NULL, treating it as zero means no discount is applied, so the multiplier becomes (1−0)=1(1 - 0) = 1. The correct fix is COALESCE(discount_rate, 0), which substitutes 0 whenever discount_rate is NULL. D applies exactly this logic and selects only the two required columns — invoice_id and net_total — making it the correct answer. A is the most tempting trap: the formula looks right, but it skips COALESCE entirely. Any row with a NULL discount rate will produce a NULL net_total instead of the full price. B uses COALESCE(discount_rate, 1), which substitutes 1 for NULL. That turns the multiplier into (1−1)=0(1 - 1) = 0, wiping out the entire line total — the opposite of the intended behavior. C gets the COALESCE logic right but selects four columns (invoice_id, quantity, unit_price, and net_total). The problem explicitly requires exactly invoice_id and net_total, so this violates the output specification. Study tip: When you see nullable numeric columns in SQL, immediately ask yourself whether arithmetic on them needs COALESCE. Also read column requirements carefully — extra columns in the SELECT list can disqualify an otherwise correct query.

Question 4

The assignments table can contain repeated rows with the same department_id and job_code. A query must return one row for each distinct combination of those two columns while keeping them as two separate output columns.

Which query meets the requirement?

  1. SELECT department_id, job_code FROM assignments;
  2. SELECT DISTINCT department_id FROM assignments;
  3. SELECT DISTINCT department_id, job_code FROM assignments; (correct answer)
  4. SELECT DISTINCT department_id, DISTINCT job_code FROM assignments;
Explanation: When you need to eliminate duplicate rows in SQL, the DISTINCT keyword is your tool — but understanding where it goes and how it works is critical. DISTINCT applies to the entire row of selected columns, not to individual columns independently. SELECT DISTINCT department_id, job_code FROM assignments — option C — is the correct approach. It tells the database to return only unique combinations of both columns together. If department 10 with job code "ENG" appears five times in the table, it appears exactly once in the results. Both columns remain as separate output columns, satisfying the requirement precisely. Option A has no DISTINCT at all, so every row — duplicates included — is returned. Option B uses DISTINCT but only selects department_id, which means you lose job_code entirely from the output. You'd get unique departments, but not the distinct pairs the question asks for. Option D is the most tempting trap: it tries to apply DISTINCT twice, once per column. This is invalid SQL syntax — you cannot write DISTINCT before individual column names separately. DISTINCT is a single modifier that belongs right after SELECT and governs all selected columns at once. A useful rule of thumb: think of DISTINCT as a filter on the whole result row, not a property of any single column. When a question asks for unique combinations across multiple columns, place DISTINCT once after SELECT and list all the columns you need. Any syntax that repeats DISTINCT per column should immediately signal an error.

Question 5

The view active_customers currently contains customer_id, customer_name, and status. A service contract requires exactly two output columns, customer_id followed by status. Additional columns may be added to the view later, and they must not appear in the service output.

Which query satisfies the contract and remains unaffected if the view gains more columns?

  1. SELECT customer_id, status FROM active_customers; (correct answer)
  2. SELECT * FROM active_customers;
  3. SELECT customer_id, customer_name, status FROM active_customers;
  4. SELECT status, customer_id FROM active_customers;
Explanation: When a query must satisfy a strict column contract — specific columns, specific order, immune to future schema changes — you need to think about explicit column selection. The question tests whether you understand the risks of SELECT * and implicit assumptions about a view's structure. Choosing A (SELECT customer_id, status FROM active_customers) is the correct approach because it explicitly names exactly the two required columns in the exact required order. If the view later gains columns like email or phone, this query ignores them entirely — the output never changes. The contract stays intact. B (SELECT *) is the classic trap here. It returns every column the view exposes, meaning today you get three columns, and tomorrow — after the view is updated — you might get five. The service contract specifies exactly two columns, so SELECT * is a maintenance time bomb that breaks the contract silently. C (SELECT customer_id, customer_name, status) explicitly returns three columns, which immediately violates the two-column requirement. Even though it avoids SELECT *, hardcoding customer_name defeats the purpose — the contract never asked for it. D (SELECT status, customer_id) names the correct two columns but reverses their order. Since the contract requires customer_id followed by status, swapping the sequence violates the specification. Column order matters in structured service outputs, especially when downstream systems rely on positional mapping. The takeaway: whenever you see a contract or interface that demands a fixed, predictable result set, always name your columns explicitly and in order. SELECT * is convenient for exploration but dangerous in production code.

Question 6

The contacts table contains contact_id, mobile_phone, and home_phone. A directory must return exactly contact_id and preferred_phone. For each contact, preferred_phone must be the mobile number when it is non-null; otherwise, it must be the home number.

Which query returns the required columns and applies the specified preference order?

  1. SELECT contact_id, COALESCE(home_phone, mobile_phone) AS preferred_phone FROM contacts;
  2. SELECT contact_id, mobile_phone || home_phone AS preferred_phone FROM contacts;
  3. SELECT contact_id, COALESCE(mobile_phone, home_phone) AS preferred_phone FROM contacts; (correct answer)
  4. SELECT contact_id, mobile_phone, home_phone AS preferred_phone FROM contacts;
Explanation: When a question asks you to select a value from one column "if available," otherwise fall back to another, that's a signal to reach for COALESCE. This function accepts a list of arguments and returns the first non-null value it encounters, making it the perfect tool for priority-based fallback logic. Here, the rule is clear: prefer mobile_phone, fall back to home_phone. Option C — COALESCE(mobile_phone, home_phone) — does exactly that. SQL evaluates the arguments left to right, so if mobile_phone is non-null, it's returned immediately; if it's null, SQL moves to home_phone. The alias AS preferred_phone satisfies the column naming requirement, and selecting only contact_id and preferred_phone matches the exact output specification. Option A uses COALESCE(home_phone, mobile_phone) — the arguments are reversed, so it would prioritize the home number over mobile, which is the opposite of what the requirement states. This is a classic trap: the function is correct, but the argument order is wrong. Option B uses the concatenation operator ||, which would join both phone strings together into one combined value rather than selecting between them. This produces garbage output like "555-1234555-5678" instead of a single preferred number. Option D selects mobile_phone as a separate column and aliases only home_phone as preferred_phone, returning three columns instead of two and ignoring the fallback logic entirely. A handy memory aid: think of COALESCE as a ranked list — first argument wins if it's not null. Always write your highest-priority column first.

Question 7

The orders table has columns order_id and customer_id. The customers table has columns customer_id and customer_name. A report uses a LEFT JOIN so that orders without a matching customer remain in the result. It must return exactly order_id, the order row's customer_id labeled ordering_customer_id, and the matched customer name labeled buyer_name.

Which query returns the required columns and preserves the order row's customer ID when no customer matches?

  1. SELECT o.order_id, customer_id AS ordering_customer_id, c.customer_name AS buyer_name FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.customer_id;
  2. SELECT o.order_id, o.customer_id AS ordering_customer_id, c.customer_name AS buyer_name FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.customer_id; (correct answer)
  3. SELECT o.order_id, c.customer_id AS ordering_customer_id, c.customer_name AS buyer_name FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.customer_id;
  4. SELECT o.customer_id AS order_id, o.order_id AS ordering_customer_id, c.customer_name AS buyer_name FROM orders AS o LEFT JOIN customers AS c ON o.customer_id = c.customer_id;
Explanation: When writing a LEFT JOIN query, two skills are tested simultaneously: table-qualifying your column references to avoid ambiguity, and understanding what a LEFT JOIN actually preserves. A LEFT JOIN keeps every row from the left table (orders) even when no match exists in the right table (customers) — meaning any column pulled from customers will be NULL for unmatched rows. That's exactly why B is correct. It selects o.customer_id AS ordering_customer_id, explicitly pulling customer_id from the orders table using the o. prefix. Even when no customer matches and c.customer_id would be NULL, o.customer_id still holds its original value. The alias ordering_customer_id satisfies the naming requirement, and c.customer_name AS buyer_name rounds out the required columns. A fails because customer_id is written without a table qualifier. Since both orders and customers have a customer_id column, this creates an ambiguous column reference — most SQL engines will throw an error rather than guess which table you mean. C uses c.customer_id AS ordering_customer_id, pulling the ID from the customers table instead of orders. For any unmatched order row, c.customer_id is NULL, so you lose the very value the report needs to preserve — the original order's customer ID. D swaps the aliases entirely, labeling o.customer_id as order_id and o.order_id as ordering_customer_id. The output columns exist but are mislabeled, producing incorrect results. Study tip: Whenever a join involves columns with the same name in both tables, always table-qualify every column reference — it prevents ambiguity errors and ensures you're reading from the intended source.

Question 8

The employees table contains a salary column but does not contain base_salary or proposed_salary. A planning extract must initially show the current salary twice, with the two output columns labeled base_salary and proposed_salary.

Which query creates the required two-column extract?

  1. SELECT salary AS base_salary FROM employees;
  2. SELECT salary AS base_salary, salary AS proposed_salary FROM employees; (correct answer)
  3. SELECT salary, salary FROM employees;
  4. SELECT salary AS base_salary, proposed_salary AS salary FROM employees;
Explanation: When working with SQL's SELECT clause, you can reference the same source column multiple times and assign each instance a different alias using the AS keyword. This is exactly what the question tests — your ability to reshape output columns without needing separate source columns. The requirement calls for two output columns, base_salary and proposed_salary, both drawing from the single salary column. Option B accomplishes this perfectly: SELECT salary AS base_salary, salary AS proposed_salary FROM employees; lists salary twice, aliasing the first instance as base_salary and the second as proposed_salary. SQL treats each comma-separated expression independently, so repeating a column name is completely valid. Option A only selects salary once, aliased as base_salary. It produces a single-column result, missing proposed_salary entirely. Option C selects salary twice but applies no aliases — the output columns would both be labeled salary, failing the labeling requirement. Option D is the trickiest distractor: it references proposed_salary as if it were a real column in the table, but the passage explicitly states that column does not exist. This query would throw a "column not found" error at runtime. A useful pattern to remember: in SQL, column aliases defined with AS only affect the output label — they don't create new data or affect what you can reference elsewhere in the same SELECT list. You always reference source column names (like salary), and you can do so as many times as you need. On exam questions involving column aliasing, check whether the query references real table columns versus invented names that don't exist in the schema.

Question 9

Using standard SQL, the table sales.orders is assigned the correlation name o in the FROM clause. A query must return only order_id and customer_id, retaining those original output labels.

Which query correctly references the columns after the table alias has been introduced?

  1. SELECT sales.orders.order_id, sales.orders.customer_id FROM sales.orders AS o;
  2. SELECT orders.order_id, orders.customer_id FROM sales.orders AS o;
  3. SELECT o.order_id AS "o.order_id", o.customer_id AS "o.customer_id" FROM sales.orders AS o;
  4. SELECT o.order_id, o.customer_id FROM sales.orders AS o; (correct answer)
Explanation: When you assign a table alias (also called a correlation name) in the FROM clause using AS, that alias replaces the original table reference for the rest of the query. This is the core concept being tested: once an alias is defined, you must use it — and only it — to qualify column names. Option D, SELECT o.order_id, o.customer_id FROM sales.orders AS o;, is correct because it uses the alias o to prefix the columns, produces output labeled order_id and customer_id (the natural column names, no renaming), and is clean standard SQL. Option A fails because once o is declared as the alias, referencing sales.orders.order_id is illegal — the fully-qualified schema-table path is no longer a valid qualifier within that query scope. Most SQL engines will throw an error here. Option B partially improves on A but still references orders.order_id using the table name rather than the alias o. After AS o is introduced, orders is no longer a recognized qualifier — only o is. Option C is a clever trap. It uses the alias correctly (o.order_id) but then adds AS "o.order_id", which renames the output column to the literal string o.order_id (including the dot). The question requires the output label to remain order_id, so this violates the requirement. A quick rule of thumb: the alias owns the namespace. Once defined, use it exclusively to qualify columns, and never add unnecessary AS labels if the question asks you to preserve the original column names.

Question 10

Using standard SQL, the employees table contains employee_name and hire_date. A report must return those two values with the exact case-sensitive column labels Employee Name and Start Date, including the spaces.

Which query assigns the required output labels using standard SQL delimited identifiers?

  1. SELECT employee_name AS Employee Name, hire_date AS Start Date FROM employees;
  2. SELECT employee_name AS 'Employee Name', hire_date AS 'Start Date' FROM employees;
  3. SELECT employee_name AS [Employee Name], hire_date AS [Start Date] FROM employees;
  4. SELECT employee_name AS "Employee Name", hire_date AS "Start Date" FROM employees; (correct answer)
Explanation: When you need column aliases that contain spaces or other special characters, SQL requires a way to clearly delimit — that is, mark the boundaries of — the alias so the parser doesn't misread it. This is the concept of delimited identifiers, and the ANSI/ISO SQL standard defines exactly one mechanism for them: double quotes. Double-quoting an alias like "Employee Name" tells any standard-compliant database engine to treat everything inside the quotes as a single identifier, spaces included, preserving exact case. That's why D is correct — it uses "Employee Name" and "Start Date", which satisfies both the spacing requirement and the standard. A fails immediately because it writes the alias as bare words with no delimiters at all. The parser sees Employee as the alias and Name as an unexpected token, producing a syntax error before the query even runs. B uses single quotes, which in standard SQL denote a string literal, not an identifier. Some databases (like MySQL in certain modes) accept this as an alias, but it is not standard SQL behavior — on stricter engines it either errors or behaves unexpectedly. The question explicitly asks for standard SQL. C uses square brackets ([Employee Name]), which is the delimiting syntax specific to T-SQL (Microsoft SQL Server and Sybase). It is a vendor extension, not part of the ANSI standard, so it fails the "standard SQL" requirement. A useful memory anchor: double quotes = standard SQL identifiers; single quotes = string literals. Whenever an exam question specifies "standard SQL" or "ANSI SQL," default to double-quoted identifiers for aliases and object names.