SQL WHERE statement: specify query conditions

The WHERE clause in SQL is used to filter records. It is typically used in conjunction with statements like SELECT, UPDATE, and DELETE to specify the exact criteria that the rows or records must meet to be selected, updated, or deleted.

Here is the basic syntax for the WHERE clause:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

column1, column2, ...: The names of the columns you want to select.
table_name: The name of the table from which you want to fetch the data.
condition: The condition that a record must meet to be selected.

Let's look at a few examples. Assume you have the following Customers table:

ID	Name	Age	City
1	Tom	25	New York
2	Alice	30	Los Angeles
3	Bob	35	Chicago
4	Charlie	40	New York
5	David	45	San Francisco

Here's how you can use the WHERE clause to select customers who are based in New York:

SELECT * 
FROM Customers
WHERE City = 'New York';

The result would be:

ID	Name	Age	City
1	Tom	25	New York
4	Charlie	40	New York

You can also use the WHERE clause in an UPDATE statement. For example, you could update the age of Alice like this:

UPDATE Customers
SET Age = 31
WHERE Name = 'Alice';

You can also use the WHERE clause with a DELETE statement. To delete all customers who live in San Francisco:

DELETE FROM Customers
WHERE City = 'San Francisco';

Be careful when using the DELETE statement without a WHERE clause. This will delete all rows in the table.

Also, you can use operators like <, >, <=, >=, <> or !=, BETWEEN, LIKE, and IN in your conditions to make them more specific.

For example:

SELECT * 
FROM Customers
WHERE Age >= 30 AND City = 'New York';

Using WHERE Clause in SQL Queries:
- Description: The WHERE clause filters rows based on a specified condition in SQL queries.
- Code Example:
```
SELECT column1, column2
FROM your_table
WHERE condition;
```

Specifying Conditions in SQL SELECT Statements:

Code Example:

SELECT product_name, price
FROM products
WHERE category_id = 1;

Filtering Data with SQL WHERE:

Code Example:

SELECT employee_name, salary
FROM employees
WHERE department_id = 2;

Multiple Conditions in SQL WHERE:

Code Example:

SELECT order_id, order_date
FROM orders
WHERE customer_id = 1 AND order_status = 'Shipped';

SQL WHERE Clause Examples:

Code Example 1:

SELECT product_name, price
FROM products
WHERE price > 100;

Code Example 2:

SELECT employee_name, hire_date
FROM employees
WHERE hire_date >= '2022-01-01';

Complex Conditions in SQL WHERE:

Code Example:

SELECT customer_name, total_amount
FROM orders
WHERE (order_status = 'Shipped' OR order_status = 'Delivered')
  AND total_amount > 1000;

Combining AND and OR in SQL WHERE:

Code Example:

SELECT product_name, price
FROM products
WHERE (category_id = 1 AND stock_quantity > 0)
   OR (category_id = 2 AND stock_quantity > 5);

SQL WHERE vs HAVING Clause:

Difference: WHERE filters rows before aggregation, while HAVING filters results after aggregation.

Example:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING AVG(salary) > 60000;

Using Comparison Operators in SQL WHERE:

Code Example:

SELECT product_name, price
FROM products
WHERE price BETWEEN 50 AND 100;

SQL WHERE and NULL Values:

Code Example:

SELECT customer_name, email
FROM customers
WHERE email IS NULL;

Wildcard Usage in SQL WHERE:

Code Example:

SELECT employee_name, job_title
FROM employees
WHERE job_title LIKE 'Manager%';

Subqueries in SQL WHERE Clause:

Code Example:

SELECT product_name, price
FROM products
WHERE category_id IN (SELECT category_id FROM categories WHERE category_name = 'Electronics');

Debugging SQL WHERE Conditions:
- Tip: Break down complex conditions into smaller parts for easier debugging.
- Example:
```
SELECT column1, column2
FROM your_table
WHERE (condition1 OR condition2) AND condition3;
```