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

3
AVG function

• Returns the average of a set of values usable
  only on columns of number type
• SyntaxSELECT AVG(salary)FROM employeesWHERE
  job_id LIKE REP

4
SUM function

• Returns the sum of a set of values usable only
  on columns of number type
• SyntaxSELECT SUM(salary)FROM employeesWHERE
  job_id LIKE REP

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
  departmentsSELECT MAX(last_name)FROM
  employeesSELECT MIN(hire_date),
  MAX(hire_date)FROM employeesWHERE job_id LIKE
  REP

6
COUNT function

• Returns the number of rows counted with non null
  values for the expression specifiedSELECT
  COUNT(commission_pct)FROM employees

7
COUNT function contd

• COUNT() returns the number of rows in the
  tableSELECT COUNT()FROM employees

8
STDDEV function

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

9
VARIANCE function

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

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_n
  ame))FROM employees

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.

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 employeesYES!SELECT
  commission_pct, NVL(commission_pct, 0)FROM
  employees

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 employeesWhich
  column will have the lowest value?

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

15
Results of previous query
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 lt 01-JUN-00GROUP BY
  department_id, job_id
• SELECT d.department_id,d.department_name,
  MIN(e.hire_date) AS Min DateFROM departments
  d, employees eWHERE e.department_idd.department_
  idGROUP BY ?????

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.

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 lt 01-JUN-00GROUP BY
  department_id, job_idHAVING department_id gt50

19
The HAVING clause example

• SELECT department_id, job_id, SUM(salary)FROM
  employeesWHERE hire_date lt 01-JUN-00GROUP BY
  department_id, job_idHAVING department_id gt50

20
WHERE or HAVING ??

• The WHERE clause is used to restrict rows.
• SELECT department_id, MAX(salary)FROM
  employeesWHERE department_idgt20GROUP 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)gt 1000

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
  gt(SELECT salaryFROM employeesWHERE employee_id
  104)

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.

23
SUBQUERIES

• There are two types of subqueries
• Single-row subqueries that use single-row
  operators (gt,.gt,lt,ltgt,lt) 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.

24
SINGLE-ROW SUBQUERY

• SELECT last_nameFROM employeesWHERE salary gt
  (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??

25
Example

• SELECT department_id, department_nameFROM
  departmentsWHERE department_id (SELECT
  department_id FROM employees WHERE
  salary lt 4000)
• What will result?

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

27
Results of previous query
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,sa
  laryFROM f_staffsWHERE salary lt
  (SELECT MAX(salary) FROM f_staffs)

29
Example results

• Subquery returns MAX(salary) 60

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

31
GROUP Functions in HAVING Clause

• SELECT department_id, MIN(salary)FROM
  employeesGROUP BY department_idHAVING
  MIN(salary) gt (SELECT MIN(salary) FROM
  employees WHERE department_id 50)
• Inner query returns 7500 for minimum salary