Why ordering organizes results in MySQL - See It in Action

Why ordering organizes results

📖 Scenario: You work at a bookstore that keeps track of book sales in a database table called sales. Each sale record has the book_title and the copies_sold. You want to see the sales data sorted to understand which books sell the most and which sell the least.

🎯 Goal: Build a SQL query that retrieves all sales records and orders them by the number of copies sold, from highest to lowest.

📋 What You'll Learn

Create a table called sales with columns book_title (VARCHAR) and copies_sold (INT).

Insert exactly these three rows into sales: ('The Alchemist', 150), ('1984', 200), ('The Hobbit', 120).

Write a SQL query that selects all columns from sales and orders the results by copies_sold in descending order.

Ensure the final query uses ORDER BY copies_sold DESC.

💡 Why This Matters

🌍 Real World

Ordering query results helps businesses quickly see top-selling products or important data sorted by priority.

💼 Career

Database developers and analysts use ORDER BY to organize data reports and dashboards for decision making.

1

Create the sales table and insert data

Create a table called sales with columns book_title as VARCHAR(100) and copies_sold as INT. Then insert these exact rows: ('The Alchemist', 150), ('1984', 200), and ('The Hobbit', 120).

MySQL

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set up the SELECT query

Write a SQL query that selects all columns from the sales table using SELECT * FROM sales.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 120);

-- Write SELECT query to get all sales
-- Your code here

Hint

Use SELECT * FROM sales; to get all rows and columns.

3

Add ordering by copies_sold descending

Modify the SELECT query to order the results by copies_sold in descending order using ORDER BY copies_sold DESC.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 120);

-- Add ORDER BY clause to sort by copies_sold descending
-- Your code here

Hint

Use ORDER BY copies_sold DESC to sort from highest to lowest.

4

Complete the query with ordering

Ensure the final query is SELECT * FROM sales ORDER BY copies_sold DESC; to organize the sales results from most to least copies sold.

MySQL

CREATE TABLE sales (
  book_title VARCHAR(100),
  copies_sold INT
);

INSERT INTO sales (book_title, copies_sold) VALUES
('The Alchemist', 150),
('1984', 200),
('The Hobbit', 120);

-- Finalize the query to order results by copies_sold descending
-- Your code here

Hint

Check that your query orders by copies_sold descending.

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