Business Analytics Quiz: Select Where And Order By
10 questions · exam conditions
0:00
Select Where And Order ByQuestion 1 of 10

Four invoices have the following values: I101 has an amount of 500 and an invoice date of May 3; I102 has an amount of 750 and an invoice date of May 4; I103 has an amount of 500 and an invoice date of May 1; and I104 has an amount of 500 and an invoice date of May 1.

What is the complete invoice order produced by this query?

SELECT invoice_id FROM invoices ORDER BY amount DESC, invoice_date ASC, invoice_id ASC;

I102, I101, I103, I104
I103, I104, I101, I102
I102, I104, I103, I101
I102, I103, I104, I101
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Select Where And Order By

Practice Select Where And Order By in Business Analytics 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 Where And Order By, giving you a quick way to practice the rules, question types, and explanations that matter most for Business Analytics.

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

Four invoices have the following values: I101 has an amount of 500 and an invoice date of May 3; I102 has an amount of 750 and an invoice date of May 4; I103 has an amount of 500 and an invoice date of May 1; and I104 has an amount of 500 and an invoice date of May 1.

What is the complete invoice order produced by this query?

SELECT invoice_id FROM invoices ORDER BY amount DESC, invoice_date ASC, invoice_id ASC;

  1. I102, I101, I103, I104
  2. I103, I104, I101, I102
  3. I102, I104, I103, I101
  4. I102, I103, I104, I101 (correct answer)
Explanation: When a SQL query uses multiple ORDER BY columns, sorting happens in priority order: the first column sorts all rows, the second column breaks ties among rows that match on the first, and so on. Here, the query sorts by amount DESC first, then invoice_date ASC, then invoice_id ASC. Start by ranking the invoices by amount descending: I102 (750) sits alone at the top. The remaining three — I101, I103, and I104 — all share amount 500, so you move to the tiebreaker: invoice_date ASC. I103 and I104 both have May 1, while I101 has May 3, so I103/I104 come before I101. Now apply the final tiebreaker, invoice_id ASC, to I103 vs. I104: alphabetically, I103 comes before I104. The final order is I102, I103, I104, I101, which is answer D. Answer A (I102, I101, I103, I104) incorrectly places I101 before I103 and I104, ignoring that May 3 comes after May 1 in ascending date order. Answer B (I103, I104, I101, I102) reverses the amount sort entirely, placing the highest amount last instead of first. Answer C (I102, I104, I103, I101) gets the top and bottom right but swaps I103 and I104, forgetting that invoice_id ASC means I103 precedes I104 alphabetically. A useful habit: work through multi-column sorting one column at a time, forming groups of ties before moving to the next sort key. Sketch a small table and sort it column by column — this prevents the most common mistake of jumping to later sort keys before fully resolving the earlier ones.

Question 2

Six leads have these scores: L1 has 70, L2 has 80, L3 has 75, L4 has 80, L5 has 80.1, and L6 has 69.9. The lead_score column is numeric.

What sequence is returned by this query?

SELECT lead_id FROM leads WHERE lead_score BETWEEN 70 AND 80 ORDER BY lead_score DESC, lead_id ASC;

  1. L5, L2, L4, L3, L1
  2. L1, L3, L2, L4
  3. L4, L2, L3, L1
  4. L2, L4, L3, L1 (correct answer)
Explanation: When reading SQL queries like this, train yourself to work through three filters in order: the WHERE clause eliminates rows, then ORDER BY sorts what remains, and finally the column list determines what you actually see. The BETWEEN 70 AND 80 filter in SQL is inclusive on both ends, so it keeps any lead_score where 70 ≤ score ≤ 80. Running through each lead: L1 (70) ✓, L2 (80) ✓, L3 (75) ✓, L4 (80) ✓, L5 (80.1) ✗ — exceeds 80, L6 (69.9) ✗ — falls below 70. So four leads survive: L1, L2, L3, L4. Now apply ORDER BY lead_score DESC, lead_id ASC. Sorting by score descending first: L2 and L4 both score 80, L3 scores 75, L1 scores 70. The tie between L2 and L4 is broken by lead_id ASC, so L2 comes before L4. Final sequence: L2, L4, L3, L1 — confirming D. Choice A is wrong because it includes L5 (score 80.1), which exceeds the upper bound of BETWEEN and is excluded. Choice B sorts ascending instead of descending, reversing the intended order. Choice C places L4 before L2 in the tie-break, which violates the lead_id ASC secondary sort — L2 comes before L4 alphabetically/numerically. A reliable study tip: whenever you see BETWEEN with decimal values nearby, always check whether boundary scores are exactly on the limit. SQL's inclusive BETWEEN trips up many students who forget that 80.1 is outside BETWEEN 70 AND 80.

Question 3

A risk-review queue contains four accounts: A has High risk and expected loss of 20000; B has Medium risk and expected loss of 80000; C has High risk and expected loss of 20000; and D has Low risk and expected loss of 90000.

What sequence is produced by this query?

SELECT account_id FROM accounts ORDER BY CASE WHEN risk_level = 'High' THEN 0 ELSE 1 END ASC, expected_loss DESC, account_id ASC;

  1. A, C, D, B (correct answer)
  2. D, B, A, C
  3. C, A, D, B
  4. A, C, B, D
Explanation: When a SQL ORDER BY clause contains multiple criteria, the database sorts by each one in sequence — think of it as a tiebreaker chain. Here, the query applies three sort keys: first a CASE expression, then expected_loss DESC, then account_id ASC. Work through each account's sort key values:
AccountCASE valueExpected Lossaccount_id
A0 (High)20,000A
B1 (Medium)80,000B
C0 (High)20,000C
D1 (Low)90,000D
Step 1: Sort by CASE ASC — High-risk accounts (value 0) come first: A and C before B and D. Step 2: Within the High-risk group (A, C), sort by expected_loss DESC. Both have 20,000 — a true tie — so move to the tiebreaker. Step 3: Within that tie, sort by account_id ASC alphabetically: A before C. Step 4: Within the non-High group (B, D), sort by expected_loss DESC: D (90,000) before B (80,000). Final sequence: A, C, D, B — confirming answer A is correct. Answer B reverses the entire ordering logic, placing Low/Medium accounts first. Answer C swaps A and C, ignoring that account_id ASC breaks their expected-loss tie alphabetically (A < C). Answer D places B before D, ignoring that 90,000 > 80,000 in a descending sort. Study tip: When tracing multi-column ORDER BY, always apply sort keys one at a time and only move to the next key when the current one produces a tie — this methodical approach prevents rushing to the wrong sequence.

Question 4

A marketing table contains these records: Campaign A is Email, Active, with ROI 1.3; Campaign B is Social, Active, with ROI 0.9; Campaign C is Email, Paused, with ROI 0.8; and Campaign D is Search, Active, with ROI 1.5. The channel, status, and roi columns contain no NULL values.

Which campaigns are returned by the following query?

SELECT campaign_id FROM campaigns WHERE channel = 'Email' OR status = 'Active' AND roi >= 1.2 ORDER BY campaign_id;

  1. Campaigns A and D only
  2. Campaigns A and C only
  3. Campaigns A, C, and D (correct answer)
  4. Campaigns A, B, and D
Explanation: When a SQL WHERE clause mixes OR and AND, operator precedence determines how conditions group — and this is exactly what's being tested here. AND always binds more tightly than OR, so the query is interpreted as: WHERE channel = 'Email' OR (status = 'Active' AND roi >= 1.2) This means a campaign is returned if it meets either condition: (1) it's an Email campaign, or (2) it's Active with ROI ≥ 1.2. Let's evaluate each record. Campaign A is Email → satisfies condition 1 ✓. Campaign B is Social and Active with ROI 0.9 — not Email, and ROI fails the ≥ 1.2 threshold ✗. Campaign C is Email → satisfies condition 1 ✓. Campaign D is Search and Active with ROI 1.5 — not Email, but Active with ROI 1.5 ≥ 1.2 → satisfies condition 2 ✓. So Campaigns A, C, and D are returned — confirming answer C. Answer A is wrong because it excludes Campaign C. Campaign C is Email, which satisfies the first condition regardless of its Paused status — status is irrelevant when the Email condition alone is sufficient. Answer B is wrong because it excludes Campaign D, which correctly qualifies through the AND sub-condition. Answer D is wrong because it includes Campaign B, which fails both conditions: it's not Email, and its ROI of 0.9 doesn't meet the ≥ 1.2 threshold. A reliable rule of thumb: whenever you see OR and AND in the same WHERE clause, mentally add parentheses around the AND condition first — it always evaluates before OR.

Question 5

A sales table contains five rows with these category-region pairs: Electronics-East, Electronics-East, Electronics-West, Furniture-East, and Furniture-East.

What does the following query return?

SELECT DISTINCT category, region FROM sales ORDER BY category ASC, region ASC;

  1. Electronics-East, Electronics-West, Furniture-East (correct answer)
  2. Electronics-East, Electronics-East, Electronics-West, Furniture-East, Furniture-East
  3. Electronics-East, Furniture-East, Electronics-West
  4. Electronics, followed by Furniture, with no region values
Explanation: When working with SQL queries, you need to track what each clause does independently — DISTINCT, ORDER BY, and column selection all play separate roles. SELECT DISTINCT category, region tells the database to return only unique combinations of both columns together. Looking at the five rows — Electronics-East, Electronics-East, Electronics-West, Furniture-East, Furniture-East — you can identify three unique pairs: Electronics-East (the duplicate is removed), Electronics-West, and Furniture-East. The ORDER BY category ASC, region ASC then sorts these alphabetically first by category, then by region within each category. Electronics comes before Furniture, and among Electronics rows, East comes before West. This gives you exactly: Electronics-East, Electronics-West, Furniture-East — confirming A is correct. Answer B is the trap most students fall into — it returns all five original rows as if no DISTINCT keyword were present. B represents what a plain SELECT category, region would return, ignoring deduplication entirely. Answer C shows the correct three unique rows but in the wrong order — it sorts by region first (East, East, West) rather than by category first, misreading how multi-column ORDER BY works. Answer D reflects a misunderstanding that DISTINCT applies only to the first column listed; in reality, DISTINCT operates on the full row of selected columns together, so region values are absolutely included in the output. A useful pattern to remember: DISTINCT always evaluates the entire combination of selected columns, not just the first one. Whenever you see SELECT DISTINCT with multiple columns, ask yourself "what unique pairs (or tuples) exist?" rather than focusing on individual columns.

Question 6

A customer-success analyst must create a contact list containing customer_id and email. A customer should be included only when email_consent equals 'Y' and the email value is not NULL. The database follows standard SQL null-comparison rules.

Which query produces the required contact list and sorts it by customer_id?

  1. SELECT customer_id, email FROM customers WHERE email_consent = 'Y' AND email IS NOT NULL ORDER BY customer_id; (correct answer)
  2. SELECT customer_id, email FROM customers WHERE email_consent = 'Y' AND email <> NULL ORDER BY customer_id;
  3. SELECT customer_id, email FROM customers WHERE email_consent = 'Y' OR email IS NOT NULL ORDER BY customer_id;
  4. SELECT customer_id, email FROM customers WHERE email_consent = 'Y' AND email = NOT NULL ORDER BY customer_id;
Explanation: When filtering rows in SQL, one of the trickiest concepts is how databases handle NULL values. NULL represents the absence of a value — it is not a string, not zero, and critically, it cannot be tested with standard equality or inequality operators like = or <>. Instead, SQL requires the special syntax IS NULL or IS NOT NULL. Answer A is correct because it precisely satisfies both business requirements: email_consent = 'Y' ensures only consenting customers are included, and email IS NOT NULL correctly filters out records with missing email addresses using the proper null-check syntax. The ORDER BY customer_id clause then sorts the results as required. Answer B fails because email <> NULL uses a standard inequality operator against NULL. Under SQL's three-valued logic, any comparison with NULL — including <> — evaluates to UNKNOWN, not TRUE. This means the condition never passes, and the query returns zero rows regardless of your data. Answer C uses OR instead of AND, which fundamentally changes the logic. It would return customers who either have consent or have a non-null email — so you'd accidentally include customers who consented but have no email, or customers who never consented at all. Both conditions must be true simultaneously, requiring AND. Answer D uses email = NOT NULL, which is simply invalid SQL syntax. NOT NULL is a constraint keyword, not a value expression — you cannot use it on the right side of an equality comparison. A good rule of thumb: whenever you need to check for missing data in SQL, always reach for IS NULL or IS NOT NULL — never = NULL or <> NULL.

Question 7

A reporting database follows standard SQL behavior: a column alias defined in the SELECT list may be used in ORDER BY, but it is not available to the WHERE clause. The analyst needs products whose revenue - cost is strictly greater than 10000, sorted from highest to lowest margin.

Which query meets the requirement?

  1. SELECT product_id, revenue - cost AS margin FROM products WHERE margin > 10000 ORDER BY margin DESC;
  2. SELECT product_id, revenue - cost AS margin FROM products WHERE revenue - cost > 10000 ORDER BY margin DESC; (correct answer)
  3. SELECT product_id, revenue - cost AS margin FROM products WHERE revenue - cost > 10000 ORDER BY margin ASC;
  4. SELECT product_id, revenue - cost AS margin FROM products WHERE revenue - cost >= 10000 ORDER BY margin DESC;
Explanation: When writing SQL queries, you need to understand the order of clause evaluation: the database processes WHERE before it processes SELECT. This matters because any alias you define in the SELECT list — like margin — doesn't exist yet when WHERE is being evaluated. Only after filtering does the engine finalize column expressions and their aliases. ORDER BY, however, runs last, so it can reference aliases defined in SELECT. Option B is correct because it filters using the full expression revenue - cost > 10000 in the WHERE clause (bypassing the alias problem) and then sorts with ORDER BY margin DESC, which correctly uses the alias and returns results from highest to lowest margin — exactly what the requirement specifies. Option A is tempting but invalid: it uses WHERE margin > 10000, referencing the alias margin before it's been defined. Most SQL engines will throw an error here, and the passage explicitly tells you aliases are unavailable in WHERE. Option C gets the filtering logic right but sorts ASC (ascending, lowest to highest) instead of DESC (descending, highest to lowest) — a direct contradiction of the requirement. Option D also has correct sort order and correct expression in WHERE, but uses >= 10000 instead of > 10000, which includes margins equal to 10000. The requirement says strictly greater than, so this answer is off by one condition. A useful rule of thumb: "WHERE filters rows before aliases exist; ORDER BY runs after." Whenever you need to filter on a calculated column, repeat the full expression in WHERE rather than relying on the alias.

Question 8

An A/B testing table stores conversions and visitors as integer columns. In this database, division of one integer by another truncates the fractional portion. The analyst needs variants with at least a 5% conversion rate, excluding rows with zero visitors, and wants the highest conversion rate first. Standard SQL alias rules apply.

Which query correctly produces the requested result?

  1. SELECT variant, conversions * 1.0 / visitors AS rate FROM tests WHERE visitors > 0 AND rate >= 0.05 ORDER BY rate DESC;
  2. SELECT variant, conversions / visitors AS rate FROM tests WHERE visitors > 0 AND conversions / visitors >= 0.05 ORDER BY rate DESC;
  3. SELECT variant, conversions * 1.0 / visitors AS rate FROM tests WHERE visitors > 0 AND conversions * 1.0 / visitors >= 0.05 ORDER BY rate DESC; (correct answer)
  4. SELECT variant, conversions * 1.0 / visitors AS rate FROM tests WHERE visitors > 0 AND conversions * 1.0 / visitors >= 0.05 ORDER BY visitors DESC;
Explanation: When working with SQL queries that involve computed columns, you need to juggle three distinct challenges simultaneously: integer division behavior, alias availability in WHERE, and correct ORDER BY column. Missing any one of them breaks the query. The core issue here is that dividing two integers in this database truncates the result — so conversions / visitors for, say, 3 out of 100 visitors yields 0, not 0.03. Multiplying by 1.0 first forces floating-point arithmetic, giving you the true decimal rate. Additionally, SQL evaluates the WHERE clause before the SELECT clause, meaning a column alias defined in SELECT (like rate) is not yet available to filter on in WHERE. C is correct because it uses conversions * 1.0 / visitors in both the SELECT and WHERE clauses (ensuring accurate decimal division in both places), filters out zero-visitor rows with visitors > 0, applies the 5% threshold correctly, and sorts by rate DESC — highest conversion rate first. A fails because it references the alias rate in the WHERE clause, which SQL hasn't computed yet at that stage — this will throw an error or be ignored depending on the database. B fails on two fronts: conversions / visitors uses integer division, so any rate below 1.0 truncates to 0, making the >= 0.05 filter meaningless and the displayed rates wrong. D gets the calculation and filtering right but sorts by visitors DESC instead of rate DESC, returning the most-visited variants rather than the highest-converting ones. Remember this pattern: recalculate expressions in WHERE rather than reusing SELECT aliases, and always check whether integer division could silently destroy your decimal values.

Question 9

The ordered_at column stores timestamps, including fractional seconds. An analyst needs every order placed during March 2026 and no orders from another month.

Which WHERE clause most reliably defines the required timestamp interval?

  1. WHERE ordered_at BETWEEN '2026-03-01' AND '2026-03-31'
  2. WHERE ordered_at >= '2026-03-01' AND ordered_at <= '2026-04-01'
  3. WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01' (correct answer)
  4. WHERE ordered_at > '2026-03-01' AND ordered_at < '2026-03-31'
Explanation: When filtering timestamps that include fractional seconds (e.g., 2026-03-31 23:59:59.999), the boundary you set on the end of the range is critical. A date string like '2026-03-31' is typically interpreted as 2026-03-31 00:00:00.000 — meaning any timestamp later that same day slips through or gets excluded incorrectly depending on the operator you use. Option C — ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01' — handles this perfectly. The lower bound uses >= to include everything from the very first moment of March 1st, and the upper bound uses strict less-than (<) against April 1st. This captures every possible fractional timestamp on March 31st (like 23:59:59.9999) while excluding anything in April. This is the correct answer. Option A uses BETWEEN '2026-03-01' AND '2026-03-31', which translates to <= '2026-03-31 00:00:00' — dropping all March 31st orders placed after midnight. A significant portion of a day's data vanishes silently. Option B uses ordered_at <= '2026-04-01', which looks safe but actually includes 2026-04-01 00:00:00.000 exactly — a timestamp that belongs to April, not March. Option D compounds two errors: > on the start excludes March 1st orders entirely, and < '2026-03-31' cuts off the entire last day of the month. Study tip: Whenever you filter a timestamp column by month, default to the half-open interval pattern — >= first day and < first day of next month. This pattern is robust against fractional seconds and eliminates boundary guesswork.

Question 10

In a contracts table, status and amount never contain NULL. An analyst writes the filter WHERE NOT (status = 'Closed' OR amount < 1000).

Which alternative filter returns exactly the same records?

  1. WHERE status <> 'Closed' OR amount >= 1000
  2. WHERE status <> 'Closed' AND amount >= 1000 (correct answer)
  3. WHERE status = 'Closed' AND amount < 1000
  4. WHERE status <> 'Closed' AND amount < 1000
Explanation: Whenever you negate a logical expression in SQL (or Boolean logic generally), you should reach for De Morgan's Laws: negating an OR flips it to AND, and negating an AND flips it to OR — while also flipping each individual condition. Formally: NOT(P OR Q)≡NOT P AND NOT Q\text{NOT}(P \text{ OR } Q) \equiv \text{NOT } P \text{ AND NOT } Q Here, your original filter is NOT (status = 'Closed' OR amount < 1000). Applying De Morgan's Law, NOT distributes inward and flips the OR to AND:
  • NOT (status = 'Closed') becomes status <> 'Closed'
  • NOT (amount < 1000) becomes amount >= 1000
  • The OR becomes AND
This gives you status <> 'Closed' AND amount >= 1000, which is exactly answer B — the correct choice. A is wrong because it uses OR instead of AND. This returns any record where either condition holds, which is a much broader set of rows than the original filter produces. C is wrong because it describes the un-negated inner expression — it matches exactly what the original filter is excluding, not what it keeps. D is wrong because while it correctly negates the status condition, it leaves amount < 1000 unchanged rather than flipping it to amount >= 1000, violating De Morgan's Law on the second clause. As a study tip, whenever you see NOT (... OR ...) or NOT (... AND ...) in SQL questions, immediately write out De Morgan's transformation before reading the answer choices — it prevents you from falling for the very common trap of forgetting to flip the logical operator.