Comparison operators in MySQL - Mini Project: Build & Apply

Using Comparison Operators in SQL Queries

📖 Scenario: You are managing a small bookstore database. You want to find books based on their prices and stock levels using comparison operators.

🎯 Goal: Build SQL queries step-by-step that use comparison operators to filter books by price and stock.

📋 What You'll Learn

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

Insert specific book records with exact values

Write a query using the > operator to find books priced above a certain value

Write a query using the <= operator to find books with stock less than or equal to a certain number

💡 Why This Matters

🌍 Real World

Filtering data by conditions is common in databases to find specific records, like books priced above a certain amount or with low stock.

💼 Career

Knowing how to use comparison operators in SQL is essential for database querying roles, data analysis, and backend development.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (varchar 100), price (decimal 5,2), and stock (integer). Then insert these exact rows: (1, 'Learn SQL', 29.99, 10), (2, 'Python Basics', 39.99, 5), (3, 'Data Science', 49.99, 0).

MySQL

-- Create the books table and insert the rows below
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set a price threshold variable

Create a variable called @price_limit and set it to 30.00. This will be used to find books priced above this value.

MySQL

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

INSERT INTO books (id, title, price, stock) VALUES
(1, 'Learn SQL', 29.99, 10),
(2, 'Python Basics', 39.99, 5),
(3, 'Data Science', 49.99, 0);

-- Set the price limit variable
-- Your code here

Need a hint?

Use SET to assign a value to the variable @price_limit.

3

Query books priced above the threshold

Write a SQL query to select all columns from books where price is greater than @price_limit. Use the > comparison operator.

MySQL

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

INSERT INTO books (id, title, price, stock) VALUES
(1, 'Learn SQL', 29.99, 10),
(2, 'Python Basics', 39.99, 5),
(3, 'Data Science', 49.99, 0);

SET @price_limit = 30.00;

-- Select books where price > @price_limit
-- Your code here

Need a hint?

Use WHERE price > @price_limit to filter the rows.

4

Query books with low stock using <= operator

Create a variable called @stock_limit and set it to 5. Then write a SQL query to select all columns from books where stock is less than or equal to @stock_limit. Use the <= comparison operator.

MySQL

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

INSERT INTO books (id, title, price, stock) VALUES
(1, 'Learn SQL', 29.99, 10),
(2, 'Python Basics', 39.99, 5),
(3, 'Data Science', 49.99, 0);

SET @price_limit = 30.00;

SELECT * FROM books WHERE price > @price_limit;

-- Set stock limit and select books where stock <= @stock_limit
-- Your code here

Need a hint?

Use SET @stock_limit = 5 and then WHERE stock <= @stock_limit in your query.