Why ordering organizes results in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to order the results by the column 'name' in ascending order.

MySQL

SELECT * FROM employees ORDER BY [1];

Drag options to blanks, or click blank then click option'

Aname

Bsalary

Cage

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column that does not exist in the table.

Forgetting to specify the column after ORDER BY.

The ORDER BY clause sorts the results by the specified column. Here, ordering by name arranges the rows alphabetically by employee name.

2fill in blank

medium

Complete the code to order the results by 'salary' in descending order.

MySQL

SELECT * FROM employees ORDER BY salary [1];

Drag options to blanks, or click blank then click option'

AASC

BDESC

CBY

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using ASC instead of DESC for descending order.

Omitting the order direction keyword.

The keyword DESC after ORDER BY salary sorts the salaries from highest to lowest.

3fill in blank

hard

Fix the error in the code to correctly order results by 'age' ascending.

MySQL

SELECT * FROM employees ORDER [1] age;

Drag options to blanks, or click blank then click option'

AON

BFROM

CBY

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ORDER FROM' or 'ORDER ON' instead of 'ORDER BY'.

Leaving out the keyword after ORDER.

The correct syntax is ORDER BY age. The keyword BY is required after ORDER.

4fill in blank

hard

Fill both blanks to order results by 'department' ascending and then by 'salary' descending.

MySQL

SELECT * FROM employees ORDER BY [1] [2];

Drag options to blanks, or click blank then click option'

Adepartment

Bsalary

CDESC

DASC

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up column names and order directions.

Using DESC for the first column when ascending is needed.

The query orders first by department in ascending order (default or specified by ASC), then by salary descending if needed.

Note: This example only fills the first ordering column and its direction; to order by two columns, the query would list both with their directions separated by commas.

5fill in blank

hard

Fill all three blanks to order results by 'department' ascending, then 'salary' descending, then 'age' ascending.

MySQL

SELECT * FROM employees ORDER BY [1] [2], [3] ASC;

Drag options to blanks, or click blank then click option'

Adepartment

Bsalary

CDESC

Dage

Attempts:

3 left

💡 Hint

Common Mistakes

Placing order directions in wrong positions.

Confusing column names with order keywords.

This query orders by department ascending (default), then by salary descending, and finally by age ascending.

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