Filtering with WHERE Clause Flashcards

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?

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

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.

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

Answer: LIKE

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.

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?

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

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.

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?

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

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.

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

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

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.

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?

Answer: WHERE Amount BETWEEN 500 AND 1000

The `BETWEEN` operator is used to select values within a given range. The values can be numbers, text, or dates. The `BETWEEN` operator is inclusive: the start and end values are included in the result.