Chapter 7 Beginner 56 Questions

Practice Questions — ORDER BY, LIMIT, and DISTINCT

← Back to Notes
16 Easy
20 Medium
13 Hard

Topic-Specific Questions

Question 1
Easy
Sort all products by price ascending. Return name and price.
ORDER BY price (ASC is default).
SELECT name, price FROM products ORDER BY price;
Question 2
Easy
Get the top 5 most expensive products.
ORDER BY DESC + LIMIT.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 5;
Question 3
Easy
List unique categories in the products table.
SELECT DISTINCT.
SELECT DISTINCT category FROM products;
Question 4
Easy
Count how many unique cities the products are from.
COUNT(DISTINCT ...).
SELECT COUNT(DISTINCT city) FROM products;
Question 5
Easy
Sort all products by stock ascending, but only show the lowest 3 stock items.
LIMIT + ASC sort.
SELECT name, stock FROM products
ORDER BY stock ASC
LIMIT 3;
Question 6
Easy
Get page 2 of products (5 per page), sorted by id.
OFFSET = (2-1) * 5 = 5.
SELECT * FROM products
ORDER BY id
LIMIT 5 OFFSET 5;
Question 7
Easy
How many rows?
SELECT DISTINCT category FROM products;
Count distinct categories.
3 rows
Question 8
Easy
What is the first row?
SELECT name FROM products ORDER BY price DESC LIMIT 1;
Highest priced product.
iPhone 15
Question 9
Medium
Sort products by category ascending, then by price descending within each category.
Multi-column ORDER BY.
SELECT name, category, price FROM products
ORDER BY category ASC, price DESC;
Question 10
Medium
Find the 2nd cheapest product (lowest price is 1st). Return name and price.
ORDER BY ASC, OFFSET 1, LIMIT 1.
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 1 OFFSET 1;
Question 11
Medium
Return the product with the highest inventory value (price * stock).
Sort by price * stock DESC, LIMIT 1.
SELECT name, price, stock, price * stock AS inventory_value
FROM products
ORDER BY price * stock DESC
LIMIT 1;
Question 12
Medium
List unique (category, city) combinations sorted by category then city.
DISTINCT on multiple columns.
SELECT DISTINCT category, city FROM products
ORDER BY category, city;
Question 13
Medium
Get page 3 of products, 4 items per page, ordered by name.
OFFSET = (3-1) * 4 = 8.
SELECT * FROM products
ORDER BY name
LIMIT 4 OFFSET 8;
Question 14
Medium
What is returned?
SELECT DISTINCT category, city FROM products
WHERE category = 'Sports';
Sports is in which cities?
Sports Bengaluru
Sports Mumbai
Sports Pune
(3 rows)
Question 15
Easy
What is the difference between ASC and DESC in ORDER BY?
Direction of sorting.
ASC means ascending — smallest first, largest last. This is the default. DESC means descending — largest first, smallest last. For strings, ASC is alphabetical (A to Z), DESC is reverse (Z to A). For dates, ASC is oldest first, DESC is newest first.
Question 16
Medium
Why should you always use ORDER BY with LIMIT?
Determinism.
Without ORDER BY, SQL does not guarantee any particular order of rows. LIMIT just takes the first n rows of whatever order the database chose, which can change between queries. Using ORDER BY makes the result deterministic and predictable. Always pair LIMIT with ORDER BY in production code.
Question 17
Medium
Explain the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column).
What each counts.
COUNT(*) counts all rows in the result, including NULLs. COUNT(column) counts rows where column is NOT NULL — NULLs are skipped. COUNT(DISTINCT column) counts unique non-NULL values — duplicates and NULLs are excluded.
Question 18
Medium
Find the 3rd highest priced product using LIMIT and OFFSET.
OFFSET 2, LIMIT 1 after ORDER BY DESC.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 2;
Question 19
Hard
Return 3 products with the highest stock, but only among Grocery items.
WHERE filter + ORDER BY + LIMIT.
SELECT name, stock FROM products
WHERE category = 'Grocery'
ORDER BY stock DESC
LIMIT 3;
Question 20
Hard
Count how many unique categories exist for products priced above 1000.
WHERE + COUNT(DISTINCT).
SELECT COUNT(DISTINCT category) FROM products
WHERE price > 1000;
Question 21
Hard
Why does LIMIT 10000 OFFSET 99990 become slow? How would you fix it for production?
Think about what MySQL has to do for OFFSET.
OFFSET requires MySQL to scan and discard every row up to the offset position. So OFFSET 99990 scans 99,990 rows just to throw them away. This is O(n). Fix: use keyset pagination — remember the last id/value from the previous page and use WHERE id > last_seen_id ORDER BY id LIMIT n. This is O(log n) with an index and stays fast regardless of page depth.
Question 22
Hard
What does this return (from employees table)?
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;
3rd highest distinct salary.
81000 (Karan Mehta's salary)
Question 23
Hard
Return all products sorted by category ASC, but within each category sort by stock DESC and then price ASC.
Three sort keys.
SELECT name, category, stock, price FROM products
ORDER BY category ASC, stock DESC, price ASC;
Question 24
Hard
Paginate Electronics products, 2 per page. Write the query for page 2.
WHERE first, then ORDER BY, then LIMIT/OFFSET.
SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY id
LIMIT 2 OFFSET 2;
Question 25
Hard
In MySQL, how are NULLs sorted by default in ORDER BY?
NULLs treated as smallest or largest?
MySQL treats NULL as the smallest value. So ORDER BY col ASC puts NULLs first (at the top), and ORDER BY col DESC puts NULLs last. This differs from PostgreSQL which does the opposite by default. For portable code, use ORDER BY col IS NULL, col to force NULLs to the bottom regardless of database.

Mixed & Application Questions

Question 1
Easy
Sort products by name alphabetically.
ORDER BY name.
SELECT * FROM products ORDER BY name;
Question 2
Easy
Get the cheapest product.
ORDER BY ASC, LIMIT 1.
SELECT name, price FROM products
ORDER BY price ASC
LIMIT 1;
Question 3
Easy
Show how many products we have in total.
COUNT(*).
SELECT COUNT(*) FROM products;
Question 4
Easy
Find all distinct prices (in case of duplicates).
DISTINCT price.
SELECT DISTINCT price FROM products ORDER BY price;
Question 5
Easy
How many rows?
SELECT * FROM products LIMIT 0;
Limit zero.
0 rows
Question 6
Easy
List distinct cities in alphabetical order.
DISTINCT + ORDER BY.
SELECT DISTINCT city FROM products ORDER BY city;
Question 7
Easy
What is the first product returned?
SELECT name FROM products ORDER BY stock DESC LIMIT 1;
Highest stock.
Soap
Question 8
Medium
Get the 3 most expensive Electronics.
WHERE + ORDER BY DESC + LIMIT.
SELECT name, price FROM products
WHERE category = 'Electronics'
ORDER BY price DESC
LIMIT 3;
Question 9
Medium
Get 2 products from the middle of the alphabetically sorted list (positions 6-7).
OFFSET 5, LIMIT 2.
SELECT name FROM products
ORDER BY name
LIMIT 2 OFFSET 5;
Question 10
Medium
Count distinct categories for products in Mumbai.
WHERE + COUNT(DISTINCT).
SELECT COUNT(DISTINCT category) FROM products
WHERE city = 'Mumbai';
Question 11
Medium
Sort products descending by price, but return only the 4th and 5th in the list.
OFFSET 3, LIMIT 2.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 2 OFFSET 3;
Question 12
Medium
How many rows?
SELECT DISTINCT category, city FROM products
WHERE city = 'Mumbai';
Unique (category, city) for Mumbai.
2 rows
Question 13
Medium
Return the product with highest stock. Use ORDER BY DESC LIMIT 1.
Direct sort and limit.
SELECT name, stock FROM products
ORDER BY stock DESC
LIMIT 1;
Question 14
Medium
Paginate all products with 3 per page. Write query for page 4.
OFFSET = (4-1)*3 = 9.
SELECT * FROM products
ORDER BY id
LIMIT 3 OFFSET 9;
Question 15
Medium
Write a query for employees (from chapter 6) that returns their names in descending salary order.
Single-column sort.
SELECT name, salary FROM employees
ORDER BY salary DESC;
Question 16
Medium
Find the 2nd highest salary in the employees table.
Classic interview. LIMIT 1 OFFSET 1 after DESC sort.
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
Question 17
Medium
How many rows?
SELECT DISTINCT category FROM products
WHERE price > 50000;
Products above 50000 are all in which category?
1 row
Question 18
Medium
Get top 5 Grocery items by stock. Return name, stock.
WHERE + ORDER BY + LIMIT.
SELECT name, stock FROM products
WHERE category = 'Grocery'
ORDER BY stock DESC
LIMIT 5;
Question 19
Hard
Return a list showing unique (city, category) pairs sorted by city ascending, then category descending.
DISTINCT + multi-column ORDER BY.
SELECT DISTINCT city, category FROM products
ORDER BY city ASC, category DESC;
Question 20
Hard
Find the 3 cheapest products in each category using LIMIT (hint: you cannot do it with a single LIMIT — but write a query for just Grocery).
For a single category, use WHERE + ORDER BY + LIMIT.
-- For Grocery only:
SELECT name, price FROM products
WHERE category = 'Grocery'
ORDER BY price ASC
LIMIT 3;
Question 21
Hard
What does this return?
SELECT COUNT(*), COUNT(DISTINCT category), COUNT(DISTINCT city)
FROM products;
Three counts: rows, unique categories, unique cities.
12, 3, 4
Question 22
Hard
Return the top 3 cities by the number of products they have. (Hint: you'll need GROUP BY — preview of chapter 10.)
GROUP BY city, COUNT(*), ORDER BY count DESC.
SELECT city, COUNT(*) AS num_products FROM products
GROUP BY city
ORDER BY num_products DESC
LIMIT 3;
Question 23
Hard
Return the 5th to 8th most expensive products (like items on page 2 with 4 per page).
LIMIT 4 OFFSET 4.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 4 OFFSET 4;
Question 24
Hard
What is the 2nd row?
SELECT name FROM products
ORDER BY category, price DESC;
Categories alphabetically, then price DESC.
Laptop (2nd row)

Multiple Choice Questions

MCQ 1
What is the default sort order of ORDER BY?
  • A. DESC (descending)
  • B. ASC (ascending)
  • C. Random
  • D. No default — must be specified
Answer: B
B is correct. ASC (ascending) is the default. ORDER BY price and ORDER BY price ASC are identical.
MCQ 2
Which clause limits the number of returned rows?
  • A. TOP
  • B. LIMIT
  • C. MAX
  • D. ONLY
Answer: B
B is correct. LIMIT is MySQL's syntax. SQL Server uses TOP, Oracle uses ROWNUM. The SQL standard is actually FETCH FIRST n ROWS ONLY.
MCQ 3
What does DISTINCT do?
  • A. Sorts rows
  • B. Removes duplicate rows
  • C. Returns rows in reverse order
  • D. Limits to the first row
Answer: B
B is correct. DISTINCT removes duplicates from the result set. Applies to the whole row of selected columns.
MCQ 4
Which query returns the 5 most expensive products?
  • A. SELECT * FROM products LIMIT 5 ORDER BY price;
  • B. SELECT * FROM products ORDER BY price LIMIT 5;
  • C. SELECT * FROM products ORDER BY price DESC LIMIT 5;
  • D. SELECT TOP 5 * FROM products ORDER BY price DESC;
Answer: C
C is correct. ORDER BY price DESC sorts most expensive first, then LIMIT 5 takes top 5. Option A has wrong clause order. Option B gives cheapest, not most expensive. Option D is SQL Server syntax, not MySQL.
MCQ 5
What does LIMIT 10 OFFSET 20 return?
  • A. Rows 1 through 10
  • B. Rows 20 through 30
  • C. Rows 21 through 30 (skip 20, take 10)
  • D. Rows 10 through 20
Answer: C
C is correct. OFFSET 20 skips the first 20 rows. LIMIT 10 takes the next 10. So rows 21-30.
MCQ 6
For pagination with 10 items per page, what is the OFFSET for page 4?
  • A. 10
  • B. 20
  • C. 30
  • D. 40
Answer: C
C is correct. Formula: OFFSET = (page - 1) * page_size = (4 - 1) * 10 = 30. Page 4 shows rows 31-40.
MCQ 7
Which query counts unique values in a column?
  • A. COUNT(col)
  • B. COUNT(DISTINCT col)
  • C. DISTINCT COUNT(col)
  • D. UNIQUE(col)
Answer: B
B is correct. COUNT(DISTINCT col) counts unique non-NULL values. COUNT(col) counts non-NULL rows (with duplicates). The others are not valid SQL syntax.
MCQ 8
What is the correct order of clauses?
  • A. SELECT, ORDER BY, FROM, WHERE, LIMIT
  • B. SELECT, FROM, WHERE, ORDER BY, LIMIT
  • C. FROM, SELECT, WHERE, LIMIT, ORDER BY
  • D. Any order is fine
Answer: B
B is correct. The syntactic order is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Putting them in any other order is a syntax error.
MCQ 9
What does SELECT DISTINCT category, city return?
  • A. Distinct categories and distinct cities separately
  • B. Unique (category, city) combinations
  • C. Distinct category from the first column only
  • D. An error — DISTINCT only works on one column
Answer: B
B is correct. DISTINCT applies to the entire row of selected columns. So this returns unique (category, city) pairs.
MCQ 10
Why should LIMIT always be paired with ORDER BY?
  • A. LIMIT does not work without ORDER BY
  • B. Without ORDER BY, the 'first n' rows are unpredictable
  • C. ORDER BY makes LIMIT faster
  • D. It is just a style preference
Answer: B
B is correct. Without ORDER BY, the database can return rows in any order (depends on storage, indexes, optimizer). LIMIT just grabs the first n of whatever that order is — unpredictable and risky.
MCQ 11
Which is equivalent to LIMIT 10 OFFSET 5 in MySQL?
  • A. LIMIT 10, 5
  • B. LIMIT 5, 10
  • C. LIMIT 15
  • D. OFFSET 5 LIMIT 10
Answer: B
B is correct. MySQL's two-argument form is LIMIT offset, count. So LIMIT 5, 10 means offset 5, limit 10 — same as LIMIT 10 OFFSET 5. This confuses everyone — prefer the OFFSET keyword.
MCQ 12
To get the 3rd highest distinct salary, which works?
  • A. ORDER BY salary DESC LIMIT 3
  • B. ORDER BY salary DESC LIMIT 1 OFFSET 2
  • C. ORDER BY salary ASC LIMIT 3
  • D. ORDER BY salary DESC LIMIT 2 OFFSET 1
Answer: B
B is correct. DESC sort puts highest first. OFFSET 2 skips positions 1 and 2 (highest and 2nd highest). LIMIT 1 returns the 3rd highest. Remember to add DISTINCT if you want to skip ties.
MCQ 13
In MySQL, ORDER BY col ASC places NULLs where?
  • A. Always at the end
  • B. At the beginning (NULLs treated as smallest)
  • C. In random positions
  • D. NULLs cause an error in ORDER BY
Answer: B
B is correct. MySQL treats NULL as the smallest value, so ASC puts NULLs at the top. DESC puts them at the bottom. PostgreSQL does the opposite. For portable code, use ORDER BY col IS NULL, col.
MCQ 14
You want all Grocery products sorted from cheapest to most expensive. Correct query?
  • A. SELECT * FROM products WHERE category = 'Grocery' ORDER BY price DESC;
  • B. SELECT * FROM products ORDER BY price WHERE category = 'Grocery';
  • C. SELECT * FROM products WHERE category = 'Grocery' ORDER BY price;
  • D. SELECT * FROM products WHERE ORDER BY price category = 'Grocery';
Answer: C
C is correct. WHERE before ORDER BY (correct clause order), ASC is default. Option A sorts descending (reverse direction). Options B and D have invalid clause order.
MCQ 15
What does COUNT(DISTINCT salary) return for a column with values [100, 200, 200, 300, NULL]?
  • A. 5
  • B. 4
  • C. 3
  • D. 2
Answer: C
C is correct. COUNT(DISTINCT) excludes NULL and counts unique values. Unique non-NULL values: 100, 200, 300 — that's 3.
MCQ 16
For a products table sorted by price DESC with LIMIT 5 OFFSET 3, which rows are returned?
  • A. Rows 1-5 sorted by price descending
  • B. Rows 3-7 sorted by price descending
  • C. Rows 4-8 sorted by price descending
  • D. Rows 5-10 sorted by price descending
Answer: C
C is correct. OFFSET 3 skips positions 1-3. LIMIT 5 takes positions 4-8 (5 rows: positions 4, 5, 6, 7, 8).
MCQ 17
Why is deep OFFSET (like OFFSET 100000) slow?
  • A. MySQL does not support high OFFSET values
  • B. MySQL must scan and discard all rows up to the offset
  • C. Network bandwidth slows it down
  • D. LIMIT caches results which fills memory
Answer: B
B is correct. OFFSET makes MySQL read and throw away rows until it reaches the offset position. For OFFSET 100000, that's 100,000 discarded rows. Fix: keyset pagination with WHERE id > last_seen_id.
MCQ 18
What does SELECT DISTINCT * FROM products do?
  • A. Returns one of each unique category
  • B. Returns all rows since full rows are rarely duplicates
  • C. Removes duplicates based on id only
  • D. Syntax error — DISTINCT cannot work with *
Answer: B
B is correct. DISTINCT * removes rows where ALL columns are identical. With a primary key like id, no two rows are identical, so DISTINCT * returns everything. It's valid syntax but rarely useful.
MCQ 19
In a multi-column ORDER BY col1 ASC, col2 DESC, how are ties in col1 broken?
  • A. By col2 ascending
  • B. By col2 descending
  • C. Randomly
  • D. By insertion order
Answer: B
B is correct. Each column has its own direction. When col1 ties, col2 DESC orders the tied rows with highest col2 first.
MCQ 20
Which query would let you find the employee with the 5th highest salary, handling ties correctly?
  • A. SELECT * FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 4
  • B. SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 4
  • C. SELECT * FROM employees LIMIT 5
  • D. SELECT MAX(salary) FROM employees
Answer: B
B is correct. DISTINCT handles ties — if two people have the same salary, they count as one rank. Without DISTINCT, the result depends on which duplicate is returned. Option A would fail for a table with duplicate salaries in the top 5.

Coding Challenges

Challenge 1: Top 3 Highest Priced Products

Easy
Return the 3 most expensive products, showing name and price.
Sample Input
products table.
Sample Output
iPhone 15 79999.00 Laptop 65000.00 Samsung TV 45999.00
Use ORDER BY + LIMIT.
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

Challenge 2: Unique Cities

Easy
List all distinct cities where products are stored, sorted alphabetically.
Sample Input
products table.
Sample Output
Bengaluru Delhi Mumbai Pune
Use DISTINCT + ORDER BY.
SELECT DISTINCT city FROM products ORDER BY city;

Challenge 3: Pagination Page 2

Medium
Get products 6 through 10 when sorted by id. This is page 2 with 5 items per page.
Sample Input
products table.
Sample Output
Bread, Headphones, Yoga Mat, Rice 5kg, Laptop (ids 6-10)
OFFSET = (page-1)*size.
SELECT * FROM products
ORDER BY id
LIMIT 5 OFFSET 5;

Challenge 4: 2nd Highest Salary

Medium
Given the employees table from chapter 6, find the 2nd highest distinct salary.
Sample Input
employees table.
Sample Output
92000
Use DISTINCT, ORDER BY DESC, LIMIT 1 OFFSET 1.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Challenge 5: Most Expensive Per Alphabetical Order

Medium
Sort products alphabetically by name. For each, show position in the sorted list (using row number via variables or window functions). Simpler version: just return name and price sorted by name.
Sample Input
products table.
Sample Output
All 12 products in alphabetical order.
ORDER BY name.
SELECT name, price FROM products ORDER BY name;

Challenge 6: Count Distinct Per Filter

Medium
Count the number of distinct categories for products priced below 5000.
Sample Input
products table.
Sample Output
3
WHERE + COUNT(DISTINCT).
SELECT COUNT(DISTINCT category) FROM products
WHERE price < 5000;

Challenge 7: Top 2 From Each City Category (Tricky)

Hard
Return the single most expensive product in Mumbai, then the single most expensive product in Delhi. Combine using UNION (preview of chapter 14) or write separate queries.
Sample Input
products table.
Sample Output
Mumbai: iPhone 15 (79999) Delhi: Samsung TV (45999)
Two queries or UNION.
-- Two separate queries:
SELECT name, price FROM products WHERE city = 'Mumbai' ORDER BY price DESC LIMIT 1;
SELECT name, price FROM products WHERE city = 'Delhi' ORDER BY price DESC LIMIT 1;

-- Or using UNION (covered later):
(SELECT name, price, city FROM products WHERE city = 'Mumbai' ORDER BY price DESC LIMIT 1)
UNION
(SELECT name, price, city FROM products WHERE city = 'Delhi' ORDER BY price DESC LIMIT 1);

Challenge 8: Nth Highest — Parameterized Pattern

Hard
Write the query pattern to find the Nth highest distinct price from products. Test with N=4 (4th highest).
Sample Input
products table.
Sample Output
For N=4, the 4th highest distinct price. Prices DESC: 79999, 65000, 45999, 4999, 3499, ... So 4th = 4999 (Running Shoes).
LIMIT 1 OFFSET N-1 with DISTINCT.
-- Template: replace N with the desired rank
SELECT DISTINCT price FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 3;  -- for N=4, offset = N-1 = 3

-- Returns: 4999 (Running Shoes)

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass