SQL - Structured Query Language Filtering with WHERE Clause Questions and Answers

Question 1

A user wants to retrieve records for all employees who are either in the 'Sales' department or have a salary greater than 50000.
However, the query should only include employees hired after '2022-01-01'.
Which WHERE clause correctly implements this logic?

Accepted Answer

WHERE (Department = 'Sales' OR Salary > 50000) AND HireDate > '2022-01-01'

Answer

WHERE Department = 'Sales' OR Salary > 50000 AND HireDate > '2022-01-01'

Answer

WHERE Department = 'Sales' AND Salary > 50000 OR HireDate > '2022-01-01'

Answer

WHERE (Department = 'Sales' AND HireDate > '2022-01-01') OR Salary > 50000

Question 2

Which of the following operators is used to perform pattern matching in a WHERE clause?

Accepted Answer

LIKE

Answer

IN

Answer

BETWEEN

Answer

IS

Question 3

A database table named `Products` contains a `Price` column (numeric) and a `SupplierID` column (numeric). Some products do not have a supplier assigned, and their `SupplierID` is NULL. How would you select all products with a `Price` over 100 that also have no assigned supplier?

Accepted Answer

SELECT * FROM Products WHERE Price > 100 AND SupplierID IS NULL;

Answer

SELECT * FROM Products WHERE Price > 100 AND SupplierID = NULL;

Answer

SELECT * FROM Products WHERE Price > 100 OR SupplierID IS NULL;

Answer

SELECT * FROM Products WHERE Price > 100 AND SupplierID <> NULL;

Question 4

You need to write a query to find all customers who live in 'London', 'Paris', or 'Tokyo'. Which WHERE clause is the most concise and efficient way to achieve this?

Accepted Answer

WHERE City IN ('London', 'Paris', 'Tokyo')

Answer

WHERE City = 'London' AND City = 'Paris' AND City = 'Tokyo'

Answer

WHERE City = 'London' OR City = 'Paris' OR City = 'Tokyo'

Answer

WHERE City LIKE 'London' OR City LIKE 'Paris' OR City LIKE 'Tokyo'

Question 5

What is the primary purpose of the `WHERE` clause in a SQL SELECT statement?

Accepted Answer

To filter records and retrieve only those that fulfill a specified condition.

Answer

To sort the result set in a specific order.

Answer

To group rows that have the same values into summary rows.

Answer

To specify which columns should be returned.

Question 6

A query needs to select all invoices with an `Amount` between 500 and 1000, inclusive of both values. Which `WHERE` clause correctly represents this condition?

Accepted Answer

WHERE Amount BETWEEN 500 AND 1000

Answer

WHERE Amount > 500 AND Amount < 1000

Answer

WHERE Amount >= 500 OR Amount <= 1000

Answer

WHERE Amount IN (500, 1000)

A user wants to retrieve records for all employees who are either in the 'Sales' department or have a salary greater than 50000.However, the query should only include employees hired after '2022-01-01'.Which WHERE clause correctly implements this logic?

A user wants to retrieve records for all employees who are either in the 'Sales' department or have a salary greater than 50000.
However, the query should only include employees hired after '2022-01-01'.
Which WHERE clause correctly implements this logic?