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

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

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

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

Hint

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

Practice

1. In SQL, which operator is evaluated first in a WHERE clause without parentheses: AND or OR?

easy

A. OR is evaluated before AND

B. AND is evaluated before OR

C. Both are evaluated at the same time

D. It depends on the database system

Solution

1. Step 1: Understand SQL operator precedence

   In SQL, AND has higher precedence than OR. This means AND conditions are evaluated before OR conditions when no parentheses are used.

2. Step 2: Apply to WHERE clause evaluation

   Without parentheses, SQL evaluates all AND conditions first, then applies OR. This affects which rows match the query.

3. Final Answer:

   AND is evaluated before OR -> Option B

4. Quick Check:

   Operator precedence: AND > OR [OK]

Hint: Remember: AND before OR unless parentheses change it [OK]

Common Mistakes:

• Thinking OR is evaluated before AND
• Assuming both have equal precedence
• Believing evaluation order depends on database

2. Which of the following WHERE clauses is syntactically correct in SQL?

easy

A. WHERE age > 30 OR AND salary < 50000 OR city = 'NY'

B. WHERE age > 30 AND OR salary < 50000 city = 'NY'

C. WHERE age > 30 salary < 50000 OR city = 'NY'

D. WHERE age > 30 AND salary < 50000 OR city = 'NY'

Solution

1. Step 1: Check logical operator usage

   WHERE age > 30 AND salary < 50000 OR city = 'NY' uses AND and OR correctly between conditions. Options A, B, and D have misplaced or missing operators.

2. Step 2: Validate syntax correctness

   SQL requires logical operators between conditions. WHERE age > 30 AND salary < 50000 OR city = 'NY' follows this rule, so it is syntactically valid.

3. Final Answer:

   WHERE age > 30 AND salary < 50000 OR city = 'NY' -> Option D

4. Quick Check:

   Logical operators must separate conditions [OK]

Hint: Operators must be between conditions, no missing or extra words [OK]

Common Mistakes:

• Placing AND or OR without conditions
• Missing operators between conditions
• Using operators in wrong order

3. Given the table Employees with columns age, salary, and city, what rows will this query return?

SELECT * FROM Employees WHERE age > 30 OR salary < 50000 AND city = 'NY';

medium

A. Rows where age > 30 or (salary < 50000 and city = 'NY')

B. Rows where (age > 30 or salary < 50000) and city = 'NY'

C. Rows where age > 30 and salary < 50000 and city = 'NY'

D. Rows where age > 30 and (salary < 50000 or city = 'NY')

Solution

1. Step 1: Apply operator precedence rules

   Since AND has higher precedence than OR, the condition is evaluated as: age > 30 OR (salary < 50000 AND city = 'NY').

2. Step 2: Interpret the WHERE clause

   The query returns rows where either age is greater than 30, or both salary is less than 50000 and city is 'NY'.

3. Final Answer:

   Rows where age > 30 or (salary < 50000 and city = 'NY') -> Option A

4. Quick Check:

   AND evaluated before OR [OK]

Hint: AND groups tighter than OR without parentheses [OK]

Common Mistakes:

• Ignoring operator precedence and grouping incorrectly
• Assuming OR groups before AND
• Misreading the condition order

4. Identify the error in this SQL query:

SELECT * FROM Employees WHERE age > 25 OR salary < 40000 AND;

medium

A. Missing FROM clause

B. Incorrect use of OR operator

C. Missing condition after AND operator

D. Incorrect column names

Solution

1. Step 1: Analyze the WHERE clause syntax

   The query ends with AND; which means the AND operator is not followed by a condition. This is a syntax error.

2. Step 2: Confirm other parts are correct

   The SELECT and FROM clauses are present and column names seem valid. The error is specifically the incomplete WHERE clause.

3. Final Answer:

   Missing condition after AND operator -> Option C

4. Quick Check:

   Logical operators must have conditions on both sides [OK]

Hint: Check that AND/OR always have conditions after them [OK]

Common Mistakes:

• Leaving dangling AND or OR without conditions
• Forgetting to complete WHERE clause
• Assuming syntax is correct despite missing parts

5. You want to select employees who are either older than 40 or work in 'LA' with a salary less than 60000. Which WHERE clause correctly uses operator precedence to get this result?

Options:
A) WHERE age > 40 AND salary < 60000 OR city = 'LA'
B) WHERE (age > 40 OR salary < 60000) AND city = 'LA'
C) WHERE age > 40 OR (salary < 60000 AND city = 'LA')
D) WHERE (age > 40 OR city = 'LA') AND salary < 60000

hard

A. WHERE age > 40 OR (salary < 60000 AND city = 'LA')

B. WHERE (age > 40 OR salary < 60000) AND city = 'LA'

C. WHERE age > 40 AND salary < 60000 OR city = 'LA'

D. WHERE (age > 40 OR city = 'LA') AND salary < 60000

Solution

1. Step 1: Understand the desired logic

   You want employees who are either older than 40, or who both work in 'LA' and have salary less than 60000.

2. Step 2: Apply operator precedence and parentheses

   Since AND has higher precedence, a clause like WHERE age > 40 AND salary < 60000 OR city = 'LA' groups as (age > 40 AND salary < 60000) OR city = 'LA'. But for our desired logic age > 40 OR (salary < 60000 AND city = 'LA'), we need explicit parentheses as in WHERE age > 40 OR (salary < 60000 AND city = 'LA').

3. Step 3: Compare options

   WHERE age > 40 AND salary < 60000 OR city = 'LA' groups as (age > 40 AND salary < 60000) OR city = 'LA' -- incorrect. WHERE (age > 40 OR salary < 60000) AND city = 'LA' requires city = 'LA' even for age > 40 -- incorrect. WHERE (age > 40 OR city = 'LA') AND salary < 60000 requires salary < 60000 always -- incorrect. WHERE age > 40 OR (salary < 60000 AND city = 'LA') correctly implements the logic.

4. Final Answer:

   WHERE age > 40 OR (salary < 60000 AND city = 'LA') -> Option A

5. Quick Check:

   Use parentheses to group AND conditions with OR correctly [OK]

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

Common Mistakes:

• Not using parentheses and misreading condition grouping
• Assuming OR groups before AND
• Changing logic by wrong parentheses placement