Worksheets › Class 12 › SQL: Functions, GROUP BY and Joins › Cheat sheet
Class 12 SQL: Functions, GROUP BY and Joins: cheat sheet
CBSE Computer Science. 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 |
Watch out
- Thinking COUNT(column) counts the NULLs: only COUNT(*) does.
- Putting a condition on an aggregate in WHERE: it belongs in HAVING.
- Writing 72.5 for a MySQL AVG of whole numbers: it shows 72.5000.
- Adding the rows of two tables for a Cartesian product instead of multiplying them.
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;
Practise SQL: Functions, GROUP BY and Joins
Revise with this page, then practise: every SQL: Functions, GROUP BY and Joins sheet in the app is new, with an answer key and working.
Coming soon for Android and iPhone.