Mountain View Community Hospital

1
Mountain View Community Hospital

• Team 9B
• Kurt Blankenship
• Melissa Thomas
• LeKeith Grace
• Richie Crawford

2
Description of Project Case
Use the SQL data model constructed for MVCH in
Chapter 4 to complete the project questions and
project exercises.
3
WHERE Clause Example for MVCH
SELECT TREATMENT_NO FROM TREATMENT_T50 WHERE
TREATMENT_NAME Brain Surgery
SQLgt SELECT TREATMENT_NO 2 FROM TREATMENT_T50
3 WHERE TREATMENT_NAME'Brain Surgery'
4
Compound Condition example for MVCH

• SQLgt SELECT ROOM_NO
• FROM ROOM_ITEM-ITEM_T50
• WHERE ITEM_NO 03
• AND DATE_T 3-JAN-02

NotePad SELECT ROOM_NO FROM
ROOM_ITEM_T50 WHERE ITEM_NO 03 AND DATE_T
3-JAN-02
5
Examples cont.
SELECT PATIENT_NAME FROM PATIENT_T50 WHERE
PHYSICIAN_ID 1235 OR PATIENT_NO 1012

• SQLgt SELECT PATIENT_NAME
• FROM PATIENT_T50
• WHERE PHYSICIAN_ID 1235
• OR PATIENT_NO 1012

6
Cont.
SELECT PHYSICIAN_NAME FROM PHYSICIAN_T50 WHERE
NOT (PHYSICIAN_ID 1238)

• SQLgt SELECT PHYSICIAN_NAME
• FROM PHYSICIAN_T50
• WHERE NOT (PHYSICIAN_ID 1238)

7
Sorting with Multiple Keys in Descending Order
for MVCH
SELECT PATIENT_NAME, PATIENT_NO, BED_NO FROM
PATIENT_T50 ORDER BY PATIENT_NO DESC,
PATIENT_NAME

• SQLgt SELECT PATIENT_NAME, PATIENT_NO, BED_NO
• FROM PATIENT_T50
• ORDER BY PATIENT_NO DESC, PATIENT_NAME

8
Range Examples for MVCH
SELECT DESCRIPTION, UNIT_COST FROM ITEM_T50 WHERE
UNIT_COST gt 4.50 AND UNIT_COST lt100
SQLgt SELECT DESCRIPTION, UNIT_COST 2 FROM
ITEM_T50 3 WHERE UNIT_COST gt 4.50 AND UNIT_COST
lt 100
9
PROJECT EXERSICE 4A
Q Select information from one table
NotePad Information SELECT EMP_NO,EMP_NAME FROM
EMPLOYEE_T50 ORDER BY EMP_NAME - Copy
and Paste into SQL.
10
PROJECT EXERCISE 4A ANSWER
A SQLgt SELECT EMP_NO,EMP_NAME 2 FROM
EMPLOYEE_T50 3 ORDER BY EMP_NAME EMP_NO
EMP_NAME --------- -------------------------
6543 Jacob Cook 8765 Jon Templeton 5549
Nicki Wooten 4492 Rachel Lee 1987
Richie Crawford
11
PROJECT EXERCISE 4B
Q Aggregate information from one attribute in a
table. NotePad information. SELECT
COUNT() FROM ITEM_T50 WHERE DESCRIPTION'Asprin'
-COPY AND PASTE INTO SQL.

12
PROJECT EXERCISE 4B ANSWER
SQLgt SELECT COUNT () 2 FROM ITEM_T50 3
WHERE DESCRIPTION'Asprin' COUNT() ---------
1
13
PROJECT EXERCISE 4C
Try the various functions such as MIN, MAX,
AVG. NotePad information SELECT
MIN(UNIT_COST) FROM ITEM_T50
SELECT MAX(UNIT_COST) FROM ITEM_T50
SELECT AVG(UNIT_COST) FROM ITEM_T50
14
PROJECT EXERCISE 4C ANSWER
SQLgt SELECT MIN(UNIT_COST) 2 FROM
ITEM_T50 MIN(UNIT_COST) --------------
1.5
15
PROJECT EXERCISE 4C ANSWER
SQLgt SELECT MAX(UNIT_COST) 2 FROM
ITEM_T50 MAX(UNIT_COST) --------------
87.5
16
PROJECT EXERCISE 4C ANSWER
SQLgt SELECT AVG(UNIT_COST) 2 FROM
ITEM_T50 AVG(UNIT_COST) --------------
18.316667
17
MELISSAS QUESTION

• How would MVCH find the names of the patients in
  beds 4, 5, and 6 And show the results?
• SELECT PATIENT_NAME, BED_NO
• FROM PATIENT_T50
• WHERE BED_NO gt '3' AND BED_NO lt '7'

18
LeKeiths Question
How could MVCH use the WHERE clause to retrieve
records from the patient in bed 4 only?
19
Richies Question
If Mountain View Community Hospital had a patient
wonder from his/her room, but does not know
his/her name. Knowing only the patient_no and
bed_no that was originally assigned to the
patient, how can they use a compound condition to
find the patients name.
20
Kurts Question
Use Aggregation Information as in Project
Exercise 4b to find if the treatment
Amputations can take place at MVCH.