Title: SQL
1SQL
Chapter 9
An Introduction to Structured Query Language (SQL)
(With Considerable Modifications)
2SQL
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)
3SQL
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 ???
4SQL
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
5SQL
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
Others
- OLE (Object Linking and Embedding)
6SQL
Database Definition
Oracle Allows for the following Basic data types
to be entered into Fields (There are others)
Characters
CHAR
- Up to 255 characters are allowed
Fieldname CHAR(N)
- The length (N) MUST be specified
- If less than N characters are entered, N spaces
will STILL be allocated
7SQL
Database Definition
Characters
VARCHAR
- Up to 255 characters are allowed
fieldname VARCHAR(N)
- The length (N) MUST be specified
- ONLY the number characters are entered will be
allocated space
VARCHAR2, LONG VARCHAR
8SQL
Database Definition
Numeric Data Types
INTEGER
Typically, up to 10 - 11 digits are allowed
-2,147,483,648 through 2,147,483,647
fieldname INTEGER
SMALLINT
Typically, up to 4 - 5 digits are allowed
-32,768 through 32,767
fieldname SMALLINT
9SQL
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
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
10SQL
Database Definition
Date and Time Data Types
DATE
Date Value
- (Typically) Displayed as mm/dd/yy
fieldname DATE
TIME
Time Value
- (Typically) Displayed as hhmmss
fieldname TIME
11SQL
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)
12SQL
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).
13SQL
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
14SQL
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
15SQL
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 ( , )
16SQL
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)
17SQL
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 ???
18SQL
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
19SQL
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
20SQL
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) )
21SQL
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
22SQL
Database Definition
? A Schema consists of
- Tables (which we previously created)
? 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
? Usernames allowed (and how allowed) to access
the tables
23SQL
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) )
24SQL
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
25SQL
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
26SQL
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
27SQL
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
28SQL
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 ( , )
29SQL
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) )
30SQL
Database Definition
? Finally, Lets create two more tables
Patient
? Remember, this is a Many-to-Many Relationship
31SQL
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
32SQL
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
33SQL
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) )
34SQL
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) )
35SQL
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) )
36SQL
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
37SQL
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
38SQL
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)
)
39SQL
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
40SQL
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
41SQL
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)
42SQL
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
43SQL
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)
44SQL
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)
45SQL
Database Structure Modification
? Altering the database (FOR MOST RDBMS)
- Adding a new column to a table
(Foreign Key references are automatically removed)
- Changing a columns data type, size, and default
value is allowed ONLY if there are NO DATA in the
column being modified
46SQL
Database Structure Modification
? Altering the database (FOR MOST RDBMS)
(Continued)
- 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
47SQL
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
48SQL
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
49SQL
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)
This assumes that the field CAN be converted into
a numeric Field
4B7876D
- If physid contained the data
It could not be converted
50SQL
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
51SQL
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)
ALTER TABLE patient ADD FOREIGN KEY
(physname) REFERENCES (physician)
52SQL
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
53SQL
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.
54SQL
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
55SQL
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
56SQL
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
57SQL
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
58SQL
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)
59SQL
Database Queries
? SQL has the following built-in Operators
Operator(s) Description / Multiplication
Division - Addition Subtraction
Suppose we had a table called inventory
60SQL
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
61SQL
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
62SQL
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
63SQL
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
64SQL
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
65SQL
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)
66SQL
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
67SQL
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
68SQL
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
69SQL
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
70SQL
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
71SQL
Database Queries
In Tabular Form, this appeared as
Patient Table
Treatment Table
Illness Table
Prescription Table
72SQL
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)
73SQL
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
74SQL
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
75SQL
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
76SQL
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)
77SQL
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
78SQL
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
79SQL
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
80SQL
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
81SQL