Structured Query Language

1. Structured Query Language

2. Group Functions What are group functions ? • Group Functions • Group functions operate on sets of rows to give one result per group. These sets may be the whole table or the table split into groups.

3. What are Group Functions? DNO SALARY --------- ---------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 “maximum Salary in The Employee table” MAX(SAL) ---------- 5000

4. Types of Group Functions • AVG • COUNT • MAX • MIN • SUM

5. Group Functions • The avg(fieldname) function returns the average value of a numeric column’s returned values. • The min(fieldname) and max(fieldname) functions return minimum and maximum numeric value of a column respectively. • The count(fieldname) returns an integer representing the count of the number of rows retrieved where value of the fieldname is not null.. • The count(*) returns count of all rows, including the null values. • The sum(fieldname) returns sum of a numeric column.

6. Query 1 • Find the maximum salary, the minimum salary, and the average salary among all employees. SELECT MAX(SALARY), MIN(SALARY), AVG(SALARY)FROM EMPLOYEE

7. Query 2 • Retrieve the total number of employees in the company SELECT COUNT (*) FROM EMPLOYEE

8. Groups of data • Until now, all group functions have treated the table as one large group of information. At times, you need to divide the table of information into smaller groups. This can be done by using the GROUP BY clause.

9. GROUPING • In many cases, we want to apply the group functions to subgroups of rows in a table • Each subgroup of rows consists of the set of tuples that have the same value for the grouping attribute(s) • The function is applied to each subgroup independently • SQL has a GROUP BY-clause for specifying the grouping attributes, which must also appear in the SELECT-clause

10. Query 3 • For each department, retrieve the department number, the number of employees in the department, and their average salary SELECT DNO, COUNT (*), AVG (SALARY)FROM EMPLOYEEGROUP BY DNO

11. Aggregate Functions • In the previous query, the EMPLOYEE table rows are divided into groups, each group having the same value for the grouping attribute DNO • The COUNT and AVG functions are applied to each such group of rows separately • The SELECT-clause includes only the grouping attribute and the functions to be applied on each group

13. Excluding Group Results • Sometimes we want to retrieve the values of the group functions for only those groups that satisfy certain conditions • The HAVING-clause is used for specifying a selection condition on groups (rather than on individual rows) • The having clause is designed for use with the group by clause to restrict the groups that appear. This is very similar to the where clause, except that the where clause filters individual rows, whereas the having clause filters groups.

14. Excluding Group Results EMP DEPTNO SAL --------- ---------- 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250 5000 “maximum salary per department greater than $2900” DEPTNO MAX(SAL) ---------- ---------- 10 5000 20 3000 3000 2850

15. Query 4 Display the department numbers, and maximum salary for those departments whose maximum salary is greater than $40500. SELECT DNO, MAX(SALARY) FROM EMPLOYEE GROUP BY DNO HAVING MAX(SALARY) > 40500

16. Query 5 • Display the number of employees, and the average salaries for each department that have more than one employee working : Select dno, count(ssn), avg(salary) From employee Group by dno Having count(ssn) > 1;

17. Query 6 • Count the number of distinct salary values in the employee table SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE

18. Query 7 • Retrieve the department number that has more than one location SELECT dnumber FROM dept_locations GROUP BY dnumber HAVING count(dnumber)>2

19. Query 8 • Write a query that will display the difference between the highest salary and lowest salary . Label the column DIFFERENCE SELECT max(salary)-min(salary) DIFFERENCE FROM employee