Agenda for 02/21/2006

1
Agenda for 02/21/2006

• Learn how to use more than one table in a query.
• Discuss how DBMS processes multiple tables.
• Explain the different types of joins.
• Describe join conditions.
• Do lab exercise

2
Sample Database ERD
Assume referential integrity is NOT maintained in
this database
3
Multiple Table Access SELECT FROM emp, time
Vocabulary Words Select List Result
Table Join Cartesian Product/Cross Join Join
Condition
4
Combining Tables Based on a Shared Column (newer
syntax using join condition)
SELECT name, contractid, datetime, amoun
t/60 FROM time INNER JOIN emp ON time.empid
emp.empid
5
Viewing all rows from one of the tables in a join
(newer syntax using join condition)
SELECT name, time.empid contractid, datetime,
amount/60 FROM time LEFT OUTER JOIN emp ON emp.emp
id time.empid
Imagine that the time table is left (because it
is declared first) and the emp table is right
(because it is declared second)
6
Right outer join Viewing all rows in other
table (newer syntax using join condition)
SELECT name, time.empid,
contractid, datetime,
amount/60 FROM time RIGHT OUTER
JOIN emp ON time.empid emp.empid
Remember that the time table is left (because
it is declared first) and the emp table is
right (because it is declared second)
7
Full outer join Viewing all rows in both tables
(newer syntax using join condition option 1
is not available)
SELECT name, time.empid, contractid,
datetime, amount/60 FROM time FULL OUTER
JOIN emp ON time.empid emp.empid
8
Combining more than two tables into a single
result table
SELECT emp.name, time.empid, time.worktypeid,
work.description, datetime, amount/60 FROM
time INNER JOIN emp ON time.empid
emp.empid INNER JOIN work ON time.worktypeid
work.worktypeid
9
Displaying all data in TIME table
COLUMN emp_name heading Employee Name COLUMN
descr heading Type of Work SELECT
NVL(emp.name,'NOT IN EMPLOYEE TABLE') emp_name,
time.empid, time.worktypeid,
NVL(work.description,'NOT IN WORK TABLE)
descr, datetime, amount/60 FROM time LEFT
OUTER JOIN emp ON time.empid emp.empid LEFT
OUTER JOIN work ON time.worktypeid
work.worktypeid ORDER BY emp.name
10
Adding a standard condition
SELECT NVL(emp.name,'NOT IN EMPLOYEE TABLE')
emp_name, time.empid, time.worktypeid,
NVL(work.description,'NOT IN WORK TABLE)
descr, datetime, amount/60 FROM time LEFT
OUTER JOIN emp ON time.empid emp.empid LEFT
OUTER JOIN work ON time.worktypeid
work.worktypeid WHERE datetime BETWEEN
01-jan-2006 and 15-jan-2006 ORDER BY
emp.name
11
Summarizing Data
COLUMN descr heading Type of
Work SELECT NVL(work.description, No
Description) descr, round(sum(amount/60),
2) FROM time LEFT OUTER JOIN work ON
work.worktypeid time.worktypeid GROUP BY
work.description
The GROUP BY statement is frequently used with a
result table created by multiple underlying tables
12
Recursive Relationship with Employee to Assign
Manager
13
Self-join also called a recursive join (newer
syntax using join condition)
SELECT worker.empid "worker", worker.name
"worker name", manager.empid
"manager", manager.name "manager
name" FROM emp worker INNER JOIN emp
manager ON worker.mgrid manager.empid
What change is necessary to make all employees
appear whether there is a manager?