TEAM 9B SQLORACLE INPUT COMMANDS Pratt Ch. 3

1
TEAM 9B SQLORACLE INPUT
COMMANDS Pratt Ch. 3

• Amber Hallmark
• Josh Kissee
• Josh Melton
• Josh Michaelis
• Darryl Spinks
• Mitch Trotter

2
Pratt Ch. 3 Overview

• The focus of Pratt Ch.3 is the SQL SELECT command
  used for data retrieval.
  
• A wide variety of questions related to query
  relative data can be answered by using the SELECT
  CLAUSE, FROM CLAUSE, WHERE CLAUSE.
  
• No special formatting rules will apply.

3
SQLORACLE INPUT COMMANDS

• Use of the Where Clause-restricts a query
  output.
  
• Compound Conditions-connects two or more simple
  conditions using operatorsAND, OR, NOT.

• Sorting with Multiple Keys in Descending
  Order-sorts multiple keys with an order clause.
  
• Ranges-comparison operators are used to
  establish a range.
  

4
Use of the WHERE Command

• The WHERE clause is used to retrieve records that
  satisfy some condition. Inserted after the
  SELECT-FROM combination, the WHERE clause is
  followed by any conditions (restrictions) that
  apply to the data that will need to be
  retrieved.

5
WHERE Example 1Patient No

• SQLSELECT PATIENT_NO,TREATMENT_TIME
• 2 FROM PERFORMS_T500
• 3 WHERE PATIENT_NO1013
• PATIENT_NO TREATMENT_TIME
• ------------------------------------
• 1013 745

6
WHERE Example 2Patient Name

• SQL SELECT PATIENT_NO, PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE PATIENT_NAME'Nelly Jones'
• PATIENT_NO PATIENT_NAME
• ---------- ---------------------------
• 1014 Nelly Jones

7
WHERE Example 3Viagra

• SQL SELECT ITEM_NO, DESCRIPTION, UNIT_COST
• 2 FROM ITEM_T500
• 3 WHERE DESCRIPTION'Viagra'
• ITEM_NO DESCRIPTION UNIT_COST
• ------- ----------- ---------
• 1 Viagra 9.5

8
COMPOUND CONDITIONSOverview

• Compound Conditions are the next advancement from
  simple conditions. We will use operators such as
  AND, OR, NOT. These operators will connect two
  simple conditions. For each operators making a
  connection, the inverse is either true/false
  depending upon the operator used.

9
Compound Conditions

• Compound Conditions are formed by connecting two
  or more simple conditions using the AND, OR, and
  NOT operators.
  
•  
• AND - When the AND operator connects simple
  conditions, all the simple conditions must be
  true in order for the compound condition to be
  true.
•  
• OR - When the OR operator connects simple
  conditions, the compound condition will be true
  whenever any one simple of the simple conditions
  are true.
•  
• NOT - When the NOT operator is used the preceding
  condition reverses the truth of the original
  condition.
  
•  

10
COMPOUND CONDITIONS

• SQL SELECT DESCRIPTION
• 2 FROM ITEM_T500
• 3 WHERE UNIT_COST'87.50'
• 4 AND ITEM_NO04
• DESCRIPTION
• -------------------------
• Novacaine

11
COMPOUND CONDITIONS

• SQL SELECT PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE BED_NO'006'
• 4 OR PATIENT_NO'1015'
• PATIENT_NAME
• -------------------------
• Ozzy Osbourn

12
COMPOUND CONDITIONS

• SQL SELECT PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE NOT (BED_NO'0001')
• PATIENT_NAME
• -------------------------
• Garret Jackson
• M.C. Hammer
• Shonna Lanier
• Nelly Jones
• Ozzy Osbourn

13
Sorting with Multiple Keys in Descending Order

• This input involves using two keys, where one key
  is used to sort.
  
• Major Key-most important column
• Minor Key-less importance, used in cases of data
  duplication
  
• Can sort with descending or ascending order.

14
S.M.K.D.OOrder by Item Number

• SQL SELECT ITEM_NO,PATIENT_NO,DATE_T,QUANTITY
• 2 FROM CONSUMES_T500
• 3 ORDER BY ITEM_NO DESC,PATIENT_NO
•  
• ITEM_NO PATIENT_NO DATE_T QUANTITY
• --------- ---------- --------- ---------
• 6 1012 06-JAN-01 7
• 5 1010 05-JAN-01 6
• 4 1015 04-JAN-01 5
• 3 1013 03-JAN-01 4
• 2 1015 02-JAN-01 3
• 1 1010 01-JAN-01 2

15
S.M.K.D.O.Order by Quantity

• SQL SELECT ITEM_NO,ROOM_NO,DATE_T,QUANTITY
• 2 FROM ROOM_ITEM_T500
• 3 ORDER BY QUANTITY DESC,ROOM_NO
•  
• ITEM_NO ROOM_NO DATE_T QUANTITY
• --------- --------- --------- ---------
• 6 501 06-JAN-01 7
• 5 103 05-JAN-01 6
• 4 102 04-JAN-01 5
• 3 402 03-JAN-01 4
• 2 205 02-JAN-01 3
• 1 304 01-JAN-01 2
•  

16
S.M.K.D.O.Duplicate Major Key

• SQL SELECT WARD_NO,EMP_NO,HOURS
• 2 FROM ASSIGNED_T500
• 3 ORDER BY HOURS DESC,EMP_NO
•  WARD_NO EMP_NO HOURS
• ------------------------- --------- ---------
• 106 1812 8
• 105 1987 8
• 101 4492 8
• 102 5549 8
• 103 6543 8

17
RangesOverview

• A range of values can be established by using the
  and
• The keyword BETWEEN can be used to find instances
  that fall between two values.
  
• The keyword NOT BETWEEN can be used to find the
  instances that do not fall between two values.

18
RangesFinding Ranges using and

SQL SELECT DESCRIPTION, UNIT_COST

2 FROM ITEM_T500

3 WHERE UNIT_COST 2 AND UNIT_COST

DESCRIPTION UNIT_COST

-------------------- ----------------------

Viagra 9.5

Valtrex 2.5

Midol 4.95

Ben Gay 3.95

19
RangesUsing NOT BETWEEN

• Query Which item numbers had a unit cost that
  was not between 3 and 20 in the ITEM view?
  
• Input
• SQL SELECT ITEM_NO, UNIT_COST
• 2 FROM ITEM_T500
• 3 WHERE UNIT_COST NOT BETWEEN 3 AND 20

20
Ranges Output

• ITEM_NO UNIT_COST
• --------------- -----------------
• 2 2.5
• 3 1.5
• 4 87.5

21
Ranges Example

• Query Which room numbers used a quantity
  between 2 and 6 of each item number in the
  ROOM_ITEM view?
  
• Input
• SQL SELECT ROOM_NO, QUANTITY, ITEM_NO
• 2 FROM ROOM_ITEM_T500
• 3 WHERE QUANTITY 2 AND QUANTITY

22
Ranges Output

• ROOM_NO QUANTITY ITEM_NO
• ---------- -------- ---------------
• 205 3 2
• 402 4 3
• 102 5 4

23
TEAM 9BProject Exercise 4A-4CTake-Home Final
Exam Ques.

• Amber Hallmark
• Josh Kissee
• Josh Melton
• Josh Michaelis
• Darryl Spinks
• Mitch Trotter

24
Project Exercise 4ATesting Queries

• Project Exercise 4A question
• Select information from only one of the tables
  using sample data collected in the MVCH
  database.
  

25
PE 4A

• SQL SELECT PATIENT_NAME,PATIENT_NO
• 2 FROM PATIENT_T500
• PATIENT_NAME PATIENT_NO
• ------------------------- ----------
• Kylynne Edwards 1010
• Garret Jackson 1011
• M.C. Hammer 1012
• Shonna Lanier 1013
• Nelly Jones 1014
• Ozzy Osbourn 1015
• 6 rows selected.

26
PE 4A

• SQL SELECT PATIENT_NO,BED_NO
• 2 FROM PATIENT_T500
• PATIENT_NO BED_NO
• ---------- ---------
• 1010 1
• 1011 2
• 1012 3
• 1013 4
• 1014 5
• 1015 6
• 6 rows selected.

27
Project Exercise 4BAggregate Information
Overview

• Functions such as COUNT in a column list of a
  SELECT command may be used to specify that the
  resulting answer table is to contain aggregated
  data instead of row-level data.
• Using the COUNT aggregate function will give a
  one-row answer.
  
• The COUNT() function will include all rows
  selected by a query regardless of whether any of
  the rows contain null values.

28
Project Exercise 4BAnswer

• SQL SELECT COUNT ()
• 2 FROM PATIENT_T500
• 3 WHERE PHYSICIAN_ID '1234'
• COUNT()
• ---------
• 2

29
Project Exercise 4BAnswer (cont.)

• SQL SELECT COUNT ()
• 2 FROM ASSIGNED_T500
• 3 WHERE HOURS '8'
• COUNT()
• ---------
• 6

30
Project Exercise 4CTesting Queries

• Project Exercise 4C question
• Try out the various functions such as MIN, MAX,
  and AVG.
  
• We will use the sample data from MVCH to form the
  outputs.

31
PE 4CQUANTITY-Room_Item

• SQL SELECT MIN(QUANTITY)
• 2 FROM ROOM_ITEM_T500
• MIN(QUANTITY)
• -------------
• 2
• SQL SELECT MAX(QUANTITY)
• 2 FROM ROOM_ITEM_T500
• MAX(QUANTITY)
• -------------
• 7

32
PE 4CQUANTITY-Room_Item

• SQL SELECT AVG(QUANTITY)
• 2 FROM ROOM_ITEM_T500
• AVG(QUANTITY)
• -------------
• 4.5

33
PE 4CQUANTITY-Consumes

• SQL SELECT MIN(QUANTITY)
• 2 FROM CONSUMES_T500
• MIN(QUANTITY)
• -------------
• 2
• SQL SELECT MAX(QUANTITY)
• 2 FROM CONSUMES_T500
• MAX(QUANTITY)
• -------------
• 7

34
PE4CQUANTITY-(Cont.)

• SQL SELECT AVG(QUANTITY)
• 2 FROM CONSUMES_T500
• AVG(QUANTITY)
• -------------
• 4.5
• When executing a query based on the quantity from
  the table
  
• CONSUMES, we can instantly determine range.
• This function is excellent in determining cost
  for each patient.
  
• It is also useful for understanding patient
  preferences.

35
Ambers Take-Home Final Question

• Using the COUNT () function, determine how many
  services were performed on April 4th, 2001 at
  MVCH.

36
1-Josh Meltons Take-Home Final Question

• Determine which employees worked between 4 hours
  and 12 hours at MVCH.
  

37
2-Josh Kissee Student Question

• Proposed MVCH would like to keep track of who
  their physicians treat. However, physicians treat
  multiple patients.
  
• Question How could MVCH show a physicians
  administered treatments, while separating the
  patients that are treated by them?
  

38
MitchsTake-Home Final Question

• Question Suppose that MVCH needs to know the
  Patient name and number for the patients in all
  the beds EXCEPT for bed numbers 0001 and 0005.
  What command would be used to display this
  information?

39
3 Josh Michaeliss Question

• Question -Suppose a nurse with MVCH needs the
  name of a drug, but all she knows is the
  unit_cost and the item_no. So how will she find
  the description?