WHERE with comparison operators in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a WHERE clause with greater than operator

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

SELECT * FROM Employees WHERE salary > 50000;

SQL

CREATE TABLE Employees (id INT, name VARCHAR(20), salary INT);
INSERT INTO Employees VALUES (1, 'Alice', 48000), (2, 'Bob', 52000), (3, 'Charlie', 50000), (4, 'Diana', 60000);

A Rows with id 2 and 4

B Rows with id 1 and 3

C Rows with id 3 and 4

D All rows

💡 Hint

The > operator means strictly greater than, so salary must be more than 50000.

Only Bob (52000) and Diana (60000) have salaries greater than 50000, so only their rows are returned.

❓ query_result

intermediate

Filtering rows with BETWEEN operator

What rows will this query return from the Products table with columns product_id, price if the query is:

SELECT * FROM Products WHERE price BETWEEN 10 AND 20;

SQL

CREATE TABLE Products (product_id INT, price INT);
INSERT INTO Products VALUES (1, 5), (2, 10), (3, 15), (4, 20), (5, 25);

A Rows with product_id 2, 3, and 4

B Rows with product_id 1, 2, and 3

C Rows with product_id 3 and 4 only

D Rows with product_id 2 and 5

💡 Hint

BETWEEN includes the boundary values 10 and 20.

BETWEEN 10 AND 20 means price >= 10 and price <= 20, so products with prices 10, 15, and 20 are included.

📝 Syntax

advanced

Identify the syntax error in this WHERE clause

Which option shows the correct syntax for filtering rows where age is less than 30 in the Users table?

A SELECT * FROM Users WHERE age <> 30;

B SELECT * FROM Users WHERE age =< 30;

C SELECT * FROM Users WHERE age << 30;

D SELECT * FROM Users WHERE age < 30;

💡 Hint

The less than operator is a single character: <

Option D uses the correct less than operator <. Option D has invalid operator =<, C uses invalid <<, and A means not equal.

❓ query_result

advanced

Result of combining multiple comparison operators

Given the Orders table with columns order_id, amount, and status, what rows will this query return?

SELECT * FROM Orders WHERE amount >= 100 AND amount < 200 AND status = 'shipped';

SQL

CREATE TABLE Orders (order_id INT, amount INT, status VARCHAR(10));
INSERT INTO Orders VALUES (1, 150, 'shipped'), (2, 90, 'shipped'), (3, 180, 'pending'), (4, 120, 'shipped'), (5, 200, 'shipped');

A Rows with order_id 1, 4, and 5

B Rows with order_id 1 and 4

C Rows with order_id 2 and 3

D Rows with order_id 3 and 5

💡 Hint

Check all conditions: amount between 100 and 199 inclusive, and status exactly 'shipped'.

Orders 1 and 4 have amounts between 100 and 199 and status 'shipped'. Order 5 has amount 200 which is not less than 200, so excluded.

🧠 Conceptual

expert

Understanding NULL behavior with comparison operators

Consider a table Students with columns student_id and score where some score values are NULL. Which statement about the query below is true?

SELECT * FROM Students WHERE score > 70;

SQL

CREATE TABLE Students (student_id INT, score INT);
INSERT INTO Students VALUES (1, 85), (2, NULL), (3, 70), (4, 90);

A Rows with student_id 2 and 3 are returned because NULL is ignored.

B All rows are returned because NULL is treated as zero.

C Rows with student_id 1 and 4 are returned; rows with NULL score are excluded.

D No rows are returned because NULL causes the comparison to fail.

💡 Hint

In SQL, any comparison with NULL results in unknown, so those rows are filtered out.

Comparisons with NULL do not evaluate to true, so rows with NULL in score are excluded. Only rows with score > 70 (85 and 90) are returned.