Logical Operators (AND, OR, NOT)

Logical operators are used in the WHERE clause to combine multiple conditions, allowing you to create more specific and powerful filters for your data. The main logical operators are AND, OR, and NOT.

Database Preparation

Run this block to set up the Books table.

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INTEGER,
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 101, 'Fantasy', 1954),
       (4, 'Dune', 103, 'Sci-Fi', 1965);

SELECT * FROM Books;

1. The AND Operator

Query:

SELECT Title, Genre, PublishedYear
FROM Books
WHERE Genre = 'Fantasy' AND PublishedYear > 1950;

2. The OR Operator

Query:

SELECT Title, Genre
FROM Books
WHERE Genre = 'Sci-Fi' OR Genre = 'Dystopian';

3. The NOT Operator

Query:

SELECT Title, Genre
FROM Books
WHERE NOT Genre = 'Fantasy';

Operation: Find all books that are 'Fantasy' AND (were published either before 1940 OR after 1960).

Query:

SELECT Title, Genre, PublishedYear
FROM Books
WHERE Genre = 'Fantasy' AND (PublishedYear < 1940 OR PublishedYear > 1960);

Explanation:

1. The conditions inside the parentheses are evaluated first: (PublishedYear < 1940 OR PublishedYear > 1960).
2. A book will pass this check if it was published before 1940 (like "The Hobbit") or after 1960 (like "Dune").
3. Then, the AND condition is applied. The query will only return rows that are also in the 'Fantasy' genre.

Result:

Without Parentheses: If we had written WHERE Genre = 'Fantasy' AND PublishedYear < 1940 OR PublishedYear > 1960, the AND would be evaluated first, giving a different and incorrect result. Parentheses are crucial for clarity and correctness.

Hands-on Exercise

Task: Find all books that are NOT in the 'Fantasy' or 'Sci-Fi' genres.

Your Query:

-- Write your query here

Expected Results: