AND, OR, NOT logical operators in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of AND and OR in WHERE clause

Given the table Employees with columns id, name, department, and salary, what rows will this query return?

SELECT * FROM Employees WHERE department = 'Sales' AND salary > 50000 OR department = 'HR';

MySQL

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

A Rows with id 3 and 4 only

B Rows with id 1 and 4 only

C Rows with id 1, 2, 3, and 4

D Rows with id 1, 3, and 4

💡 Hint

Remember that AND has higher precedence than OR in SQL.

The condition is evaluated as (department = 'Sales' AND salary > 50000) OR (department = 'HR'). So, employees in Sales with salary > 50000 (id 1) and all employees in HR (id 3 and 4) are returned.

❓ query_result

intermediate

Effect of NOT operator in WHERE clause

Consider the same Employees table. What rows will this query return?

SELECT * FROM Employees WHERE NOT (department = 'IT' OR salary < 50000);

MySQL

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

A Rows with id 2 and 3 only

B Rows with id 1, 4, and 5

C Rows with id 1 and 4 only

D Rows with id 1, 2, 3, 4, and 5

💡 Hint

NOT reverses the condition inside the parentheses.

The condition inside NOT is (department = 'IT' OR salary < 50000). Rows where this is true are excluded. So, only employees not in IT and with salary >= 50000 remain: Alice (id 1) and Diana (id 4).

🧠 Conceptual

advanced

Understanding operator precedence in complex conditions

Which of the following statements about SQL logical operator precedence is correct?

A AND has higher precedence than OR, so AND conditions are evaluated before OR.

B AND and OR have the same precedence and are evaluated left to right.

C OR has higher precedence than AND, so it is evaluated first.

D NOT has the lowest precedence among AND and OR.

💡 Hint

Think about how SQL evaluates multiple logical operators without parentheses.

In SQL, NOT has the highest precedence, then AND, then OR. So AND conditions are evaluated before OR conditions.

📝 Syntax

advanced

Identify the syntax error in logical operators usage

Which option contains a syntax error in the WHERE clause using logical operators?

A SELECT * FROM Employees WHERE NOT department = 'Sales' AND salary > 50000;

B SELECT * FROM Employees WHERE department = 'Sales' AND OR salary > 50000;

C SELECT * FROM Employees WHERE NOT (department = 'Sales' OR salary < 50000);

D SELECT * FROM Employees WHERE department = 'Sales' OR salary > 50000;

💡 Hint

Look for misplaced or extra logical operators.

Option B has 'AND OR' together without a condition between them, which is invalid syntax.

❓ optimization

expert

Optimizing a query with multiple logical operators

You want to select employees who are either in the 'Marketing' department or have a salary greater than 70000, but not those in 'Marketing' with salary less than or equal to 70000. Which query is the most efficient and correct?

A SELECT * FROM Employees WHERE NOT (department = 'Marketing' AND salary <= 70000) AND (department = 'Marketing' OR salary > 70000);

B SELECT * FROM Employees WHERE department = 'Marketing' OR salary > 70000 AND NOT (department = 'Marketing' AND salary <= 70000);

C SELECT * FROM Employees WHERE department = 'Marketing' OR salary > 70000;

D SELECT * FROM Employees WHERE (department = 'Marketing' AND salary > 70000) OR (salary > 70000);

💡 Hint

Use De Morgan's laws and operator precedence to simplify conditions.

Option A correctly excludes employees in Marketing with salary <= 70000 while including others in Marketing with salary > 70000 or those with salary > 70000 in other departments.