2.4 Lab - SELECT Statements

Introduction

In this lab, you will learn how to use the SELECT statement to retrieve data from a MySQL database. This lab provides hands-on practice with various features of the SELECT statement, including filtering data with WHERE, sorting results using ORDER BY, limiting rows with LIMIT, and performing basic aggregation with functions like COUNT and AVG.

Objectives

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

1. Use the SELECT statement to query data from a table.
2. Filter rows using the WHERE clause.
3. Sort query results with the ORDER BY clause.
4. Limit the number of rows returned with LIMIT.
5. Use aggregate functions like COUNT, SUM, and AVG.

---

Lab Steps

---

Step 1: Connect to MySQL

1. Open your terminal or command prompt.

2. Log in to the MySQL server with the following command:

   mysql -u root -p
   

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

3. Use the lab_database (or create it if it does not 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 NOT NULL
   );
   

2. Verify the table creation:

   DESCRIBE products;
   

---

Step 3: Insert Sample Data

1. Insert the following sample data into the products table:

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

2. Confirm the data insertion by querying all rows:

   SELECT * FROM products;
   

---

Step 4: Query Data

Task 1: Retrieve All Data

SELECT * FROM products;

• This retrieves all rows and columns from the products table.

Task 2: Retrieve Specific Columns

SELECT name, price FROM products;

• This retrieves only the name and price columns.

Task 3: Filter Data with WHERE

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

• This retrieves the names and prices of all products in the "Electronics" category.

Task 4: Filter Data with Multiple Conditions

SELECT name, price FROM products WHERE price > 100 AND stock < 20;

• This retrieves products with a price greater than $100 and a stock less than 20.

---

Step 5: Sort and Limit Results

Task 1: Sort by Price in Ascending Order

SELECT name, price FROM products ORDER BY price ASC;

• This retrieves the names and prices of products, sorted by price in ascending order.

Task 2: Sort by Price in Descending Order

SELECT name, price FROM products ORDER BY price DESC;

• This retrieves the names and prices of products, sorted by price in descending order.

Task 3: Limit the Number of Rows

SELECT name, price FROM products LIMIT 3;

• This retrieves only the first 3 rows.

---

Step 6: Use Aggregate Functions

Task 1: Count the Number of Products

SELECT COUNT(*) AS total_products FROM products;

• This counts the total number of products in the table.

Task 2: Calculate the Average Price of Products

SELECT AVG(price) AS average_price FROM products;

• This calculates the average price of all products.

Task 3: Calculate the Total Stock

SELECT SUM(stock) AS total_stock FROM products;

• This calculates the total number of items in stock across all products.

Task 4: Find the Maximum and Minimum Prices

SELECT MAX(price) AS highest_price, MIN(price) AS lowest_price FROM products;

• This retrieves the highest and lowest prices among all products.

---

Step 7: Exit MySQL

1. Exit the MySQL command line:

   EXIT;
   

2. Close your terminal or command prompt.

---

Summary

In this lab, you practiced using the SELECT statement in MySQL to query data from a table. You learned how to:

• Retrieve all or specific columns from a table.
• Filter data using the WHERE clause.
• Sort query results using ORDER BY.
• Limit the number of rows with LIMIT.
• Use aggregate functions such as COUNT, SUM, and AVG.

These skills form the foundation for interacting with relational databases and are essential for writing effective SQL queries. In the next lab, we will explore how to filter, sort, and join data across multiple tables.