Example 2 on GROUP BY and HAVING in SQL

For Complete YouTube Video: Click Here

We will try to understand Example 2 on GROUP BY and HAVING in SQL in this class.

We have already discussed the concepts of GROUP BY and HAVING in SQL.

We will discuss this class based on the assumption that you already know the cartesian product in SQL.

Example 2 on GROUP BY and HAVING in SQL

Question: Write a query to find the locations whose employee’s salary sum is greater than 100000.

We need the company database’s employee, works_on, and project table to answer this question, as shown below.

The query is below. 

SELECT p.plocation FROM employee e, works_on w, project p WHERE p.pnumber = w.pno AND w.essn = e.ssn GROUP BY p.plocation HAVING SUM(e.salary) > 100000;

The SQL will do the cartesian product of employee, works_on, and the project from the above query.

On the resultant table, the WHERE conditions p.pnumber = w.pno AND w.essn = e.ssn will be applied.

We get the resultant table from the above ‘WHERE’ conditions with the employees working in each location.

On that resultant table, GROUP BY and Having will produce the output.

The output of the query is as shown below.