WHERE Clause

Estimated reading: 2 minutes 15 views

1. How does the WHERE clause filter records in a table?

The WHERE clause filters records by specifying conditions that must be met for the rows to be included in the query result. It restricts the rows returned by the query based on the condition provided.
Example:

				
					SELECT * FROM employees 
WHERE salary > 50000;

2. Write a query to find all rows where a column value is within a range (e.g., 10 to 20).

You can use the BETWEEN operator to specify a range of values for filtering.
Example:

				
					SELECT * FROM employees 
WHERE age BETWEEN 10 AND 20;

3. What operators can you use with the WHERE clause (=, <, LIKE, etc.)? Provide examples.

Some common operators used with the WHERE clause are:

=: Equal to.
<> or !=: Not equal to.
<: Less than.
>: Greater than.
<=: Less than or equal to.
>=: Greater than or equal to.
BETWEEN: Within a range.
LIKE: Matches a pattern.
IN: Matches any value in a list.
IS NULL or IS NOT NULL: Checks for NULL values.

Examples:

				
					SELECT * FROM employees WHERE salary = 50000;
SELECT * FROM employees WHERE department_id IN (1, 2, 3);
SELECT * FROM employees WHERE name LIKE 'J%';

4. How do you filter rows where a column value is NULL or NOT NULL?

Use the IS NULL or IS NOT NULL operators to filter rows based on whether a column has a NULL value or not.
Example:

				
					SELECT * FROM employees WHERE salary IS NULL;
SELECT * FROM employees WHERE salary IS NOT NULL;

5. Write a query to find records that match a pattern using the LIKE keyword.

The LIKE keyword is used to search for patterns in a column. You can use % (any number of characters) and _ (a single character) as wildcards.
Example:

				
					SELECT * FROM employees 
WHERE name LIKE 'J%'; -- Finds names starting with 'J'

SELECT * FROM employees 
WHERE name LIKE '_ohn'; -- Finds names like 'John', 'Aohn', etc.