Mountain View Community Hospital

1
Mountain View Community Hospital

• Team 9B
• Sarah Branscum
• Willena Brown
• David Castling
• Chapter 7

2
Introduction

• SQLgt oracle input commands will be used to query
  and sort data.
• Four of these commands will be used including the
  where command, sorting with multiple keys in
  decending order, and ranges.

3
Where commands

• Where commands restrict a query output
• The WHERE command retrieves records that only
  meet a set of conditions or restrictions

4
WHERE Example

• select patient_no, patient_name
• from patient_t800
• where patient_name'Vanessa Redgrave'

5
Sorting with Multiple Keys in a Descending Order

• This function involves two keys one of which is
  used to guide the sort.
• Major Key is the key that commands the most
  important column.
• Minor Key is the key that commands the column of
  less importance.
• The sort of course can occur in a descending or
  ascending order.

6
Sorting with Multiple Keys in Descending Order
Example

• select item_no,room_no,date_T,quantity
• from room_item_T800
• order by quantity desc,room_no

7
Ranges

• A range of data can be generated by using
  comparison operators and a couple keywords.
• They include
• Greater than sign gt
• Less than sign lt
• Keyword BETWEEN
• Keyword NOT BETWEEN

8
Keywords

• BETWEEN is used to find the data that falls
  between two given values.
• NOT BETWEEN is used to find the data that does
  not fall between two values.

9
Range Example

• select item_no,unit_cost
• from item_T800
• where unit_cost not between 2 and 10

10
Range Example

• select room_no, item_no, quantity
• from room_item_T800
• where quantity gt 2 and quantity lt 10000

11
Project Exercise 4a

• Generate a SQL command that will list information
  from only one of the tables
• Solution
• Select emp_no, emp_name from employee_T800
• order by emp_name

12
Project Exercise 4b

• Generate a SQL command that aggregates
  information from one attribute in a table
• Solution
• Select Count()
• From Item_T800
• Where Description Scalpel

13
Project Exercise 4c

• Generate a SQL command that uses various
  functions such as MIN, MAX, and AVG
• Solution
• Select Min(Unit_Cost)
• From item_T800

14
Sarahs Question

• If Mountain View Community Hospital wanted to
  Query the data of the items that cost GREATER
  than 10 per unit, how would the command be
  written?
• Solution

15
Willenas Question

• Can MVCH benefit by using single query tables? If
  so give some example on how they can use the
  query.

16
Davids Question

• Give an example of how MVCH could use the WHERE
  clause to retrieve records for a given data range
  of patients.
• Query
• SQLgtSELECT PATIENT_NAME, PATIENT_AGE
• FROM PATIENT_T50
• WHERE PATIENT_AGE lt 40