SQL

1
SQL
Chapter 9
An Introduction to Structured Query Language (SQL)
(With Considerable Modifications)
2
SQL
Why SQL??
? De facto standard in RDBMS
? Can be implemented on all platforms
? Processes data on a SET Level
? Works with groups of data (Tables)
? Relatively Few Statements
? Portability
? Ease of Learning
? Ease of use
? Developed around concept of
? Data Definition Language (DDL)
? Data Manipulation Language (DML)
? Data Control Language (DCL)
3
SQL
Database Definition
? Recall our Physician Table
Physician Table Attributes/Fields
? We know the fields we need, and perhaps even
what field names we will use, BUT what type of
data will we use to store the information ???
What Types of data are there ???
4
SQL
Database Definition
Basic Data Types (C Data Types used for
illustration)
Character
char/signed
1-byte
-128 to 127
(Maybe)
char/signed
(OR)
2-byte
-32,768 to 32,767
unsigned char
(Maybe)
1-byte
0 to 255
(OR)
unsigned char
2-bytes
0 to 65,537
Integer
int/signed
2-bytes
-32,768 to 32,767
(Maybe)
(OR)
int/signed
4-bytes
-2,147,483,648 to 2,147,483,647
unsigned int
2-bytes
0 to 65,537
(Maybe)
(OR)
unsigned int
4-bytes
0 to 4,294,967,295
long/signed
4-bytes
-2,147,483,648 to 2,147,483,647
(OR)
long/signed
8-bytes
-9,223,372,036,854,780,000 to .
unsigned long
4-bytes
0 to 4,294,967,295
(OR)
unsigned long
4-bytes
0 to 18,467,440,737,906,000,000
float
4-bytes
7 decs. precision
Real
double
8-bytes
10 decs. precision
long double
16-bytes
10 decs. precision
5
SQL
Database Definition
Of course, there are additional abstract data
structures
Arrays
A fixed number of contiguous data elements all of
the same type
Strings
An array of data type char
Structs
A combination of two or more data types

• Structured data objects

• Records

Others

• Date/Time

• Graphics

• OLE (Object Linking and Embedding)

• Hyperlinks

6
SQL
Database Definition
Oracle Allows for the following Basic data types
to be entered into Fields (There are others)
Characters
CHAR

• A string

• Up to 255 characters are allowed

Fieldname CHAR(N)

• Usage

• The length (N) MUST be specified

• If less than N characters are entered, N spaces
  will STILL be allocated

7
SQL
Database Definition
Characters
VARCHAR

• A string

• Up to 255 characters are allowed

fieldname VARCHAR(N)

• Usage

• The length (N) MUST be specified

• ONLY the number characters are entered will be
  allocated space

VARCHAR2, LONG VARCHAR
8
SQL
Database Definition
Numeric Data Types
INTEGER
Typically, up to 10 - 11 digits are allowed

• Range (Typically)

-2,147,483,648 through 2,147,483,647

• Usage

fieldname INTEGER
SMALLINT
Typically, up to 4 - 5 digits are allowed

• Range (Typically)

-32,768 through 32,767

• Usage

fieldname SMALLINT
9
SQL
Database Definition
Numeric Data Types
DECIMAL(m,n)
A Real (Floating-Point) Number with m total
digits (INCLUDING the sign and decimal point) and
n decimals to the right of the decimal point

• m may (Typically) be as large as 19

• n may (Typically) be as large as 7 - 8

• Usage

fieldname DECIMAL(10,4)
This allows for a number between
99999.9999 through -9999.999
(The sign counts)
But ONLY to 3 decimal point of precision
10
SQL
Database Definition
Date and Time Data Types
DATE
Date Value

• (Typically) Displayed as mm/dd/yy

• Usage

fieldname DATE
TIME
Time Value

• (Typically) Displayed as hhmmss

• Useful in Time Stamping

• Usage

fieldname TIME
11
SQL
Database Definition
? NOW, Returning to our Physician Table
Lets assume the following Field Names and Data
types
Physician Table Attributes/Fields
physid CHAR(9)
physname CHAR(30)
specialty CHAR(15)
street CHAR(20)
city CHAR(20)
state CHAR(2)
zip CHAR(5)
12
SQL
Database Definition
? Before going on to SQL, Lets see how other
languages might create the record
In COBOL
01 PHYSICIAN 05 PHYSID PIC X(9). 05
PHYSNAME PIC X(30). 05 SPECIALTY PIC
X(15). 05 STREET PIC X(20). 05
CITY PIC X(20). 05 STATE PIC
X(2). 05 ZIP PIC X(5).
13
SQL
Database Definition
In Pascal
In C
struct physician char physid10 char
physname31 char specialty16 char
street21 char city21 char
state3 char zip6
type physician record physid array1..9 of
char physname array1..30 of
char specialty array1..15 of
char street array1..20 of char city
array1..20 of char state array1..2 of
char zip array1..5 of char end
14
SQL
Database Definition
? The SQL Commands Needed to create the table are
Physician Table Attributes/Fields
CREATE TABLE physician ( physid CHAR(9),
physname CHAR(30), specialty CHAR(15),
street CHAR(20), city CHAR(20),
state CHAR(2), zip CHAR(5) )
Every SQL Command ends with a semicolon
15
SQL
Database Definition
? Lets Examine the command

• We have CREATEd a new table (file) called
  physician

CREATE TABLE physician ( physid CHAR(9),
physname CHAR(30), specialty CHAR(15),
street CHAR(20), city CHAR(20),
state CHAR(2), zip CHAR(5) )

• Each record we enter will contain 7 fields

• Each record will require 9 30
  15 20 20 2 5 101 Bytes of Storage

• We encased our field declarations between
  parentheses ()

• Each field (except for the last) was separated by
  a comma ( , )

16
SQL
Database Definition
But dont we know more about the table??
? Yes, We Know

• The Table physician has as its primary key the
  field physid

• It wouldnt make sense to leave the physname
  field blank (although theoretically, we could)

? We Could also make some assumptions

• The physician lives in Texas (Code TX)

17
SQL
Database Definition
? Lets rewrite the command
Physician Table Attributes/Fields
CREATE TABLE physician ( physid CHAR(9) NOT
NULL,
physname CHAR(30) NOT NULL,
Adding these Qualifiers will assure that the
fields will NOT be left empty
This is Superfluous. WHY ???
18
SQL
Database Definition
? Lets rewrite the command
Physician Table Attributes/Fields
CREATE TABLE physician ( physid CHAR(9) NOT
NULL,
physname CHAR(30) NOT NULL,
specialty CHAR(15), street CHAR(20),
city CHAR(20), state CHAR(2), DEFAULT TX,
If nothing is entered into the state field, TX
will be entered by default
19
SQL
Database Definition
? Lets rewrite the command
Physician Table Attributes/Fields
CREATE TABLE physician ( physid CHAR(9) NOT
NULL,
physname CHAR(30) NOT NULL,
specialty CHAR(15), street CHAR(20),
city CHAR(20), state CHAR(2), DEFAULT TX,
zip CHAR(5), PRIMARY KEY (physid) )
physid has been identified as the primary key
20
SQL
Database Definition
? We Can get also add additional constraints

• Lets assume that we can only have one (1)
  specialist in each area

CREATE TABLE physician ( physid CHAR(9) NOT
NULL,
physname CHAR(30) NOT NULL, specialty CHAR(15)
NOT NULL, street CHAR(20), city CHAR(20),
state CHAR(2), DEFAULT TX, zip CHAR(5),
PRIMARY KEY (physid) UNIQUE (specialty) )

21
SQL
Database Definition
? We need to Back-Track

• Remember that we previously discussed Schemas

? The Network Schema
? The conceptual Organization of the entire
database
? The Network Subschema
? The conceptual Organization of the database as
seen by the applications programs accessing it
? We need to Develop our tables with this in mind
22
SQL
Database Definition
? A Schema consists of

• Tables (which we previously created)

• Views (Or Virtual Table)

? A subset of rows and columns
? Not a physical entity (as are tables)
? Can be treated as tables
? Created by the DBMS each time they are
referenced by the user as a query

• User Authorization

? Usernames allowed (and how allowed) to access
the tables
23
SQL
Database Definition
? A more appropriate CREATE might have been
CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE
physician ( physid CHAR(9),
physname CHAR(30) NOT NULL,
specialty CHAR(15) NOT NULL,
street CHAR(20), city CHAR(20),
state CHAR(2), DEFAULT TX, zip CHAR(5),
PRIMARY KEY (physid)
UNIQUE (specialty) )
24
SQL
Database Definition
? Some Basic CREATE TABLE Guidelines

• Choose Numeric data types ONLY if calculations
  are to be performed on the field

• Make the lengths of the character columns long
  enough to accommodate future values (Remember the
  Y2K Problem)

• Apply the same guidelines for DECIMAL data

• Dont automatically choose VARCHAR over CHAR

• Use EXACTLY the same data types for columns which
  will be compared often or used in calculations
  together

• Use NOT NULL when a column MUST contain a value

• PRIMARY KEYS ARE ALWAYS NOT NULL

25
SQL
Database Definition
? Lets Consider the relationship between our
Physician and Patient tables
Physician
? Remember what this means

• A Patient MUST have one (and ONLY one) Physician

• A Physician MAY have more than 1 (one) Patient

26
SQL
Database Definition
? We Previously defined our Patient fields
? And how Patient relates to our Physician table
Patient Table Attributes/Fields
Physician Table Attributes/Fields
physid has in Patient is a foreign key
27
SQL
Database Definition
? We now need to define our Patient fields
Patient Table Attributes/Fields
CREATE TABLE patient ( patid CHAR(9) NOT NULL,
name CHAR(30) NOT
NULL,
physid CHAR(9) NOT NULL, PRIMARY
KEY (patid) FOREIGN KEY (physid)
REFERENCES physician (physid))
Lets Pretend that field Address doesnt Exist
We have established our relationship between
patient and physician
28
SQL
Database Definition
? Again, Lets Examine the command

• We have CREATEd a new table (file) called patient

• Each record we enter will contain 3 fields

• Each record will require 9 30
  9 48 Bytes of Storage

• We have linked two (2) tables together

• We encased our field declarations between
  parentheses ()

• Each field (except for the last) was separated by
  a comma ( , )

29
SQL
Database Definition
? We are on the way to building a Script
(Program)

• A text file that contains SQL commands

? So far, our Script would appear as
CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE
physician ( physid CHAR(9) NOT NULL,
physname CHAR(30) NOT NULL,
specialty CHAR(15) NOT NULL,
street CHAR(20), city CHAR(20),
state CHAR(2), DEFAULT TX, zip CHAR(5),
PRIMARY KEY (physid) UNIQUE (specialty)
)
CREATE TABLE patient ( patid CHAR(9) NOT NULL,
name CHAR(30) NOT
NULL,
physid CHAR(9) NOT NULL, PRIMARY
KEY (patid), FOREIGN KEY (physid)
REFERENCES physician (physid) )
30
SQL
Database Definition
? Finally, Lets create two more tables
Patient
? Remember, this is a Many-to-Many Relationship
31
SQL
Database Definition
? First, lets create our illness table
CREATE TABLE illness ( illcode CHAR(10),
name CHAR(20) NOT NULL,
PRIMARY KEY (illcode) UNIQUE (name) )
Lets Pretend that field Others doesnt Exist
32
SQL
Database Definition
? We now need to define our Suffers fields
CREATE TABLE suffers ( patid CHAR(9) NOT NULL,
illcode CHAR(10) NOT
NULL,
sdate DATE NOT NULL, stime TIME NOT NULL,
PRIMARY KEY (patid, illcode), FOREIGN KEY
patid REFERENCES patient (patid),
FOREIGN KEY illcode REFERENCES illness
(illcode) )
Lets Pretend that field Others doesnt Exist
33
SQL
Database Definition
? Our Script would appear as
CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE
physician ( physid CHAR(9) NOT NULL,
physname CHAR(30) NOT NULL,
specialty CHAR(15) NOT NULL,
street CHAR(20), city CHAR(20),
state CHAR(2), DEFAULT TX, zip CHAR(5),
PRIMARY KEY (physid) UNIQUE (specialty)
)
CREATE TABLE illness ( illcode CHAR(10) NOT
NULL,
name CHAR(20) NOT NULL,
PRIMARY KEY (illcode) UNIQUE (name) )
CREATE TABLE suffers ( patid CHAR(9),
illcode CHAR(10) NOT NULL,
sdate DATE NOT NULL, stime TIME NOT NULL,
PRIMARY KEY (patid, illcode), FOREIGN KEY
(patid) REFERENCES patient (patid),
FOREIGN KEY (illcode) REFERENCES
illness (illcode) )
CREATE TABLE patient ( patid CHAR(9) NOT NULL,
name CHAR(30) NOT
NULL,
physid CHAR(9) NOT NULL, PRIMARY
KEY (patid), FOREIGN KEY (physid)
REFERENCES physician (physid) )
34
SQL
Database Definition
? Additional options in CREATE
? CHECK

• Suppose ALL of our physicians could ONLY live in
  Arlington, Dallas, or Fort Worth

• Our physician table could be rewritten as

CREATE TABLE physician ( physid CHAR(9) ,
physname CHAR(30) NOT NULL,
specialty CHAR(15), street CHAR(20),
city CHAR(20), state CHAR(2), DEFAULT TX,
zip CHAR(5), PRIMARY KEY (physid),
CHECK (city in Arlington, Dallas, Ft.
Worth) )
35
SQL
Database Definition
? Additional CHECK options

• If we had an integer field called salary, and
  salary HAD to be greater than 50000

CREATE SCHEMA ( ººººº CHECK (salary gt 50000) )

• If we had an integer field called salary, and
  salary HAD to be greater than 50000 BUT less than
  100000

CREATE SCHEMA ( ººººº CHECK (salary BETWEEN
50000 AND 100000) )
36
SQL
Database Definition
? Security/Privilege Options

• The owner (AUTHORIZATION pkirs) has full access

• If we wished to allow user clinton (which MUST be
  a registered username) full access to table
  physician

CREATE SCHEMA ATHORIZATION pkirs CREATE TABLE
physician ( ººººº ) GRANT ALL ON physician TO
clinton
37
SQL
Database Definition

• If we wished to allow user clinton to insert new
  records into to table physician

GRANT INSERT ON physicianTO clinton

• If we wished to allow user clinton ONLY to edit
  tables physician and patient

GRANT UPDATE ON physician, patient TO clinton

• If we allow user clinton ONLY to view physician
  and patient data

GRANT SELECT ON physician, patient TO clinton

• If we allow everyone to view and update table
  physician

GRANT SELECT, UPDATE ON physician TO PUBLIC
38
SQL
Database Modification
? Inserting records

INSERT INTO physician VALUES
(123456789, Smith, John, Surgery,
123Main, El Paso, TX, 76019)

• Note that this corresponds to our physician table
  structure

( physid CHAR(9), physname CHAR(30),
specialty CHAR(15), street CHAR(20),
city CHAR(20), state CHAR(2), zip CHAR(5)
)
39
SQL
Database Modification
? Saving records

• Any new records added (or changes made to
  records) will not be permanent until we enter the
  command

COMMIT
? Changing our minds

• If we decide we shouldnt have entered the record
  (or made the changes), we can enter the command

ROLLBACK

• Which will restore the database back to how it
  was before the last COMMIT was entered

40
SQL
Database Modification
? Updating Records

• Right now, our physician table appears as

• If we wished to change John Smiths specialty
  from surgery to OB/GYN we would enter the command

UPDATE physician SET specialty
OB/GYN WHERE physid 123456789

• NOTE The changes would be applied only AFTER we
  COMMIT

41
SQL
Database Modification
? Updating Records

• Notice also that the command

UPDATE physician SET specialty
OB/GYN WHERE physid 123456789

• Requires us to find for a specific record using
  the search condition

WHERE physid 123456789
(a TRUE/FALSE condition)
42
SQL
Database Modification
? Deleting Records

• To delete a record from a database, we must once
  again find it and issue the command

DELETE FROM physician WHERE physid
123456789

• Once Again, The changes would be applied only
  AFTER we COMMIT

43
SQL
Database Modification
? Deleting Records

• We could also delete multiple records

DELETE FROM patient WHERE physid NOT IN
(123456789, 234567890)

• This would delete all records who did not have
  these two physicians as their primary providers

(We can get much fancier here)
44
SQL
Database Modification
? Deleting Tables

• To delete a table from a database, we need only
  issue the command

DROP TABLE physician

• A DROP DOES NOT require an explicit COMMIT
  statement (although a ROLLBACK will undo the
  command)

45
SQL
Database Structure Modification
? Altering the database (FOR MOST RDBMS)

• Unrestricted Alterations

• Adding a new column to a table

• Deleting a primary key

(Foreign Key references are automatically removed)

• Deleting a foreign key

• Restricted Alterations

• Changing a columns data type, size, and default
  value is allowed ONLY if there are NO DATA in the
  column being modified

46
SQL
Database Structure Modification
? Altering the database (FOR MOST RDBMS)
(Continued)

• Restricted Alterations

• Adding a Primary Key, BUT only if there are no
  duplicate values

• Adding UNIQUE and CHECK condition ONLY if the
  field matches the added condition

• Adding a foreign key allowed ONLY if the values
  are NOT NULL or exist in the referenced Table

• Changing a column name is Not Allowed

• Deleting a column is NOT allowed

47
SQL
Database Structure Modification
? Altering the database

• To add a field to a database

ALTER TABLE physician ADD (malpracticefees
DECIMAL(7,2))

• We can also specify WHERE to add it and add
  constraints

ALTER TABLE physician ADD (malpracticefees
DECIMAL(8,2)) BEFORE street INIT 10000.00
48
SQL
Database Structure Modification
? Altering the database

• Suppose that we find out that we need more space
  for our specialty field, presently

specialty CHAR(15),

• We might enter the command

ALTER TABLE physician, MODIFY (specialty
CHAR(20))

• which would accept up to 20 characters for a
  physician specialty

49
SQL
Database Structure Modification
? Altering the database

• If, for some reason, we decided to change physid
  from a character to a numeric field, we might
  enter the command

ALTER TABLE physician MODIFY (physid INTEGER)

• NOTE

This assumes that the field CAN be converted into
a numeric Field
4B7876D

• If physid contained the data

It could not be converted
50
SQL
Database Structure Modification
? Altering the database

• To remove a primary or foreign key, we might
  issue the command

DROP CONSTRAINT .

• NOTE If this sounds like a cop-out, it is
  because there are a number of variations

51
SQL
Database Structure Modification
? Altering the database

• IF we have designated a primary of foreign key
  (or we have removed them as above), we could now
  add them as

ALTER TABLE physician ADD PRIMARY KEY
(physname)

• AND

ALTER TABLE patient ADD FOREIGN KEY
(physname) REFERENCES (physician)
52
SQL
Importing Data from another Database
? NOTE

• Data imported from other tables MUST be of the
  same data type

• IF we had a table physinfo which was IDENTICAL to
  our table physician, we could enter the command

INSERT INTO physician SELECT FROM
physinfo
53
SQL
Importing Data from another Database

• IF the table physinfo had some fields which
  matched our table physician (and some which
  perhaps didnt), we might enter the command

INSERT INTO physician, (street, city, state, zip)
SELECT (streetname, cityname, statename,
zipcode) FROM physinfo WHERE NOT (physid
idnum)

• NOTE Fields in table physician had which were
  NOT imported from table physinfo will remain
  empty.

54
SQL
Database Queries
? Extracting information from a database
? Performed using high-level Query Languages
? Very similar across SQL platforms
(Although differences DO exist)
? Use of standard English commands

• SELECT

• SELECT

• FROM

• WHERE

• GROUP BY

• HAVING

• ORDER BY

55
SQL
Database Queries
? The Command
SELECT FROM physinfo
Would result in the (approximate) display
physid physname specialty
street city state
zip ------------- ---------------------
------------- ------------- --------
------- -------- 123456789 Smith, John
OB/GYN 123 Main El Paso TX
76019
? The Command
SELECT physid, physname, specialty FROM physician
Would result in the (approximate) display
physid physname specialty
------------- --------------------
--------------- 123456789 Smith, John
OB/GYN
56
SQL
Database Queries
? In SQL, We also have the choice of listing ALL
(using SELECT , OR SELECT ALL) records or
DISTINCT records
SELECT DISTINCT specialty FROM physician
Might result in the (approximate) display
specialty ------------- Surgery Dermatology OB/GYN
Opthamology ENT Pediatrics
57
SQL
Database Queries
? The Command
SELECT physid, physname FROM physician
WHERE (specialty Surgery OR specialty
Dermatology)
Might result in the (approximate) display
physid physname -------------
-------------------- 123456789 Smith, John
234567890 Jones, Mary 345678901
Houston, Sam 456789012 Bush, G.W. 567890123
Clinton, Hillary
NOTE Because we are using the OR operator, ALL
records meeting the condition will be shown
58
SQL
Database Queries
? The Command
SELECT physid, physname FROM physician
WHERE (specialty Surgery AND
specialty Dermatology)
Might result in the (approximate) display
physid physname -------------
--------------------
NOTE With the AND operator, ONLY those records
meeting BOTH conditions will be shown (in this
case, None)
59
SQL
Database Queries
? SQL has the following built-in Operators
Operator(s) Description / Multiplication
Division - Addition Subtraction
Suppose we had a table called inventory
60
SQL
Database Queries
? Suppose we wanted to find the items where our
profit margin was 60 or more
? In this case our profit is defined as
- 1
100
? For our table inventory, our profit margins
are
61
SQL
Database Queries
? The Command
SELECT partnum, price - cost FROM
inventory WHERE ((price / cost - 1) 100
gt 0.60)
Would result in the (approximate) display
partnum price - cost -------------
--------------- 101 1.33
102 1.23 105 5.83
Notice we didnt do a very good job labeling our
fields
62
SQL
Database Queries
? The Command
SELECT partnum AS Part, cost AS Cost, price
AS Price, price - cost AS Profit, (price /
cost - 1) 100 AS Margin FROM inventory
WHERE ((price / cost - 1) 100 gt 0.60)
ORDER BY ((price / cost - 1) 100) DESC
Would result in the (approximate) display
Part Cost Price Profit Margin ------
-------- ------- --------- ---------- 105
7.19 12.99 5.83 81.42 102 1.76 2.99 1.23
69.89 101 0.89 1.45 1.33 62.95
63
SQL
Database Queries
? SQL has a number of built-in numeric functions

Function Output COUNT The number of rows
containing the attribute MIN The minimum
attribute encountered MAX The maximum attribute
encountered SUM The total of all values for a
selected attribute AVG The arithmetic mean for
a selected attribute
64
SQL
Database Queries
? The Command
SELECT COUNT (DISTINCT partnum) AS No. Parts,
MIN (price) AS Cheapest, MAX(price) AS
Costliest, SUM (cost onhand) AS Inv. Cost,
SUM (price onhand) AS Inv. Potential, AVG
((price / cost - 1) 100) AS Ave. Margin, SUM
((price onhand) - (cost onhand)) AS Pot.
Profit, FROM inventory
Would result in the (approximate) display
No. Parts Cheapest Costliest Inv. Cost Inv.
Potential Ave. Margin Pot. Profit ------------
------------ ----------- ------------
------------------ ----------------- -------------
6 1.45 17.99 986.86 1631.68 59.06 644.82
65
SQL
Database Queries
? SQL also has a number of built-in special
functions

Function Output BETWEEN Used to define Range
Limits IS NULL Used to check if an attribute
contains NULL Values LIKE Used to check similar
Character Strings IN Used to check if an
attribute is contained IN a set of
Values EXISTS Used to check if an attribute
value exists (The opposite of IS NULL)
66
SQL
Database Queries
? The Command
SELECT partnum, cost, price FROM
inventory WHERE partnum BETWEEN 102 AND 104
Would result in the (approximate) display
partnum cost price -------------
---------- --------- 102 1.76 2.99
103 12.83 17.99 104 4.88 7.22
67
SQL
Database Queries
? The Command
SELECT physid, physname FROM physician WHERE
physname LIKE Smi
Where is a wildcard
Might result in the (approximate) display
Physid physname -------------
------------- 123456789 Smithers
234567890 Smith 456789012 Smickman 678901234 S
miley
68
SQL
Database Queries
? The LIKE command can take on a few forms
Search String Pattern Matched
Sm Finds Any string starting with Sm
Sm Finds Any string containing Sm Sm Finds
Any string ending with Sm S_m Finds any 3 letter
string beginning with S and ending in
m S_m Finds any string containing the letter S
followed by any character, followed by the
character m NOT LIKE The opposite of LIKE
69
SQL
Database Queries
? Often, it is necessary to JOIN tables together
on common keys (E.g., a foreign key)
? We Might enter the command
SELECT physid, physname, patid, name FROM
physician, patient WHERE physician.physid
patient.physid
NOTE If two tables have the same field names, we
need to indicate which is which using DOT NOTATION
This might result in the (approximate) display
Physid physname patid name -------------
------------- ------------- -------------- 1234567
89 Smith 987654321 Gore 123456789
Smith 876543210 Bradley 456789012
Jones 765432109 Richardson 678901234 Bush 6543210
98 Sanders
70
SQL
Database Queries
? We can also JOIN multiple tables together
? Remember our expanded relationship
Physician
? Where we had a ternary relationship between
patient, illness, and prescription
71
SQL
Database Queries
In Tabular Form, this appeared as
Patient Table
Treatment Table
Illness Table
Prescription Table
72
SQL
Database Queries
? One command we might enter is
SELECT physname, patient.name, illness.name,
prescription.drugcode FROM physician, patient,
treatment, illness, prescription WHERE
physician.physid patient.physid AND
patient.patid treatment.patid AND
treatment.illcode illness.illcode AND
treatment.drugcode prescription.drugcode ORDER
BY physname
(It CAN get involved)
73
SQL
Database Queries
? This command might yield the outcome
physname patient.name illness.name prescription.dr
ugcode ------------- ----------------- ---------
------- ----------------------------- Bush Iman Be
auty S228C99 Clinton Lewinski Sore
throat L001H98 Clinton Flowers Humiliation J897T11
Guilianni Clinton Humiliation A345B23 Jones Ric
hardson Depression J897T11 Jones Vonnegut Writersb
lk K980F66 Smith Gore Dullness A345B23
Smith Bradley Tallness S228C99 Smith Beethoven De
afness A345B23 Smith Aikman Sore
Arm A345B23 Smith Bradley Tallness A345B23 Smith
Gore Euphoria S228C99 Smith Astaire Brk
Leg A345B23
74
SQL
Database Queries
? NOW, remember our original Concern
The head administrator wants a list of all of Dr.
Smiths patients, their illnesses, and what
prescriptions were given to those people.
? Lets assume that we had a field in our
prescription table called drugname (for the name
of the drug).
? Our command might be
SELECT physname, patient.name, illness.name,
prescription.drugname FROM physician, patient,
treatment, illness, prescription WHERE
physician.physid patient.physid AND
patient.patid treatment.patid AND
treatment.illcode illness.illcode AND
treatment.drugcode prescription.drugcode AND
physid 123456789
75
SQL
Database Queries
? This command might yield the outcome
physname patient.name illness.name prescription.dr
ugname ------------- ----------------- ---------
------- ------------------------------ Smith
Gore Dullness Thorazine Smith Bradley Tallness
Aspirin Smith Beethoven Deafness Thorazine Smith A
ikman Sore Arm Thorazine Smith Bradley Tallness
Thorazine Smith Gore Euphoria Aspirin
Smith Astaire Brk Leg Thorazine
And Maybe the Administrator DOES have a valid
concern
76
SQL
Database Queries
? If we wanted to get fancier, we might issue
the command
SELECT physname, patient.name, illness.name,
prescription.drugname FROM physician, patient,
treatment, illness, prescription WHERE
physician.physid patient.physid AND
patient.patid treatment.patid AND
treatment.illcode illness.illcode AND
treatment.drugcode prescription.drugcode AND
physid 123456789 ORDER BY COUNT
(prescription.drugname, patient.name)
77
SQL
Database Queries
? This command might yield the outcome
physname patient.name illness.name prescription.dr
ugname ------------- ----------------- ---------
------- ------------------------------ Smith Aikma
n Sore Arm Thorazine Smith Astaire Brk
Leg Thorazine Smith Beethoven Deafness Thorazine
Smith Bradley Tallness Thorazine Smith
Gore Dullness Thorazine Smith Bradley Tallness
Aspirin Smith Gore Euphoria Aspirin
78
SQL
Database Queries
? Notice that our queries can become complex,
and that reentering the command each time becomes
tedious
? We can create permanent VIEWS

• A VIEW is a logical table (NOT physical) which
  contains a query

• A VIEW holds the query commands and is run each
  time it is called

• A VIEW must be CREATEd and selected when to be run

79
SQL
Database Queries
? To create our view
CREATE VIEW drugs_given AS SELECT physname,
patient.name, illness.name, prescription.drugcod
e FROM physician, patient, treatment, illness,
prescription WHERE physician.physid
patient.physid AND patient.patid
treatment.patid AND treatment.illcode
illness.illcode AND treatment.drugcode
prescription.drugcode ORDER BY physname
80
SQL
Database Queries
? To run our view
SELECT FROM drugs_given WHERE physid
123456789 ORDER BY COUNT (prescription.drugname)
, patient.name
This might again result in the (approximate)
display
physname patient.name illness.name prescription.dr
ugname ------------- ----------------- ---------
------- ------------------------------ Smith Aikma
n Sore Arm Thorazine Smith Astaire Brk
Leg Thorazine Smith Beethoven Deafness Thorazine
Smith Bradley Tallness Thorazine Smith
Gore Dullness Thorazine Smith Bradley Tallness
Aspirin Smith Gore Euphoria Aspirin
81
SQL