Why ordering matters in SQL - Challenge Your Understanding

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SQL query with ORDER BY?

Consider a table Employees with columns id and name:

id | name
---+-------
1  | Alice
2  | Bob
3  | Charlie
4  | Diana

What is the result of this query?

SELECT name FROM Employees ORDER BY id DESC;

SQL

SELECT name FROM Employees ORDER BY id DESC;

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

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

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

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

💡 Hint

ORDER BY with DESC sorts from highest to lowest.

The query orders employees by id in descending order, so the names appear from the highest id (4) to the lowest (1).

❓ query_result

intermediate

How does ordering affect LIMIT results?

Given the same Employees table, what does this query return?

SELECT name FROM Employees ORDER BY name ASC LIMIT 2;

SQL

SELECT name FROM Employees ORDER BY name ASC LIMIT 2;

A ["Diana", "Charlie"]

B ["Bob", "Alice"]

C ["Charlie", "Diana"]

D ["Alice", "Bob"]

💡 Hint

ORDER BY name ASC sorts names alphabetically from A to Z.

The query orders names alphabetically ascending and returns only the first two rows.

📝 Syntax

advanced

Which query correctly orders by multiple columns?

Which SQL query orders the Employees table first by name ascending, then by id descending?

A SELECT * FROM Employees ORDER BY name ASC, id DESC;

B SELECT * FROM Employees ORDER BY id DESC, name ASC;

C SELECT * FROM Employees ORDER BY name DESC, id ASC;

D SELECT * FROM Employees ORDER BY id ASC, name DESC;

💡 Hint

Ordering columns are listed in the order of priority.

The correct syntax orders by name ascending first, then id descending.

🧠 Conceptual

advanced

Why does ordering matter in aggregate queries?

Consider this query:

SELECT department, COUNT(*) FROM Employees GROUP BY department ORDER BY COUNT(*) DESC;

Why is the ORDER BY clause important here?

A It sorts departments by the number of employees, showing the largest first.

B It filters out departments with fewer employees.

C It groups employees by department before counting.

D It changes the count to a sum of employee IDs.

💡 Hint

Think about what ordering by an aggregate function does.

ORDER BY COUNT(*) DESC sorts the grouped results so departments with more employees appear first.

🔧 Debug

expert

What error occurs with this ORDER BY usage?

Given the Employees table, what error does this query produce?

SELECT name FROM Employees ORDER BY salary;

Assume the salary column does not exist in the table.

SQL

SELECT name FROM Employees ORDER BY salary;

A No error, query runs and returns all names unordered.

B SyntaxError: Incorrect SQL syntax near 'salary'.

C Runtime error: Column 'salary' does not exist.

D Warning: Ignored unknown column 'salary' and returned unordered results.

💡 Hint

Ordering by a non-existent column causes an error.

The database raises a runtime error because the salary column is not found.