TEAM 9B Processing Single Table Queries for MVCH

1
TEAM 9BProcessing Single Table Queries for MVCH

• Richard Johnson
• Justin Terry
• Javier Castano

2
Use of WHERE clause with MVCH

• SELECT TREATMENT_NO
• FROM TREATMENT_T5050
• WHERE TREATMENT_NAME Amputation
• Copy and Paste into SQL

3
SQL Output

• SQLgt SELECT TREATMENT_NO
• 2 FROM TREATMENT_T5050
• 3 WHERE TREATMENT_NAME 'Amputation'
• TREATMENT_NO
• ---------------------
• 3456

4
Compound Condition Inputs for MVCH

• SELECT ROOM_NO
• FROM ROOM_ITEM_T5050
• WHERE ITEM_NO 01
• AND DATE_T 1-NOV-03
• Copy and Paste into SQL

5
SQL Output

• SQLgt SELECT ROOM_NO
• 2 FROM ROOM_ITEM_T5050
• 3 WHERE ITEM_NO '01'
• 4 AND DATE_T '1-NOV-03'
• ROOM_NO
• -------------
• 304

6
Example Continued

• SELECT PATIENT_NAME
• FROM PATIENT_T5050
• WHERE PHYSICIAN_ID 1237
• OR PATIENT_NO 1014
• Copy and Paste into SQL

7
SQL Output

• SQLgt SELECT PATIENT_NAME
• 2 FROM PATIENT_T5050
• 3 WHERE PHYSICIAN_ID '1236'
• 4 OR PATIENT_NO '1012'
• PATIENT_NAME
• --------------------
• Erik Taggert

8
Example Continued

• SELECT PHYSICIAN_NAME
• FROM PHYSICIAN_T5050
• WHERE NOT (PHYSICIAN_ID 1239)
• Copy and Paste into SQL

9
SQL Output

• SQLgt SELECT PHYSICIAN_NAME
• 2 FROM PHYSICIAN_T5050
• 3 WHERE NOT (PHYSICIAN_ID '1236')
• PHYSICIAN_NAME
• -------------------------
• Elinor Trim
• Lance Ketterman
• Allyson Kaya
• Alana Britten
• Sofia Days

10
Sorting in Multiple Keys in Descending Order for
MVCH

• SELECT PATIENT_NAME, PATIENT_NO, BED_NO
• FROM PATIENT_T5050
• ORDER BY PATIENT_NO DESC,
• PATIENT_NAME
• Copy and Paste into SQL

11
SQL Output

• SQLgt SELECT PATIENT_NAME, PATIENT_NO, BED_NO
• 2 FROM PATIENT_T5050
• 3 ORDER BY PATIENT_NO DESC, PATIENT_NAME
• PATIENT_NAME PATIENT_NO BED_NO
• --------------------
  ---------------- ----------
• Kelly Stolp
  1015 6
• Nelson Mance
  1014 5
• Tyrone Lall
  1013 4
• Erik Taggert
  1012 3
• Hillary Tripodi
  1011 2
• Ted Stutz
  1010 1
• 6 rows selected.

12
Example of a Range

• SELECT DESCRIPTION, UNIT_COST
• FROM ITEM_T5050
• WHERE UNIT_COST gt4.50 AND UNIT_COSTlt100
• Copy and Paste into SQL

13
SQL Output

• SQLgt SELECT DESCRIPTION, UNIT_COST
• 2 FROM ITEM_T5050
• 3 WHERE UNIT_COSTgt4.50 AND UNIT_COSTlt100
• DESCRIPTION UNIT_COST
• ------------------ ---------------
• Viagra 9.5
• Novacaine 87.5
• Midol 4.95

14
Project Exercise 4a

• Select information from one table.
• In notepad type
• SELECT PATIENT_NO, PATIENT_NAME
• FROM PATIENT_T5050
• ORDER BY PATIENT_NO
• Copy and Paste into SQL

15
Project Exercise 4a Answer

• SQLgt SELECT PATIENT_NO, PATIENT_NAME
• 2 FROM PATIENT_T5050
• 3 ORDER BY PATIENT_NO
• PATIENT_NO PATIENT_NAME
• ----------------- --------------------
• 1010 Ted Stutz
• 1011 Hillary Tripodi
• 1012 Erik Taggert
• 1013 Tyrone Lall
• 1014 Nelson Mance
• 1015 Kelly Stolp
• 6 rows selected.

16
Project Exercise 4b

• Aggregate information from one attribute in a
  table
• In notepad type
• SELECT COUNT()
• FROM ITEM_T5050
• WHERE DESCRIPTION Viagra
• Copy and paste into SQL

17
Project exercise 4b Answer

• SQLgt SELECT COUNT()
• 2 FROM ITEM_T5050
• 3 WHERE DESCRIPTION 'Viagra'
• COUNT()
• ------------
• 1

18
Project Exercise 4c

• Try out the various functions such as
• MIN
• In notepad type
• SELECT MIN (UNIT_COST)
• FROM IETM_T5050
• Copy and Paste into SQL

19
Project Exercise 4c Cont

• MAX
• In notepad type
• SELECT MAX (UNIT_COST)
• FROM ITEM_T5050
• Copy and Paste into SQL

20
Project Exercise 4c Cont

• AVG
• In notepad type
• SELECT AVG (UNIT_COST)
• FROM ITEM_T5050
• Copy and Paste into SQL

21
Project Exercise 4c Answer

• MIN
• SQLgt SELECT MIN (UNIT_COST)
• 2 FROM ITEM_T5050
• MIN(UNIT_COST)
• ----------------------
• 1.5

22
Project Exercise 4c Answer Cont

• MAX
• SQLgt SELECT MAX (UNIT_COST)
• 2 FROM ITEM_T5050
• MAX(UNIT_COST)
• ----------------------
• 87.5

23
Project Exercise 4c Answer Cont

• AVG
• SQLgt SELECT AVG (UNIT_COST)
• 2 FROM ITEM_T5050
• AVG(UNIT_COST)
• ---------------------
• 18.3166667

24
Students Questions
25
Richards Question

• How would MVCH find the names of the
  patients that were assigned to beds numbered one
  through four.

26
Justins Question

• How could MVCH use the WHERE clause to retrieve
  records from one patient only?

27
Javiers Question

• Suppose that MVCH has a patient that die and
  they need to contact the family of the patient,
  but they cannot find the name.
• Having only the patient number and the bed
  number. How can they find the patients name?

28
The End