ORDER BY with ASC and DESC in SQL - Mini Project: Build & Apply

Sorting Products by Price and Name

📖 Scenario: You are managing a small online store database. You want to organize your product list so that it is easy to find items by price and name.

🎯 Goal: Build SQL queries that sort products by their price in ascending order and by their name in descending order.

📋 What You'll Learn

Create a table called products with columns id, name, and price.

Insert exactly three products with specified names and prices.

Write a query to select all products ordered by price in ascending order.

Write a query to select all products ordered by name in descending order.

💡 Why This Matters

🌍 Real World

Sorting data is a common task in databases to help users find information quickly, like sorting products by price or name in an online store.

💼 Career

Knowing how to use ORDER BY with ASC and DESC is essential for database querying roles, data analysis, and backend development.

1

Create the products table and insert data

Create a table called products with columns id (integer), name (text), and price (integer). Then insert these three products exactly: (1, 'Apple', 100), (2, 'Banana', 50), and (3, 'Cherry', 75).

SQL

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

Need a hint?

Use CREATE TABLE to make the table, then use INSERT INTO three times to add the products.

2

Set up the query to order by price ascending

Write a SQL query that selects all columns from products and orders the results by the price column in ascending order using ORDER BY price ASC.

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES (1, 'Apple', 100);
INSERT INTO products (id, name, price) VALUES (2, 'Banana', 50);
INSERT INTO products (id, name, price) VALUES (3, 'Cherry', 75);

-- Write a query to select all products ordered by price ascending
-- Your code here

Need a hint?

Use SELECT * FROM products ORDER BY price ASC; to get products sorted by price from lowest to highest.

3

Write a query to order products by name descending

Write a SQL query that selects all columns from products and orders the results by the name column in descending order using ORDER BY name DESC.

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES (1, 'Apple', 100);
INSERT INTO products (id, name, price) VALUES (2, 'Banana', 50);
INSERT INTO products (id, name, price) VALUES (3, 'Cherry', 75);

SELECT * FROM products ORDER BY price ASC;

-- Write a query to select all products ordered by name descending
-- Your code here

Need a hint?

Use SELECT * FROM products ORDER BY name DESC; to get products sorted by name from Z to A.

4

Combine ordering by price ascending and name descending

Write a SQL query that selects all columns from products and orders the results first by price in ascending order, then by name in descending order using ORDER BY price ASC, name DESC.

SQL

CREATE TABLE products (id INTEGER, name TEXT, price INTEGER);
INSERT INTO products (id, name, price) VALUES (1, 'Apple', 100);
INSERT INTO products (id, name, price) VALUES (2, 'Banana', 50);
INSERT INTO products (id, name, price) VALUES (3, 'Cherry', 75);

SELECT * FROM products ORDER BY price ASC;

SELECT * FROM products ORDER BY name DESC;

-- Write a query to select all products ordered by price ascending and then name descending
-- Your code here

Need a hint?

Use ORDER BY price ASC, name DESC to sort by price first, then by name in reverse order.