Week 5 September 26

1
Week 5September 26

• Oracle Control Files
• Basic Data Types
• SQL Creating and Altering a Table,Imposing
  Integrity Constraints,DML

2
SQL Terminology

• ISO terminology
• Relations (entity type) ? Tables
• Attributes (properties) ? Columns
• Tuples (entities) ? Rows

column
Row
3
Oracle Control Files

• Loads data into tables in batch mode
• Two parts
• Control language
• Data

4
Anatomy of a Control FileUsing Displacement
(i.e., Column Position)
Data contained in control file
load data infile into table videos (category_cod
e position(12)
char, stock_number
position(510) char, video_title
position(1257) char, retail_price
position(5966) decimal
external, running_time
position(6770) integer external, rating
position(7175)
char, distributor_code
position(7779) char, year_released
position(8291) date(10)
"mm-dd-yyyy", active_date
position(92101) date(10) "mm-dd-yyyy", stock_o
n_hand position(102103)
integer external, stock_on_order
position(104105) integer external) begindata SF
000025 Kronos
19.95 78NR BBE 01-01-195908-19-1995
412 DC 000036 Alice in Wonderland
22.95 75NR DIS
01-01-195103-11-1995 410 DC 000100 Little
Mermaid, The 26.95
82NR DIS 01-01-198901-12-1991 317 SF 000101
Navy vs. the Night Monsters, The
19.95 90NR BBE 01-01-196609-29-1992 6 0 SF
000102 Monster Mania
9.99 60NR MCA 01-01-199806-01-1998 2
6 1 2 3 4
5 6 7 8 9
10 123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345
6789012345
Table name
Data types and format
Column names
Data
5
Anatomy of a Control FileUsing Displacement
(i.e., Column Position)
Data contained in control file
load data infile append into table videos
Optional append
load data infile into table videos (category_cod
e position(12)
char, stock_number
position(510) char, video_title
position(1257) char, retail_price
position(5966) decimal
external, running_time
position(6770) integer external, rating
position(7175)
char, distributor_code
position(7779) char, year_released
position(8291) date(10)
"mm-dd-yyyy", active_date
position(92101) date(10) "mm-dd-yyyy", stock_o
n_hand position(102103)
integer external, stock_on_order
position(104105) integer external) begindata SF
000025 Kronos
19.95 78NR BBE 01-01-195908-19-1995
412 DC 000036 Alice in Wonderland
22.95 75NR DIS
01-01-195103-11-1995 410 DC 000100 Little
Mermaid, The 26.95
82NR DIS 01-01-198901-12-1991 317 SF 000101
Navy vs. the Night Monsters, The
19.95 90NR BBE 01-01-196609-29-1992 6 0 SF
000102 Monster Mania
9.99 60NR MCA 01-01-199806-01-1998 2
6 1 2 3 4
5 6 7 8 9
10 123456789012345678901234567890123456789012345
67890123456789012345678901234567890123456789012345
6789012345
Table name
Data types and format
Column names
Reading data from a file
LOAD DATA INFILE 'manufac.dat' INTO TABLE
manufacturers
Data
6
Anatomy of a Control FileUsing a Delimiter
Data contained in control file
LOAD DATA INFILE INTO TABLE video_categories FIE
LDS TERMINATED BY "," OPTIONALLY ENCLOSED BY
'"' (category_code, category_title) begindata AC,A
ction BB,Big Book Best Sellers BC,British Comedy
and Drama
Table name
Declared delimiter
Column names
Data
7
Basic Data Types in Oracle
String Data Types String Data Types String Data Types
Fixed length char(length) char(30)
Variable length varchar2(maximum-length) Varchar(30)
Numeric Data Types Numeric Data Types Numeric Data Types
Fixed point number(precision,scale) dec(precision,scale) Where precision total number length, scale number of decimal places number(8,2) dec(6,2)
Integer int, smallint
Floating point number, dec, float
Date Data Types Date Data Types Date Data Types
Date date
Other data types exist
8
Anatomy of a CREATE TABLE
SQLgt create table computer_products 2
(model_number varchar2(12) primary key,
3 product_description varchar2(50) default
'N/A', 4 list_price dec(6,2)
default 0, 5 retail_price dec(6,2)
default 0, 6 retail_unit char(2)
default 'EA', 7 stock_on_hand int
default 0, 8 stock_on_order int
default 0, 9 last_shipment_received date, 10
manufacturer_code varchar2(3)) Table
created.
Constraint
Data type
Column name
9
Integrity Constraints

• Imposed to protect the database from becoming
  inconsistent
• Types
• Required data
• Domain constraints
• Entity constraints
• Referential integrity
• Enterprise constraints

10
Integrity Constraints

• Required data
• A column cannot be null (not null)
• Domain constraints
• Values assigned to a column must be from a
  defined domain

SQLgt CREATE TABLE REVENUES 2
(TRANSACTION_NUMBER ROWID PRIMARY KEY, 3
TRANSACTION_DATE DATE NOT NULL, 4
TRANSACTION_TYPE CHAR(1) CONSTRAINT
CK_TRANS_TYPE 5 CHECK (TRANSACTION_TYPE
IN('R','S','E','A','X'))) Table created.
11
Domain Constraint

• Using BETWEEN

SQLgt create table limousines 2 (fleet_number
varchar2(10) primary key, 3 vehichle_type
varchar2(15) not null, 4 seat_capacity
smallint 5 constraint ck_seat_capacity
check 6 (seat_capacity between 1 and
12)) Table created.
12
Integrity Constraints

• Entity constraints
• Primary key must contain a unique, no null value
  for each row

SQLgt CREATE TABLE VIDEO_REVENUES 2
(TRANSACTION_NUMBER ROWID PRIMARY KEY, 3
TRANSACTION_TYPE CHAR(1) CONSTRAINT
CK_TRANS_TYPE 4 CHECK (TRANSACTION_TYPE
IN('R','S','E','A','X'))) Table created.
13
Entity IntegrityComposite Key
SQLgt create table invoice_items 2
(invoice_number int, 3 item_number int
constraint ck_invoice_number 4 check
(item_number in (1,2,3,4,5,6,7,8,9,10)), 5
product_code varchar2(10), 6 quantity int
default 1, 7 price float not null, 8
constraint pk_invoice_items primary key 9
(invoice_number, item_number), 10 constraint
fk_invoice_number 11 foreign key
(invoice_number) 12 references
invoices(invoice_number), 13 constraint
fk_product_code 14 foreign key
(product_code) 15 references
products(product_code)) Table created.
Neither column is unique by itself
Composite key
14
Integrity Constraints

• Referential integrity
• The value of a foreign key must exist in another
  table (i.e., parent) as at least a candidate key
• Normally, when a row in the parent is deleted and
  a child exists, one of fours actions can be
  taken
• Cascade
• Set null
• Set default
• No action

Supported through triggers
15
Integrity Constraints
SQLgt CREATE TABLE REVENUES 2
(TRANSACTION_NUMBER ROWID PRIMARY KEY, 3
TRANSACTION_TYPE CHAR(1) CONSTRAINT
CK_TRANS_TYPE 4 CHECK (TRANSACTION_TYPE
IN('R','S','E','A','X')), 5 PRODUCT_CODE, 6
CONSTRAINT FK_PRODUCT_CODE FOREIGN KEY
(PRODUCT_CODE) 7 REFERENCES PRODUCTS(PRODUCT_CO
DE) 8 ON DELETE CASCADE) Table created.
Referential integrity
CONSTRAINT index-name FOREIGN KEY
(column-name) REFERENCES table-name(key-name) ON
DELETE CASADE
16
Integrity Constraints

• Enterprise constraints
• Organizational constraints (i.e., business rules)

17
Another Example of Creating a Table with
Referential Integrity Constraints
Table name
Column names, data types and constraints
Table name
Column names, data types and constraints
?
?
18
Example of Creating a Table with Referential
Integrity Constraints
Two ways of imposing constraints
Table name
Column name
Referential integrity constraints
?
Constraint name (index)
19
Example of Creating a Table with Referential
Integrity Constraints
Two ways of imposing constraints
Refers to column name within the table
Constraint name (index)
?
Column name
Table name
20
In Case of ErrorALTER Table

• Alter table SQL command
• Add a column
• Modify a column
• Delete a column
• A column cannot be renamed
• Drop the column
• Add the column

21
Customers table defined
22
Adding a Column
ALTER TABLE table-name ADD (column-name data-type)
Table name
Optional constraint
Data type
Column name
23
Modifying a Column Definition
ALTER TABLE table-name MODIFY (column-name
data-type)
Table name
New definition
Column name
24
Deleting a Column
ALTER TABLE table-name DROP (column-name)
Table name
Column name
25
Basic SQL Data RetrievalFrom Relational Algebra
to SQL
??license, make(??colorSilver(Cars))
Columns (attributes) to retrieve (projection)
Select license, make from Cars where color
Silver
SELECT column-list FROM table-name WHERE
condition
Table (relation) specification
Row (tuple) specification (selection)
26
Examples Using Products Table

• Products (product_code (key), product_description,
  product_cost, product_MSRP, product_retail_price,
  retail_unit, manufacturer_code (foreign key),
  active_date, number_on_hand, number_on_order,
  number_committed)

27
Projection

• ? product_code, product_description(Products)
• SELECT product_code, product_description
• FROM products
• Result All rows with only the product_code and
  product_description columns are retrieved

Column-list
28
SQLgt select product_code, product_description
from products PRODUCT_CO PRODUCT_DESCRIPTION

---------- ----------------------
--------
301-III Direct/Reflecting
Speakers
3800 Three-way
Speaker System
4312 Studio
Monitors
901Classic
Direct/Reflecting Spkr System
AM3
Acoustimass Speaker System
AM5
Acoustimass 5 Speaker System
AM7
Acoustimass 7 Speaker System
AT-10
Loudspeakers
AT-15
Three-way Speaker

CCS-350 Compact System w/CD Player

CCS-450 Compact System w/CD Player

CCS-550 75-watt System w/CD Changer

CD-1000C Compact Disc Changer

CDP-297 Compact Disc Player

CDP-397 Compact Disc Player

CDP-C225 Disc Jockey CD
Changer . . . 100 rows selected.
29
Selection

• ? manufacturer_code SON (Products)
• SELECT
• FROM products
• WHERE manufacturer_code SON
• Result Only those rows with SON for their
  manufacturer_code are retrieved along with all
  columns.

? All columns
Proposition (predicate)
30
Four Ways to Build a Selection
?

• Relational operator (, lt, lt, gt, gt, ltgt)
• Logical AND and OR
• WHERE column-name x AND column-name y
• Specific range using BETWEEN
• WHERE column-name BETWEEN x AND y
• Specific values using IN
• WHERE column-name IN (list-of-values)
• Character match using LIKE and wildcards (, _)
• UPPER and LOWER functions
• WHERE LOWER(column-name) LIKE character-string
  

31
Four ways to build a SELECTION1. Relational
and Logical Operators

• Relational operators
• equals
• gt, lt greater than, less than
• lt less than or equal to
• gt greater than or equal to
• ltgt not equal to
• Logical operators
• AND, OR AND evaluated before OR
• NOT NOT evaluated before AND and OR

32
WHERE (Condition)

• SELECT
• FROM products
• WHERE manufacturer_code SON
• OR manufacturer_code PAN
• SELECT
• FROM products
• WHERE product_msrp gt 100 AND product_msrp lt 500
• AND product_code SON OR product_code PAN

33
select from products where manufacturer_code'SO
N' PRODUCT_CO PRODUCT_DESCRIPTION
PRODUCT_COST PRODUCT_MSRP PRODUCT_RETAIL_PRICE RE
MAN ---------- ------------------------------
------------ ------------ --------------------
-- --- ACTIVE_DA NUMBER_ON_HAND
NUMBER_ON_ORDER NUMBER_COMMITTED
---------
-------------- --------------- ----------------
CDP-297
Compact Disc Player 84.47
129.95 116.96 EA SON
25-AUG-96 4 0
0


CDP-397 Compact Disc Player
97.47 149.95 134.96 EA SON
25-AUG-96 11 0
0


CDP-C225 Disc Jockey CD Changer
129.97 199.95 179.96 EA
SON 25-AUG-96 10 0
0


CDP-C325 Disc Jockey CD Changer
149.47 229.95 206.96
EA SON 25-AUG-96 1
0 0


CDP-C425 Disc Jockey CD Changer
162.47 249.95
224.96 EA SON 25-AUG-96 5
0 0


CDP-C525 Disc Jockey CD
Changer 194.97 299.95
269.96 EA SON 25-AUG-96 6
0 0


SL-S600 Super-Beta
Video Recorder 214.47 329.95
296.96 EA SON 25-AUG-96
13 0 0


TC-W490 Double
Cassette Deck 110.47 169.95
152.96 EA SON 25-AUG-96
6 0 0


12 rows
selected.
34
Projection on a Selection
? product_code, product_description (?
manufacturer_code SON or PAN (Products))

• SELECT product_code, product_description
• FROM products
• WHERE manufacturer_code SON
• OR manufacturer_code PAN

35
SQLgt select product_code, product_description
from products where 2 manufacturer_code'SON'
or manufacturer_code'PAN' PRODUCT_CO
PRODUCT_DESCRIPTION

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

CDP-297 Compact Disc Player

CDP-397 Compact Disc Player

CDP-C225 Disc Jockey CD Changer

CDP-C325 Disc Jockey CD Changer

CDP-C425 Disc Jockey CD Changer

CDP-C525 Disc Jockey CD Changer

PV-2201 HQ VHS Video Cassette Recorde

PV-4210 4-Head VHS Video Cass Recorde

PV-4250 HiFi VHS Video Cass Recorder

SC-T095 Compact Stereo System

SC-TC430 Compact System w/CD
Changer
SL-S600 Super-Beta Video
Recorder
TC-W490 Double Cassette
Deck
TC-WR590 Double
Cassette Deck
TC-WR690
Double Cassette Deck
TC-WR790
Double Cassette Deck
TC-WR875
Double Cassette Deck
17
rows selected.
36
SQLgt select product_code, product_description,
product_msrp 2 from products 3 where
manufacturer_code'SON' or manufacturer_code'PAN'
4 and product_msrp gt 100 and product_msrp
lt500 PRODUCT_CO PRODUCT_DESCRIPTION
PRODUCT_MSRP
---------- ------------------------------
------------
CDP-297 Compact Disc Player
129.95
CDP-397 Compact Disc Player
149.95
CDP-C225 Disc Jockey CD Changer
199.95
CDP-C325 Disc Jockey CD Changer
229.95
CDP-C425 Disc Jockey CD Changer
249.95
CDP-C525 Disc Jockey CD Changer
299.95
PV-2201 HQ VHS Video Cassette
Recorde 229.95
PV-4210 4-Head VHS Video
Cass Recorde 299.95
PV-4250 HiFi VHS Video
Cass Recorder 349.95
SC-T095 Compact
Stereo System 139.95
SC-TC430
Compact System w/CD Changer 429.95
SL-S600
Super-Beta Video Recorder 329.95
TC-W490
Double Cassette Deck 169.95

TC-WR590 Double Cassette Deck
199.95
TC-WR690 Double Cassette Deck
249.95
TC-WR790 Double Cassette Deck
329.95
TC-WR875 Double Cassette Deck
429.95
17 rows selected.
37
Four ways to build a SELECTION 2. Range Search
with BETWEEN

• SELECT product_code, product_description,
  product_msrp
• FROM products
• WHERE product_msrp BETWEEN 100 and 500
• AND manufacturer_code SON OR
  manufacturer_code PAN

? Inclusive
38
SQLgt select product_code, product_description,
product_msrp, 2 manufacturer_code 3 from
products 4 where manufacturer_code 'SON' or
manufacturer_code 'PAN' 5 and product_msrp
between 100 and 500 PRODUCT_CO
PRODUCT_DESCRIPTION PRODUCT_MSRP MAN

---------- ------------------------------
------------ ---
CDP-297 Compact Disc Player
129.95 SON
CDP-397 Compact Disc Player
149.95 SON
CDP-C225 Disc Jockey CD Changer
199.95 SON
CDP-C325 Disc Jockey CD Changer
229.95 SON
CDP-C425 Disc Jockey CD Changer
249.95 SON
CDP-C525 Disc Jockey CD Changer
299.95 SON
PV-2201 HQ VHS Video Cassette
Recorde 229.95 PAN
PV-4210 4-Head VHS Video
Cass Recorde 299.95 PAN
PV-4250 HiFi VHS Video
Cass Recorder 349.95 PAN
SC-T095 Compact
Stereo System 139.95 PAN
SC-TC430
Compact System w/CD Changer 429.95 PAN
SL-S600
Super-Beta Video Recorder 329.95 SON
TC-W490
Double Cassette Deck 169.95 SON

TC-WR590 Double Cassette Deck
199.95 SON
TC-WR690 Double Cassette Deck
249.95 SON
TC-WR790 Double Cassette Deck
329.95 SON
TC-WR875 Double Cassette Deck
429.95 SON
17 rows selected.
39
Four ways to build a SELECTION 3. Search for
Specific Values Using IN

• SELECT product_code, product_description
• FROM products
• WHERE manufacturer_code IN ('SON', 'PAN', 'BOS')

List of values
40
SQLgt select product_code, product_description 2
from products 3 where manufacturer_code in
('SON', 'PAN', 'BOS') PRODUCT_CO
PRODUCT_DESCRIPTION ---------- -------------------
---------------- DVD-A110 DVD/CD
Player DVP-S7000 DVD/CD Player DVP-S3000 DVD/CD
Player DVP-S500D DVD/CD Player KV-20S40 20"
Trinitron TV KV-20V80 20" Digital Comb Filter
TV KV-27V22 27" Trinitron TV KV-27V26 27"
Trinitron TV KV-27V36 27" Picture-in-Picture
TV KV-32V36 32" 1-Tuner PIP TV KV-35V36 35"
1-Tuner PIP TV KV-32XBR48 32" Trinitron XBR
TV KV-35XBR48 35" Trinitron XBR TV KV-35XBR88 35"
Trinitron XBR TV . . . TC-KE500S Cassette
Deck 95 rows selected.
41
Four ways to build a SELECTION 4. Pattern
Match with LIKE

• Wildcard characters
• any sequence of zero or more characters
• _ (underscore) any single character
• SELECT product_code, product_description
• FROM products
• WHERE product_code LIKE C
• Result All rows with product codes beginning
  with C and their corresponding product_description
  will be retrieved.

42
SQLgt select product_code, product_description
from products 2 where product_code like
'C' PRODUCT_CO PRODUCT_DESCRIPTION

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

CCS-350 Compact System w/CD Player

CCS-450 Compact System w/CD Player

CCS-550 75-watt System w/CD Changer

CD-1000C Compact Disc Changer

CDP-297 Compact Disc Player

CDP-397 Compact Disc Player

CDP-C225 Disc Jockey CD Changer

CDP-C325 Disc Jockey CD Changer

CDP-C425 Disc Jockey CD
Changer
CDP-C525 Disc Jockey
CD Changer
CS-13RX 13" Color
Television
CS-13SX1 13"
Stereo Monitor/Television
CS-20SX1
20" Stereo Monitor/Television
CT-WN70R
61 Cassette Changer
14 rows
selected.
43
Pattern Match with LIKE and UPPER Function

• SELECT product_code, product_descriptionFROM
  productsWHERE UPPER(product_description) LIKE
  UPPER('casset')

Changes to upper case
44
SQLgt select product_code, product_description 2
from products 3 where upper(product_descriptio
n) like upper('casset') PRODUCT_CO
PRODUCT_DESCRIPTION ---------- -------------------
---------------- TC-KE400S Cassette
Deck TC-KE500S Cassette Deck CT-W606DR Double
Cassette Deck CT-W616DR Double Cassette
Deck TD-W254 Double Auto-reverse Cassette
Deck TD-W354 Double Auto-reverse Cassette
Deck TD-W718 Dual Auto-reverse Rec Cassette
Deck RS-TR373 Double Auto-reverse Cassette
Deck RS-TR575 Double Auto-reverse Cassette
Deck K-903 Dual Electronic Cassette
Deck TC-WE405 Dual Cassette Deck TC-WE605S
Dual Auto-reverse Cassette Deck K-90
Double Cassette Deck 42 rows selected.
45
Like Without Matching the Case
SQLgt select product_code, product_description 2
from products 3 where product_description
like 'casset' no rows selected
Cassette ? cassette
46
ORDER BY Sorting the Results

• SELECT manufacturer_code, product_code,
  product_description
• FROM products
• ORDER BY manufacturer_code, product_code

Major (sort) key
Minor (sort) key
47
select manufacturer_code, product_code,
product_description 2 from products order by
manufacturer_code, product_code MAN PRODUCT_CO
PRODUCT_DESCRIPTION
---
---------- ------------------------------

AIW NSX-D2 Mini Component System

AIW XK-S9000 Cassette Deck

BOS 301-III Direct/Reflecting Speakers

BOS 901Classic Direct/Reflecting Spkr System

BOS AM3 Acoustimass Speaker System

BOS AM5 Acoustimass 5 Speaker System

BOS AM7 Acoustimass 7 Speaker
System
BOS VS-100 Center Channel Mini
Speaker
CRV AT-10 Loudspeakers

CRV AT-15 Three-way Speaker

CRV SW-12B Subwoofer System

DA PS-6a Point Source
Speaker System
DA PS-8c Point
Source Speaker Sytem
DA PS-9
Point Source Speaker System
GMI PVX-31
Single Ch 31/3rd Octave Bands
GMI
XL-1800QII Prof Manual DJ Turntable

GMI XL-BD10 Semi-Auto Belt-Dr Turntable

GMI XL-DD20 Semi-Automatic Turntable

... 100 rows selected.
48
Using DISTINCT

• SELECT DISTINCT(manfuacturer_code)
• FROM products
• Result The non-duplicated manufacturer_codes
  will be retrieved.

49
select distinct(manufacturer_code) from
products MAN

---

AIW

BOS

CRV

DA

GMI

HVC

JBL

JVC

MIT

PAN

PIN

PIO

SAM

SHE

SON

TEA

TEC

THN

YAM

19 rows selected.
50
Equi-Join
R R.aj ? S.bj S

• A resulting relation that contains tuples
  satisfying the predicate of equality between two
  attributes of the same domain from the Cartesian
  product of R and S

51
Equi-Join
SQLgt select product_code, p.manufacturer_code,
2 m.manufacturer_code 3 from products p,
manufacturers m 4 where p.manufacturer_code
m.manufacturer_code PRODUCT_CO MAN
MAN ---------- --- --- RS1B INF INF SM165
INF INF CC1M INF INF 100 BOS
BOS 201-IV BOS BOS VS-100 BOS BOS 6 rows
selected.
Products
Manufacturers
RS1B INF SM165 INF CC1M INF
100 BOS 201-IV BOS VS-100 BOS
ADV Advent BOS Bose INF Infinity
52
Outer Joins in Oracle

• An outer join returns all rows that satisfy the
  join condition and those rows from one table for
  which no rows from the other satisfy the join
  condition.
• Such rows are not returned by a simple join.
• To perform an outer join of tables A and B and
  return all rows from A, apply the outer join
  operator () to all columns of B in the join
  condition.
• For all rows in A that have no matching rows in
  B, a NULL is returned for any select list
  expressions containing columns of B.

53
Left Outer Join

• All rows on the left table (i.e., products) are
  kept

SQLgt select product_code, product_description,
2 p.manufacturer_code, m.manufacturer_code 3
from products p, manufacturers m 4 where
p.manufacturer_code m.manufacturer_code() PRO
DUCT_CO PRODUCT_DESCRIPTION MAN
MAN ---------- -----------------------------------
--- --- 100 Compact Speakers
BOS BOS 201-IV Direct/Reflecting
Speakers BOS BOS VS-100 Center
Channel Mini Speaker BOS BOS RS1B
2-way Bookshelf Speakers INF INF SM165
Bookshelf-sized Speakers INF
INF CC1M Center Channel Speaker
INF INF 6 rows selected.
54
Right Outer Join

• All rows on the right table (i.e., manufacturers)
  are kept

SQLgt select product_code, product_description,
2 p.manufacturer_code, m.manufacturer_code 3
from products p, manufacturers m 4 where
p.manufacturer_code() m.manufacturer_code PR
ODUCT_CO PRODUCT_DESCRIPTION MAN
MAN ---------- -----------------------------------
--- ---
ADV 100 Compact Speakers
BOS BOS 201-IV Direct/Reflecting
Speakers BOS BOS VS-100 Center
Channel Mini Speaker BOS BOS RS1B
2-way Bookshelf Speakers INF INF SM165
Bookshelf-sized Speakers INF
INF CC1M Center Channel Speaker
INF INF 7 rows selected.
?
55
Cartesian Product
SQLgt select from products, manufacturers RS1B
2-way Bookshelf Speakers INF 188 PR
ADV Advent SM165 Bookshelf-sized Speakers
INF 205 PR ADV Advent CC1M Center
Channel Speaker INF 164 EA ADV Advent 100
Compact Speakers BOS 205 PR
ADV Advent 201-IV Direct/Reflecting Speakers
BOS 205 PR ADV Advent VS-100 Center
Channel Mini Speaker BOS 116.96 EA ADV
Advent RS1B 2-way Bookshelf Speakers INF
188 PR INF Infinity SM165 Bookshelf-sized
Speakers INF 205 PR INF Infinity CC1M
Center Channel Speaker INF 164 EA INF
Infinity 100 Compact Speakers
BOS 205 PR INF Infinity 201-IV
Direct/Reflecting Speakers BOS 205 PR INF
Infinity VS-100 Center Channel Mini Speaker
BOS 116.96 EA INF Infinity RS1B 2-way
Bookshelf Speakers INF 188 PR BOS
Bose SM165 Bookshelf-sized Speakers INF
205 PR BOS Bose CC1M Center Channel
Speaker INF 164 EA BOS Bose 100
Compact Speakers BOS 205 PR BOS
Bose 201-IV Direct/Reflecting Speakers BOS
205 PR BOS Bose VS-100 Center Channel Mini
Speaker BOS 116.96 EA BOS Bose 18 rows selected.
56
Cartesian Product
SQLgt select from products, manufacturers RS1B
2-way Bookshelf Speakers INF 188 PR
ADV Advent SM165 Bookshelf-sized Speakers
INF 205 PR ADV Advent CC1M Center
Channel Speaker INF 164 EA ADV Advent 100
Compact Speakers BOS 205 PR
ADV Advent 201-IV Direct/Reflecting Speakers
BOS 205 PR ADV Advent VS-100 Center
Channel Mini Speaker BOS 116.96 EA ADV
Advent RS1B 2-way Bookshelf Speakers INF
188 PR INF Infinity SM165 Bookshelf-sized
Speakers INF 205 PR INF Infinity CC1M
Center Channel Speaker INF 164 EA INF
Infinity 100 Compact Speakers
BOS 205 PR INF Infinity 201-IV
Direct/Reflecting Speakers BOS 205 PR INF
Infinity VS-100 Center Channel Mini Speaker
BOS 116.96 EA INF Infinity RS1B 2-way
Bookshelf Speakers INF 188 PR BOS
Bose SM165 Bookshelf-sized Speakers INF
205 PR BOS Bose CC1M Center Channel
Speaker INF 164 EA BOS Bose 100
Compact Speakers BOS 205 PR BOS
Bose 201-IV Direct/Reflecting Speakers BOS
205 PR BOS Bose VS-100 Center Channel Mini
Speaker BOS 116.96 EA BOS Bose 18 rows selected.
I ? J rows (tuples), N M columns (attributes)
Products RS1B 2-way Bookshelf Speakers
INF 188 PR SM165 Bookshelf-sized Speakers
INF 205 PR CC1M Center Channel
Speaker INF 164 EA 100 Compact
Speakers BOS 205 PR 201-IV
Direct/Reflecting Speakers BOS 205 PR VS-100
Center Channel Mini Speaker BOS 116.96 EA
Manufacturers ADV Advent INF Infinity BOS Bose
3 x 6 18 rows
57
Union, Intersection, Set Difference
??attribute-1, ..., attribute-n(R) ? ?
-???attribute-1, ..., attribute-n(S)

• SELECT colunn-name1, ..., column-namen
• FROM table1
• UNION INTERSECT MINUS
• SELECT column-name1, ..., column-namen
• FROM table2

Columns must correspond to one another
R
S
58
Example Tables
TEAC_200_products W-410C Double Cassette
Deck TEA 89.96 EA W-525R
Auto-Reverse Double Cassette TEA 170.96
EA V-370 Cassette Deck TEA
63.86 EA V-390CHX Cassette Deck
TEA 89.96 EA EQA-3 Stereo 10-Band
Equalizer TEA 62.96 EA PD-555 CD
Player TEA 116.96 EA PD-C400
Remote CD Player TEA 152.96
EA 7 Rows
TEAC_150_250_products W-525R Auto-Reverse
Double Cassette TEA 170.96 EA W-585R Double
Cassette Deck TEA 224.96 EA PD-C400
Remote CD Player TEA 152.96 EA 3
Rows
59
Union
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
SQLgt select from teac_200_products 2 union
3 select from teac_150_250_products EQA-3
Stereo 10-Band Equalizer TEA 62.96
EA PD-555 CD Player TEA
116.96 EA PD-C400 Remote CD Player
TEA 152.96 EA V-370 Cassette Deck
TEA 63.86 EA V-390CHX
Cassette Deck TEA 89.96
EA W-410C Double Cassette Deck TEA
89.96 EA W-525R Auto-Reverse Double
Cassette TEA 170.96 EA W-585R Double
Cassette Deck TEA 224.96 EA 8
rows selected.
60
Intersection
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
SQLgt select from teac_200_products 2
intersect 3 select from teac_150_250_products
PD-C400 Remote CD Player TEA
152.96 EA W-525R Auto-Reverse Double
Cassette TEA 170.96 EA
61
Set Difference
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)

• Performed on two union compatible tables (i.e.,
  same columns)
• Displays the rows unique to one of the two tables
  (i.e., found in one but not the other)
• The order of the tables matters!

62
Set Difference
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
SQLgt select from teac_200_products 2 minus
3 select from teac_150_250_products EQA-3
Stereo 10-Band Equalizer TEA 62.96
EA PD-555 CD Player TEA
116.96 EA V-370 Cassette Deck
TEA 63.86 EA V-390CHX Cassette Deck
TEA 89.96 EA W-410C
Double Cassette Deck TEA 89.96 EA
Why?
SQLgt select from teac_150_250_products 2
minus 3 select from teac_200_products W-585
R Double Cassette Deck TEA
224.96 EA
63
Set Difference
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
SQLgt select from teac_200_products 2 minus
3 select from teac_150_250_products EQA-3
Stereo 10-Band Equalizer TEA 62.96
EA PD-555 CD Player TEA
116.96 EA V-370 Cassette Deck
TEA 63.86 EA V-390CHX Cassette Deck
TEA 89.96 EA W-410C
Double Cassette Deck TEA 89.96 EA
TEAC_200_products W-410C W-525R V-370 V-390CHX E
QA-3 PD-555 PD-C400
7 Rows
Duplicates
TEAC_150_250_products W-525R W-585R PD-C400
These rows are unique to TEAC_200_products
3 Rows
64
Set Difference
??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
SQLgt select from teac_150_250_products 2
minus 3 select from teac_200_products W-585
R Double Cassette Deck TEA
224.96 EA
TEAC_150_250_products W-525R W-585R PD-C400
3 Rows
TEAC_200_products W-410C W-525R V-370 V-390CHX E
QA-3 PD-555 PD-C400
This rows is unique to TEAC_150_250_products
Duplicates
7 Rows
65
Calculation and Format Models (Masks)
Concatenation
SQLgt select product_code, 2 manufacturer_name
' - ' product_description 3 as
"Description", 4 number_on_hand as "Stock on
Hand", 5 to_char(product_retail_price,'9,990.
00') as "Selling Price", 6 to_char(number_on_ha
nd product_retail_price,'9,990.00') 7 as
"Inventory Value" 8 from products p,
manufacturers m 9 where p.manufacturer_code
m.manufacturer_code 10 and product_retail_price
lt 100
Alias
Format model
Converts numeric or date data type to
character (required for a format mask)
66
Calculation and Format Models (Masks)(Results)
PRODUCT_CO Description Stock on
Hand Selling Pr Inventory ----------
---------------------------------------------
------------- XL-BD10 Gemini - Semi-Auto
Belt-Dr Turntable 1 80.96 80.96 V-370
Teac - Cassette Deck 1
63.86 63.86 V-390CHX Teac - Cassette Deck
1 89.96 89.96 W-410C
Teac - Double Cassette Deck 1
89.96 89.96 SH-8017 Technics - Graphic
Equalizer 1 62.96 62.96 SL-BD20
Technics - Belt-Drive Semi-Auto Turntabl 1
89.96 89.96 EQA-3 Teac - Stereo
10-Band Equalizer 1 62.96
62.96 CD-1000C Sherwood - Compact Disc Changer
1 89.96 89.96 8 rows selected.
(Edited to fit the slide)
67
Applicable Columns
SQLgt describe products Name
Null? Type --------------------
------------------- -------- -------------
PRODUCT_CODE NOT NULL
VARCHAR2(10) PRODUCT_DESCRIPTION
VARCHAR2(35) PRODUCT_COST
NUMBER(8,2)
PRODUCT_MSRP
NUMBER(8,2) PRODUCT_RETAIL_PRICE
NUMBER(8,2) RETAIL_UNIT
CHAR(2)
MANUFACTURER_CODE
CHAR(3) ACTIVE_DATE
DATE NUMBER_ON_HAND
NUMBER(6) NUMBER_ON_ORDER
NUMBER(6)
NUMBER_COMMITTED
NUMBER(6) INACTIVE_DATE
DATE SQLgt describe manufacturers
Name Null?
Type ---------------------------------------
-------- ------------- MANUFACTURER_CODE
NOT NULL CHAR(3) MANUFACTURER_NAME
NOT NULL VARCHAR2(30)
68
SQLgt select manufacturer_name, sum(number_on_hand)
, 2 max(product_retail_price),min(product_retai
l_price), 3 avg(product_retail_price),count(pro
duct_code) 4 from products p, manufacturers m
5 where p.manufacturer_code
m.manufacturer_code 6 group by
manufacturer_name 7 having sum(number_on_hand)
gt 25 8 order by manufacturer_name MANUFACTURE
R_NAME SUM(NUMBER_ON_HAND) MAX(PRODUCT_RETAIL_P
RICE) -------------------- -------------------
------------------------- MIN(PRODUCT_RETAIL_PRICE
) AVG(PRODUCT_RETAIL_PRICE) COUNT(PRODUCT_CODE) --
----------------------- -------------------------
------------------- JVC
30 1266
116.96 417.31867
30 Polk
26 1614
135 427.07692
26 Sony 68
2474 116.96
561.34426
68 Technics 32
629.96 62.96
200.97625 32
Do not follow this query too closely for your
homework assignment
69
SQLgt select manufacturer_name manufacturer, 2
to_char(sum(number_on_hand),'9,990')"Total on
Hand", 3 to_char(max(product_retail_price),'9,
990.00') "Max Price", 4 to_char(min(product_ret
ail_price),'9,990.00') "Min Price", 5
to_char(avg(product_retail_price),'9,990.00')
6 "Average Price", 7 to_char(count(product_
code),'9,990') "Total Products" 8 from
products p, manufacturers m 9 where
p.manufacturer_code m.manufacturer_code 10
group by manufacturer_name 11 having
sum(number_on_hand) gt 25 12 order by
manufacturer_name MANUFACTURER Total Max
Price Min Price Average Pr Total ---------------
-- ------ ---------- ---------- ----------
------ JVC 30 1,266.00
116.96 417.32 30 Polk
26 1,614.00 135.00 427.08 26 Sony
68 2,474.00 116.96
561.34 68 Technics 32
629.96 62.96 200.98 32
Output edited to fit slide
Do not follow this query too closely for your
homework assignment
70
(No Transcript)