Why ordering organizes results in MySQL - Challenge Your Understanding

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 in the result of this query?

SELECT name FROM Employees ORDER BY salary DESC;

MySQL

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

A Charlie, Bob, Alice

B Alice, Charlie, Bob

C Bob, Charlie, Alice

D Alice, Bob, Charlie

💡 Hint

ORDER BY salary DESC sorts salaries from highest to lowest.

The ORDER BY clause sorts the rows by salary in descending order, so the highest salary (Bob:7000) comes first, then Charlie (6000), then Alice (5000).

🧠 Conceptual

intermediate

Why does ORDER BY affect the result order?

Why does using ORDER BY in a SQL query change the order of the rows returned?

A Because ORDER BY tells the database how to sort the rows before returning them.

B Because ORDER BY filters out rows that don't match the condition.

C Because ORDER BY changes the data in the table permanently.

D Because ORDER BY groups rows with the same values together.

💡 Hint

Think about what sorting means in real life, like sorting books by title.

ORDER BY sorts the rows based on specified columns before sending them back, so the output order changes but the data stays the same.

📝 Syntax

advanced

Which query correctly orders by two columns?

You want to order the Employees table first by department ascending, then by salary descending. Which query is correct?

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(20), department VARCHAR(20), salary INT);

A SELECT * FROM Employees ORDER BY department DESC, salary ASC;

B SELECT * FROM Employees ORDER BY department, salary DESC ASC;

C SELECT * FROM Employees ORDER BY department ASC salary DESC;

D SELECT * FROM Employees ORDER BY department ASC, salary DESC;

💡 Hint

Multiple columns in ORDER BY are separated by commas, each with its own direction.

Option D correctly uses commas and specifies ASC and DESC for each column. Option D has invalid syntax with 'DESC ASC'. Option D misses a comma. Option D reverses the order directions.

❓ optimization

advanced

How does indexing affect ORDER BY performance?

You have a large table with millions of rows. You run SELECT * FROM Sales ORDER BY sale_date DESC; but it is slow. How can adding an index help?

A An index on <code>sale_date</code> will filter out duplicate rows automatically.

B An index on <code>sale_date</code> lets the database quickly find rows in order without sorting all rows.

C An index on <code>sale_date</code> will make the table smaller in size.

D An index on <code>sale_date</code> will prevent rows from being deleted.

💡 Hint

Think about how a phone book is sorted and how you find names quickly.

An index on the column used in ORDER BY allows the database to retrieve rows in sorted order efficiently, avoiding a full sort operation.

🔧 Debug

expert

Why does this ORDER BY query return unexpected results?

You run this query:

SELECT name, age FROM Users ORDER BY age;

But the results are not sorted by age as expected. What could cause this?

MySQL

CREATE TABLE Users (name VARCHAR(20), age VARCHAR(3));
INSERT INTO Users VALUES ('Anna', '25'), ('Ben', '5'), ('Cara', '100');

A The age column is stored as text, so ORDER BY sorts lexicographically, not numerically.

B The table has no primary key, so ORDER BY does not work.

C The ORDER BY clause must be written as ORDER BY age ASCENDING.

D The query is missing a GROUP BY clause to sort properly.

💡 Hint

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

Since age is stored as text, sorting is alphabetical: '100' comes before '25' and '5'. To sort numerically, age should be stored as a number type.