SQL - Structured Query Language Filtering with WHERE Clause Questions and Answers 1 — 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?
- WHERE (Department = 'Sales' OR Salary > 50000) AND HireDate > '2022-01-01' (Correct answer)
- WHERE Department = 'Sales' OR Salary > 50000 AND HireDate > '2022-01-01'
- WHERE Department = 'Sales' AND Salary > 50000 OR HireDate > '2022-01-01'
- WHERE (Department = 'Sales' AND HireDate > '2022-01-01') OR Salary > 50000
Correct 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.
Question 2: Which of the following operators is used to perform pattern matching in a WHERE clause?
- IN
- BETWEEN
- LIKE (Correct answer)
- IS
Correct 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.
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?
- SELECT * FROM Products WHERE Price > 100 AND SupplierID = NULL;
- SELECT * FROM Products WHERE Price > 100 OR SupplierID IS NULL;
- SELECT * FROM Products WHERE Price > 100 AND SupplierID IS NULL; (Correct answer)
- SELECT * FROM Products WHERE Price > 100 AND SupplierID <> NULL;
Correct 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.
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?
- WHERE City = 'London' AND City = 'Paris' AND City = 'Tokyo'
- WHERE City IN ('London', 'Paris', 'Tokyo') (Correct answer)
- WHERE City = 'London' OR City = 'Paris' OR City = 'Tokyo'
- WHERE City LIKE 'London' OR City LIKE 'Paris' OR City LIKE 'Tokyo'
Correct 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.
Question 5: What is the primary purpose of the `WHERE` clause in a SQL SELECT statement?
- To sort the result set in a specific order.
- To group rows that have the same values into summary rows.
- To specify which columns should be returned.
- To filter records and retrieve only those that fulfill a specified condition. (Correct answer)
Correct 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.
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?
- WHERE Amount > 500 AND Amount < 1000
- WHERE Amount >= 500 OR Amount <= 1000
- WHERE Amount BETWEEN 500 AND 1000 (Correct answer)
- WHERE Amount IN (500, 1000)
Correct 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.
SQL - Structured Query Language Filtering with WHERE Clause Questions and Answers 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?