Filtering Records with SQL's WHERE Clause

This challenge focuses on understanding and implementing the fundamental WHERE clause in SQL. The WHERE clause is crucial for selecting specific rows from a database table based on defined conditions, allowing for targeted data retrieval and manipulation. Mastering this concept is essential for any data-driven application.

Problem Description

You are tasked with writing a SQL query that retrieves specific records from a given table based on a set of filtering conditions. You will be provided with a table schema and a set of criteria. Your query should return only the rows that satisfy all specified conditions.

Requirements:

Select all columns from the specified table.
Apply filtering conditions using the WHERE clause.
The conditions will involve comparing values in different columns against specific literals or other column values.
The query should be robust and handle cases where no records match the criteria.

Expected Behavior:

The query should return an empty result set if no records in the table meet the specified filtering criteria. Otherwise, it should return all rows that satisfy all conditions specified in the WHERE clause.

Edge Cases:

Conditions involving NULL values.
Using various comparison operators (=, !=, <, >, <=, >=).
Combining multiple conditions using logical operators (AND, OR).

Examples

Example 1:

Input Table: Customers

customer_id	first_name	last_name	city	state	signup_date
1	John	Doe	New York	NY	2023-01-15
2	Jane	Smith	Los Angeles	CA	2023-03-20
3	Peter	Jones	New York	NY	2023-05-10
4	Mary	Brown	Chicago	IL	2023-01-15

Filtering Criteria:

city is 'New York'
state is 'NY'

Output:

customer_id	first_name	last_name	city	state	signup_date
1	John	Doe	New York	NY	2023-01-15
3	Peter	Jones	New York	NY	2023-05-10

Explanation: The query selects records where the city column is exactly 'New York' AND the state column is exactly 'NY'.

Example 2:

Input Table: Orders

order_id	customer_id	order_date	total_amount	status
101	1	2023-02-10	150.50	shipped
102	2	2023-04-05	75.00	pending
103	1	2023-02-20	200.00	delivered
104	3	2023-06-01	99.99	pending
105	2	2023-04-15	120.25	shipped

Filtering Criteria:

status is 'pending' OR total_amount is greater than 100.00

Output:

order_id	customer_id	order_date	total_amount	status
102	2	2023-04-05	75.00	pending
104	3	2023-06-01	99.99	pending
101	1	2023-02-10	150.50	shipped
105	2	2023-04-15	120.25	shipped

Explanation: The query selects records where either the status is 'pending' OR the total_amount is greater than 100.00. Notice that order_id 102 and 104 meet the 'pending' condition, while order_id 101 and 105 meet the total_amount condition.

Example 3:

Input Table: Products

product_id	product_name	category	price	stock_quantity
P001	Laptop	Electronics	1200	50
P002	Keyboard	Electronics	75	150
P003	Mouse	Electronics	25	NULL
P004	Desk Chair	Furniture	300	20
P005	Monitor	Electronics	300	30

Filtering Criteria:

stock_quantity IS NULL

Output:

product_id	product_name	category	price	stock_quantity
P003	Mouse	Electronics	25	NULL

Explanation: This demonstrates filtering for NULL values. The IS NULL operator is specifically used for this purpose, as direct comparison with NULL using = would not work as expected.

Constraints

The table will contain at least one row.
Column names will be alphanumeric.
Literal values in conditions will be of appropriate data types (strings, numbers, dates).
You will not need to worry about aggregate functions or subqueries for this challenge.
The provided filtering criteria will be unambiguous.

Notes

Remember that SQL is case-insensitive for keywords but often case-sensitive for string literals depending on database configuration. For this challenge, assume standard case-sensitivity for string comparisons unless otherwise specified.
Pay close attention to the logical operators (AND, OR) and how they combine conditions.
When dealing with NULL values, use the IS NULL or IS NOT NULL operators.

Filtering Records with SQL's WHERE Clause

Problem Description

Requirements:

Select all columns from the specified table.
Apply filtering conditions using the WHERE clause.
The conditions will involve comparing values in different columns against specific literals or other column values.
The query should be robust and handle cases where no records match the criteria.

Expected Behavior:

Edge Cases:

Conditions involving NULL values.
Using various comparison operators (=, !=, <, >, <=, >=).
Combining multiple conditions using logical operators (AND, OR).

Examples

Example 1:

Input Table: Customers

customer_id	first_name	last_name	city	state	signup_date
1	John	Doe	New York	NY	2023-01-15
2	Jane	Smith	Los Angeles	CA	2023-03-20
3	Peter	Jones	New York	NY	2023-05-10
4	Mary	Brown	Chicago	IL	2023-01-15

Filtering Criteria:

city is 'New York'
state is 'NY'

Output:

customer_id	first_name	last_name	city	state	signup_date
1	John	Doe	New York	NY	2023-01-15
3	Peter	Jones	New York	NY	2023-05-10

Explanation: The query selects records where the city column is exactly 'New York' AND the state column is exactly 'NY'.

Example 2:

Input Table: Orders

order_id	customer_id	order_date	total_amount	status
101	1	2023-02-10	150.50	shipped
102	2	2023-04-05	75.00	pending
103	1	2023-02-20	200.00	delivered
104	3	2023-06-01	99.99	pending
105	2	2023-04-15	120.25	shipped

Filtering Criteria:

status is 'pending' OR total_amount is greater than 100.00

Output:

order_id	customer_id	order_date	total_amount	status
102	2	2023-04-05	75.00	pending
104	3	2023-06-01	99.99	pending
101	1	2023-02-10	150.50	shipped
105	2	2023-04-15	120.25	shipped

Example 3:

Input Table: Products

product_id	product_name	category	price	stock_quantity
P001	Laptop	Electronics	1200	50
P002	Keyboard	Electronics	75	150
P003	Mouse	Electronics	25	NULL
P004	Desk Chair	Furniture	300	20
P005	Monitor	Electronics	300	30

Filtering Criteria:

stock_quantity IS NULL

Output:

product_id	product_name	category	price	stock_quantity
P003	Mouse	Electronics	25	NULL

Explanation: This demonstrates filtering for NULL values. The IS NULL operator is specifically used for this purpose, as direct comparison with NULL using = would not work as expected.

Constraints

The table will contain at least one row.
Column names will be alphanumeric.
Literal values in conditions will be of appropriate data types (strings, numbers, dates).
You will not need to worry about aggregate functions or subqueries for this challenge.
The provided filtering criteria will be unambiguous.

Notes

Remember that SQL is case-insensitive for keywords but often case-sensitive for string literals depending on database configuration. For this challenge, assume standard case-sensitivity for string comparisons unless otherwise specified.
Pay close attention to the logical operators (AND, OR) and how they combine conditions.
When dealing with NULL values, use the IS NULL or IS NOT NULL operators.