Multiple Table Queries For MVCH

1
(No Transcript)
2
Multiple Table Queries For MVCH

• Team 10B
• Marsha Nebeker
• Patricia Cloninger

3
Introduction

• Previous chapter introduced SQL and explored its
  capabilities for querying one table. The real
  power of the relational model derives from its
  storage or data in many related entities. Taking
  advantage of this approach to data storage
  requires establishing the relationships and
  constructing queries that use data from multiple
  queries.

4
Processing Multiple Tables

• The linking of related tables varies among
  different types of relational systems. In SQL,
  the WHERE clause of the SELECT command is also
  used for multiple table operations. In fact, the
  SELECT can include references to several tables
  in the same command. SQL has two ways to use
  SELECT for combining data from related tables
• Join
• Subqueries

5
Requirements of a Join

• Indicate in the SELECT clause all columns to
  display.
• List in the FROM clause all tables involved in
  the query.
• Give the conditions in the WHERE clause to
  restrict data to be retrieved.

6
JOIN

• The most frequently used relational operation,
  which combines data together from two or more
  related tables into one resultant table, is
  called a join. Each row in the resultant table
  returns contained data from row in different
  input tables where values for common columns
  match. This chapter describes four types of
  joins
• Equi-joins, Natural joins, Outer joins, and Union
  joins

7
Types of Joins

• Equi-join a join in which the joining condition
  is based on equality between values in the common
  columns
• Natural join same as equi-join, except one of
  the duplicate columns is eliminated in the result
  table
• Outer join a join in which rows that do not
  have matching values in common columns are
  included in the table
• Union join return a table that includes all
  data from each table that was joined

8
Subqueries

• The other method for multiple table processing is
  subqueries. This method involves placing an inner
  query within a clause of another query. The inner
  query provides values for the condition of the
  outer query. These are sometimes referred to as
  being nested.

9
Types of Subqueries

• Correlated subquery In SQL, a subquery in which
  processing the inner query depends on data from
  the outer query.
• Noncorrelated subquery a subquery in which the
  inner query is independent from the data of the
  outer query.

10
Join vs. Subqueries

• Often times the join and subquery approach may
  be used to accomplish the same result. Other
  times only a join OR a subquery will work. An
  example in which a join and a subquery will
  return the same result will be demonstrated
  later.

11
Project Case

• Use the SQL database design implemented in
  Chapter 7 for Mountain View Community Hospital to
  complete the project questions and project
  exercises.

12
Project Questions

• 1. What version of SQL will you use to do the
  project exercises?
• SQL Plus and Oracle 8i
• 2. Does your SQL-based DBMS support dymanic SQL,
  functions, stored procedures and UDTs?
• Yes
• These capabilities have been included as vendor
  specific extensions, and will continue to exist
  for some time.

13
Project Exercises

• 1.a. Select information from two or more tables.
• SQLgt SELECT PATIENT_T654.PATIENT_NO,
  PATIENT_T654.PATIENT_NAME, PATIENT_T654.BED_NO,
  BED_T654.ROOM_NO, BED_T654.WARD_NO
• 2 FROM PATIENT_T654, BED_T654
• 3 WHERE PATIENT_T654.BED_NOBED_T654.BED_NO

14
1.a. Response to query

• PATIENT_NO PATIENT_NAME BED_NO
  ROOM_NO WARD_NO
• ---------- ------------------------- ----------
  ---------- ----------
• 1010 Bizzy Bone 1
  304 101
• 1011 P Diddy 2
  205 102
• 1012 Ice Cube 3
  402 103
• 1013 Christina Aguleria 4
  102 104
• 1014 Justin Timberlake 5
  103 105
• 1015 Ozzy Osbourn 6
  501 106
• 6 rows selected.

15
Project Exercises

• 1.b. Use a subquery syntax.
• SQLgt SELECT PATIENT_NAME
• FROM PATIENT_T654
• WHERE PATIENT_T654.PATIENT_NO IN
• (SELECT PATIENT_NO FROM PERFORMS_T654
• WHERE TREATMENT_DATE04-April-01)
• Response to query
• PATIENT_NAME
• -----------------------
• P Diddy

16
Project Exercises

• 1.c. Report of whether or not each ward has been
  fully assigned in the upcoming period.
• SQLgt SELECT EMPLOYEE_T654.EMP_NO,
  EMPLOYEE_T654.EMP_NAME, WARD_T654.WARD_NAME
• 2 FROM EMPLOYEE_T654, WARD_T654, ASSIGNED_T654
• 3 WHERE WARD_T654.WARD_NOASSIGNED_T654.WARD_NO

17
1c Response to query

• EMP_NO EMP_NAME WARD_NAME
• --------- -------------------------
  -------------------------
• 4492 Justin Wallis Intensive
  Care
• 5549 Steve Gibson Intensive
  Care
• 6543 Heather Harrison Intensive
  Care
• 8765 Tiffani Ratchford Intensive
  Care
• 1987 Tony Baker Intensive
  Care
• 1812 Elvin Turner Intensive
  Care
• 4492 Justin Wallis Pediatric
  Ward
• 5549 Steve Gibson Pediatric
  Ward
• 6543 Heather Harrison Pediatric Ward
• 8765 Tiffani Ratchford Pediatric Ward
• 1987 Tony Baker Pediatric
  Ward

18
1c continued

• EMP_NO EMP_NAME WARD_NAME
• ---------- -------------------------
  -------------------------
• 1812 Elvin Turner Pediatric
  Ward
• 4492 Justin Wallis Maternity
  Ward
• 5549 Steve Gibson Maternity
  Ward
• 6543 Heather Harrison Maternity
  Ward
• 8765 Tiffani Ratchford Maternity
  Ward
• 1987 Tony Baker Maternity
  Ward
• 1812 Elvin Turner Maternity
  Ward
• 4492 Justin Wallis Cosmetic
  Ward
• 5549 Steve Gibson Cosmetic
  Ward
• 6543 Heather Harrison Cosmetic Ward
• 8765 Tiffani Ratchford Cosmetic
  Ward

19
1d End of query

• EMP_NO EMP_NAME WARD_NAME
• ---------- -------------------------
  -------------------------
• 1987 Tony Baker Cosmetic
  Ward
• 1812 Elvin Turner Cosmetic
  Ward
• 4492 Justin Wallis Radiology
  Ward
• 5549 Steve Gibson Radiology
  Ward
• 6543 Heather Harrison Radiology Ward
• 8765 Tiffani Ratchford Radiology
  Ward
• 1987 Tony Baker Radiology
  Ward
• 1812 Elvin Turner Radiology
  Ward
• 4492 Justin Wallis Cardiac
  Ward
• 5549 Steve Gibson Cardiac Ward
• 6543 Heather Harrison Cardiac Ward
• EMP_NO EMP_NAME WARD_NAME
• ---------- -------------------------
  -------------------------
• 8765 Tiffani Ratchford Cardiac Ward
• 1987 Tony Baker Cardiac
  Ward
• 1812 Elvin Turner Cardiac
  Ward

20
Project Exercises

• 1.d. Use a UNION Statement.
• SELECT PERSON_T654.PERSON_LAST_NAME,PERSON_T654.P
  ERSON_FIRST_NAME,TREATMENT_T654.TREATMENT_NAME.
• COUNT(TREATMENT_T654.TREATMENT_ID) AS
  CountOfTreatment_ID
• FROM PERSON_T654, PATIENT_T654, TREATMENT_T654,
  PERFORMS_T654
• WHERE TREATMENT_T654.TREATMENT_ID
  PERFORMS_T654.TREATMENT_ID and PATIENT_T654.PATIEN
  T_ID PERFORMS_T654.PATIENT_ID and
  PERSON_T654.PERSON_ID PATIENT_T654.PERSON_ID
• GROUP BY PERSON_T654.PERSON_LAST_NAME,
  PERSON_T654.PERSON_FIRST_NAME, TREATMENT_T654.TREA
  TMENT_NAME
• HAVING (((TREATMENT_T654.TREATMENT_NAME) Brain
  Surgery) AND ((COUNT(TREATMENT_T654.TREATMENT_ID)
  ) gt 1))

21
Project Exercises 1d cont

• UNION ALL SELECT PERSON_T654.PERSON_LAST_NAME,
  PERSON_T654.PERSON_FIRST_NAME, TREATMENT_T654.TREA
  TMENT_NAME,
• COUNT (TREATMENT_T654.TREATMENT_ID) AS
  CountOfTreatment_ID
• FROM PERSON_T654, PATIENT_T654, TREATMENT_T654,
  PERFORMS_T654
• WHERE TREATMENT_T654.TREATMENT_ID
  PERFORMS_T654.TREATMENT_ID and PATIENT_T654.PATIEN
  T_ID PERFORMS_T654.PATIENT_ID and
  PERSON_T654.PERSON_ID PATIENT_T654.PERSON_ID
• GROUP BY PERSON_T654.PERSON_LAST_NAME,
  PERSON_T654.PERSON_FIRST_NAME, TREATMENT_T654.TREA
  TMENT_NAME
• HAVING (((TREATMENT_T654.TREATMENT_NAME)
  Drug) AND ((COUNT (TREATMENT_T654.TREATMENT_ID))
  gt 1))

22
Project Exercises

• 2.a. For a given physician, which treatments
  has that physician performed on each patient
  referred by that physician to the hospital.

23
Project Exercises 2.a.

• SELECT DISTINCT PERFORMS_T654.PHYSICIAN_ID,
  PERSON_T654.PERSON_LAST_NAME, PERSON_T654.PERSON_F
  IRST_NAME, PERFORMS_T654.PATIENT_ID,
  P1.PERSON_LAST_NAME, P1.PERSON_FIRST_NAME,
• PERFORMS_T654.TREATMENT_ID, TREATMENT_T654.TREATM
  ENT_NAME
• FROM PERFORMS_T654, PATIENT_T654,
  PHYSICIAN_T654, PERSON_T654, PERSON_T654
  P1,TREATMENT_T654
• WHERE PERFORMS_T654.PHYSICIAN_ID
  PATIENT_T654.PHYSICIAN_ID
• and PERFORMS_T654.PHYSICIAN_ID
  PHYSICIAN_T654.PHYSICIAN_ID and
  PERFORMS_T654.PATIENT_ID PATIENT_T654.PATIENT_ID
  
• and PHYSICIAN_T654.PERSON_ID
  PERSON_T654.PERSON_ID
• and PATIENT_T654.PERSON_ID P1.PERSON_ID and
  PERFORMS_T654.
• TREATMENT_ID TREATMENT_T654.TREATMENT_ID

24
Project Exercises

• 2.b. For the query in 2a, include physicians
  who have not referred patients to the hospital.

25
Project Exercises 2.b.

• SELECT DISTINCT PERFORMS_T654.PHYSICIAN_ID,
  PERSON_T654.PERSON_LAST_NAME, PERSON_T654.PERSON_F
  IRST_NAME,
• PERFORMS_T654.PATIENT_ID, P1.PERSON_LAST_NAME,
• P1.PERSON_FIRST_NAME, PERFORMS_T654.TREATMENT_ID,
  TREATMENT_T654.TREATMENT_NAME
• FROM PERFORMS_T654, PATIENT_T654, PHYSICIAN_T654,
  PERSON_T654, PERSON_T654 P1,TREATMENT_T654
• WHERE PERFORMS-T654.PHYSICIAN_IDPATIENT_T654.PHYS
  ICIAN_ID
• and PERFORMS_T654.PHYSICIAN_IDPHYSICIAN_T654.PHY
  SICIAN_ID
• and PERFORMS_T654.PATIENT_IDPATIENT_T654.PATIENT
  _ID
• and PHYSICIAN_T654.PERSON_IDPERSON_T654.PERSON_I
  D
• and PATIENT_T654.PERSON_IDP1.PERSON_ID
• and PERFORMS_T654.TREATMENT_IDTREATMENT_T654.TRE
  ATMENT_ID

26
Project Exercises

• 2.c. For each patient, what is the average number
  of treatments performed on him by each physician.
• SELECT PERFORMS_T654.PATIENT_NO,
  AVG(TT654.TOT_TREATMENT)
• FROM PERFORMS_T654,
• (SELECT PATIENT_NO,COUNT()AS TOT_TREATMENT
• FROM PERFORMS_T654 GROUP BY PATIENT_NO) TT654
• WHERE PERFORMS_T654.PATIENT_NO TT654.PATIENT_NO
• GROUP BY PERFORMS_T654.PATIENT_NO

27
2c Response to query

• PATIENT_NO AVG(TT654.TOT_TREATMENT)
• ---------- ------------------------
• 1010 1
• 1011 1
• 1012 1
• 1013 1
• 1014 1
• 1015 1
• 6 rows selected.

28
Project Exercises

• 2.d. For each nurse in charge, what is the
  total number of hours worked by all employees who
  work in the care center that nurse supervises?
• SELECT CARE_CENTER_T654.IN_CHARGE,
• SUM(ASSIGNMENT_T654.HOURS_WORKED)
• FROM CARE_CENTER_T654 ASSIGNMENT_T654
• WHERE ASSIGNMENT_T654.CARECENTER_ID
  CARE_CENTER_T654.CARECENTER_ID
• GROUP BY IN_CHARGE

29
Marshas Question

• Using the MVCH database, write a query to find
  out what treatments where performed on what date.

30
Patricias Question

• Write a query that would give you the name of a
  specific patient on the MVCH database.