AND, OR, NOT logical operators in MySQL - Mini Project: Build & Apply

Using AND, OR, NOT Logical Operators in SQL Queries

📖 Scenario: You are managing a small bookstore database. You want to find books based on different conditions like genre, price, and availability.

🎯 Goal: Build SQL queries using AND, OR, and NOT logical operators to filter books from the books table.

📋 What You'll Learn

Create a books table with columns id, title, genre, price, and in_stock.

Insert specific book records into the books table.

Write a query using AND to find books that are in a specific genre and below a certain price.

Write a query using OR to find books that are either in stock or below a certain price.

Write a query using NOT to find books that are not in a specific genre.

💡 Why This Matters

🌍 Real World

Filtering data in databases is a daily task for managing inventories, customer data, or any information system.

💼 Career

Understanding logical operators in SQL is essential for database administrators, data analysts, and backend developers to write effective queries.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (varchar 100), genre (varchar 50), price (decimal 5,2), and in_stock (boolean). Then insert these exact rows: (1, 'The Great Gatsby', 'Fiction', 10.99, TRUE), (2, 'Python Basics', 'Education', 25.00, FALSE), (3, 'Cooking 101', 'Cooking', 15.50, TRUE), (4, 'Mystery Night', 'Fiction', 8.99, FALSE), (5, 'Gardening Tips', 'Hobby', 12.00, TRUE).

MySQL

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

Hint

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

2

Set a price limit variable

Create a variable called @price_limit and set it to 15.00 to use in your queries.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2),
  in_stock BOOLEAN
);

INSERT INTO books (id, title, genre, price, in_stock) VALUES
(1, 'The Great Gatsby', 'Fiction', 10.99, TRUE),
(2, 'Python Basics', 'Education', 25.00, FALSE),
(3, 'Cooking 101', 'Cooking', 15.50, TRUE),
(4, 'Mystery Night', 'Fiction', 8.99, FALSE),
(5, 'Gardening Tips', 'Hobby', 12.00, TRUE);

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

Hint

Use SET @variable_name = value; to create a variable in MySQL.

3

Write a query using AND and OR

Write a query to select title and price from books where the genre is 'Fiction' AND the price is less than @price_limit. Then write another query to select title and price where the book is in_stock OR the price is less than @price_limit.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2),
  in_stock BOOLEAN
);

INSERT INTO books (id, title, genre, price, in_stock) VALUES
(1, 'The Great Gatsby', 'Fiction', 10.99, TRUE),
(2, 'Python Basics', 'Education', 25.00, FALSE),
(3, 'Cooking 101', 'Cooking', 15.50, TRUE),
(4, 'Mystery Night', 'Fiction', 8.99, FALSE),
(5, 'Gardening Tips', 'Hobby', 12.00, TRUE);

SET @price_limit = 15.00;

-- Query 1: Select title and price where genre is 'Fiction' AND price < @price_limit
-- Query 2: Select title and price where in_stock is TRUE OR price < @price_limit
-- Your code here

Hint

Use WHERE with AND and OR to combine conditions.

4

Write a query using NOT

Write a query to select title and genre from books where the genre is not 'Fiction'.

MySQL

CREATE TABLE books (
  id INT,
  title VARCHAR(100),
  genre VARCHAR(50),
  price DECIMAL(5,2),
  in_stock BOOLEAN
);

INSERT INTO books (id, title, genre, price, in_stock) VALUES
(1, 'The Great Gatsby', 'Fiction', 10.99, TRUE),
(2, 'Python Basics', 'Education', 25.00, FALSE),
(3, 'Cooking 101', 'Cooking', 15.50, TRUE),
(4, 'Mystery Night', 'Fiction', 8.99, FALSE),
(5, 'Gardening Tips', 'Hobby', 12.00, TRUE);

SET @price_limit = 15.00;

SELECT title, price FROM books WHERE genre = 'Fiction' AND price < @price_limit;

SELECT title, price FROM books WHERE in_stock = TRUE OR price < @price_limit;

-- Query: Select title and genre where genre is NOT 'Fiction'
-- Your code here

Hint

Use NOT before a condition to select rows that do not match it.

Practice

1. Which logical operator in MySQL requires all conditions to be true for the whole expression to be true?

easy

A. XOR

B. OR

C. NOT

D. AND

Solution

1. Step 1: Understand AND operator behavior

   The AND operator returns true only if every condition joined by it is true.

2. Step 2: Compare with other operators

   OR requires at least one true condition, NOT reverses truth, XOR is exclusive or.

3. Final Answer:

   AND -> Option D

4. Quick Check:

   AND means all true = A [OK]

Hint: AND means all conditions must be true [OK]

Common Mistakes:

• Confusing AND with OR
• Thinking NOT requires all true
• Mixing XOR with AND

2. Which of the following is the correct syntax to select rows where age is greater than 18 and status is 'active'?

easy

A. SELECT * FROM users WHERE age > 18 AND status = 'active';

B. SELECT * FROM users WHERE NOT age > 18 AND status = 'active';

C. SELECT * FROM users WHERE age > 18 OR status = 'active';

D. SELECT * FROM users WHERE age > 18 NOT status = 'active';

Solution

1. Step 1: Identify correct logical operator for both conditions

   We want rows where both age > 18 and status = 'active' are true, so use AND.

2. Step 2: Check syntax correctness

   SELECT * FROM users WHERE age > 18 AND status = 'active'; uses AND correctly and has valid SQL syntax.

3. Final Answer:

   SELECT * FROM users WHERE age > 18 AND status = 'active'; -> Option A

4. Quick Check:

   AND joins conditions correctly = C [OK]

Hint: Use AND to combine multiple true conditions [OK]

Common Mistakes:

• Using OR instead of AND
• Incorrect NOT placement
• Syntax errors like missing AND

3. Given the table employees with columns department and salary, what rows will this query return?

SELECT * FROM employees WHERE department = 'Sales' OR salary > 50000 AND NOT department = 'HR';

medium

A. Rows where department is 'Sales' or salary > 50000 or department is not 'HR'

B. Rows where department is 'Sales' and salary > 50000 and department is not 'HR'

C. Rows where department is 'Sales' or salary > 50000 and department is not 'HR'

D. Rows where salary > 50000 only

Solution

1. Step 1: Understand operator precedence

   AND has higher precedence than OR, so condition is: department = 'Sales' OR (salary > 50000 AND NOT department = 'HR').

2. Step 2: Interpret the conditions

   Rows match if department is 'Sales', or if salary > 50000 and department is not 'HR'.

3. Final Answer:

   Rows where department is 'Sales' or salary > 50000 and department is not 'HR' -> Option C

4. Quick Check:

   AND before OR, NOT reverses condition = B [OK]

Hint: AND binds tighter than OR; use parentheses to clarify [OK]

Common Mistakes:

• Ignoring operator precedence
• Misreading NOT placement
• Assuming OR applies to all conditions

4. Identify the error in this query:

SELECT * FROM orders WHERE NOT order_date > '2023-01-01' AND status = 'shipped' OR;

medium

A. Incorrect use of NOT operator

B. Missing condition after OR

C. AND should be OR

D. order_date comparison syntax error

Solution

1. Step 1: Check query syntax

   The query ends with OR but no condition follows it, causing syntax error.

2. Step 2: Verify other parts

   NOT and AND usage is correct; order_date comparison is valid.

3. Final Answer:

   Missing condition after OR -> Option B

4. Quick Check:

   OR must have two conditions = A [OK]

Hint: OR must always connect two conditions [OK]

Common Mistakes:

• Leaving OR without a following condition
• Misplacing NOT operator
• Confusing AND and OR usage

5. You want to select all customers who are NOT in the 'VIP' group AND have made a purchase OR have a balance greater than 1000. Which query correctly expresses this logic?

hard

A. SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000);

B. SELECT * FROM customers WHERE NOT (group = 'VIP' AND purchases > 0) OR balance > 1000;

C. SELECT * FROM customers WHERE NOT group = 'VIP' AND purchases > 0 OR balance > 1000;

D. SELECT * FROM customers WHERE NOT group = 'VIP' OR purchases > 0 AND balance > 1000;

Solution

1. Step 1: Translate the logic into conditions

   We want customers NOT in 'VIP' group AND (have purchases OR balance > 1000).

2. Step 2: Check operator precedence and parentheses

   AND has higher precedence than OR, so parentheses around (purchases > 0 OR balance > 1000) ensure correct grouping.

3. Step 3: Evaluate options

   SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000); correctly uses NOT on group, AND with grouped OR conditions. Others misplace parentheses or NOT.

4. Final Answer:

   SELECT * FROM customers WHERE NOT group = 'VIP' AND (purchases > 0 OR balance > 1000); -> Option A

5. Quick Check:

   NOT group, AND grouped OR = D [OK]

Hint: Use parentheses to group OR conditions with AND [OK]

Common Mistakes:

• Missing parentheses causing wrong logic
• Applying NOT to wrong part
• Confusing AND and OR precedence