Operator precedence in WHERE in SQL - Mini Project: Build & Apply

Operator precedence in WHERE clause

📖 Scenario: You are managing a small bookstore database. You want to find books based on certain conditions using SQL queries.

🎯 Goal: Build a SQL query step-by-step that uses the WHERE clause with multiple conditions and understands how operator precedence affects the results.

📋 What You'll Learn

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

Insert 5 specific book records into the books table.

Write a SELECT query using WHERE with AND and OR conditions without parentheses.

Modify the query by adding parentheses to change the operator precedence and get a different result.

💡 Why This Matters

🌍 Real World

Filtering and sorting data from a database is a common task in many applications like online stores, libraries, and inventory systems.

💼 Career

Understanding operator precedence in SQL WHERE clauses helps you write accurate queries that return the correct data, a key skill for database developers and analysts.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), author (text), price (decimal), and in_stock (boolean). Then insert these exact 5 rows into books: (1, 'The Alchemist', 'Paulo Coelho', 10.99, true), (2, '1984', 'George Orwell', 8.99, false), (3, 'Clean Code', 'Robert C. Martin', 30.00, true), (4, 'The Pragmatic Programmer', 'Andrew Hunt', 25.50, true), (5, 'The Hobbit', 'J.R.R. Tolkien', 15.00, false).

SQL

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

Need a hint?

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

2

Write a query with mixed AND and OR conditions

Write a SQL SELECT query to get all columns from books where the price is less than 20 or the book is in_stock and the author is 'Paulo Coelho'. Use the WHERE clause with price < 20 OR in_stock = TRUE AND author = 'Paulo Coelho' exactly, without parentheses.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price DECIMAL,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, author, price, in_stock) VALUES
  (1, 'The Alchemist', 'Paulo Coelho', 10.99, TRUE),
  (2, '1984', 'George Orwell', 8.99, FALSE),
  (3, 'Clean Code', 'Robert C. Martin', 30.00, TRUE),
  (4, 'The Pragmatic Programmer', 'Andrew Hunt', 25.50, TRUE),
  (5, 'The Hobbit', 'J.R.R. Tolkien', 15.00, FALSE);

-- Write a SELECT query with WHERE price < 20 OR in_stock = TRUE AND author = 'Paulo Coelho'
-- Your code here

Need a hint?

Remember that AND has higher precedence than OR in SQL.

3

Add parentheses to change operator precedence

Modify the previous SELECT query by adding parentheses so that the OR condition is evaluated first. Use WHERE (price < 20 OR in_stock = TRUE) AND author = 'Paulo Coelho' exactly.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price DECIMAL,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, author, price, in_stock) VALUES
  (1, 'The Alchemist', 'Paulo Coelho', 10.99, TRUE),
  (2, '1984', 'George Orwell', 8.99, FALSE),
  (3, 'Clean Code', 'Robert C. Martin', 30.00, TRUE),
  (4, 'The Pragmatic Programmer', 'Andrew Hunt', 25.50, TRUE),
  (5, 'The Hobbit', 'J.R.R. Tolkien', 15.00, FALSE);

-- Modify the SELECT query to use parentheses: WHERE (price < 20 OR in_stock = TRUE) AND author = 'Paulo Coelho'
-- Your code here

Need a hint?

Use parentheses to group price < 20 OR in_stock = TRUE together.

4

Complete the query with ordering

Add an ORDER BY price DESC clause to the last query to sort the results by price from highest to lowest.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  author TEXT,
  price DECIMAL,
  in_stock BOOLEAN
);

INSERT INTO books (id, title, author, price, in_stock) VALUES
  (1, 'The Alchemist', 'Paulo Coelho', 10.99, TRUE),
  (2, '1984', 'George Orwell', 8.99, FALSE),
  (3, 'Clean Code', 'Robert C. Martin', 30.00, TRUE),
  (4, 'The Pragmatic Programmer', 'Andrew Hunt', 25.50, TRUE),
  (5, 'The Hobbit', 'J.R.R. Tolkien', 15.00, FALSE);

-- Add ORDER BY price DESC to the query
-- Your code here

Need a hint?

Use ORDER BY price DESC at the end of the query to sort results from highest to lowest price.