WHERE with NOT operator in SQL - Mini Project: Build & Apply

Filtering Data Using WHERE with NOT Operator

📖 Scenario: You are managing a small bookstore database. You want to find books that are not in a specific genre to recommend diverse reading options to customers.

🎯 Goal: Build an SQL query that selects all books except those in the genre 'Science Fiction' using the WHERE clause with the NOT operator.

📋 What You'll Learn

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

Insert exactly 5 books with specified titles and genres.

Write a query that selects all columns from books where the genre is not 'Science Fiction'.

💡 Why This Matters

🌍 Real World

Filtering data to exclude unwanted categories is common in reporting and data analysis, such as showing products not in a certain category or customers not from a specific region.

💼 Career

Knowing how to use WHERE with NOT helps database professionals write precise queries to retrieve exactly the data needed, improving efficiency and clarity.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and genre (text). Then insert these 5 books exactly: (1, 'Dune', 'Science Fiction'), (2, 'Pride and Prejudice', 'Romance'), (3, '1984', 'Science Fiction'), (4, 'The Hobbit', 'Fantasy'), (5, 'To Kill a Mockingbird', 'Classic').

SQL

-- Create the books table and insert 5 books
-- Your code here

Need a hint?

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

2

Set the genre to exclude

Create a variable or placeholder named excluded_genre and set it to the string 'Science Fiction'. This will be used to filter out books of this genre.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT
);

INSERT INTO books (id, title, genre) VALUES
  (1, 'Dune', 'Science Fiction'),
  (2, 'Pride and Prejudice', 'Romance'),
  (3, '1984', 'Science Fiction'),
  (4, 'The Hobbit', 'Fantasy'),
  (5, 'To Kill a Mockingbird', 'Classic');

-- Define the genre to exclude
-- Your code here

Need a hint?

Since standard SQL does not support variables in plain queries, use a comment to indicate the excluded genre.

3

Write the query using WHERE NOT

Write an SQL SELECT statement to get all columns from books where the genre is not equal to 'Science Fiction'. Use the WHERE clause with the NOT operator and the equality operator =.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT
);

INSERT INTO books (id, title, genre) VALUES
  (1, 'Dune', 'Science Fiction'),
  (2, 'Pride and Prejudice', 'Romance'),
  (3, '1984', 'Science Fiction'),
  (4, 'The Hobbit', 'Fantasy'),
  (5, 'To Kill a Mockingbird', 'Classic');

-- Variable to hold the genre to exclude
-- This is a placeholder for the filtering value
-- In SQL scripts, this might be a comment or a variable in procedural extensions
-- For this project, just write a comment line
-- excluded_genre = 'Science Fiction'

-- Write the SELECT query to get all books except those in 'Science Fiction'
-- Your code here

Need a hint?

Use WHERE NOT genre = 'Science Fiction' to exclude that genre.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by title in ascending order.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT
);

INSERT INTO books (id, title, genre) VALUES
  (1, 'Dune', 'Science Fiction'),
  (2, 'Pride and Prejudice', 'Romance'),
  (3, '1984', 'Science Fiction'),
  (4, 'The Hobbit', 'Fantasy'),
  (5, 'To Kill a Mockingbird', 'Classic');

-- Variable to hold the genre to exclude
-- This is a placeholder for the filtering value
-- In SQL scripts, this might be a comment or a variable in procedural extensions
-- For this project, just write a comment line
-- excluded_genre = 'Science Fiction'

SELECT * FROM books
WHERE NOT genre = 'Science Fiction'
-- Your code here

Need a hint?

Use ORDER BY title ASC to sort the results alphabetically by title.