Why ordering matters in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the table ordered by the column 'age' in ascending order.

SQL

SELECT * FROM users ORDER BY [1];

Drag options to blanks, or click blank then click option'

Aname

Bsalary

Cage

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column that does not exist or is unrelated to sorting by age.

Forgetting to use ORDER BY clause.

The ORDER BY clause sorts the results by the specified column. Here, ordering by age arranges users from youngest to oldest.

2fill in blank

medium

Complete the code to order the results by 'salary' in descending order.

SQL

SELECT * FROM employees ORDER BY [1] DESC;

Drag options to blanks, or click blank then click option'

Aname

Bage

Cdepartment

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Using ascending order when descending is required.

Choosing a wrong column like 'age' or 'name'.

Using ORDER BY salary DESC sorts employees from highest to lowest salary.

3fill in blank

hard

Fix the error in the query to order by 'date_joined' ascending.

SQL

SELECT * FROM members ORDER BY [1] ASC;

Drag options to blanks, or click blank then click option'

Adate_joined

Bdatejoined

Cjoined_date

Djoin_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using a similar but incorrect column name.

Misspelling the column name or missing underscores.

The correct column name is date_joined. Using the exact column name is necessary for the query to work.

4fill in blank

hard

Fill both blanks to order the products by 'category' ascending and then by 'price' descending.

SQL

SELECT * FROM products ORDER BY [1] [2], price DESC;

Drag options to blanks, or click blank then click option'

Acategory

BASC

CDESC

Dprice

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up ascending and descending orders.

Using the wrong column name for the first order.

The query first orders by category ASC (alphabetically) and then by price DESC (highest price first) within each category.

5fill in blank

hard

Fill all three blanks to order the sales records by 'region' ascending, then 'sales_date' descending, and finally by 'amount' ascending.

SQL

SELECT * FROM sales ORDER BY [1] [2], [3] DESC, amount ASC;

Drag options to blanks, or click blank then click option'

Aregion

BASC

Csales_date

DDESC

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping ascending and descending orders.

Using wrong column names or order directions.

The query orders sales by region ASC (alphabetically), then sales_date DESC (newest first), and finally amount ASC (lowest to highest).

Practice

1. What does the ORDER BY clause do in an SQL query?

easy

A. It sorts the result rows based on specified columns.

B. It filters rows based on a condition.

C. It groups rows with the same values.

D. It limits the number of rows returned.

Solution

1. Step 1: Understand the purpose of ORDER BY

   The ORDER BY clause is used to arrange the rows returned by a query in a specific order based on one or more columns.

2. Step 2: Differentiate from other clauses

   Filtering rows is done by WHERE, grouping by GROUP BY, and limiting rows by LIMIT. These are different from sorting.

3. Final Answer:

   It sorts the result rows based on specified columns. -> Option A

4. Quick Check:

   ORDER BY = sorts rows [OK]

Hint: ORDER BY arranges rows; WHERE filters rows [OK]

Common Mistakes:

• Confusing ORDER BY with WHERE clause
• Thinking ORDER BY filters rows
• Mixing ORDER BY with GROUP BY

2. Which of the following is the correct syntax to sort a table named Employees by the column Salary in descending order?

easy

A. SELECT * FROM Employees ORDER Salary DESC;

B. SELECT * FROM Employees ORDER BY DESC Salary;

C. SELECT * FROM Employees SORT BY Salary DESC;

D. SELECT * FROM Employees ORDER BY Salary DESC;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax to sort results is ORDER BY column_name ASC|DESC. The keyword BY is mandatory after ORDER.

2. Step 2: Check each option

   SELECT * FROM Employees ORDER Salary DESC; misses BY. SELECT * FROM Employees SORT BY Salary DESC; uses SORT BY which is invalid in SQL. SELECT * FROM Employees ORDER BY DESC Salary; places DESC before the column, which is incorrect. SELECT * FROM Employees ORDER BY Salary DESC; correctly uses ORDER BY Salary DESC.

3. Final Answer:

   SELECT * FROM Employees ORDER BY Salary DESC; -> Option D

4. Quick Check:

   ORDER BY + column + DESC = correct syntax [OK]

Hint: ORDER BY must be followed by column name then ASC/DESC [OK]

Common Mistakes:

• Omitting BY after ORDER
• Using SORT BY instead of ORDER BY
• Placing DESC before column name

3. Given the table Products with columns ProductID and Price, what will be the output of this query?

SELECT ProductID FROM Products ORDER BY Price ASC LIMIT 3;

medium

A. The three products with the highest prices.

B. The three products with the lowest prices.

C. All products sorted by ProductID ascending.

D. An error because LIMIT cannot be used with ORDER BY.

Solution

1. Step 1: Understand ORDER BY with ASC

   Ordering by Price ASC sorts products from lowest to highest price.

2. Step 2: Understand LIMIT 3

   LIMIT 3 restricts the output to only the first 3 rows after sorting.

3. Final Answer:

   The three products with the lowest prices. -> Option B

4. Quick Check:

   ORDER BY Price ASC + LIMIT 3 = lowest 3 products [OK]

Hint: ASC means lowest first; LIMIT picks top rows [OK]

Common Mistakes:

• Confusing ASC with DESC
• Thinking LIMIT causes error with ORDER BY
• Assuming sorting by ProductID instead of Price

4. Consider this query:

SELECT Name, Age FROM Users ORDER BY Age DESC, Name;

It returns an error. What is the likely cause?

medium

A. Missing ASC or DESC after the second column in ORDER BY.

B. ORDER BY cannot have more than one column.

C. Name column does not exist in Users table.

D. ORDER BY must come before SELECT.

Solution

1. Step 1: Check ORDER BY syntax for multiple columns

   When ordering by multiple columns, each column can have its own ASC or DESC. If omitted, ASC is assumed, so this is usually valid.

2. Step 2: Verify if omission causes error

   In standard SQL, omitting ASC/DESC after the second column is allowed and defaults to ASC. So this is not an error cause.

3. Step 3: Reconsider options

   ORDER BY cannot have more than one column. is false; multiple columns are allowed. Name column does not exist in Users table. depends on schema but question implies columns exist. ORDER BY must come before SELECT. is false; ORDER BY comes after SELECT.

4. Step 4: Real cause

   Actually, the query is valid syntax. The question says it returns an error, so the most likely cause is that the Name column does not exist in the table.

5. Final Answer:

   Name column does not exist in Users table. -> Option C

6. Quick Check:

   Nonexistent column in ORDER BY causes error [OK]

Hint: Check column names carefully in ORDER BY [OK]

Common Mistakes:

• Assuming ASC/DESC is mandatory for every column
• Thinking ORDER BY must be before SELECT
• Believing multiple columns in ORDER BY cause errors

5. You want to find the top 2 highest paid employees from the Staff table, but if two employees have the same salary, you want to list the one with the alphabetically earlier LastName first. Which query achieves this?

hard

A. SELECT * FROM Staff ORDER BY Salary DESC, LastName ASC LIMIT 2;

B. SELECT * FROM Staff ORDER BY LastName ASC, Salary DESC LIMIT 2;

C. SELECT * FROM Staff ORDER BY Salary ASC, LastName DESC LIMIT 2;

D. SELECT * FROM Staff ORDER BY Salary DESC LIMIT 2;

Solution

1. Step 1: Identify primary ordering by Salary descending

   We want the highest salaries first, so ORDER BY Salary DESC is correct.

2. Step 2: Add secondary ordering by LastName ascending

   If salaries tie, order by LastName ASC to get alphabetically earlier names first.

3. Step 3: Use LIMIT 2 to get top two employees

   LIMIT 2 restricts output to only two rows.

4. Final Answer:

   SELECT * FROM Staff ORDER BY Salary DESC, LastName ASC LIMIT 2; -> Option A

5. Quick Check:

   Salary DESC then LastName ASC with LIMIT 2 = correct [OK]

Hint: Order by main column DESC, tie-breaker ASC, then LIMIT [OK]

Common Mistakes:

• Reversing order directions
• Ordering by LastName before Salary
• Omitting tie-breaker ordering