HAP 709 Healthcare Databases

1
HAP 709 Healthcare Databases

• SQL Data Manipulation Language (DML)
• Updated Fall, 2009

2
SQL Components
SQL
DCL
DDL
DML
Data I/O
RDBMS Structure
DBA Activities
Create Record
Create/Delete DBs
Create Users
Read Record
Delete Users
Create/Delete Tables
Grant privileges
Update Record
Alter Tables
Implement AccessSecurity
Delete Record
3
SQL manipulates entire column of data

• No need to repeat the commands for each record

4
SQL is non-procedural

• You do not need to tell the computer how to do
  the tasks. All you need to tell the computer is
  what you want to see and the computer will figure
  out how to produce the results you want to see

5
Typical Commands

• Details of commands are provided online. Here we
  review a select few commands
• Key words are reserved for command
  specifications. These words cannot be used as
  names for fields or tables.

6
Data Type Must be Specified

• Number
• Integer, Small integer, Big integer, Numeric data
  (with fraction), Decimal (with precision)
• String
• Character, Large character, National character
• Boolean
• Date/times
• Date, Time with and without time zone, Timestamp
  with and without time zone
• Intervals

7
Data Manipulation Commands

• Combined numeric values
• Calculate intervals among dates/times
• Process a series of Boolean statements
• Concatenate strings together

8
Logical Connectives

• Allows you to build complex predicates out of
  simple ones
• Set functions
• Count, Max, Min, Sum, Avg, Stdev
• Sub-queries

9
Using SQL with Microsoft Access

• Open database and select queries from objects
• Select create query in design view
• Add tables and close add tables button
• Choose SQL view
• Delete the select statement and enter commands
  you want
• When finished save and enter a name for the query

10
INSERT INTO Syntax

• INSERT INTO ltmyTablegt VALUES
• (ltField1gt ltDataTypegt, ltField2gt ltDataTypegt,)

11
INSERT INTO Syntax

• INSERT INTO ltmyTablegt VALUES
• (ltField1gt ltDataTypegt, ltField2gt ltDataTypegt,)

INSERT INTO PAT VALUES(983883,'JOHN','MARTINEZ')
12
INSERT INTO in MS Access
Note In MS Access the INSERT INTO is called an
Append Query
13
UPDATE Statement (1)

• UPDATE ltmyTablegt SET
• ltField1gt ltValue1gt
• WHERE condition

14
UPDATE Statement (1)

• UPDATE ltmyTablegt SET
• ltField1gt ltValue1gt
• WHERE condition

UPDATE PAT SET PAT_FNM 'JOHNNY' WHERE PAT_ID
983883
15
MS Access Example
16
Updating Multiple Records(1)
A new field needs to be populated after
modification of the original table structure
17
UPDATE Statement (2)

• UPDATE ltmyTablegt join SET
• ltField1gt ltValue1gt
• WHERE condition

18
UPDATE Statement (2)

• UPDATE ltmyTablegt join SET
• ltField1gt ltValue1gt
• WHERE condition

UPDATE PAT INNER JOIN TEMP ON PAT.PAT_ID
TEMP.PAT_ID SET PAT.PAT_TITLE TEMP.TITLE
19
Multiple Updates in MS Access(1)
20
Updating Multiple Records(2)
The medical procedure cost table needs to be
reflect a 12.5 increase
21
UPDATE Statement (3)

• UPDATE ltmyTablegt SET
• ltField1gt ltValue1gt
• WHERE condition

22
UPDATE Statement (3)

• UPDATE ltmyTablegt SET
• ltField1gt ltValue1gt
• WHERE condition

UPDATE MED_PROCEDURE SET COST 1.125 COST
23
Multiple Updates in MS Access(2)
24
Deleting a Record
DELETE FROM ltmyTablegt condition
25
Deleting a Record
DELETE FROM ltmyTablegt condition

• DELETE FROM PAT WHERE PAT_ID 983883

26
MS Access Example
27
Reading the Data the SELECT Statement

• SELECT fields FROM ltmyTablegt condition

28
Reading the Data the SELECT Statement

• SELECT fields FROM ltmyTablegt condition

SELECT PAT_LNM FROM PAT SELECT FROM
PAT SELECT MED_PROC_NM FROM MED_PROCEDURE WHERE
COST gt 20000
29
MS Access
30
MS Access
31
MS Access
32
Joins
Who is the primary physician for patient Mary
Lindfors?
33
Natural Join
Who is/are the primary physician(s) for patient
Mary Lindfors?
SELECT PAT_FNM, PAT_LNM, CLNCIAN_NM FROM PAT,
CLNCIAN WHERE PAT.PAT_ID CLNCIAN.PAT_ID AND
PAT_FNM 'MARY' AND PAT_LNM 'LINDFORS'
34
MS Access
35
Outer Joins RIGHT JOIN
SELECT PAT.PAT_FNM, PAT.PAT_LNM,
CLNCIAN.CLNCIAN_NM FROM CLNCIAN RIGHT JOIN PAT ON
CLNCIAN.PAT_ID PAT.PAT_ID
Shows all the records from PAT and those records
from CLNCIAN where the PAT_ID values are equal in
both tables
36
LEFT JOIN
SELECT PAT.PAT_FNM, PAT.PAT_LNM,
CLNCIAN.CLNCIAN_NM FROM CLNCIAN LEFT JOIN PAT ON
CLNCIAN.PAT_ID PAT.PAT_ID
Shows all the records from CLNCIAN and those
records from PAT where the PAT_ID values are
equal in both tables
37
Union Operator

• The tables must have the same number of columns
• Corresponding columns must all have identical
  data types and lengths
• Command syntax
• Select From ltFirst Table namegt
• Union
• Select From ltSecond Table namegt

38
Union of Two Tables
Recalled
Medication in Use
39
Union of Two Tables
Recalled
Medication in Use
40
Union of Two Tables
Recalled
Medication in Use
41
Intersect

• Only rows of data that appear in both source
  tables are selected
• Command Syntax
• Select From ltTable namegt
• Intersect Corresponding (ltFieldnamegt,
  ltFieldnamegt, )
• Select From ltSecond Table namegt

42
Except

• Return all rows that appear in first table but
  not in the second table
• Select From ltTable namegt
• Except Corresponding (ltFieldnamegt, ltFieldnamegt,
  )
• Select From ltSecond Table namegt

43
Take Home Lessons

• It is possible to write your own SQL for data
  manipulation