A
Explain the difference between the WHERE and HAVING clauses in SQL. In what scenarios would you use each clause? Provide examples to illustrate your points.
The WHERE clause is used to filter records before any groupings are made. It operates on individual rows and is typically used with SELECT, UPDATE, and DELETE statements.
SELECT customer_name, amount FROM orders WHERE amount > 100;
The HAVING clause is used to filter records after aggregation has taken place. It operates on grouped records and is often used with aggregate functions like COUNT, SUM, and AVG.
SELECT customer_name, SUM(amount) AS total_spent FROM orders GROUP BY customer_name HAVING SUM(amount) > 500;
HAVING is necessary when you want to filter results based on aggregate functions, whereas WHERE cannot be used for aggregated data.
SELECT product_id, COUNT(*) AS total_sales FROM sales GROUP BY product_id HAVING COUNT(*) > 10;