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

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this ORDER BY query?

Given the table Employees with columns id, name, and salary, what will be the order of name after running this query?

SELECT name FROM Employees ORDER BY salary ASC;

MySQL

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

A ["Alice", "Charlie", "Bob"]

B ["Alice", "Bob", "Charlie"]

C ["Charlie", "Bob", "Alice"]

D ["Bob", "Charlie", "Alice"]

💡 Hint

ORDER BY salary ASC sorts salaries from smallest to largest.

The query orders employees by their salary in ascending order. Bob has the lowest salary (3000), then Charlie (4000), then Alice (5000).

📝 Syntax

intermediate

Which query has a syntax error in ORDER BY clause?

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

A SELECT * FROM Products ORDER BY;

B SELECT * FROM Products ORDER BY name ASC;

C SELECT * FROM Products ORDER BY price DESC;

D SELECT * FROM Products ORDER BY id;

💡 Hint

ORDER BY must be followed by a column name or expression.

Option A ends ORDER BY without specifying a column, causing a syntax error.

❓ optimization

advanced

Which query is most efficient to order by a single indexed column?

Assuming Orders table has an index on order_date, which query will best use the index to order results?

A SELECT * FROM Orders ORDER BY order_date DESC;

B SELECT * FROM Orders ORDER BY order_date ASC;

C SELECT * FROM Orders ORDER BY order_date + INTERVAL 1 DAY;

D SELECT * FROM Orders ORDER BY YEAR(order_date);

💡 Hint

Ordering by the indexed column directly is faster than using functions on it.

Ordering by order_date ASC uses the index efficiently. Using functions like YEAR() or arithmetic disables index usage.

🔧 Debug

advanced

Why does this ORDER BY query return unexpected results?

Given this query:

SELECT product_name FROM Products ORDER BY price;

Prices are stored as VARCHAR, not numeric. What is the cause of unexpected order?

A Prices are sorted as strings, so '100' comes before '20'.

B ORDER BY cannot sort VARCHAR columns.

C The query is missing ASC or DESC keyword.

D The table lacks a primary key causing order issues.

💡 Hint

Think about how strings are sorted compared to numbers.

VARCHAR columns sort lexicographically, so '100' is less than '20' because '1' < '2'.

🧠 Conceptual

expert

What is the effect of ORDER BY on query result without LIMIT?

Consider a table Customers with 1000 rows. What does ORDER BY last_name do when used without LIMIT?

A Returns rows in random order because LIMIT is missing.

B Returns only the first row sorted by last_name.

C Returns all rows sorted by last_name in ascending order.

D Returns rows sorted by last_name but only if an index exists.

💡 Hint

ORDER BY affects the order of all rows returned.

ORDER BY sorts the entire result set. Without LIMIT, all rows are returned sorted.