SQL Quiz: Order By
10 questions · exam conditions
0:00
Order ByQuestion 1 of 10

The employees table contains these rows: E1 is in Analytics with a salary of 90000; E2 is in Analytics with a salary of 110000; E3 is in Sales with a salary of 110000; E4 is in Sales with a salary of 110000; and E5 is in Sales with a salary of 95000.

What is the employee ID sequence returned by this query?

SELECT employee_id FROM employees ORDER BY department ASC, salary DESC, employee_id DESC;

E2, E1, E4, E3, E5
E1, E2, E5, E4, E3
E4, E3, E5, E2, E1
E2, E4, E3, E5, E1
← Back to quizzes

SQL Quiz

SQL Quiz: Order By

Practice Order By 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 Order By, 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 these rows: E1 is in Analytics with a salary of 90000; E2 is in Analytics with a salary of 110000; E3 is in Sales with a salary of 110000; E4 is in Sales with a salary of 110000; and E5 is in Sales with a salary of 95000.

What is the employee ID sequence returned by this query?

SELECT employee_id FROM employees ORDER BY department ASC, salary DESC, employee_id DESC;

  1. E2, E1, E4, E3, E5 (correct answer)
  2. E1, E2, E5, E4, E3
  3. E4, E3, E5, E2, E1
  4. E2, E4, E3, E5, E1
Explanation: When you see ORDER BY with multiple columns, think of it as a tiebreaker system: the first column sorts everything, then the second column resolves ties within those groups, and so on. Here, the query sorts first by department ASC (alphabetical ascending), then by salary DESC (highest first), then by employee_id DESC (highest first as a final tiebreaker). Step 1 — Department: Analytics comes before Sales alphabetically, so E1 and E2 appear first, followed by E3, E4, and E5. Step 2 — Salary within Analytics: E2 earns 110,000 and E1 earns 90,000, so descending salary puts E2 before E1. That gives you E2, E1 so far. Step 3 — Salary within Sales: E3 and E4 both earn 110,000, and E5 earns 95,000. Descending salary places E3 and E4 before E5. But E3 and E4 are still tied, so the third sort column kicks in: employee_id DESC means E4 (higher ID) comes before E3. Final Sales order: E4, E3, E5. Combined result: E2, E1, E4, E3, E5 — which is answer A. Answer B applies ascending salary instead of descending. Answer C ignores the department sort entirely, reversing the Analytics/Sales order. Answer D mixes E1 to the end as if it belonged to Sales, misreading which department E1 is in. Study tip: When tracing multi-column ORDER BY, always work column by column and only apply the next sort within groups that are still tied — don't let later columns override earlier ones.

Question 2

A grouped report must list product categories from the greatest number of orders to the fewest. If categories have equal order counts, the category with greater total revenue must appear first. Any remaining tie must be resolved alphabetically by category.

Which query applies the required sort priorities and directions?

  1. SELECT category, COUNT(*) AS order_count, SUM(revenue) AS total_revenue FROM orders GROUP BY category ORDER BY order_count DESC, total_revenue DESC, category ASC; (correct answer)
  2. SELECT category, COUNT(*) AS order_count, SUM(revenue) AS total_revenue FROM orders GROUP BY category ORDER BY total_revenue DESC, order_count DESC, category ASC;
  3. SELECT category, COUNT(*) AS order_count, SUM(revenue) AS total_revenue FROM orders GROUP BY category ORDER BY order_count ASC, total_revenue DESC, category ASC;
  4. SELECT category, COUNT(*) AS order_count, SUM(revenue) AS total_revenue FROM orders GROUP BY category ORDER BY order_count DESC, category ASC, total_revenue DESC;
Explanation: When a question asks you to sort query results by multiple criteria, you need to map each business rule to a specific column and direction in the ORDER BY clause — and the sequence of those columns matters enormously. SQL evaluates sort keys left to right, so the first column listed is the primary sort, the second breaks ties from the first, and so on. The passage establishes three rules in priority order: (1) most orders first → order_count DESC, (2) higher revenue breaks ties → total_revenue DESC, (3) alphabetical order resolves remaining ties → category ASC. Answer A translates all three rules exactly in the correct sequence and with the correct directions, making it the right choice. B is wrong because it leads with total_revenue DESC instead of order_count DESC, reversing the priority of the first two sort criteria. Revenue is a tiebreaker, not the primary sort. C uses order_count ASC as the primary sort, which would place categories with the fewest orders first — the exact opposite of what the passage requires. D gets the first column right (order_count DESC) but then inserts category ASC before total_revenue DESC, swapping the second and third tiebreakers. Alphabetical sorting would kick in before revenue is even considered, violating the stated rules. A helpful strategy: when you see multi-level sorting requirements in plain language, number the rules before looking at the answer choices, then verify each answer choice column by column against your numbered list. Mismatched order and mismatched direction (ASC vs. DESC) are the two classic traps in these questions.

Question 3

The sales table has three West rows: W1 with revenue 200, W2 with revenue 150, and W3 with revenue 150. It also has East rows with arbitrary revenues. Assume order_id values compare in the displayed alphanumeric order.

Which sequence contains the first three order IDs returned by this query?

SELECT order_id FROM sales ORDER BY region DESC, revenue, order_id DESC;

  1. W1, W3, W2
  2. W2, W3, W1
  3. W3, W2, W1 (correct answer)
  4. The East IDs appear before all West IDs.
Explanation: When you see ORDER BY with multiple columns, read it left to right — each column is a tiebreaker for the one before it. The key is also recognizing what ASC and DESC mean for each column individually. Here, the sort is region DESC, revenue ASC (default), order_id DESC. Since "West" comes after "East" alphabetically, sorting region DESC puts West rows first. That means D is immediately eliminated — West rows appear before East rows, not after. Now within the West rows, sort by revenue ascending (no keyword means ASC by default). W1 has revenue 200; W2 and W3 both have revenue 150. So the two lower-revenue rows come first, and W1 comes last among the West rows. That rules out A (which places W1 first) and B (which also starts with W2, but wrong order follows). For the tiebreaker between W2 and W3 — both have revenue 150 — we use order_id DESC. Since order IDs compare alphanumerically and W3 > W2, descending order puts W3 before W2. So the final sequence is W3, W2, W1, confirming C. A is wrong because it puts W1 first, ignoring that revenue 200 sorts last in ascending order. B is wrong because it places W2 before W3, reversing the descending order_id tiebreaker. D is wrong because region DESC elevates West above East. Study tip: When analyzing multi-column sorts, annotate each column's direction explicitly — especially the default ASC — before tracing the sort logic row by row.

Question 4

Four accounts have these balances: account A has -120, account B has 80, account C has -80, and account D has 0.

What account sequence is produced by ORDER BY ABS(balance) DESC, balance ASC?

  1. A, C, B, D (correct answer)
  2. A, B, C, D
  3. D, C, B, A
  4. A, D, C, B
Explanation: When you see a multi-column ORDER BY, think of it as a tiebreaker system: the first column sorts everyone, and the second column only kicks in when two rows share the same first-column value. Here, ABS(balance) strips the sign from each balance — so A becomes 120, B becomes 80, C becomes 80, and D becomes 0. Sorting those absolute values DESC gives you 120 first (account A), then 80 (accounts B and C are tied), then 0 (account D). For the tie between B and C, the second criterion balance ASC breaks it using the original signed values: C is -80 and B is 80, so C comes before B (ascending means negatives first). The final sequence is A, C, B, D — confirming answer A. Choice B (A, B, C, D) ignores the tiebreaker entirely and places B before C, as if sorted only by absolute value without resolving the tie correctly. Choice C (D, C, B, A) reverses the entire ordering — this would result from ORDER BY ABS(balance) ASC, balance DESC, essentially flipping both sort directions. Choice D (A, D, C, B) misplaces D by treating 0 as a middle value rather than recognizing it has the smallest absolute value and belongs last. A useful habit: when you see multiple ORDER BY columns, mentally build a table with each computed sort key, rank rows by the first key, then use subsequent keys only within tied groups. That step-by-step approach prevents you from accidentally applying all criteria globally at once.

Question 5

A support queue must display priority values in this custom order: Urgent, High, Normal. Within the same priority, the newest request must appear first. The priority column stores exactly those three values.

Which ORDER BY clause produces the required queue order?

  1. ORDER BY priority ASC, requested_at DESC
  2. ORDER BY CASE priority WHEN 'Urgent' THEN 1 WHEN 'High' THEN 2 ELSE 3 END DESC, requested_at DESC
  3. ORDER BY CASE priority WHEN 'Urgent' THEN 3 WHEN 'High' THEN 2 ELSE 1 END ASC, requested_at ASC
  4. ORDER BY CASE priority WHEN 'Urgent' THEN 1 WHEN 'High' THEN 2 ELSE 3 END ASC, requested_at DESC (correct answer)
Explanation: When SQL can't sort text values in a custom order naturally, you need to translate those text values into numbers using a CASE expression — then sort by the numbers. The question here tests exactly that pattern, plus a secondary sort condition. The goal is: Urgent first, then High, then Normal, with newest requests first within each group. The cleanest approach is to map Urgent → 1, High → 2, Normal → 3, then sort that mapping ASC (1 comes before 2 comes before 3). For the tie-breaker, "newest first" means sorting requested_at DESC. That's precisely what D does — it's the correct answer. A is wrong because sorting priority ASC alphabetically produces "High → Normal → Urgent," which is the opposite of what's required. Alphabetical order and business logic rarely match. B uses the right numeric mapping (Urgent=1, High=2, Normal=3) but sorts it DESC, which flips the order to Normal → High → Urgent — exactly backwards from the goal. C reverses the numeric mapping (Urgent=3, High=2, Normal=1) and uses ASC for the first sort, which would also yield Normal → High → Urgent. It compounds the confusion by also sorting requested_at ASC, putting the oldest requests first — wrong on both counts. A reliable strategy: whenever you need a custom sort order, assign the smallest number to the item that should appear first, then sort ASC. Think of it as building your own ranking scale. Also always double-check your secondary sort direction — DESC for "newest first," ASC for "oldest first."

Question 6

The results table contains: Ana with score 88 and attempt 2; Bo with score 88 and attempt 1; Cy with score 91 and attempt 3; and Dee with score 88 and attempt 1.

What name sequence is returned by this query?

SELECT name, score AS points, attempt FROM results ORDER BY 2 DESC, 3 ASC, 1 DESC;

  1. Cy, Ana, Dee, Bo
  2. Bo, Dee, Ana, Cy
  3. Cy, Bo, Dee, Ana
  4. Cy, Dee, Bo, Ana (correct answer)
Explanation: When you see ORDER BY 2 DESC, 3 ASC, 1 DESC in SQL, remember that column numbers refer to the position in the SELECT list — here, 2 means score/points, 3 means attempt, and 1 means name. The sort applies left to right: first sort by score descending, then break ties by attempt ascending, then break remaining ties by name descending. Let's walk through the data: Cy has score 91, so Cy comes first regardless. The remaining three (Ana, Bo, Dee) all share score 88, so we move to the second sort: attempt ascending. Bo and Dee both have attempt 1, while Ana has attempt 2 — so Ana slots after the tied pair. Now among Bo and Dee (both score 88, attempt 1), we apply the third sort: name descending alphabetically. "D" comes after "B" in the alphabet, so descending order puts Dee before Bo. The final sequence is Cy, Dee, Bo, Ana, which is answer D. Choice A (Cy, Ana, Dee, Bo) misapplies the tiebreaker — it sorts attempt correctly but then ignores the name sort, placing Ana before the attempt-1 group. Choice B (Bo, Dee, Ana, Cy) reverses the score sort entirely, putting the lowest score first. Choice C (Cy, Bo, Dee, Ana) gets the first tiebreaker right but flips the name sort to ascending instead of descending, swapping Bo and Dee. A useful habit: when you see ORDER BY with numbers, mentally substitute the column names first, then simulate the multi-level sort one criterion at a time, left to right.

Question 7

Three shipments have these timestamps: O1 was shipped on July 3 and created on July 1; O2 was shipped on July 3 and created on June 30; and O3 was shipped on July 2 and created on June 29. All dates are in the same year.

What order-ID sequence results from ORDER BY shipped_at DESC, created_at ASC?

  1. O1, O2, O3
  2. O2, O1, O3 (correct answer)
  3. O3, O2, O1
  4. O2, O3, O1
Explanation: When you see ORDER BY with multiple columns, think of it as a tiebreaker system: the first column is the primary sort, and each additional column only applies when rows are tied on the previous one. Here, shipped_at DESC sorts first. O1 and O2 both shipped July 3, while O3 shipped July 2. Since we're sorting descending, July 3 outranks July 2 — so O3 goes last. Now you have a tie between O1 and O2, both shipped July 3. The tiebreaker is created_at ASC, meaning earlier creation dates come first. O2 was created June 30, O1 was created July 1 — so O2 comes before O1. Final sequence: O2, O1, O3, which is answer B. Choice A (O1, O2, O3) gets the tiebreaker backwards — it places O1 before O2, as if sorting created_at DESC instead of ASC. Choice C (O3, O2, O1) reverses the entire primary sort, treating shipped_at as ascending rather than descending. Choice D (O2, O3, O1) misapplies the primary sort entirely by inserting O3 in the middle, ignoring that O3's July 2 ship date should place it last under DESC ordering. A useful habit: when you see multi-column ORDER BY, mentally process one column at a time. Sort all rows by the first column, identify any ties, then apply the second column only within those tied groups. Also watch the ASC/DESC modifier on each column independently — they don't have to match, and that's exactly where exam questions plant their traps.

Question 8

A query uses ORDER BY department ASC, hire_date DESC. Two employees are in the same department and have the same hire date. No other sort expression is present.

Which statement about the relative order of those two employees is correct?

  1. Their relative order is not guaranteed because all specified sort keys are equal. (correct answer)
  2. Their relative order follows primary-key order even though the key is not specified.
  3. Their relative order follows insertion order because the explicit keys are tied.
  4. Their relative order follows ascending employee name as an implicit final key.
Explanation: When a SQL query includes an ORDER BY clause, the database engine sorts rows according to the specified expressions — and only those expressions. If two rows are identical across every sort key listed, the engine has no instruction telling it which row should come first. At that point, the result is non-deterministic: the database is free to return those tied rows in any order it chooses, and that order can vary between executions, query plans, or database versions. Answer A correctly captures this — when all specified sort keys are tied, relative order is simply not guaranteed. Answer B is a common misconception. Some students assume the database "falls back" to the primary key as a tiebreaker, but SQL standards make no such promise. A database engine might internally use an index that happens to align with the primary key, but this is an implementation detail you cannot rely on — it is not guaranteed behavior. Answer C similarly assumes a fallback that doesn't exist in the SQL standard. Physical insertion order is a storage-level concept. Relational databases do not promise that rows are stored or retrieved in insertion order, and ORDER BY gives you no such guarantee either. Answer D invents a rule that simply does not exist. There is no implicit alphabetical tiebreaker on employee name or any other column not listed in the ORDER BY clause. Study tip: Whenever you need a guaranteed, stable sort order in SQL, make sure your ORDER BY clause is unique — typically by appending a primary key column as the final sort expression. If any ties can exist, order is undefined.

Question 9

The order_lines table contains P1 with quantity 2 and unit price 30, P2 with quantity 5 and unit price 12, and P3 with quantity 3 and unit price 25.

What is the product-code sequence returned by this query?

SELECT product_code, quantity * unit_price AS line_total FROM order_lines ORDER BY line_total DESC, product_code ASC;

  1. P1, P2, P3
  2. P2, P1, P3
  3. P3, P1, P2 (correct answer)
  4. P3, P2, P1
Explanation: When you see ORDER BY with multiple columns, think of it as a tiebreaker system: the first column is the primary sort, and each subsequent column only kicks in when rows are equal on the previous one. Start by calculating each line_total:
  • P1: 2×30=602 \times 30 = 60
  • P2: 5×12=605 \times 12 = 60
  • P3: 3×25=753 \times 25 = 75
The query sorts by line_total DESC first, meaning the highest value comes first. P3 has the largest total (75), so it appears first. P1 and P2 are tied at 60, so the tiebreaker product_code ASC applies — alphabetically, P1 comes before P2. The final sequence is P3, P1, P2, making C the correct answer. A (P1, P2, P3) would result from sorting product_code ASC alone, completely ignoring the line_total column — a common mistake when students overlook that the first sort key dominates. B (P2, P1, P3) reflects a correct descending sort on line_total but reverses the tiebreaker, as if product_code were sorted DESC instead of ASC. D (P3, P2, P1) gets the primary sort right (P3 first) but breaks the P1/P2 tie in the wrong direction, again ignoring the ASC on product_code. A useful habit: always work through multi-column ORDER BY in layers — resolve the primary sort first, then apply secondary sorts only within groups of ties. This step-by-step approach prevents you from accidentally mixing up which column controls the final order.

Question 10

A report must list orders by status alphabetically. Within each status, the newest order must appear first. Orders having the same status and timestamp must be listed by increasing order_id.

Which ORDER BY clause satisfies all of the report requirements?

  1. ORDER BY status ASC, order_time ASC, order_id ASC
  2. ORDER BY status ASC, order_time DESC, order_id ASC (correct answer)
  3. ORDER BY order_time DESC, status ASC, order_id ASC
  4. ORDER BY status DESC, order_time DESC, order_id DESC
Explanation: When you see a multi-column ORDER BY question, translate each requirement into a sort rule — in the order they're stated. Here, you need: (1) status alphabetically, (2) newest order first within each status, (3) ascending order_id to break ties. "Alphabetically" means ascending (ASC), so status ASC must come first. "Newest first" means descending timestamp — larger/later timestamps should appear at the top — so order_time DESC comes second. Finally, "increasing order_id" for ties means order_id ASC last. That gives you ORDER BY status ASC, order_time DESC, order_id ASC, which is B. Looking at the distractors: A sorts order_time ASC, putting the oldest orders first within each status — the exact opposite of what the requirement says. It's an easy trap if you default to ASC without checking the direction. C puts order_time DESC first instead of status, meaning orders are grouped by time across all statuses rather than by status first — the primary sort key is completely wrong. D sorts status DESC (reverse alphabetical, like Z→A) and order_id DESC (decreasing), violating both the "alphabetical" and "increasing order_id" requirements simultaneously. A reliable strategy: underline the direction words in the problem — "alphabetically" = ASC, "newest first" = DESC, "increasing" = ASC — and write them out before you look at the choices. Multi-column ORDER BY questions almost always include one distractor that flips a single sort direction, so verify every column's direction, not just the first one.