Database Programming

1
Database Programming

• Sections 5 6 Group functions, COUNT,
  DISTINCT, NVL, GROUP BY, HAVING clauses,
  Subqueries

2
Cartesian Product

• SELECT last_name, department_nameFROM employees,
  departments
• 20 records x 8 record 160 rows

3
Oracle Proprietary Joins

• Equijoin (Natural Join, Join ... Using, Join ...
  On)
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id
  d.department_id
• Inner Join example

4
Non-equijoin (Join.. On)

• SELECT e.employee_id, e.last_name,e.salary,
  j.grade_levelFROM employees e,job_grades jWHERE
  e.salary gt j.lowest_salAND e.salary lt
  j.highest_sal
• SELECT e.employee_id, e.last_name,e.salary,
  j.grade_levelFROM employees e,job_grades jWHERE
  e.salary BETWEEN j.lowest_salAND j.highest_sal

5
Outer Joins (Right Outer Join, Left Outer Join)

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id()
  d.department_id
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e RIGHT OUTER departments dON( e.department_id
  d.department_id)

6
Outer Joins (Right Outer Join, Left Outer Join)

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id
  d.department_id ()
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e LEFT OUTER departments dON( e.department_id
  d.department_id)

7
Self-Joins (Join.. ON)

• SELECT e.last_name emp, m.last_name mgrFROM
  employees e JOIN employees mON(e.manager_id
  m.employee_id)
• Joining the table to itself
• Not common join

8
Cross Join (Cartesian Product)

• SELECT last_name, department_nameFROM employees
  CROSS JOIN departments
• SELECT last_name, department_nameFROM employees,
  departments

9
Natural Join (Equijoin)

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e NATURAL JOIN departments d
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id
  d.department_id

10
Join .. Using (Equijoin)

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e JOIN departments dUSING (department_id )
• Compare with previous and next slide

11
Join .. On (Equijoin)

• SELECT e.employee_id, e.last_name,
  e.department_id, d.location_idFROM employees e
  JOIN departments dON (e.department_id
  d.department_id )

12
Join .. On (Non equijoin)

• SELECT e.employee_id, e.last_name,e.salary,j.grade
  _idFROM employees e JOIN job_grades jON
  (e.salary BETWEEN j.lowest_sal AND j.highest_sal)

13
Right Outer Join ()

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id()
  d.department_id
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e RIGHT OUTER departments dON( e.department_id
  d.department_id)

14
Left OUTER JOINS ()

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e, departments dWHERE e.department_id
  d.department_id ()
• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e LEFT OUTER departments dON( e.department_id
  d.department_id)

15
Full Outer Join

• SELECT e.employee_id, e.last_name,
  e.department_id, d.department_nameFROM employees
  e FULL OUTER departments dON( e.department_id
  d.department_id)