Team 10B: Multiple Table Queries for MVCH

1
Team 10B Multiple Table Queries for MVCH

• Steven Hartley
• Marcus Gragg
• Health Smith
• Elton Smith

2
Introduction

• Previous chapters introduced SQL and explored its
  capabilities for querying one table. The real
  power of the relational model derivers from its
  power of the relational model derives from it
  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.

3
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

4
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 retrieved

5
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 for types of
  joins Equi-joins, Natural joins, Outer joins,
  and Union joins

6
Types of Joins

• Equi-joins- A join which the joining condition is
  based on equality between values in the common
  columns
• Natural joins- 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 joins- return a table that includes all
  data from each table that was joined

7
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.

8
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.

9
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. The
  following is an example in which a join and a
  subquery will return the same result.

10
Project Questions

• 1. What version of SQL will you use the project
  exercise?
• 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.

11
Project Exercises

• 1(a) Select information from two or more tables.
• SQL SLECT PATIENT_T650.PATEINT_NO. PATIENT_T650.
  PATEINT_NAME.
• PATIENT_T650. BED_NO.
• 2 BED_T650.ROOM_NO.BED_T650.WARD_NO.BED_T650.BE
  D_NO
• 3 FROM PATIENT_T650.BED_T650
• 4 WHERE PATIENT_T650.BED_NO
  BED_T650.BED_NO.
• 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

12
Project Exercises

• 1(b) Use a subquery syntax.
• SQLSELECT PATIENT_NAME FROM PATEINT_T650
• 2 WHERE PATIENT_T650.PATIENT_NO IN
• 3 (SELECT PATIENT_NO FROM PERFORMS_T650
• 4 WHERE TREATMENT_DATE04-APRIL-01)
• PATEINT_NAME
• ------------------------
• P DIDDY

13
Project Exercises

• 1(c) Report of whether or not each ward has
  been fully assigned in the upcoming period.
• SQL SELECT EMPLOYEE_T650.EMP.NO,
  EMPLOYEE_T650.EMP_
• NAME, WARD_T650.WARD_NAME
• 2 FROM EMPLOYEE _T650, WARD_T650,
  ASSIGNED_T650
• 3 WHERE WARD_T650. WARD_NOASSIGNED_T650.WARD_N
  O

14

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

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

15
1(d) A union query SELECT PERSON_t.Person_Last_
Name,PERSON_t.Person_First_Name. TREATMENT_t.Tre
atment_Name, Count(TREATMENT_t,Treatment_ID) AS
CountOfTreatment_ID FROM PERSON_t,PATIENT_t,TREA
TMENT_t,PERFORMS_t WHERE TREATMENT_t.Treatment_I
D PERFORMS_t.Treatment_ID AND PATIENT_t.Patie
nt_ID PERFORMS_t.Patient_id AND
PERSON_t.Person_ID PATIENT_t.Person_ID GROUP
BY PERSON_t.Person_Last_Name, PERSON_t.Person_Fi
rst_Name, TREATMENT_t.Treatment_Name HAVING
(((TREATMENT_t.Treatment_Name)Homeopathic) AN
D ((Count(TREATMENT_t.Treatment_ID))1)) UNION
ALL SELECT PERSON_t.Person_Last_Name, PERSON_T.P
erson_First_Name, TREATMENT_t.Treatment_Name,
Count(TREATMENT_t.Treatment_ID) AS
CountOfTreatment_ID FROM PERSON_T,PATIENT_t,TREA
TMENT_t, PERFORMS_t, WHERE TREATMENT_t.Treatment
_ID PERFORMS_t.Treatment_ID
AND PATIENT_t.Patient_ID PERFORMS_t.Patient_i
d AND PERSON_t.Person_ID PATIENT_t.Person_ID
GROUP BY PERSON_t.Person_Last_Name, PERSON_T.Pe
rson_First_Name, TREATMENT_t.Treatment_Name HA
VING (((TREATMENT_t.Treatment_Name)Drug)
AND (((Count(TREATMENT_t.Treatment_ID))1))
16
2(a) For a given physician, which treatments
has that physician performed on each patient
referred by that physician to the hospital.
SELECT DISTINCT PERFORMS_T.PHYSICIAN_ID, PER
SON_T.PERSON_LAST_NAME, PERSON_T.PERSON_FIRST_NAM
E, PERFORMS_T.PATIENT_ID.P1.PERSON_LAST_NAME, P1
.PERSON_FIRST_NAME, PERFORMS_T.TREATMENT_ID,TREAT
MENT_T.TREATMENT_NAME FROM PERFORMS_T.PATIENT_T.P
HYSICIAN_T, PERSON_T,PERSON_T P1,
TREATMENT_T WHERE PERFORMS_T.PHYSICIAN_ID
PATIENT_T.PHYSICIAN_ID AND PERFORMS_T.PHYSICIA
N_ID PHYSICIAN_T.PHYSICIAN_ID AND
PERFORMS_T.PATIENT_ID PATIENT_T.PATIENT_ID
AND PHYSICIAN_T.PERSON_ID PERSON_T.PERSON_ID
AND PATIENT_T.PERSON_ID P1.PERSON_ID AND
PERFORMS_T.TREATMENT_ID TREATMENT_TTREATMENT_
ID
17
2(b) For the query in (a), include physicians
who have not referred patients to the
hospital SELECT DISTINCT PERFORMS_T.PHYSICIA
N_ID, PERSON_T.PERSON_LAST_NAME,
PERSON_T.PERSON_FIRST_NAME, PERFORMS_T.PATIENT_ID,
P1.PERSON_LAST_NAME,P1.PERSON_FIRST_NAME,
PERFORMS_T.TREATMENT_ID,TREATMENT_T.TREATMENT_NAM
E FROM PERFORMS_T, PATIENT_T, PHYSICIAN_T,
PERSON_T, PERSON_T P1,TREATMENT_T WHERE
PERFORMS_T.PHYSICIAN_ID PATIENT_T.PHYSICIAN_ID
AND PERFORMS_T.PHYSICIAN_ID
PHYSICIAN_T.PHYSICIAN_ID AND
PERFORMS_T.PATIENT_ID PATIENT_T.PATIENT_ID
AND PHYSICIAN_T.PERSON_ID PERSON_T.PERSON_ID
AND PATIENT_T.PERSON_ID P1.PERSON_ID
AND PERFORMS_T.TREATMENT_ID TREATMENT_T.TREA
TMENT_ID
18
2(c) For each patient, what is the
average number of treatments performed on him/her
by each physician? SELECT PERFORMS_T.PATIENT_
ID.AVG(TT.TOT_TREATMENT) FROM PERFORMS_T, (SELEC
T PATIENT_ID,count() AS TOT_TREATMENT
FROM PERFOMRS_T GROUP BY PATIENT_ID) TT WHERE
PERFORMS_T.PATIENT_ID TT.PATIENT_ID GROUP BY
PERFORMS_T.PATIENT_ID
19
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_T.IN_CHARGE, SUM(ASSIGNMENT_T.HOURS_W
ORKED) FROM CARE_CENTER_T,ASSIGNMENT_T WHERE
ASSIGNMENT_T.CARECENTER_ID CARE_CENTER_T.CARECE
NTER_ID GROUP BY IN_CHARGE
20
Heaths Question

• What are the two basic approaches for joining
  two tables and which one(s) can be used by MVCH?
  
• Describe each approach for joining the tables in
  MVCH.

21
Marcuss question

• Because a lot of times information you need is in
  multiple tables you need to join tables. Explain
  one of the possible ways to join tables and give
  a SQL command example?

22
Eltons Question

• If an MVCH physician want to find out which of
  has patients has put in an order for a
  prescription what SQL command would he use?

23
Stevens Question

• What does a specialty formed correlated subquery
  require? And how is it used in MVCH?