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

Need a 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

Need a 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

Need a 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

Need a hint?

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