Why ordering matters in SQL - See It in Action

Why Ordering Matters in SQL Queries

📖 Scenario: You are working with a small bookstore database. You want to see the list of books sorted by their price to decide which books to promote.

🎯 Goal: Build a SQL query that retrieves book titles and prices from the books table and orders the results by price in ascending order.

📋 What You'll Learn

Create a table called books with columns id, title, and price

Insert three books with exact titles and prices

Write a SELECT query to get title and price from books

Order the results by price in ascending order

💡 Why This Matters

🌍 Real World

Ordering data is important in real databases to show results in a meaningful way, like cheapest to most expensive products.

💼 Career

Database developers and analysts often write queries with ORDER BY to prepare reports and dashboards.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (decimal). Then insert these three books exactly: (1, 'The Art of SQL', 30.00), (2, 'Learning Databases', 25.50), and (3, 'SQL Basics', 20.00).

SQL

-- Create the books table and insert the three books
-- 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 SELECT query that retrieves the title and price columns from the books table.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Art of SQL', 30.00),
  (2, 'Learning Databases', 25.50),
  (3, 'SQL Basics', 20.00);

-- Write SELECT query to get title and price from books
-- Your code here

Hint

Use SELECT title, price FROM books; to get the columns.

3

Add ordering by price

Modify the SELECT query to order the results by the price column in ascending order using ORDER BY price ASC.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Art of SQL', 30.00),
  (2, 'Learning Databases', 25.50),
  (3, 'SQL Basics', 20.00);

-- Modify the SELECT query to order by price ascending
-- Your code here

Hint

Add ORDER BY price ASC at the end of the SELECT query.

4

Complete the query with descending order

Change the ORDER BY clause to sort the books by price in descending order using ORDER BY price DESC.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, price) VALUES
  (1, 'The Art of SQL', 30.00),
  (2, 'Learning Databases', 25.50),
  (3, 'SQL Basics', 20.00);

-- Change ORDER BY to sort by price descending
-- Your code here

Hint

Replace ASC with DESC in the ORDER BY clause.

Practice

1. What does the ORDER BY clause do in an SQL query?

easy

A. It sorts the result rows based on specified columns.

B. It filters rows based on a condition.

C. It groups rows with the same values.

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 based on one or more columns.

2. Step 2: Differentiate from other clauses

   Filtering rows is done by WHERE, grouping by GROUP BY, and limiting rows by LIMIT. These are different from sorting.

3. Final Answer:

   It sorts the result rows based on specified columns. -> Option A

4. Quick Check:

   ORDER BY = sorts rows [OK]

Hint: ORDER BY arranges rows; WHERE filters rows [OK]

Common Mistakes:

• Confusing ORDER BY with WHERE clause
• Thinking ORDER BY filters rows
• Mixing ORDER BY with GROUP BY

2. Which of the following is the correct syntax to sort a table named Employees by the column Salary in descending order?

easy

A. SELECT * FROM Employees ORDER Salary DESC;

B. SELECT * FROM Employees ORDER BY DESC Salary;

C. SELECT * FROM Employees SORT BY Salary DESC;

D. SELECT * FROM Employees ORDER BY Salary DESC;

Solution

1. Step 1: Recall correct ORDER BY syntax

   The correct syntax to sort results is ORDER BY column_name ASC|DESC. The keyword BY is mandatory after ORDER.

2. Step 2: Check each option

   SELECT * FROM Employees ORDER Salary DESC; misses BY. SELECT * FROM Employees SORT BY Salary DESC; uses SORT BY which is invalid in SQL. SELECT * FROM Employees ORDER BY DESC Salary; places DESC before the column, which is incorrect. SELECT * FROM Employees ORDER BY Salary DESC; correctly uses ORDER BY Salary DESC.

3. Final Answer:

   SELECT * FROM Employees ORDER BY Salary DESC; -> Option D

4. Quick Check:

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

Hint: ORDER BY must be followed by column name then ASC/DESC [OK]

Common Mistakes:

• Omitting BY after ORDER
• Using SORT BY instead of ORDER BY
• Placing DESC before column name

3. Given the table Products with columns ProductID and Price, what will be the output of this query?

SELECT ProductID FROM Products ORDER BY Price ASC LIMIT 3;

medium

A. The three products with the highest prices.

B. The three products with the lowest prices.

C. All products sorted by ProductID ascending.

D. An error because LIMIT cannot be used with ORDER BY.

Solution

1. Step 1: Understand ORDER BY with ASC

   Ordering by Price ASC sorts products from lowest to highest price.

2. Step 2: Understand LIMIT 3

   LIMIT 3 restricts the output to only the first 3 rows after sorting.

3. Final Answer:

   The three products with the lowest prices. -> Option B

4. Quick Check:

   ORDER BY Price ASC + LIMIT 3 = lowest 3 products [OK]

Hint: ASC means lowest first; LIMIT picks top rows [OK]

Common Mistakes:

• Confusing ASC with DESC
• Thinking LIMIT causes error with ORDER BY
• Assuming sorting by ProductID instead of Price

4. Consider this query:

SELECT Name, Age FROM Users ORDER BY Age DESC, Name;

It returns an error. What is the likely cause?

medium

A. Missing ASC or DESC after the second column in ORDER BY.

B. ORDER BY cannot have more than one column.

C. Name column does not exist in Users table.

D. ORDER BY must come before SELECT.

Solution

1. Step 1: Check ORDER BY syntax for multiple columns

   When ordering by multiple columns, each column can have its own ASC or DESC. If omitted, ASC is assumed, so this is usually valid.

2. Step 2: Verify if omission causes error

   In standard SQL, omitting ASC/DESC after the second column is allowed and defaults to ASC. So this is not an error cause.

3. Step 3: Reconsider options

   ORDER BY cannot have more than one column. is false; multiple columns are allowed. Name column does not exist in Users table. depends on schema but question implies columns exist. ORDER BY must come before SELECT. is false; ORDER BY comes after SELECT.

4. Step 4: Real cause

   Actually, the query is valid syntax. The question says it returns an error, so the most likely cause is that the Name column does not exist in the table.

5. Final Answer:

   Name column does not exist in Users table. -> Option C

6. Quick Check:

   Nonexistent column in ORDER BY causes error [OK]

Hint: Check column names carefully in ORDER BY [OK]

Common Mistakes:

• Assuming ASC/DESC is mandatory for every column
• Thinking ORDER BY must be before SELECT
• Believing multiple columns in ORDER BY cause errors

5. You want to find the top 2 highest paid employees from the Staff table, but if two employees have the same salary, you want to list the one with the alphabetically earlier LastName first. Which query achieves this?

hard

A. SELECT * FROM Staff ORDER BY Salary DESC, LastName ASC LIMIT 2;

B. SELECT * FROM Staff ORDER BY LastName ASC, Salary DESC LIMIT 2;

C. SELECT * FROM Staff ORDER BY Salary ASC, LastName DESC LIMIT 2;

D. SELECT * FROM Staff ORDER BY Salary DESC LIMIT 2;

Solution

1. Step 1: Identify primary ordering by Salary descending

   We want the highest salaries first, so ORDER BY Salary DESC is correct.

2. Step 2: Add secondary ordering by LastName ascending

   If salaries tie, order by LastName ASC to get alphabetically earlier names first.

3. Step 3: Use LIMIT 2 to get top two employees

   LIMIT 2 restricts output to only two rows.

4. Final Answer:

   SELECT * FROM Staff ORDER BY Salary DESC, LastName ASC LIMIT 2; -> Option A

5. Quick Check:

   Salary DESC then LastName ASC with LIMIT 2 = correct [OK]

Hint: Order by main column DESC, tie-breaker ASC, then LIMIT [OK]

Common Mistakes:

• Reversing order directions
• Ordering by LastName before Salary
• Omitting tie-breaker ordering