HAVING Clause :
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause.
To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause
SYNTAX :
SELECT Column1, Aggregate function (column2)
FROM "List of tables"
GROUP BY "Column list"
HAVING <search condition>
Arguments :
<search_condition>
Specifies the search condition for the group or the aggregate to meet.
The text, image, and ntext data types cannot be used in a HAVING clause.
1. Write a Query to fetch the project name assigned to more than one employee.
Ans: Select projectname, count(*) as Noofemployees from projectdetail group by projectname
having count(*) >1.
2. Difference between Having Clause and Where Clause.
Ans:
Where :
* Where clause can be used with Select, Update and Delete Statement.
* We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause.
* Where Clause is used on the individual records.
* Where clause places conditions on the selected columns.
Having:
* Having clause can be used only with Select statement.
* We can use aggregate function in Having clause.We can use column name in Having clause
but the column must be contained in the group by clause.
* Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
* Having clause places conditions on groups created by the GROUP BY clause
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause.
To use Having Clause, we have to use Group By Clause since it filters data that we get from Group By Clause
SYNTAX :
SELECT Column1, Aggregate function (column2)
FROM "List of tables"
GROUP BY "Column list"
HAVING <search condition>
Arguments :
<search_condition>
Specifies the search condition for the group or the aggregate to meet.
The text, image, and ntext data types cannot be used in a HAVING clause.
Example: Lets Say want to only calculate and display the average salary greater than 10000.
SELECT department, AVG(salary) from employee GROUP BY department HAVING AVG(salary)>10000.
Interview Questions on Having Clause :SELECT department, AVG(salary) from employee GROUP BY department HAVING AVG(salary)>10000.
1. Write a Query to fetch the project name assigned to more than one employee.
Ans: Select projectname, count(*) as Noofemployees from projectdetail group by projectname
having count(*) >1.
2. Difference between Having Clause and Where Clause.
Ans:
Where :
* Where clause can be used with Select, Update and Delete Statement.
* We can't use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause.
* Where Clause is used on the individual records.
* Where clause places conditions on the selected columns.
Having:
* Having clause can be used only with Select statement.
* We can use aggregate function in Having clause.We can use column name in Having clause
but the column must be contained in the group by clause.
* Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
* Having clause places conditions on groups created by the GROUP BY clause
No comments:
Post a Comment