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

The use of parentheses is crucial for controlling the order of operations. The `AND` operator has higher precedence than `OR`. The requirement is to first evaluate the `OR` condition (department is 'Sales' OR salary is greater than 50000) and then apply the `AND` condition (hire date is after '2022-01-01') to the result of the `OR` condition. Encapsulating the `OR` statement in parentheses ensures it is evaluated first.

Question 2

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

Accepted Answer

LIKE

Answer

The `LIKE` operator is used in a `WHERE` clause to search for a specified pattern in a column. It uses wildcard characters: '%' represents zero, one, or multiple characters, and '_' represents a single character.

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

To filter for rows where a column's value is NULL, you must use the `IS NULL` operator. Standard comparison operators like `=` or `<>` do not work as expected with NULL values, as NULL represents an unknown or missing value, not a specific value to compare against.

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

The `IN` operator allows you to specify multiple values in a `WHERE` clause. It is a shorthand for multiple `OR` conditions and is generally more readable and can be optimized better by the database engine.

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

The `WHERE` clause is used to extract only those records that fulfill a specified condition, effectively filtering the rows returned by the query. Sorting is done by `ORDER BY`, grouping by `GROUP BY`, and specifying columns is done in the `SELECT` list.

SQL Practice Test

SQL Practice Test

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