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.