Database Programming

1. Database Programming Sections 5 & 6 – Group functions, COUNT, DISTINCT, NVL, GROUP BY, HAVING clauses, Subqueries

2. Group functions • Group functions operate on sets of rows to give one result per group • AVG • COUNT • MAX • MIN • SUM • STDDEV • VARIANCE Marge Hohly

3. AVG function • Returns the average of a set of values – usable only on columns of number type • Syntax:SELECT AVG(salary)FROM employeesWHERE job_id LIKE ‘%REP%’; Marge Hohly

4. SUM function • Returns the sum of a set of values – usable only on columns of number type • Syntax:SELECT SUM(salary)FROM employeesWHERE job_id LIKE ‘%REP%’; Marge Hohly

5. MIN and MAX functions • Return the minimum and maximum value from a set of values • May be used with columns of NUMBERS, VARCHAR2, and DATE datatypeSELECT MIN(department_id)FROM departments;SELECT MAX(last_name)FROM employees;SELECT MIN(hire_date), MAX(hire_date)FROM employeesWHERE job_id LIKE ‘%REP%’; Marge Hohly

6. COUNT function • Returns the number of rows counted with non null values for the expression specifiedSELECT COUNT(commission_pct)FROM employees; Marge Hohly

7. COUNT function cont’d • COUNT(*) returns the number of rows in the tableSELECT COUNT(*)FROM employees; Marge Hohly

8. STDDEV function • A statistical function that returns the standard deviation ignoring null values for expressions of NUMBER typeSELECT STDDEV(salary)FROM employees; Marge Hohly

9. VARIANCE function • A statistical function that returns the variance ignoring null values for expressions NUMBER typeSELECT VARIANCE(salary)FROM employees; Marge Hohly

10. DISTINCT keyword • The DISTINCT keyword can be used with all group functions • In forces the group function to consider only non-duplicate valuesSELECT COUNT(DISTINCT(last_name))FROM employees; Marge Hohly

11. Group functions & NULL values • Group functions ignore NULL valuesSELECT AVG(commission_pct)FROM employees; • The average commission_pct will only be calculated using those rows that have a commission_pct, null rows will be excluded. Marge Hohly

12. NVL function • This is used to replace a NULL with a given value • The value must be of the same datatype as the colunm NO!SELECT commission_pct, NVL(commission_pct, ‘not eligible’)FROM employees;YES!SELECT commission_pct, NVL(commission_pct, 0)FROM employees; Marge Hohly

13. Using NVL with group functions • The NVL function is nested inside the group functionWhen you want to include rows will null values, use NVL function to add a value to the null rows.SELECT AVG(commission_pct), AVG(NVL(commission_pct,0))FROM employees;Which column will have the lowest value? Marge Hohly

14. GROUP BY Clause • Use the Group By clause to divide the rows in a table into groups than apply the Group Functions to return summary information about that group • In the example below, the rows are being GROUPed BY department_id. The AVG(group functin) is then applied to each GROUP, or department_id. • SELECT department_id, AVG(salary)FROM employeesGROUP BY department_id; Marge Hohly

15. Results of previous query Marge Hohly

16. GROUP BY Clause • With aggregate (group) functions in the SELECT clause, be sure to include individual columns from the SELECT clause in a GROUP BY clause!!! • SELECT department_id, job_id, SUM(salary)FROM employeesWHERE hire_date <= ’01-JUN-00’GROUP BY department_id, job_id; • SELECT d.department_id,d.department_name, MIN(e.hire_date) AS “Min Date”FROM departments d, employees eWHERE e.department_id=d.department_idGROUP BY????? Marge Hohly

17. GROUP BY Rules... • Use the Group By clause to divide the rows in a table into groups then apply the Group Functions to return summary information about that group. • If the Group By clause is used, all individual columns in the SELECT clause must also appear in the GROUP BY clause. • Columns in the GROUP BY clause do not have to appear in the SELECT clause • No column aliases can be used in the Group By clause. • Use the ORDER BY clause to sort the results other than the default ASC order. • The WHERE clause, if used, can not have any group functions – use it to restrict any columns in the SELECT clause that are not group functions. • Use the HAVING clause to restrict groups not the WHERE clause. Marge Hohly

18. The HAVING Clause • With the HAVING clause the Oracle Server: • Groups rows. • Applies group function to the group(s). • Displays the groups that match the criteria in the HAVING clause. • SELECT department_id, job_id, SUM(salary)FROM employeesWHERE hire_date <= ’01-JUN-00’GROUP BY department_id, job_idHAVING department_id >50; Marge Hohly

19. SELECT department_id, job_id, SUM(salary)FROM employeesWHERE hire_date <= ’01-JUN-00’GROUP BY department_id, job_idHAVING department_id >50; The HAVING clause example Marge Hohly

20. WHERE or HAVING ?? • The WHERE clause is used to restrict rows. SELECT department_id, MAX(salary)FROM employeesWHERE department_id>=20GROUP BY department_id; • The HAVING clause is used to restrict groups returned by a GROUP BY clause. SELECT department_id, MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary)> 1000; Marge Hohly

21. SUBQUERY • SELECT statement that is embedded in a clause of another SELECT statement • Can be placed in the WHERE clause, the HAVING clause, and the FROM clause. • Executes first and its output is used to complete the query condition for the main or outer query. • SELECT last_nameFROM employeesWHERE salary >(SELECT salaryFROM employeesWHERE employee_id = 104); Marge Hohly

22. SUBQUERIES • Guidelines for using subqueries are: • The subquery is enclosed in parentheses. • The subquery is placed on the right side of the comparison condition. • The outer and inner queries can get data from different tables. • Only one ORDER BY clause can be used for a SELECT statement; and, if specified, it must be the last clause in the main SELECT statement. • The only limit on the number of subqueries is the buffer size the query uses. Marge Hohly

23. SUBQUERIES • There are two types of subqueries: • Single-row subqueries that use single-row operators (>,=.>=,<,<>,<=) and return only one row from the inner query. • Multiple-row subqueries that use multiple-row operators (IN, ANY, ALL) and return more than one row from the inner query. Marge Hohly

24. SINGLE-ROW SUBQUERY • SELECT last_nameFROM employeesWHERE salary > (SELECT salary FROM employees WHERE last_name = ‘Abel’); • Subquery returns 11000, thus the main SELECT statement returns all employees with a salary greater than 11000. • What is there is more than one Abel in the company?? Marge Hohly

25. Example • SELECT department_id, department_nameFROM departmentsWHERE department_id = (SELECT department_id FROM employees WHERE salary < 4000); • What will result? Marge Hohly

26. SUBQUERIES FROM DIFFERENT TABLES • Subqueries are not limited to just one inner query. As the example illustrates, there can be more than one subquery returning information to the outer query. Also, the outer and inner queries can get data from different tables. • SELECT last_name,job_id,salary,department_idFROM employeesWHERE job_id= (SELECT job_id FROM employees WHERE employee_id = 141)AND department_id = (SELECT department_id FROM departments WHERE location_id = 1500); • Subquery returns job_id = ST_CLERK and department_id = 50 Marge Hohly

27. Results of previous query Marge Hohly

28. SUBQUERIES • Gropu functions can be used in single-row subqueries. The inner query returns a single row to the outer query.SELECT last_name,first_name,salaryFROM f_staffsWHERE salary < (SELECT MAX(salary) FROM f_staffs); Marge Hohly

29. Example results • Subquery returns MAX(salary) = 60 Marge Hohly

30. GROUP Functions in HAVING Clause • HAVING clause is used to restrict gropus and always has a group condition (such as MIN, MAX, AVG) stated • See next page for example Marge Hohly

31. SELECT department_id, MIN(salary)FROM employeesGROUP BY department_idHAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50); Inner query returns 7500 for minimum salary GROUP Functions in HAVING Clause Marge Hohly