Filtering data using WHERE clause

Basic WHERE Clause:
- Example: Retrieve all products with a price greater than $50.
- SQL Query:
```
sqlCopy code
SELECT * FROM Products WHERE price > 50;
```
- Explanation: This query retrieves all rows from the Products table where the price is greater than $50.
Equality Operator (=):
- Example: Retrieve orders with a specific order ID.
- SQL Query:
```
sqlCopy code
SELECT * FROM Orders WHERE order_id = 1001;
```
- Explanation: This query selects the row from the Orders table where the order_id is equal to 1001.
Comparing Text Values:
- Example: Retrieve customers whose names start with "John."
- SQL Query:
```
sqlCopy code
SELECT * FROM Customers WHERE customer_name LIKE 'John%';
```
- Explanation: The % wildcard is used to match any characters after "John." This query retrieves customers with names like "John Smith" or "John Doe."
Using Logical Operators:
- Example: Retrieve products with a price between $30 and $60.
- SQL Query:
```
sqlCopy code
SELECT * FROM Products WHERE price >= 30 AND price <= 60;
```
- Explanation: The AND logical operator allows you to combine multiple conditions. This query selects products with prices in the specified range.
IS NULL and IS NOT NULL:
- Example: Retrieve employees with no assigned manager.
- SQL Query:
```
sqlCopy code
SELECT * FROM Employees WHERE manager_id IS NULL;
```
- Explanation: The IS NULL condition is used to find rows where a specific column is empty (NULL).
IN Operator:
- Example: Retrieve products with specific IDs.
- SQL Query:
```
sqlCopy code
SELECT * FROM Products WHERE product_id IN (101, 102, 103);
```
- Explanation: The IN operator allows you to specify a list of values to match. This query selects products with the given IDs.
Using NOT Operator:
- Example: Retrieve customers who are not from a specific city.
- SQL Query:
```
sqlCopy code
SELECT * FROM Customers WHERE NOT city = 'New York';
```
- Explanation: The NOT operator negates a condition. This query selects customers who are not from New York.
Combining OR Operator:
- Example: Retrieve orders placed by a specific customer or with a total amount greater than $500.
- SQL Query:
```
sqlCopy code
SELECT * FROM Orders WHERE customer_id = 101 OR total_amount > 500;
```
- Explanation: The OR operator allows you to select rows that meet either of the specified conditions.
Using Functions:
- Example: Retrieve products with names containing the word "organic" (case-insensitive).
- SQL Query:
```
sqlCopy code
SELECT * FROM Products WHERE LOWER(product_name) LIKE '%organic%';
```
- Explanation: You can use functions like LOWER to transform data for comparison. This query retrieves products with names containing "organic" in any case.

These variations of the WHERE clause demonstrate the flexibility and power of SQL for filtering data based on different criteria and conditions in real-time scenarios.