GROUP BYClause: TheGROUP BYclause in SQL is used to group rows with similar values into summary rows, based on the values in one or more columns.
Syntax:
|
1 2 3 4 |
SELECT [column1, column2, ...], aggregate_function(column_name) FROM table_name GROUP BY column1, column2, ...; |
Example:
|
1 2 3 4 |
SELECT manufacturer, AVG(price) FROM cars GROUP BY manufacturer; |
This query returns the average price of cars for each manufacturer, by grouping the cars by manufacturer.
EXISTSClause: TheEXISTSclause in SQL is used to check if a subquery returns any data, and it returnsTRUEorFALSEbased on the result.
Syntax:
|
1 2 3 4 |
SELECT [column1, column2, ...] FROM table1 WHERE EXISTS (subquery); |
Example:
|
1 2 3 4 |
SELECT model, color FROM cars WHERE EXISTS (SELECT * FROM owners WHERE cars.id = owners.car_id); |
This query returns the model and color of all cars that have an owner.
HAVINGClause: TheHAVINGclause in SQL is used to filter the grouped result set, based on a condition applied to the aggregate functions.
Syntax:
|
1 2 3 4 5 |
SELECT [column1, column2, ...], aggregate_function(column_name) FROM table_name GROUP BY column1, column2, ... HAVING condition; |
Example:
|
1 2 3 4 5 |
SELECT manufacturer, AVG(price) FROM cars GROUP BY manufacturer HAVING AVG(price) > 20000; |
This query returns the average price of cars for each manufacturer, where the average price is greater than 20000.
ANYClause: TheANYclause in SQL is used with the comparison operators to match any value returned by a subquery.
Syntax:
|
1 2 3 4 |
SELECT [column1, column2, ...] FROM table1 WHERE column_name operator ANY (subquery); |
Example:
|
1 2 3 4 |
SELECT model, color FROM cars WHERE price > ANY (SELECT price FROM cars WHERE manufacturer = 'Toyota'); |
This query returns the model and color of cars whose price is greater than the price of any Toyota car.
CASEClause: TheCASEclause in SQL is used to perform conditional logic in a SELECT statement, by evaluating a set of conditions and returning a result based on the first condition that is met.
Syntax:
|
1 2 3 4 5 6 7 8 9 |
SELECT [column1, column2, ...], CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE resultN END FROM table_name; |
Example:
|
1 2 3 4 5 6 7 8 |
SELECT model, color, CASE WHEN price < 20000 THEN 'Cheap' WHEN price >= 20000 AND price < 50000 THEN 'Moderate' ELSE 'Expensive' END AS price_category FROM cars; |
This query returns the model, color, and price category (Cheap, Moderate, or Expensive) of each car in the cars table