Team 9b SQL ORACLE INPUT COMMANDS Pratt chapter 3

1
Team 9bSQL ORACLE INPUTCOMMANDSPratt chapter 3

• Shontay Scruggs
• Sheena Clark
• Sean Corder
• Rachel Hagan

2
Chapter 3 Overview

• The focus of Pratt Chapter 3 is the SQL SELECT
  Command used for data retrieval.
• SELECT CLAUSE, FROM CLAUSE, WHERE CLAUSE can be
  used to answer a wide variety of questions
  related to query relative data.
• No special formatting rule will apply

3
SQL ORACLE INPUT COMMANDS

• Use of the Where Clause restricts a query
  output.
• Sorting with Multiple Keys in Descending
  Order-sorts multiple keys with an order clause.
• Ranges-comparison operators are used to
  establish a range.
• Compound Conditions-connects two or more simple
  conditions using operators AND,OR,NOT.

4
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
Example 1 WHEREPatient No

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

6
Example 2 WHEREPatient Name

• SQLSELECT PATIENT_NO, PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE PATIENT_NAME Nelly Jones
• PATIENT_NO PATIENT_NAME
• --------------------------------------------------
  ---
• 1014 Nelly Jones

7
EXAMPLE 3 WHEREViagra

• SQLSELECET ITEM_NO, DESCRIPTION, UNIT_COST
• 2 FROM ITEM_T500
• 3 WHERE DESCRIPTIONVIAGRA
• 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 two or more
  simple conditions using the AND, OR, and NOT
  operators
• AND When the AND operator connects simple
  conditions, all the simple conditions to be true.
• OR - When the OR operator connects simple
  conditions any one simple of the simple
  conditions are true.
• NOT When the NOT operator is used the preceding
  conditions reverses the truth of the original
  conditions.

10
COMPOUND CONDITIONS

• SQL SELECT DESCRIPTION
• 2 FROM ITEM_T500
• 3 WHERE UNIT_COST87.50
• 4 AND ITEM_NO04
• DESCRIPTION
• --------------------
• Novocain

11
COMPOUND CONDITONS

• SQL SELECT PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE BED_NO006
• 4 OR PATIENT_NO1015
• PATIENT_NAME
• ---------------------
• Ozzy Osbourn

12
COMPOUND CONDITONS

• SQL SELECT PATIENT_NAME
• 2 FROM PATIENT_T500
• 3 WHERE NOT (BED_NO0001)
• 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.O Order 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, PATIENT_NO, DATE_T,
  QUANTITY
• 2 FROM CONSUMES_T5OO
• 3 ORDER BY ITEM_NO DESC, PATIENT_NO
• ITEM_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

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
Ranges overview

• 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
Ranges using 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
Project Exercise 4ATesting Queries

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

24
PE 4

• 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 Osbourne 1015
• 6 rows selected

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

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

27
Project Exercise 4bAnswer

• SQL SELECT COUNT()
• 2 FROM PATIENT_T654
• WHERE PHYSICIAN _ID1234
• COUNT()
• -----------
• 2

28
Project Exercise 4bAnswer (cont.)

• SQLSELECT COUNT()
• 2 FROM ASSIGNED_T654
• 3 WHERE HOURS 8
• COUNT()
• -----------
• 6

29
Project Exercise 4cTesting Queries

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

30
PE 4cQUANTITY-ROOM_ITEM

• SQLSELECT MIN (QUANTITY)
• 2 FROM ROOM_ITEM_T654
• MIN(QUANTITY)
• -----------
• 2
• SQLSELECT MAX(QUANTITY)
• 2 FROM ROOM_ITEM_T654
• MAX(QUANTITY)
• -----------
• 7

31
PE 4CQUANTITY-ROOM_ITEM

• SQLSELECT AVG(QUANTITY)
• 2 FROM ROOM_ITEM_T654
• AVG(QUANTITY)
• -----------
• 4.5

32
PE 4CQUANTITY-Consumes

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

33
PE 4CQUANTITY-(CONT.)

• SQLSELECT AVG(QUANTITY)
• 2 FROM CONSUMES_T654
• 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 range.
• It is also useful for understanding patient
  preferences.

34
Shontays Question

• By using the COUNT() function, determine how
  many services were performed on April 21, 2003 at
  MVCH.

35
Sheenas Question

• To determine which employees work more than 4
  hours, but less than 12 hours at MVCH, there is a
  certain keyword that you must use to perform a
  query that falls between two values.

36
Seans Question

• Which commands could determine what patients were
  not assigned to bed numbered 0001?

37
Rachels Question

• If MVCH needs to find a particular patient that
  is assigned to the bed '006' OR has a patient
  number of '1015' or both, what query would they
  use?