Chapter 7: Relational Implementation with SQL

1
(No Transcript)
2
Chapter 8 Advanced SQL
3
Sample Database
4
Multiple-Table Queries

• JOIN operation specified in FROM and WHERE
  clauses
• SELECT skill_typeFROM worker, assignmentWHERE
  worker.worker_id assignment.worker_id AND
  bldg_id 435

5
Inner Join Queries

• JOIN statement in FROM clause
• SELECT FirstName, MidName, LastName,
  FocusAreasFROM tblPersons INNER JOIN tblAgents
  ON tblPersons.PersonID tblAgents.PersonIDWHE
  RE City Burbank

6
Aliases

• Defined in the FROM clause
• useful shorthand
• necessary in recursive relations
• SELECT wrk.worker_name, sup.worker_nameFROM
  worker wrk, worker supWHERE sup.worker_id
  wrk.supv_id
• SELECT wrk.worker_name, sup.worker_nameFROM
  worker wrk INNER JOIN worker sup ON wrk.supv_id
  sup.worker_id

7
Subqueries

• SELECT skill_type FROM workerWHERE worker_id
  IN (SELECT worker_id FROM assignment WHERE
  bldg_id 435)
• Outer query and inner query
• Noncorrelated subquery creates a set of values
  that is then used IN the outer query

8
Correlated Subqueries

• Usually returns a single value, not a set
• value returned depends on record of interest in
  outer query
• SELECT worker_name FROM worker wrkWHERE
  wrk.hrly_rate gt (SELECT sup.hrly_rate FROM
  worker sup WHERE sup.worker_id wrk.supv_id)

9
EXISTS and NOT EXISTS

• EXISTS evaluates to true if the resulting set is
  not empty
• NOT EXISTS evaluates to true if the resulting set
  is empty

10
NOT EXISTS

• Select every worker that has not had an
  assignment in building 435
• SELECT worker_id FROM workerWHERE NOT
  EXISTS (SELECT FROM assignment WHERE
  assignment.worker_id worker.worker_id
  AND bldg_id 435)

11
NOT EXISTS

• Select every worker such that there is no
  building to which they are not assigned i.e.,
  select every worker who has worked in all
  buildings.
• SELECT worker_id FROM worker wWHERE NOT
  EXISTS (SELECT bldg_id FROM building b WHERE
  NOT EXISTS (SELECT FROM assignment a WHERE
  a.bldg_id b.bldg_id AND a.worker_id
  w.worker_id))

12
(No Transcript)