ASC and DESC direction in MySQL - Mini Project: Build & Apply

Sorting Data with ASC and DESC in SQL

📖 Scenario: You are managing a small bookstore database. You want to organize the list of books by their prices to help customers find the cheapest or most expensive books easily.

🎯 Goal: Build SQL queries that sort the books by price in ascending and descending order using ASC and DESC.

📋 What You'll Learn

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

Insert exactly three books with specified titles and prices.

Write a query to select all books sorted by price in ascending order using ASC.

Write a query to select all books sorted by price in descending order using DESC.

💡 Why This Matters

🌍 Real World

Sorting data is essential in databases to organize information for reports, user views, or analytics. For example, customers often want to see products from cheapest to most expensive.

💼 Career

Database developers and analysts frequently write queries with sorting to prepare data for applications, dashboards, or decision-making.

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 Alchemist', 10.99), (2, '1984', 8.99), and (3, 'Clean Code', 25.50).

MySQL

-- Create the books table and insert the three books
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the three books with exact values.

2

Set up the base SELECT query

Write a SQL query that selects all columns from the books table. Store this query in a variable called base_query as a string.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Alchemist', 10.99),
(2, '1984', 8.99),
(3, 'Clean Code', 25.50);

-- Create a variable base_query with the SELECT statement
-- Your code here

Need a hint?

Use SET @base_query = 'SELECT * FROM books' to store the query string.

3

Write the ascending order query using ASC

Write a SQL query that selects all columns from books and sorts the results by price in ascending order using ORDER BY price ASC. Store this query in a variable called asc_query as a string.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Alchemist', 10.99),
(2, '1984', 8.99),
(3, 'Clean Code', 25.50);

SET @base_query = 'SELECT * FROM books';

-- Create a variable asc_query with the query to sort by price ascending
-- Your code here

Need a hint?

Use ORDER BY price ASC to sort prices from lowest to highest.

4

Write the descending order query using DESC

Write a SQL query that selects all columns from books and sorts the results by price in descending order using ORDER BY price DESC. Store this query in a variable called desc_query as a string.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO books (id, title, price) VALUES
(1, 'The Alchemist', 10.99),
(2, '1984', 8.99),
(3, 'Clean Code', 25.50);

SET @base_query = 'SELECT * FROM books';

SET @asc_query = 'SELECT * FROM books ORDER BY price ASC';

-- Create a variable desc_query with the query to sort by price descending
-- Your code here

Need a hint?

Use ORDER BY price DESC to sort prices from highest to lowest.