4 - MySql Practical for class 12 CS and IP

 Write the sort notes on group by, having and where clause with example.

WHERE Clause:

WHERE Clause is used to filter the records/rows from the table. It can be used with SELECT, UPDATE, DELETE statements.

Select * from employees where salary>25000;

Group by Clause:

The GROUP BY clause is used with aggregate functions (MAX, SUM, AVG, COUNT, MIN) to group the results by one or more columns i.e. The GROUP BY clause is used with the SELECT statement to arrange required data into groups. 

The GROUP BY statement groups rows that have the same values. This Statement is used after the where clause. This statement is often used with some aggregate function like SUM, AVG, and COUNT etc. to group the results by one or more columns.

SELECT COUNT (City) AS COUNT_CITIES, City FROM EMPLOYEES GROUP BY City;

Having Clause:

Having Clause is like the aggregate function with the GROUP BY clause. The HAVING clause is used instead of WHERE with aggregate functions. While the GROUP BY Clause group rows that have the same values into rows. The having clause is used with the where clause in order to find rows with certain conditions. The having clause is always used after the group by clause.

SELECT COUNT (City) AS COUNT_CITIES, City FROM EMPLOYEES

GROUP BY City HAVING COUNT (City) > 1;

WHERE Clause	Having Clause	Group By Clause
WHERE Clause is used to filter the records from the table based on the specified condition.	HAVING Clause is used to filter record from the groups based on the specified condition.	The group by clause is used to group the data according to particular column or row.
WHERE Clause can be used without GROUP BY Clause	HAVING Clause cannot be used without GROUP BY Clause	Group by can be used without having clause with the select statement.
WHERE Clause implements in row operations	HAVING Clause implements in column operation	It groups the output on basis of some rows or columns.
WHERE Clause cannot contain aggregate function	The having clause can contain aggregate functions.	It cannot contain aggregate functions.
WHERE Clause is used with single row function like UPPER, LOWER etc.	HAVING Clause is used with multiple row function like SUM, COUNT etc	Group by Clause is a multiple row function
WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.	The GROUP BY clause is used in the SELECT statement.
WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause	Group By is used after the Where clause and before the HAVING Clause