ORDER BY single column in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query ordering by age ascending?

Given the table People with columns id, name, and age, what will be the order of names after running this query?

SELECT name FROM People ORDER BY age ASC;

SQL

CREATE TABLE People (id INT, name VARCHAR(20), age INT);
INSERT INTO People VALUES (1, 'Alice', 30), (2, 'Bob', 25), (3, 'Charlie', 35);

A Bob, Alice, Charlie

B Alice, Bob, Charlie

C Charlie, Alice, Bob

D Bob, Charlie, Alice

💡 Hint

ORDER BY age ASC sorts from smallest to largest age.

The query orders the rows by the age column in ascending order, so the names appear from youngest to oldest: Bob (25), Alice (30), Charlie (35).

❓ query_result

intermediate

What is the output order when ordering by salary descending?

Consider the table Employees with columns emp_id, emp_name, and salary. What is the order of emp_name after running this query?

SELECT emp_name FROM Employees ORDER BY salary DESC;

SQL

CREATE TABLE Employees (emp_id INT, emp_name VARCHAR(20), salary INT);
INSERT INTO Employees VALUES (1, 'John', 5000), (2, 'Jane', 7000), (3, 'Doe', 6000);

A John, Jane, Doe

B John, Doe, Jane

C Doe, Jane, John

D Jane, Doe, John

💡 Hint

ORDER BY salary DESC sorts from highest to lowest salary.

The query sorts employees by salary in descending order, so the highest salary comes first: Jane (7000), Doe (6000), John (5000).

📝 Syntax

advanced

Which query will cause a syntax error?

Identify the query that will cause a syntax error when ordering by the column score.

A SELECT * FROM Results ORDER BY score;

B SELECT * FROM Results ORDER BY score DESC;

C SELECT * FROM Results ORDER BY score ASCENDING;

D SELECT * FROM Results ORDER BY score ASC;

💡 Hint

Check the valid keywords for sorting order in SQL.

The keyword 'ASCENDING' is not valid in SQL. The correct keyword is 'ASC'. Options A, B, and C are valid syntax.

❓ optimization

advanced

Which query is more efficient for ordering by a single indexed column?

Given a large table Sales with an index on the date column, which query will likely perform best when ordering by date?

A SELECT * FROM Sales ORDER BY date + 0;

B SELECT * FROM Sales ORDER BY date;

C SELECT * FROM Sales ORDER BY date DESC;

D SELECT * FROM Sales ORDER BY CAST(date AS VARCHAR);

💡 Hint

Using the indexed column directly helps the database use the index.

Ordering by the indexed column directly (option B) allows the database to use the index efficiently. Options A and D apply expressions on the column, preventing index use. Option B is also efficient but descending order may require scanning index in reverse; still efficient but option B is the simplest ascending order.

🧠 Conceptual

expert

What happens if you ORDER BY a column with NULL values?

Consider a table Products with a column price that contains some NULL values. What is the default behavior of ORDER BY price ASC regarding NULLs?

A NULL values appear last in the result set.

B NULL values appear first in the result set.

C NULL values are excluded from the result set.

D The query raises an error due to NULL values.

💡 Hint

Think about how SQL treats NULLs in sorting by default.

By default, when ordering ascending, NULL values appear last because NULL is treated as unknown and considered greater than any non-NULL value. They are not excluded or cause errors.