ASC and DESC direction in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output order of this query?

Consider a table Employees with columns id, name, and salary. What will be the order of name in the result of this query?

SELECT name FROM Employees ORDER BY salary ASC;

MySQL

SELECT name FROM Employees ORDER BY salary ASC;

A Names ordered from lowest salary to highest salary

B Names in random order

C Names ordered alphabetically by name

D Names ordered from highest salary to lowest salary

💡 Hint

ASC means ascending order, from smallest to largest.

The ORDER BY clause with ASC sorts the rows by the salary column from the smallest value to the largest. So names appear in order of increasing salary.

❓ query_result

intermediate

What does DESC do in this query?

Given a table Products with columns product_id and price, what will be the order of product_id in this query?

SELECT product_id FROM Products ORDER BY price DESC;

MySQL

SELECT product_id FROM Products ORDER BY price DESC;

A Product IDs ordered from lowest price to highest price

B Product IDs ordered randomly

C Product IDs ordered by product_id ascending

D Product IDs ordered from highest price to lowest price

💡 Hint

DESC means descending order, from largest to smallest.

ORDER BY price DESC sorts the products by price from the highest value to the lowest. So product IDs appear in order of decreasing price.

📝 Syntax

advanced

Which query correctly orders by two columns with different directions?

You want to order a table Orders by customer_id ascending and then by order_date descending. Which query is correct?

A SELECT * FROM Orders ORDER BY customer_id, order_date DESC ASC;

B SELECT * FROM Orders ORDER BY customer_id ASC, order_date DESC;

C SELECT * FROM Orders ORDER BY customer_id DESC, order_date ASC;

D SELECT * FROM Orders ORDER BY customer_id ASC order_date DESC;

💡 Hint

Separate columns with commas and specify direction after each column.

Option B correctly orders by customer_id ascending and then order_date descending. Option B reverses directions. Option B has invalid syntax with DESC ASC. Option B misses a comma.

❓ optimization

advanced

How to optimize sorting large data with ASC and DESC?

You have a large table Sales with millions of rows. You often run:

SELECT * FROM Sales ORDER BY sale_date DESC;

What is the best way to optimize this query?

A Remove ORDER BY clause to speed up query

B Create an index on sale_date in ascending order

C Create an index on sale_date in descending order

D Use GROUP BY sale_date instead of ORDER BY

💡 Hint

Indexes help sorting if they match the order direction.

Creating an index on sale_date descending helps the database quickly retrieve rows in descending order without sorting all rows. Ascending index won't help for DESC order. Removing ORDER BY changes results. GROUP BY is for aggregation, not sorting.

🧠 Conceptual

expert

What is the result of mixing ASC and DESC in ORDER BY with NULL values?

Consider a table Items with a column price that contains some NULL values. What is the order of rows when running:

SELECT * FROM Items ORDER BY price ASC, name DESC;

Assuming default NULL sorting behavior in MySQL, where do NULL prices appear?

A Rows with NULL prices appear last, then prices in ascending order; names sorted descending within same price

B Rows with NULL prices appear first, then prices in ascending order; names sorted descending within same price

C Rows with NULL prices appear randomly mixed in the result

D Query causes an error because NULLs cannot be sorted with ASC and DESC

💡 Hint

MySQL sorts NULLs last by default in ascending order.

In MySQL, when ordering ascending, NULL values appear last. So rows with NULL price come after all non-NULL prices. Within same price, names are sorted descending. The query runs without error.