ORDER BY with ASC and DESC in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output order of this query?

Given the table Employees with columns id, name, and salary, what will be the order of names returned by this query?

SELECT name FROM Employees ORDER BY salary ASC;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000), (2, 'Bob', 3000), (3, 'Charlie', 7000);

A Bob, Charlie, Alice

B Charlie, Alice, Bob

C Alice, Bob, Charlie

D Bob, Alice, Charlie

💡 Hint

ORDER BY salary ASC sorts from smallest to largest salary.

The ORDER BY salary ASC sorts salaries from lowest to highest. Bob has 3000, Alice 5000, Charlie 7000, so the order is Bob, Alice, Charlie.

❓ query_result

intermediate

What is the output order of this query with DESC?

Using the same Employees table, what will be the order of names returned by this query?

SELECT name FROM Employees ORDER BY salary DESC;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 5000), (2, 'Bob', 3000), (3, 'Charlie', 7000);

A Charlie, Alice, Bob

B Charlie, Bob, Alice

C Bob, Alice, Charlie

D Alice, Bob, Charlie

💡 Hint

ORDER BY salary DESC sorts from largest to smallest salary.

ORDER BY salary DESC sorts salaries from highest to lowest. Charlie has 7000, Alice 5000, Bob 3000, so the order is Charlie, Alice, Bob.

📝 Syntax

advanced

Which query will cause a syntax error?

Identify which of these SQL queries will cause a syntax error when ordering by two columns with different directions.

A SELECT * FROM Employees ORDER BY salary, name DESC ASC;

B SELECT * FROM Employees ORDER BY salary ASC, name DESC;

C SELECT * FROM Employees ORDER BY salary DESC, name DESC;

D SELECT * FROM Employees ORDER BY salary DESC, name ASC;

💡 Hint

Check the order and syntax of ASC and DESC keywords.

Option A has 'name DESC ASC' which is invalid syntax because you cannot specify both DESC and ASC for the same column.

❓ optimization

advanced

Which query is more efficient for sorting by salary descending and then name ascending?

Assuming an index exists on (salary DESC, name ASC), which query will best use the index for sorting?

A SELECT * FROM Employees ORDER BY name ASC, salary DESC;

B SELECT * FROM Employees ORDER BY salary ASC, name DESC;

C SELECT * FROM Employees ORDER BY salary DESC, name ASC;

D SELECT * FROM Employees ORDER BY salary DESC;

💡 Hint

Indexes are most efficient when query ORDER BY matches index order.

Option C matches the index order exactly, so it can use the index efficiently for sorting.

🧠 Conceptual

expert

What is the result of this query with mixed ASC and DESC on NULL values?

Consider a table Products with columns id, price (nullable), and name. What is the order of rows returned by this query?

SELECT * FROM Products ORDER BY price ASC NULLS LAST, name DESC;

Assume the data:
1, NULL, 'Apple'
2, 10, 'Banana'
3, 10, 'Cherry'
4, NULL, 'Date'
5, 5, 'Elderberry'

A 5 Elderberry, 3 Cherry, 2 Banana, 1 Apple, 4 Date

B 5 Elderberry, 3 Cherry, 2 Banana, 4 Date, 1 Apple

C 5 Elderberry, 2 Banana, 3 Cherry, 1 Apple, 4 Date

D 5 Elderberry, 2 Banana, 3 Cherry, 4 Date, 1 Apple

💡 Hint

NULLS LAST puts NULL prices at the end; within same price, names are sorted descending.

Rows with price NULL come last. Among prices 5 and 10, 5 comes first. For price 10, names sorted descending: Cherry then Banana. NULL prices last with names descending: Date then Apple.

Practice

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

easy

A. It sorts the query results in a specified order.

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.

2. Step 2: Differentiate from other clauses

   Filtering is done by WHERE, grouping by GROUP BY, and limiting rows by LIMIT. Only ORDER BY sorts the results.

3. Final Answer:

   It sorts the query results in a specified order. -> Option A

4. Quick Check:

   ORDER BY = sorts results [OK]

Hint: ORDER BY always sorts rows; remember ASC or DESC [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 BY Salary DESC;

B. SELECT * FROM Employees SORT BY Salary DESC;

C. SELECT * FROM Employees ORDER Salary DESC;

D. SELECT * FROM Employees ORDER BY Salary DOWN;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax to sort is ORDER BY column_name ASC|DESC. Here, DESC means descending order.

2. Step 2: Check each option

   SELECT * FROM Employees ORDER BY Salary DESC; uses correct keywords and syntax. Options B, C, and D use invalid keywords like SORT BY, missing BY, or incorrect direction word.

3. Final Answer:

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

4. Quick Check:

   ORDER BY + DESC = correct syntax [OK]

Hint: ORDER BY must be followed by column and ASC or DESC [OK]

Common Mistakes:

• Using SORT BY instead of ORDER BY
• Omitting BY after ORDER
• Using DOWN instead of DESC

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

SELECT ProductID FROM Products ORDER BY Price ASC;

medium

A. Products sorted by ProductID descending.

B. Products sorted by Price from highest to lowest.

C. Products sorted by ProductID ascending.

D. Products sorted by Price from lowest to highest.

Solution

1. Step 1: Analyze ORDER BY clause

   The query orders rows by Price in ascending order, meaning from smallest to largest price.

2. Step 2: Understand output column

   The query selects ProductID but orders by Price. So the ProductID values will appear sorted according to their prices ascending.

3. Final Answer:

   Products sorted by Price from lowest to highest. -> Option D

4. Quick Check:

   ORDER BY Price ASC = lowest to highest [OK]

Hint: ORDER BY sorts by specified column, not selected column [OK]

Common Mistakes:

• Assuming sorting is by selected column instead of ORDER BY column
• Confusing ASC with DESC
• Thinking ORDER BY changes selected columns

4. Identify the error in this SQL query:

SELECT Name FROM Customers ORDER BY Age DESCENDING;

medium

A. Missing ASC keyword after ORDER BY.

B. The keyword DESCENDING is invalid; use DESC instead.

C. ORDER BY cannot be used with DESC.

D. The column Age does not exist.

Solution

1. Step 1: Check ORDER BY direction keywords

   SQL uses ASC for ascending and DESC for descending order. DESCENDING is not a valid keyword.

2. Step 2: Validate other parts

   ORDER BY works with DESC. ASC is optional. We assume Age exists as no schema given.

3. Final Answer:

   The keyword DESCENDING is invalid; use DESC instead. -> Option B

4. Quick Check:

   Use DESC, not DESCENDING [OK]

Hint: Use DESC, not DESCENDING for descending order [OK]

Common Mistakes:

• Typing DESCENDING instead of DESC
• Thinking DESC is not allowed
• Assuming ASC is mandatory

5. You have a table Students with columns Name, Grade, and Age. You want to list students sorted first by Grade descending (highest grade first), and then by Age ascending (youngest first) within the same grade. Which query achieves this?

hard

A. SELECT * FROM Students ORDER BY Age DESC, Grade ASC;

B. SELECT * FROM Students ORDER BY Grade ASC, Age DESC;

C. SELECT * FROM Students ORDER BY Grade DESC, Age ASC;

D. SELECT * FROM Students ORDER BY Age ASC, Grade DESC;

Solution

1. Step 1: Understand sorting priorities

   We want to sort by Grade descending first, so highest grades come first.

2. Step 2: Apply secondary sorting

   Within the same grade, sort by Age ascending, so younger students appear first.

3. Step 3: Match query syntax

   SELECT * FROM Students ORDER BY Grade DESC, Age ASC; matches the required order: ORDER BY Grade DESC, Age ASC. Other options reverse order or directions.

4. Final Answer:

   SELECT * FROM Students ORDER BY Grade DESC, Age ASC; -> Option C

5. Quick Check:

   ORDER BY Grade DESC, Age ASC = correct multi-level sort [OK]

Hint: List columns in ORDER BY by priority with ASC or DESC [OK]

Common Mistakes:

• Reversing ASC and DESC directions
• Swapping column order in ORDER BY
• Using only one column for sorting