WHERE with NOT operator in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of NOT operator in WHERE clause

Given a table Employees with columns id, name, and department, what is the output of the following query?

SELECT name FROM Employees WHERE NOT department = 'Sales';

SQL

CREATE TABLE Employees (id INT, name VARCHAR(50), department VARCHAR(50));
INSERT INTO Employees VALUES (1, 'Alice', 'Sales'), (2, 'Bob', 'HR'), (3, 'Charlie', 'Sales'), (4, 'Diana', 'IT');

A No rows returned

B Alice, Charlie

C Alice, Bob, Charlie, Diana

D Bob, Diana

💡 Hint

The NOT operator reverses the condition inside the WHERE clause.

The query selects employees whose department is NOT 'Sales'. Bob and Diana belong to 'HR' and 'IT' respectively, so they are returned.

🧠 Conceptual

intermediate

Understanding NOT with multiple conditions

Consider the query:

SELECT * FROM Products WHERE NOT (category = 'Electronics' OR price > 1000);

Which rows will this query return?

A Rows where category is 'Electronics' or price is more than 1000

B Rows where category is 'Electronics' and price is more than 1000

C Rows where category is not 'Electronics' and price is 1000 or less

D All rows in the table

💡 Hint

NOT reverses the entire condition inside the parentheses.

The condition inside parentheses is true if category is 'Electronics' or price is greater than 1000. NOT reverses this, so only rows where both are false are returned.

📝 Syntax

advanced

Identify the syntax error with NOT operator

Which of the following SQL queries will cause a syntax error?

A SELECT * FROM Orders WHERE NOT (status = 'Shipped' AND);

B SELECT * FROM Orders WHERE NOT(status = 'Shipped');

C SELECT * FROM Orders WHERE NOT status = 'Shipped' AND date > '2023-01-01';

D SELECT * FROM Orders WHERE NOT status = 'Shipped';

💡 Hint

Look for incomplete conditions inside parentheses.

Option A has an incomplete condition inside the parentheses after AND, causing a syntax error.

❓ optimization

advanced

Optimizing NOT conditions in WHERE clause

Which query is more efficient for filtering rows where status is not 'Active'?

A SELECT * FROM Users WHERE NOT status = 'Active';

B SELECT * FROM Users WHERE status <> 'Active';

C SELECT * FROM Users WHERE status != 'Active';

D SELECT * FROM Users WHERE NOT (status = 'Active');

💡 Hint

Consider how the database engine optimizes comparison operators.

Using the inequality operator (<>) is generally more efficient and clearer than using NOT with equality.

🔧 Debug

expert

Debugging unexpected results with NOT operator

A developer wrote this query:

SELECT * FROM Customers WHERE NOT city = 'New York' OR country = 'USA';

They expected to get customers who are not from New York and are from the USA. Why does this query return more rows than expected?

A Because OR has lower precedence than NOT, so the condition is evaluated as (NOT city = 'New York') OR country = 'USA', returning rows where city is not New York or country is USA

B Because NOT applies only to city, so it excludes only New York customers

C Because the query syntax is invalid and returns all rows

D Because the query should use AND instead of OR to combine conditions

💡 Hint

Think about operator precedence and how NOT and OR combine.

NOT has higher precedence than OR, so the condition is evaluated as (NOT city = 'New York') OR country = 'USA'. This returns rows where city is not New York or country is USA, which is more than expected.