2.6 Lab - Filtering Data

Introduction

In this lab, you will practice filtering data in a MySQL database using the WHERE clause and related operators. You will learn how to filter data with comparison operators, logical operators, pattern matching, and ranges. By the end of this lab, you will have a solid foundation in querying and filtering data to retrieve precise results.

Objectives

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

1. Use the WHERE clause to filter rows based on conditions.
2. Apply comparison and logical operators to combine conditions.
3. Use pattern matching with the LIKE operator.
4. Filter rows using IN, BETWEEN, and NULL operators.

---

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 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 NOT NULL
   );
   

2. Verify the table structure:

   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),
   ('Desk Chair', 'Furniture', 150.00, 15),
   ('Notebook', 'Stationery', 5.00, 50),
   ('Coffee Maker', 'Home Appliances', 80.00, 20),
   ('Headphones', 'Electronics', 200.00, 30);
   

2. Verify the data by querying the table:

   SELECT * FROM products;
   

---

Step 4: Filter Data

Task 1: Filter by a Single Condition

• Retrieve all products in the "Electronics" category:

  SELECT * FROM products 
  WHERE category = 'Electronics';
  

Task 2: Filter by Multiple Conditions

• Retrieve products in the "Furniture" category with a price greater than $100:

  SELECT * FROM products 
  WHERE category = 'Furniture' AND price > 100;
  

Task 3: Filter Using Comparison Operators

• Retrieve products with a stock of less than 20:

  SELECT name, stock 
  FROM products 
  WHERE stock < 20;
  

Task 4: Filter with OR

• Retrieve products in either the "Stationery" or "Home Appliances" category:

  SELECT * FROM products 
  WHERE category = 'Stationery' OR category = 'Home Appliances';
  

---

Step 5: Use Pattern Matching with LIKE

Task 1: Match Names Starting with "C"

• Retrieve products with names starting with "C":

  SELECT * FROM products 
  WHERE name LIKE 'C%';
  

Task 2: Match Names Ending with "e"

• Retrieve products with names ending in "e":

  SELECT * FROM products 
  WHERE name LIKE '%e';
  

Task 3: Match Names Containing "top"

• Retrieve products with names containing "top":

  SELECT * FROM products 
  WHERE name LIKE '%top%';
  

---

Step 6: Filter Data with IN

Task 1: Filter by Multiple Categories

• Retrieve products in the "Electronics" or "Furniture" category:

  SELECT * FROM products 
  WHERE category IN ('Electronics', 'Furniture');
  

---

Step 7: Filter Data with BETWEEN

Task 1: Filter by Price Range

• Retrieve products priced between $50 and$200:

  SELECT * FROM products 
  WHERE price BETWEEN 50 AND 200;
  

Task 2: Filter by Stock Range

• Retrieve products with stock levels between 10 and 30:

  SELECT name, stock 
  FROM products 
  WHERE stock BETWEEN 10 AND 30;
  

---

Step 8: Filter NULL Values

Task 1: Handle NULL Values

• Add a product with a NULL value for stock:

  INSERT INTO products (name, category, price, stock) 
  VALUES ('Tablet', 'Electronics', 300.00, NULL);
  

• Retrieve products with NULL stock:

  SELECT * FROM products 
  WHERE stock IS NULL;
  

• Retrieve products with non-NULL stock:

  SELECT * FROM products 
  WHERE stock IS NOT NULL;
  

---

Step 9: Combine Clauses for Complex Filtering

Task 1: Retrieve Electronics Under Specific Conditions

• Retrieve electronics priced above $100 and with stock greater than 5:

  SELECT * FROM products 
  WHERE category = 'Electronics' AND price > 100 AND stock > 5;
  

Task 2: Retrieve Top Priced Items

• Retrieve the top 3 most expensive products:

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

---

Step 10: Exit MySQL

1. Exit the MySQL command line:

   EXIT;
   

2. Close your terminal or command prompt.

---

Summary

In this lab, you practiced filtering data in MySQL using the WHERE clause and related operators. You learned how to:

• Use comparison and logical operators.
• Filter data with pattern matching using LIKE.
• Use the IN and BETWEEN operators for efficient filtering.
• Handle NULL values in queries.
• Combine multiple clauses for complex filtering.

These skills are essential for querying large datasets and retrieving only the information you need. In the next lab, we will explore sorting and grouping data for better organization and analysis.