2.8 Lab - Sorting Data

Introduction

Sorting data is essential for organizing and analyzing query results. In this lab, you will practice sorting data using the ORDER BY clause in MySQL. You will explore sorting by single and multiple columns, using ascending and descending order, handling NULL values, and combining sorting with the LIMIT clause. This lab provides hands-on experience with sorting data effectively from the command line.

Objectives

By the end of this lab, you will be able to:

1. Sort query results by a single column.
2. Sort query results by multiple columns.
3. Handle NULL values during sorting.
4. Use the LIMIT clause to retrieve a specific number of rows.
5. Combine sorting with filtering to create complex queries.

---

Lab Steps

---

Step 1: Connect to MySQL

1. Open your terminal or command prompt.

2. Log in to the MySQL server:

   mysql -u root -p
   

   • Replace root with your username if necessary.
   • Enter your password when prompted.

3. Use the lab_database (or create it if it doesn’t exist):

   CREATE DATABASE IF NOT EXISTS lab_database;
   USE lab_database;
   

---

Step 2: Create a Table

1. Create a table named products with the following structure:

   • id: Integer, auto-increment, primary key.
   • name: Text, stores the product name.
   • category: Text, stores the product category.
   • price: Decimal(10, 2), stores the product price.
   • stock: Integer, stores the number of items in stock.

   CREATE TABLE products (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name TEXT NOT NULL,
       category TEXT NOT NULL,
       price DECIMAL(10, 2) NOT NULL,
       stock INT
   );
   

2. Verify the table structure:

   DESCRIBE products;
   

---

Step 3: Insert Sample Data

1. Insert the following data into the products table:

   INSERT INTO products (name, category, price, stock) VALUES
   ('Laptop', 'Electronics', 1200.00, 10),
   ('Desk Chair', 'Furniture', 150.00, 15),
   ('Notebook', 'Stationery', 5.00, 50),
   ('Coffee Maker', 'Home Appliances', 80.00, 20),
   ('Headphones', 'Electronics', 200.00, 30),
   ('Tablet', 'Electronics', 300.00, NULL);
   

2. Verify the data:

   SELECT * FROM products;
   

---

Step 4: Sort Data

Task 1: Sort by a Single Column

• Sort products by price in ascending order:

  SELECT name, price 
  FROM products 
  ORDER BY price ASC;
  

• Sort products by price in descending order:

  SELECT name, price 
  FROM products 
  ORDER BY price DESC;
  

---

Task 2: Sort by Multiple Columns

• Sort by category in ascending order and price within each category in descending order:

  SELECT name, category, price 
  FROM products 
  ORDER BY category ASC, price DESC;
  

---

Task 3: Handle NULL Values

• Sort by stock in ascending order (note that NULL values appear first by default):

  SELECT name, stock 
  FROM products 
  ORDER BY stock ASC;
  

• Sort by stock in descending order (note that NULL values appear last by default):

  SELECT name, stock 
  FROM products 
  ORDER BY stock DESC;
  

• Explicitly place NULL values at the end in ascending order:

  SELECT name, stock 
  FROM products 
  ORDER BY stock IS NULL ASC, stock ASC;
  

---

Task 4: Combine Sorting with Filtering

• Retrieve products in the "Electronics" category, sorted by price in descending order:

  SELECT name, price 
  FROM products 
  WHERE category = 'Electronics' 
  ORDER BY price DESC;
  

---

Task 5: Limit the Number of Rows

• Retrieve the top 3 most expensive products:

  SELECT name, price 
  FROM products 
  ORDER BY price DESC 
  LIMIT 3;
  

• Retrieve the bottom 2 cheapest products:

  SELECT name, price 
  FROM products 
  ORDER BY price ASC 
  LIMIT 2;
  

---

Step 5: Explore Advanced Sorting

Task 1: Sort by Expressions

• Sort products by the total value of stock (price * stock) in descending order:

  SELECT name, price, stock, price * stock AS total_value 
  FROM products 
  ORDER BY total_value DESC;
  

Task 2: Sort by Column Aliases

• Calculate a discounted price and sort by it in ascending order:

  SELECT name, price, price * 0.9 AS discounted_price 
  FROM products 
  ORDER BY discounted_price ASC;
  

---

Step 6: Exit MySQL

1. Exit the MySQL command line:

   EXIT;
   

2. Close your terminal or command prompt.

---

Summary

In this lab, you practiced sorting data in MySQL using the ORDER BY clause. You learned how to:

• Sort data by single and multiple columns.
• Specify ascending or descending order.
• Handle NULL values explicitly in sorting.
• Combine sorting with filtering and limit the number of rows returned.

Sorting data is a critical skill for presenting query results in a clear and organized manner. In the next lab, you will explore grouping data to summarize and analyze information efficiently.