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

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

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

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

Hint

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

Practice

1. Which of the following comparison operators in MySQL means "not equal to"?

easy

A. =

B. <>

C. >=

D. <

Solution

1. Step 1: Understand the meaning of comparison operators

   The operator "<>" is used in SQL to mean "not equal to".

2. Step 2: Compare with other operators

   "=" means equal, ">=" means greater than or equal, and "<" means less than.

3. Final Answer:

   <> -> Option B

4. Quick Check:

   Not equal operator = <> [OK]

Hint: Remember: <> means not equal in SQL [OK]

Common Mistakes:

• Confusing = with not equal
• Using != which is valid but less common in MySQL
• Mixing up > and < operators

2. Which of the following is the correct syntax to check if column age is greater than 18 in a WHERE clause?

easy

A. WHERE age > 18

B. WHERE age => 18

C. WHERE age =< 18

D. WHERE age <> 18

Solution

1. Step 1: Identify the correct greater than operator

   The correct operator for "greater than" is ">". The operator ">=" means "greater than or equal to".

2. Step 2: Check syntax correctness

   "=>" and "=<" are invalid operators in SQL syntax. "<>" means not equal, which is not what we want.

3. Final Answer:

   WHERE age > 18 -> Option A

4. Quick Check:

   Greater than operator is > [OK]

Hint: Use > for greater than, not => or =< [OK]

Common Mistakes:

• Using => or =< which are invalid
• Confusing <> with greater than
• Using = instead of >

3. Given the table Employees with a column salary, what will the query SELECT * FROM Employees WHERE salary <= 50000; return?

medium

A. All employees with salary less than or equal to 50000

B. All employees with salary exactly 50000

C. All employees with salary less than 50000

D. All employees with salary greater than 50000

Solution

1. Step 1: Understand the meaning of <= operator

   The operator <= means "less than or equal to".

2. Step 2: Interpret the query condition

   The query filters employees whose salary is either less than 50000 or exactly 50000.

3. Final Answer:

   All employees with salary less than or equal to 50000 -> Option A

4. Quick Check:

   <= means less than or equal [OK]

Hint: <= means less than or equal, includes the number [OK]

Common Mistakes:

• Thinking <= means only less than
• Confusing <= with <
• Ignoring the equal part of <= operator

4. Identify the error in this MySQL query: SELECT * FROM Products WHERE price ! 100;

medium

A. Missing comparison operator after !

B. No error, query is correct

C. Missing quotes around 100

D. Incorrect use of ! for not equal, should be != or <>

Solution

1. Step 1: Analyze the use of ! operator

   In MySQL, "!" alone is not a valid comparison operator. For "not equal", use "!=" or "<>".

2. Step 2: Check the query syntax

   The query uses "price ! 100" which is invalid syntax and will cause an error.

3. Final Answer:

   Incorrect use of ! for not equal, should be != or <> -> Option D

4. Quick Check:

   Use != or <> for not equal [OK]

Hint: Use != or <> for not equal, not just ! [OK]

Common Mistakes:

• Using ! alone as not equal
• Forgetting to use != or <>
• Assuming ! means not equal in SQL

5. You want to select all customers whose age is between 20 and 30 inclusive. Which WHERE clause correctly uses comparison operators?

hard

A. WHERE age > 20 AND age < 30

B. WHERE age = 20 OR age = 30

C. WHERE age >= 20 AND age <= 30

D. WHERE age <> 20 AND age <> 30

Solution

1. Step 1: Understand the requirement for inclusive range

   Inclusive means including 20 and 30, so use >= and <= operators.

2. Step 2: Analyze each option

   WHERE age > 20 AND age < 30 excludes 20 and 30 (strictly greater and less). WHERE age = 20 OR age = 30 only selects exactly 20 or 30, not the range. WHERE age <> 20 AND age <> 30 excludes 20 and 30, opposite of requirement.

3. Final Answer:

   WHERE age >= 20 AND age <= 30 -> Option C

4. Quick Check:

   Use >= and <= for inclusive ranges [OK]

Hint: Use >= and <= for inclusive range checks [OK]

Common Mistakes:

• Using > and < excludes boundary values
• Using OR instead of AND for range
• Using <> which excludes values