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

Challenge - 5 Problems

❓ query_result

intermediate

Filter employees by department and salary

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

SELECT name FROM Employees WHERE department = 'Sales' AND salary > 50000;

SQL

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

A Alice, Bob

B Bob, Diana

C Alice, Diana

D Charlie, Diana

💡 Hint

Remember both conditions in the WHERE clause must be true for a row to be included.

The query selects employees in the Sales department with salary greater than 50000. Alice (60000) and Diana (70000) meet both conditions.

📝 Syntax

intermediate

Identify the syntax error in the WHERE clause

Which option contains a syntax error in the WHERE clause using AND operator?

SQL

SELECT * FROM Products WHERE price > 100 AND category = 'Electronics';

A SELECT * FROM Products WHERE price > 100 AND AND category = 'Electronics';

B SELECT * FROM Products WHERE price > 100 AND category = 'Electronics'

C SELECT * FROM Products WHERE price > 100 category = 'Electronics' AND;

D SELECT * FROM Products WHERE price > 100 AND category = 'Electronics';

💡 Hint

Look for repeated keywords or misplaced operators.

Option A has two AND operators in a row, which is invalid syntax.

❓ optimization

advanced

Optimize query with multiple AND conditions

You want to find customers from 'USA' who have made purchases over $1000 and have a loyalty status of 'Gold'. Which query is more efficient?

SQL

SELECT * FROM Customers WHERE country = 'USA' AND purchase_amount > 1000 AND loyalty_status = 'Gold';

A All queries have the same efficiency.

B SELECT * FROM Customers WHERE purchase_amount > 1000 AND country = 'USA' AND loyalty_status = 'Gold';

C SELECT * FROM Customers WHERE country = 'USA' AND purchase_amount > 1000 AND loyalty_status = 'Gold';

D SELECT * FROM Customers WHERE loyalty_status = 'Gold' AND purchase_amount > 1000 AND country = 'USA';

💡 Hint

The order of AND conditions does not affect query results or efficiency in standard SQL.

AND conditions are commutative; the database engine evaluates all conditions regardless of order.

🧠 Conceptual

advanced

Understanding AND operator with NULL values

Consider a table Orders with a column shipped_date that can be NULL. What is the result of this query?

SELECT * FROM Orders WHERE shipped_date > '2023-01-01' AND shipped_date < '2023-12-31';

Will rows with shipped_date as NULL be included?

A No, NULL values cause a syntax error in the query.

B No, NULL values are excluded because comparisons with NULL return unknown.

C Yes, NULL values are included because they satisfy both conditions.

D Yes, NULL values are included because AND ignores NULLs.

💡 Hint

Think about how SQL treats NULL in logical conditions.

In SQL, any comparison with NULL results in unknown, so the AND condition fails and those rows are excluded.

🔧 Debug

expert

Debug the unexpected output with AND operator

A developer wrote this query:

SELECT * FROM Employees WHERE department = 'IT' AND salary > 70000 OR position = 'Manager';

They expect to get employees in IT with salary over 70000 and also all managers regardless of department or salary. But the output includes employees not matching these rules. What is the cause?

A The OR operator has higher precedence than AND, so the query is evaluated incorrectly.

B The query syntax is invalid because OR cannot be used with AND.

C The query is missing a WHERE clause before OR.

D The AND operator has higher precedence than OR, so parentheses are needed to group conditions properly.

💡 Hint

Consider operator precedence and how SQL evaluates AND and OR.

AND has higher precedence than OR, so the query is evaluated as (department = 'IT' AND salary > 70000) OR position = 'Manager'. To get the expected result, parentheses are needed to group conditions correctly.