ORDER BY multiple columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of ORDER BY with two columns

Given the table Employees with columns Department and Salary, what is the output order of this query?

SELECT Department, Salary FROM Employees ORDER BY Department ASC, Salary DESC;

SQL

CREATE TABLE Employees (Department VARCHAR(20), Salary INT);
INSERT INTO Employees VALUES
('Sales', 5000),
('Sales', 7000),
('HR', 6000),
('HR', 5500),
('IT', 8000);

SELECT Department, Salary FROM Employees ORDER BY Department ASC, Salary DESC;

A [('HR', 6000), ('HR', 5500), ('IT', 8000), ('Sales', 7000), ('Sales', 5000)]

B [('Sales', 7000), ('Sales', 5000), ('HR', 6000), ('HR', 5500), ('IT', 8000)]

C [('HR', 5500), ('HR', 6000), ('IT', 8000), ('Sales', 5000), ('Sales', 7000)]

D [('IT', 8000), ('HR', 6000), ('HR', 5500), ('Sales', 7000), ('Sales', 5000)]

💡 Hint

ORDER BY sorts first by the first column, then by the second within each group.

The query sorts rows first by Department alphabetically ascending: HR, IT, Sales. Within each Department, Salary is sorted descending. So HR salaries 6000 then 5500, IT only one row 8000, Sales 7000 then 5000.

🧠 Conceptual

intermediate

Understanding ORDER BY with NULL values

Consider a table Products with columns Category and Price. If you run:

SELECT Category, Price FROM Products ORDER BY Category ASC, Price DESC;

and some Price values are NULL, where will those NULLs appear in the sorted result?

A NULL prices appear first within each Category when ordering Price DESC.

B NULL prices appear last within each Category when ordering Price DESC.

C NULL prices are ignored and do not appear in the result.

D NULL prices cause an error in ORDER BY.

💡 Hint

Think about how NULLs are treated in descending order sorting.

In SQL, NULLs are treated as unknown and usually appear last when sorting descending. So within each Category, rows with NULL Price come after rows with actual prices.

📝 Syntax

advanced

Identify the syntax error in ORDER BY multiple columns

Which option contains a syntax error in the ORDER BY clause when sorting by two columns Age ascending and Name descending?

A ORDER BY Age ASC, Name DESC

B ORDER BY Age, Name DESC

C ORDER BY Age ASC; Name DESC

D ORDER BY Age ASC Name DESC

💡 Hint

Check the punctuation between columns in ORDER BY.

Option D is missing a comma between 'Age ASC' and 'Name DESC', causing a syntax error. Option D has a semicolon which ends the statement early, also invalid but not in ORDER BY clause itself.

❓ optimization

advanced

Optimizing ORDER BY with multiple columns

You have a large table Sales with columns Region, SalesDate, and Amount. You often run:

SELECT * FROM Sales ORDER BY Region ASC, SalesDate DESC;

Which index will best improve the performance of this query?

A CREATE INDEX idx_region ON Sales(Region);

B CREATE INDEX idx_salesdate_region ON Sales(SalesDate DESC, Region ASC);

C CREATE INDEX idx_region_salesdate ON Sales(Region ASC, SalesDate DESC);

D CREATE INDEX idx_amount ON Sales(Amount);

💡 Hint

Indexes should match the ORDER BY column order and directions.

An index on (Region ASC, SalesDate DESC) matches the ORDER BY clause exactly, allowing the database to efficiently retrieve rows in the desired order without extra sorting.

🔧 Debug

expert

Why does this ORDER BY query return unexpected results?

You run this query:

SELECT Name, Score FROM Players ORDER BY Score, Name;

but the results are not sorted as expected by Score ascending and then Name ascending. What is the most likely cause?

A The Score column is stored as text, so sorting is lexicographical, not numeric.

B The query is missing ASC keywords, so sorting defaults to descending.

C ORDER BY cannot sort by multiple columns without parentheses.

D The database does not support ORDER BY with multiple columns.

💡 Hint

Check the data type of the columns used in ORDER BY.

If Score is stored as text, sorting will be alphabetical (e.g., '10' before '2'), causing unexpected order. Numeric sorting requires Score to be a numeric type.