SQL Quiz: Table Aliases
10 questions · exam conditions
0:00
Table AliasesQuestion 1 of 10

The relevant columns are orders(order_id, customer_id, product_id), customers(customer_id, customer_name), and products(product_id, product_name). A report must list each order with its customer and product.

Which query uses aliases consistently and joins each foreign key to the matching table's key?

SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id;
SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.product_id = c.customer_id JOIN products p ON o.customer_id = p.product_id;
SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON c.customer_id = p.product_id;
SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = p.product_id JOIN products p ON o.product_id = c.customer_id;
← Back to quizzes

SQL Quiz

SQL Quiz: Table Aliases

Practice Table Aliases 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 Table Aliases, 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 relevant columns are orders(order_id, customer_id, product_id), customers(customer_id, customer_name), and products(product_id, product_name). A report must list each order with its customer and product.

Which query uses aliases consistently and joins each foreign key to the matching table's key?

  1. SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON o.product_id = p.product_id; (correct answer)
  2. SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.product_id = c.customer_id JOIN products p ON o.customer_id = p.product_id;
  3. SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN products p ON c.customer_id = p.product_id;
  4. SELECT o.order_id, c.customer_name, p.product_name FROM orders o JOIN customers c ON o.customer_id = p.product_id JOIN products p ON o.product_id = c.customer_id;
Explanation: When writing multi-table JOIN queries, your primary job is to match each foreign key to its corresponding primary key in the correct table. Think of it like connecting puzzle pieces — orders.customer_id belongs with customers.customer_id, and orders.product_id belongs with products.product_id. Mixing these up produces logically broken joins that return wrong or empty results. Option A is correct because every JOIN condition connects the right foreign key to the right table. o.customer_id = c.customer_id links orders to the customers table, and o.product_id = p.product_id links orders to the products table. The aliases o, c, and p are also applied consistently throughout the SELECT and ON clauses. Option B swaps the join conditions deliberately — it joins orders.product_id to customers.customer_id and orders.customer_id to products.product_id. These are logically backwards; you'd be asking the database to match product IDs against customer IDs, which are unrelated keys. Option C starts correctly by joining orders to customers, but then joins c.customer_id = p.product_id — connecting a customer's ID to a product's ID. This is a type mismatch; there's no meaningful relationship between those two columns, and the products join should always originate from orders.product_id. Option D is especially tricky because it references alias p before p is even defined in the FROM clause, making it invalid SQL in most databases — and the join conditions are also swapped. Study tip: Always trace each JOIN condition back to the source table. If you're joining products, the condition must reference product_id on both sides — never a mismatched column like customer_id.

Question 2

The orders table contains billing_address_id and shipping_address_id, both referencing addresses.address_id. A maintenance query must show the billing city and shipping city for each order.

Which query uses role-based table aliases to make the two references to addresses clear and joins them correctly?

  1. SELECT o.order_id, ba.city AS billing_city, sa.city AS shipping_city FROM orders o JOIN addresses ba ON o.shipping_address_id = ba.address_id JOIN addresses sa ON o.billing_address_id = sa.address_id;
  2. SELECT o.order_id, a.city AS billing_city, a.city AS shipping_city FROM orders o JOIN addresses a ON o.billing_address_id = a.address_id JOIN addresses a ON o.shipping_address_id = a.address_id;
  3. SELECT o.order_id, ba.city AS billing_city, sa.city AS shipping_city FROM orders o JOIN addresses ba ON o.billing_address_id = ba.address_id JOIN addresses sa ON o.shipping_address_id = sa.address_id; (correct answer)
  4. SELECT o.order_id, x.city AS billing_city, y.city AS shipping_city FROM orders o JOIN addresses x ON o.billing_address_id = y.address_id JOIN addresses y ON o.shipping_address_id = x.address_id;
Explanation: When a table appears twice in a query — each instance serving a different logical role — you must join it twice under distinct aliases that clearly reflect each role. This pattern is called a self-referencing join, and the key is making sure each alias connects to the right foreign key. In this scenario, orders has two address columns: billing_address_id and shipping_address_id. To retrieve both cities, you need two separate joins to addresses, each with a meaningful alias. The correct approach (C) uses ba for "billing address" and sa for "shipping address," then wires each alias to its matching foreign key: ba joins on billing_address_id and sa joins on shipping_address_id. The SELECT then pulls ba.city as billing_city and sa.city as shipping_city — everything is consistent and unambiguous. Answer A has the aliases crossed: ba is joined on shipping_address_id and sa on billing_address_id, so despite the label billing_city, you'd actually retrieve the shipping city and vice versa — a logic error, not a syntax one. Answer B tries to reuse the same alias a for both joins, which is illegal in SQL. You cannot define the same alias twice in one query; the database engine has no way to distinguish the two instances. Answer D introduces aliases x and y but crosses the ON conditions — x joins on y.address_id and y joins on x.address_id, referencing each other before they're defined, which is invalid and logically broken. Study tip: Always match each alias to its foreign key column immediately in the ON clause, and choose alias names that reflect the role (e.g., ba/sa) — this prevents the swap trap that makes A tempting.

Question 3

Consider this query:

SELECT c.customer_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders c WHERE c.customer_id = c.customer_id AND c.total > 500 );

The intention is to return customers who have at least one order over 500.

Which rewrite removes alias shadowing and correctly correlates each qualifying order with the outer customer?

  1. SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total > 500); (correct answer)
  2. SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE c.customer_id = c.customer_id AND o.total > 500);
  3. SELECT c.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = o.customer_id AND o.total > 500);
  4. SELECT o.customer_id FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total > 500);
Explanation: When working with correlated subqueries, you need to watch for alias shadowing — a subtle bug where the same alias is reused in both the outer and inner query, causing the inner query to reference itself rather than the outer table. In the original query, orders is aliased as c, which shadows the outer customers c. The condition c.customer_id = c.customer_id then compares the orders table to itself, always returning true and breaking the correlation entirely. The fix requires giving orders a distinct alias — conventionally o — so the join condition can properly link o.customer_id (the order's customer) to c.customer_id (the outer customer row). That's exactly what A does: FROM orders o WHERE o.customer_id = c.customer_id AND o.total > 500. The o prefix unambiguously refers to the subquery's table, while c correctly reaches back to the outer customers table. This is the proper correlated subquery pattern. B is wrong because even though it aliases orders as o, the join condition still reads c.customer_id = c.customer_id — comparing the outer customer to itself, not to the order. The shadowing bug in the condition persists. C uses o.customer_id = o.customer_id, which is a self-comparison within the subquery. This is always true, meaning the EXISTS check passes for every customer regardless of whether they have matching orders. D is tempting but incorrect — it selects o.customer_id from the outer query, but o is only defined inside the subquery. That reference is out of scope and will produce an error. When you spot a subquery, immediately check that every alias is unique and that correlation conditions reference one table from each scope.

Question 4

The following query combines current and archived customer names:

SELECT c.customer_name FROM customers c WHERE c.active_flag = 'Y' UNION ALL SELECT c.customer_name FROM archived_customers WHERE archived_customers.active_flag = 'Y';

Which revision correctly accounts for the scope of a table alias in the two query blocks?

  1. SELECT c.customer_name FROM customers c WHERE c.active_flag = 'Y' UNION ALL SELECT c.customer_name FROM archived_customers a WHERE c.active_flag = 'Y';
  2. SELECT c.customer_name FROM customers c WHERE c.active_flag = 'Y' UNION ALL SELECT a.customer_name FROM archived_customers a WHERE a.active_flag = 'Y'; (correct answer)
  3. SELECT c.customer_name FROM customers c WHERE c.active_flag = 'Y' UNION ALL SELECT a.customer_name FROM archived_customers WHERE a.active_flag = 'Y';
  4. SELECT c.customer_name FROM customers c WHERE c.active_flag = 'Y' UNION ALL SELECT archived_customers.customer_name FROM archived_customers a WHERE a.active_flag = 'Y';
Explanation: When working with UNION ALL queries, one of the most important scoping rules to remember is that table aliases are local to the query block in which they are defined. Each SELECT statement separated by UNION ALL is its own independent block — an alias defined in the first block does not exist in the second, and vice versa. In the original query, the second block references archived_customers without an alias, but then inconsistently qualifies columns using the unrelated alias c from the first block. This is the bug being fixed. Option B is the correct revision. It defines alias c in the first block and uses c consistently there, then defines a separate alias a for archived_customers in the second block and uses a consistently throughout that block. Each alias lives and works entirely within its own query block — clean and correct. Option A is wrong because the second block references c.active_flag in its WHERE clause, but c was defined in the first block and is out of scope there. This would cause a runtime error. Option C introduces a partial fix: it correctly uses a.customer_name in the SELECT, but then references a.active_flag in the WHERE clause without ever defining a as an alias (the FROM clause just says archived_customers with no alias). This is self-contradictory. Option D mixes unaliased and aliased references in the same block — archived_customers.customer_name in the SELECT but a.active_flag in the WHERE — making the code inconsistent and potentially ambiguous. A reliable rule of thumb: treat each query block in a UNION as a completely separate query. Define and use aliases independently in each one.

Question 5

The intended relationships are orders.customer_id = customers.customer_id and payments.order_id = orders.order_id. A developer wrote:

SELECT o.order_id, c.customer_name, p.amount FROM orders o JOIN customers c ON p.order_id = o.order_id JOIN payments p ON o.customer_id = c.customer_id;

Which rewrite introduces each alias before it is used and places each relationship in the appropriate join condition?

  1. SELECT o.order_id, c.customer_name, p.amount FROM orders o JOIN payments p ON p.order_id = c.customer_id JOIN customers c ON o.customer_id = o.order_id;
  2. SELECT o.order_id, c.customer_name, p.amount FROM orders o JOIN customers c ON p.order_id = o.order_id JOIN payments p ON o.customer_id = c.customer_id;
  3. SELECT o.order_id, c.customer_name, p.amount FROM orders o JOIN payments p ON o.customer_id = c.customer_id JOIN customers c ON p.order_id = o.order_id;
  4. SELECT o.order_id, c.customer_name, p.amount FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN payments p ON p.order_id = o.order_id; (correct answer)
Explanation: When writing multi-table JOINs, think of SQL as reading left to right — an alias only becomes available after its table is introduced in the FROM or JOIN clause. Using an alias before its table appears is like referencing a variable before declaring it. Additionally, each JOIN condition should express the actual relationship between the two tables being joined at that step. The intended relationships are orders.customer_id = customers.customer_id and payments.order_id = orders.order_id. Answer D gets this exactly right: it starts with orders o, then joins customers c using o.customer_id = c.customer_id (both aliases already defined), then joins payments p using p.order_id = o.order_id (again, both aliases available). Every alias appears before it's used, and every condition matches its logical relationship. Answer A fails on both counts — it tries to use c.customer_id in the payments join before customers has been introduced, and the conditions are swapped to nonsensical pairings. Answer B introduces customers c first but then immediately uses p.order_id in that join condition before payments p has been defined — a forward-reference error. Answer C joins payments p second but places o.customer_id = c.customer_id as its condition (the customer relationship, not the payment-to-order relationship), while also referencing c before customers is introduced. A practical tip: always trace your FROM/JOIN sequence top to bottom and verify that every alias on both sides of each ON condition has already been introduced above it. If you see an alias used before its table appears in the query, that's an immediate red flag.

Question 6

The customers and orders tables both contain a column named status. A report must return active customers who have open orders.

Which query uses table aliases to express the required filters without creating an ambiguous column reference?

  1. SELECT c.customer_id, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE status = 'ACTIVE' AND o.status = 'OPEN';
  2. SELECT c.customer_id, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.status = 'ACTIVE' AND status = 'OPEN';
  3. SELECT c.customer_id, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE c.status = 'ACTIVE' AND o.status = 'OPEN'; (correct answer)
  4. SELECT c.customer_id, o.order_id FROM customers c JOIN orders o ON c.customer_id = o.customer_id WHERE o.status = 'ACTIVE' AND c.status = 'OPEN';
Explanation: When two joined tables share a column name, SQL needs you to be explicit about which table's column you mean — otherwise you get an ambiguous column reference error. Any time you JOIN tables that have overlapping column names, qualifying every column reference with a table alias (or full table name) is essential. The query in C does this correctly: c.status = 'ACTIVE' targets the status column in the customers table (aliased as c), and o.status = 'OPEN' targets the status column in the orders table (aliased as o). Both filters are fully qualified, both are logically correct for the business requirement, and there is zero ambiguity for the database engine. A is flawed because the first condition uses bare status — no alias prefix. Since both tables have a status column, the database cannot determine which table you mean, and the query will throw an ambiguous column error. B makes the same mistake in reverse: c.status = 'ACTIVE' is fine, but the second condition uses bare status = 'OPEN' without qualification, leaving the engine unable to resolve which table's status column to check. D has full qualification (no ambiguity errors), but the filters are applied to the wrong tables — it checks o.status = 'ACTIVE' (orders flagged active?) and c.status = 'OPEN' (customers flagged open?), which is logically backwards and won't return the intended results. A good study habit: whenever you write a JOIN, immediately prefix every column in your WHERE clause with an alias. This prevents ambiguity errors and makes your intent self-documenting.

Question 7

A report calculates total spending per customer in a derived table:

SELECT orders.customer_id, orders.total_spent FROM ( SELECT customer_id, SUM(order_total) AS total_spent FROM orders GROUP BY customer_id ) d WHERE orders.total_spent > 1000;

Which revision correctly references the columns exposed by the aliased derived table?

  1. SELECT d.customer_id, d.total_spent FROM (SELECT customer_id, SUM(order_total) AS total_spent FROM orders GROUP BY customer_id) d WHERE d.total_spent > 1000; (correct answer)
  2. SELECT orders.customer_id, d.total_spent FROM (SELECT customer_id, SUM(order_total) AS total_spent FROM orders GROUP BY customer_id) d WHERE orders.total_spent > 1000;
  3. SELECT d.customer_id, orders.total_spent FROM (SELECT customer_id, SUM(order_total) AS total_spent FROM orders GROUP BY customer_id) d WHERE d.total_spent > 1000;
  4. SELECT d.customer_id, d.order_total FROM (SELECT customer_id, SUM(order_total) AS total_spent FROM orders GROUP BY customer_id) d WHERE d.order_total > 1000;
Explanation: When you wrap a subquery in parentheses and give it an alias — like (...) d — that alias becomes the only valid table reference for the outer query. The original table name (orders) is completely hidden; the outer query sees only the columns the subquery exposes under the alias d. In this case, the subquery exposes exactly two columns: customer_id and total_spent (note: SUM(order_total) was renamed via AS total_spent, so order_total no longer exists in the derived table's output). The outer query must reference these as d.customer_id and d.total_spent. A is correct because every reference uses the alias d consistently — d.customer_id and d.total_spent in the SELECT, and d.total_spent in the WHERE clause. This is the only version that correctly treats d as the sole available table. B is wrong because it mixes orders.customer_id (invalid — orders isn't a named source in the outer query) with d.total_spent, and the WHERE clause still uses orders.total_spent. You can't reach back into the subquery's source table by name. C is wrong for the same reason in reverse: it correctly uses d.customer_id but then references orders.total_spent in the SELECT, which doesn't exist in the outer scope. D is wrong because it references d.order_total, which was never exposed by the subquery. The subquery aliased that aggregation as total_spent, so order_total simply doesn't exist at the outer level. A quick rule of thumb: once you alias a derived table, only that alias exists in the outer query — always check that every column reference matches what the subquery actually outputs.

Question 8

Consider this query:

SELECT c.customer_id, o.order_id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );

The report only needs one row per customer who has an order.

Which change corrects the alias-scope problem while preserving the stated report requirement?

  1. Change the inner alias to c so that order_id and customer_id share the same visible qualifier.
  2. Change the outer select list to SELECT c.customer_id, orders.order_id and leave the subquery unchanged.
  3. Move the alias declaration to FROM customers c, orders o while retaining the existing EXISTS subquery.
  4. Change the outer select list to SELECT c.customer_id and leave the correlated EXISTS subquery unchanged. (correct answer)
Explanation: When working with correlated subqueries, one of the most important scope rules in SQL is that aliases defined inside a subquery are invisible to the outer query, and vice versa. The outer query cannot reference columns or aliases that only exist within the subquery's scope. The original query declares alias o inside the EXISTS subquery, but the outer SELECT tries to use o.order_id — a column that lives only inside the subquery's scope. That's the alias-scope bug. The fix isn't to somehow expose the inner alias outward; it's to remove the reference that doesn't belong. Since the report requirement is simply one row per customer who has an order, you only need c.customer_id in the outer select list. The EXISTS subquery already handles the filtering logic perfectly. D is the correct answer — drop o.order_id from the outer select, and the query becomes valid while satisfying the requirement. A is wrong because renaming the inner alias to c would create a naming conflict with the outer customers c, and it still wouldn't make order_id accessible in the outer SELECT. B is wrong because orders.order_id without a proper JOIN in the outer FROM clause doesn't make that column available — orders isn't part of the outer query's table list. C is wrong because adding orders o to the outer FROM clause without a JOIN condition creates a Cartesian product, returning multiple rows per customer and violating the report requirement. The key study tip: whenever you see a column referenced in an outer SELECT with a qualifier, always verify that the table or alias with that qualifier actually appears in the outer FROM clause — subquery aliases don't count.

Question 9

A developer assigns the alias c to the customers table but continues to use the original table name as a column qualifier.

Which revision consistently uses the assigned table alias and avoids referring to the table by its original qualifier?

  1. SELECT customers.customer_id, c.customer_name FROM customers c WHERE customers.active_flag = 'Y';
  2. SELECT c.customer_id, c.customer_name FROM customers c WHERE c.active_flag = 'Y'; (correct answer)
  3. SELECT c.customer_id, customers.customer_name FROM customers c WHERE c.active_flag = 'Y';
  4. SELECT customers.customer_id, customers.customer_name FROM customers c WHERE c.active_flag = 'Y';
Explanation: When you assign a table alias in SQL, that alias becomes the preferred reference name for that table throughout the entire query — SELECT, WHERE, and all. Mixing the alias with the original table name in the same query creates inconsistency and, in some databases, can even cause errors. The goal is to pick one and use it everywhere. Option B is the correct revision because every column reference — c.customer_id, c.customer_name, and c.active_flag — uses the alias c consistently. The table is still defined as customers c in the FROM clause (which is required), but after that point, only c is used as the qualifier. This is clean, readable, and unambiguous. Option A mixes both styles: customers.customer_id and customers.active_flag use the original name, while c.customer_name uses the alias. This inconsistency is exactly the problem the question asks you to fix. Option C also mixes references — c.customer_id and c.active_flag use the alias correctly, but customers.customer_name reverts to the original table name. Option D does the opposite of what you want: it uses customers.customer_id and customers.customer_name in the SELECT clause, completely ignoring the alias, even though c.active_flag uses it in the WHERE clause. A quick strategy to remember: once you assign an alias, treat the original table name as "retired" for that query. Scan each column qualifier in every clause — if any say the original table name instead of the alias, the query is inconsistent.

Question 10

The table employees(employee_id, employee_name, manager_id) stores each manager's employee ID in manager_id. The report must list each employee alongside the name of that employee's manager.

Which query correctly uses two aliases to distinguish the employee role from the manager role?

  1. SELECT e.employee_name, m.employee_name AS manager_name FROM employees e JOIN employees m ON e.employee_id = m.manager_id;
  2. SELECT e.employee_name, m.employee_name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id; (correct answer)
  3. SELECT e.employee_name, e.employee_name AS manager_name FROM employees e JOIN employees m ON e.manager_id = m.employee_id;
  4. SELECT e.employee_name, m.employee_name AS manager_name FROM employees e JOIN employees m ON m.manager_id = m.employee_id;
Explanation: When a table references itself — like employees where manager_id points back to another row's employee_id — you need a self join. The trick is aliasing the same table twice to represent two different roles: one alias for the employee, one for the manager. Think of it as creating two virtual copies of the table and joining them on the relationship that connects them. The correct query is B. Here, e represents the employee and m represents the manager. The join condition e.manager_id = m.employee_id says: "Find the row in the manager copy (m) whose employee_id matches the employee's manager_id." This correctly navigates the relationship — the employee's manager_id field is a foreign key pointing to the manager's employee_id. A flips the join condition to e.employee_id = m.manager_id, which reverses the logic entirely. This would match employees whose ID appears as someone else's manager — returning the wrong pairs and likely omitting employees without direct reports. C selects e.employee_name twice, labeling the second as manager_name. Even though the join condition is correct, you're pulling the employee's name into both columns instead of pulling the manager's name from alias m. The result would show the same name twice for every row. D uses m.manager_id = m.employee_id in the join condition, which compares two columns within the same alias (m). This would only match rows where a person is their own manager — almost certainly returning nothing useful. As a study tip: always trace the foreign key path. Ask yourself, "Which column holds the reference, and which column is being referenced?" That relationship defines your ON clause.