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.

Practice

1. What does the ORDER BY clause do in a MySQL query?

easy

A. It groups rows with the same values.

B. It sorts the query results based on one or more columns.

C. It filters rows based on a condition.

D. It limits the number of rows returned.

Solution

1. Step 1: Understand the purpose of ORDER BY

   The ORDER BY clause is used to arrange the rows returned by a query in a specific order.

2. Step 2: Identify the sorting action

   It sorts the results based on one or more columns, either ascending or descending.

3. Final Answer:

   It sorts the query results based on one or more columns. -> Option B

4. Quick Check:

   ORDER BY = Sort results [OK]

Hint: ORDER BY means sorting results by column values [OK]

Common Mistakes:

• Confusing ORDER BY with WHERE filtering
• Thinking ORDER BY limits rows
• Mixing ORDER BY with GROUP BY

2. Which of the following is the correct syntax to sort results in descending order by the column price?

easy

A. SELECT * FROM products ORDER BY price DESC;

B. SELECT * FROM products SORT BY price DESC;

C. SELECT * FROM products ORDER price DESC;

D. SELECT * FROM products ORDER BY price DOWN;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax uses ORDER BY followed by the column name and then DESC for descending order.

2. Step 2: Check each option

   Only SELECT * FROM products ORDER BY price DESC; uses the correct keywords and order: ORDER BY price DESC.

3. Final Answer:

   SELECT * FROM products ORDER BY price DESC; -> Option A

4. Quick Check:

   ORDER BY + column + DESC = correct syntax [OK]

Hint: Use ORDER BY column DESC for descending sort [OK]

Common Mistakes:

• Using SORT BY instead of ORDER BY
• Omitting BY after ORDER
• Using DOWN instead of DESC

3. 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 ASC;

medium

A. Names sorted by salary from lowest to highest.

B. Names in random order.

C. Names sorted alphabetically by name.

D. Names sorted by salary from highest to lowest.

Solution

1. Step 1: Understand ORDER BY salary ASC

   The query sorts rows by the salary column in ascending order, meaning from smallest to largest salary.

2. Step 2: Determine the order of names

   Since the query selects name but orders by salary, the names will appear sorted by their salary from lowest to highest.

3. Final Answer:

   Names sorted by salary from lowest to highest. -> Option A

4. Quick Check:

   ORDER BY salary ASC = lowest to highest [OK]

Hint: ORDER BY column ASC sorts from smallest to largest [OK]

Common Mistakes:

• Assuming ORDER BY sorts by selected column only
• Confusing ASC with DESC
• Thinking order is alphabetical by default

4. Identify the error in this query:

SELECT * FROM orders ORDER BY DESC order_date;

medium

A. ORDER BY cannot be used with the DESC keyword.

B. The query is missing a WHERE clause.

C. The table name 'orders' is misspelled.

D. The DESC keyword is placed before the column name incorrectly.

Solution

1. Step 1: Analyze ORDER BY syntax

   The correct syntax is ORDER BY column_name DESC, where DESC comes after the column name.

2. Step 2: Identify the error in the query

   The query places DESC before the column name, which is incorrect and causes a syntax error.

3. Final Answer:

   The DESC keyword is placed before the column name incorrectly. -> Option D

4. Quick Check:

   DESC must follow column name in ORDER BY [OK]

Hint: DESC always follows the column name in ORDER BY [OK]

Common Mistakes:

• Placing DESC before column name
• Thinking DESC is optional
• Confusing ORDER BY with WHERE clause

5. You want to list products with the highest price first, but if two products have the same price, you want to sort those by name alphabetically. Which query achieves this?

hard

A. SELECT * FROM products ORDER BY price ASC, name DESC;

B. SELECT * FROM products ORDER BY name ASC, price DESC;

C. SELECT * FROM products ORDER BY price DESC, name ASC;

D. SELECT * FROM products ORDER BY price DESC name ASC;

Solution

1. Step 1: Understand sorting priorities

   The main sort should be by price descending to get highest prices first.

2. Step 2: Add secondary sort by name ascending

   For ties in price, sorting by name ascending alphabetically resolves order.

3. Step 3: Check query syntax

   SELECT * FROM products ORDER BY price DESC, name ASC; correctly uses ORDER BY price DESC, name ASC with proper commas and keywords.

4. Final Answer:

   SELECT * FROM products ORDER BY price DESC, name ASC; -> Option C

5. Quick Check:

   ORDER BY price DESC, name ASC = correct multi-level sort [OK]

Hint: Use comma to separate columns in ORDER BY with directions [OK]

Common Mistakes:

• Reversing sort order priorities
• Omitting comma between columns
• Using wrong ASC/DESC for desired order