Class 12 SQL: Functions, GROUP BY and Joins

The aggregate functions COUNT, SUM, AVG, MIN and MAX and how they treat NULL, GROUP BY and HAVING, and joining tables: Cartesian product, equi-join and natural join.

Key ideas

An aggregate function gives one value for a set of rows; every one of them skips NULL, except COUNT(*), which counts rows.

COUNT(column) counts the values that are not NULL; COUNT(DISTINCT column) counts the different ones.

AVG divides by the number of values that are not NULL; in MySQL the AVG of whole numbers shows four decimal places, such as 72.5000.

GROUP BY makes one group for each value of a column, and an aggregate then works on each group.

WHERE filters rows before they are grouped; HAVING filters the groups afterwards and may use aggregates.

A Cartesian product (FROM A, B) pairs every row of A with every row of B.

An equi-join keeps only the pairs with equal values in the common column; a NATURAL JOIN does the same but shows that column once.

Remember

Bonus 2000, NULL, 4000

COUNT(*) = 3; COUNT(Bonus) = 2; SUM(Bonus) = 6000; AVG(Bonus) = 3000.0000

All the clauses

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …

Groups

SELECT Dept, SUM(Salary) FROM EMP GROUP BY Dept HAVING SUM(Salary) > 100000;

Cartesian product

degrees add, cardinalities multiply: 4 rows and 3 rows give 12 rows

Equi-join

SELECT * FROM EMP, DEPT WHERE EMP.DeptID = DEPT.DeptID;

Columns of a join

equi-join: degree of A + degree of B; natural join: one fewer

Worked example

Table: EMPLOYEE
EmpNo | Name | Dept | Salary | Bonus
E1 | Priya | HR | 52000 | 8000
E2 | Meera | IT | 39000 | 1500
E3 | Ananya | HR | 82000 | 8500
E4 | Rahul | HR | 53000 | NULL
E5 | Arjun | HR | 60000 | 5000
E6 | Asha | HR | 67000 | NULL
Write SQL queries to display (a) the number of employees in each department; (b) the highest and lowest salary in HR; (c) the departments whose total salary is more than 100000; (d) the names of employees who get no bonus.

Working: GROUP BY with an aggregate; HAVING for a condition on groups; IS NULL

Answer: (a) SELECT Dept, COUNT(*) FROM EMPLOYEE GROUP BY Dept; (b) SELECT MAX(Salary), MIN(Salary) FROM EMPLOYEE WHERE Dept = 'HR'; (c) SELECT Dept FROM EMPLOYEE GROUP BY Dept HAVING SUM(Salary) > 100000; (d) SELECT Name FROM EMPLOYEE WHERE Bonus IS NULL;

SQL: Functions, GROUP BY and Joins

Key ideas

Remember

Watch out

Worked example