Week 4 September 19

1
Week 4September 19

• Relational Data Model
• Views

2
Relational Model

• Objectives
• A degree of data independence
• Address data semantic, consistency and redundancy
  problems
• Set-oriented data manipulation language
• Structured Query Language (SQL)

Presen- tation method
Criteria
Database
Data Set
Information
3
Presen- tation method
Data Set
Information
Criteria
Presen- tation method
Criteria
Data Set
Information
Criteria
Database
Presen- tation method
Data Set
Information
4
Domain all values an attribute can assume
Attribute-1
Attribute-2
Attribute-n
Entity

• Tuples (rows)
• Cardinalitiy number of tuples

Relation

• Attributes (columns)
• Degree of a relation number of attributes

5
Domain of an Attribute

• Set of allowable values for one or more attributes

Attribute 1
Attribute 2
Union or Intersection
Domain
Domain
Information
6
Properties of Relations

• Distinct (i.e., unique) relation name
• Each cell contains exactly one atomic (single)
  value
• No repeating groups
• Distinct attribute name
• The values of an attribute come from the same
  domain
• Order of attributes has no significance
• Each tuple is distinct (i.e., unique)
• No duplicate tuples
• Order of tuples has no significance

7
Unique Identification of a Relation
Relation
key
?
Superkey Candidate key Primary key Foreign key
8
Identifying a Tuple

• SuperkeyAn attribute or a set of attributes that
  uniquely identifies a tuple within a relation
• Candidate keyA super key such that no proper
  subset is a superkey within the relation
• Uniquely identifies the tuple (uniqueness)
• Contains no unique subset (irreducibility)
• Primary keyThe candidate key that is selected to
  identify tuples uniquely within a relation
• Should remain constant over the life of the tuple
• Most efficient way of identifying a tuple

9
Finding the Primary Key
Super Key
Candidate Key
Primary key
10
Keys
CDs Relation
129341
Help!
Beatles
Columbia
1-29150-8384-0
129342
Hard Days Night
Beatles
Columbia
1-29150-7115-0
Beatles
129343
Sergeant Peppers
Columbia
1-29150-2484-0
129344
Magical Mystery Tour
Beatles
Columbia
1-29150-7515-0
129345
Abbey Road
Beatles
Apple
1-15700-9510-0

• Attributes
• Catalog number
• Record title
• Artist name
• Record label
• UPC

Superkey? Candidate key? Primary key?
11
Selecting a Key

• Criteria
• An efficient way of identifying an entity
• The attribute (value) remains constant over the
  life of the entity
• Never changes

12
Identifying a Tuple

• Foreign keyAn attribute or set of attributes
  within one relation that matches the candidate
  key of some (possibly the same) relation

Relation
key
foreign key
Relation
key
13
Foreign Key
CDs Relation
129341
Help!
Beatles
COL
1-29150-8384-0
129342
Hard Days Night
Beatles
COL
1-29150-7115-0
Beatles
129343
Sergeant Peppers
COL
1-29150-2484-0
Must match!
129344
Magical Mystery Tour
Beatles
COL
1-29150-7515-0
129345
Abbey Road
Beatles
APP
1-15700-9510-0
COL
Columbia Records
Recording Label Relation (home relation)
APP
Apple Records
14
Relational Integrity

• Constraints placed on the set of values allowed
  for the attributes of a relation.
• Entity integrity
• No attribute of a primary key can be null (every
  tuple must be uniquely identified)
• Referential integrity
• If a foreign key exists in a relation, either the
  foreign key value must match a candidate key
  value of some tuple in its home relation, or the
  foreign key value must be wholly null (i.e., no
  key exists in the home relation)
• General constraints (reflect business practices)

15
Null Value

• Absence of any value (i.e., unknown or
  nonapplicable to a tuple)

16
Views

• A view is a virtual relation or one that does not
  actually exist, but dynamically derived
• Can be constructed by performing operations
  (i.e., select, project, join, etc.) on values of
  existing base relations
• Base relation - a named relation, corresponding
  to an entity in the conceptual schema, whose
  tuples are physically stored in the database
• View - a dynamic result of one or more relational
  operations operating on the base relations to
  produce another

17
Schema and Subschemas
Internal Level Physical Database
DBMS
DBMS Software
Some end-user applications can be supported by
views
Schema
Conceptual Level
External Level
Subschema
Subschema
Subschema
User
User
User
User
User
User
18
Views
Base Relation R
Base Relation S
Foreign Key
Key
Key
Criterion
View
19
Purpose of Views

• Provides a powerful and flexible security
  mechanism by hiding parts of the database from
  certain users
• Permits user access in a way that is customized
  to their needs
• Simplify complex operations on the base relations
• Designed to support the external model
• Provides logical independence

20
Updating Views

• Allowed on views
• Derived from a single base relation, and
• Containing the primary key or a candidate key
• NOT allowed on views
• Derived from multiple base relations
• Involving aggregations (i.e., summations) or
  groups operations
• Vendors may have other constraints on updating
  views

21
Relational LanguagesRelational Calculus vs.
Relational Algebra

• Relational calculusNonprocedural formal
  relational data manipulation language in which
  the user specifies what data should be retrieved,
  but not how to retrieve it.
• Relational algebraA theoretical language with
  operators that work on one or two relations to
  produce another relation. Ricardo, 1990

22
Relational Algebra

• Fundamental operations
• Selection (?)
• Projection (?)
• Union (?)
• Set difference
• Intersection (?)
• Cartesian product
• Join ( )
• Division

Unary operations (applied to a single relation)
23
Aggregating and Grouping Operations

• Count
• Sum
• Average (value)
• Minimum (value)
• Maximum (value)

24
Selection

• Selection operator works on a single relation and
  defines a relation that contains only those
  tuples that satisfy the specified condition
  (predicate)

??predicate(R)
Predicate
25
Projection

• Defines a relation that contains a vertical
  subset, extracting the values of specified
  attributes and eliminating duplicates.

??attribute-1, ..., attribute-n(R)
26
Projection on a Selection

• The selection extracts a subset of tuples from
  the original relation R based on the predicate.
  The project further reduces the number of
  attributes.

??attribute-1, ..., attribute-n(??predicate(R))
Original relation
??
27
Project on a Selection
??license, make(??colorSilver(Cars))
Cars
3ABC123 Acura TL Silver 4MIS345 Honda Accord DX
White 4MIS837 Honda Accord LX Black 4LLP394 Lexus
GS400 Silver 4NOP934 Acura CL MetBlue 4TLC394 Mer
cedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4GAS294 Lexus
LS430 GoldMet
28
Union

• The union of two relations R and S with I and J
  tuples, receptively, is obtained by concatenating
  them into one relation with a maximum of (I J)
  tuples, duplicate tuples being eliminated.
• R and S must be union compatible (i.e., same
  attributes)

??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
R
S
?
29
Union
Set 1
3ABC123 Acura TL Silver 4MIS345 Honda Accord DX
White 4MIS837 Honda Accord LX Black 4LLP394 Lexus
GS400 Silver 4NOP934 Acura CL MetBlue
Set 2
4TLC394 Mercedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4LLP394 Lexus
GS400 Silver 4GAS294 Lexus LS430 GoldMet
30
Set Difference

• Defines a relation consisting of the tuples that
  are in relation R, but not in S.
• R and S must be union-compatible (i.e., same
  attributes)

??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
R
S
31
Set Difference
??license, model, color(Set 1) ????license,
model, color(Set 2)
Set 1
3ABC123 Acura TL Silver 4MIS345 Honda Accord DX
White 4MIS837 Honda Accord LX Black 4LLP394 Lexus
GS400 Silver 4NOP934 Acura CL MetBlue
Set 2
4TLC394 Mercedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4LLP394 Lexus
GS400 Silver 4GAS294 Lexus LS430 GoldMet
32
Set Difference
Reversed
??attribute-1, ..., attribute-n(S)
????attribute-1, ..., attribute-n(R)
R
S
33
Set Difference
??license, model, color(Set 2) ????license,
model, color(Set 1)
Set 1
3ABC123 Acura TL Silver 4MIS345 Honda Accord DX
White 4MIS837 Honda Accord LX Black 4LLP394 Lexus
GS400 Silver 4NOP934 Acura CL MetBlue
4TLC394 Mercedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4GAS294 Lexus
LS430 GoldMet
Set 2
4TLC394 Mercedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4LLP394 Lexus
GS400 Silver 4GAS294 Lexus LS430 GoldMet
34
Intersection

• An intersection consists of the set of all tuples
  that are in both R and S.
• R and S must be union-compatible (i.e., same
  attributes)

??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
?
R
S
35
Intersection
Set 1
3ABC123 Acura TL Silver 4MIS345 Honda Accord DX
White 4MIS837 Honda Accord LX Black 4LLP394 Lexus
GS400 Silver 4NOP934 Acura CL MetBlue
4LLP394 Lexus GS400 Silver
Set 2
4TLC394 Mercedes CLK320 Red 4MSE291 BMW 525i
Teal 5AMD042 BMW M Roadster BlueMet 4LLP394 Lexus
GS400 Silver 4GAS294 Lexus LS430 GoldMet
36
Cartesian Product

• The Cartesian product defines a relation that is
  the concatenation of every tuple of relation R
  with every tuple of relation S
• I ? J tuples
• N M attributes

??attribute-1, ..., attribute-n(R)
????attribute-1, ..., attribute-n(S)
37
Cartesian Product
Cartesian Product
Relation R
Relation S
a, b, u, v a, b, w, x a, b, y, z c, d, u ,v c, d,
w, x c, d, y, z e, f, u, v e, f, w, x e, f, y, z
a, b c, d e, f
u, v w, x y, z



R ? S
38
Joins

• Theta join
• Equi-join
• Natural join
• Outer join
• Semi-join

Selection performed on two relations, R and S,
that share at least a single common attribute
39
Theta-Join vs. Equi-Join
R FS

• Theta-joinA resulting relation that contains
  tuples satisfying the predicate F from the
  Cartesian product of R and S
• The predicate F is of the formwhere ? may be
  one of the comparison operators lt, lt, gt, gt, ltgt
• Equi-join
• Where ? is

R.aj ? S.bj
R R.aj ? S.bj S
40
Theta Join
(? model (Cars)) cars.make lt MB (?
URL(Manufacturers))
Cars
Manufacturers
LS340 Lexus GS300 Lexus GS430 Lexus C320 MB E320 M
B S430 MB 325i BMW 523i BMW 530i BMW
Lexus www.lexus.com MB www.mercedesbenz.com BMW ww
w.bmw.com
?URL
model
URL
LS340 www.lexus.com GS300 www.lexus.com GS430 www.
lexus.com 325i www.bmw.com 523i www.bmw.com 530i w
ww.bmw.com
?model
41
Equi-Join
(? model(Cars)) cars.makemanufacturers
.make(? URL(Manufacturers))
Cars
Manufacturers
LS340 Lexus GS300 Lexus GS430 Lexus C320 MB E320 M
B S430 MB 325i BMW 523i BMW 530i BMW S8 Audi
Lexus www.lexus.com MB www.mercedesbenz.com BMW ww
w.bmw.com Porsche www.porsche.com
model
URL
LS340 www.lexus.com GS300 www.lexus.com GS430 www.
lexus.com C320 www.mercedesbenz.com E320 www.merce
desbenz.com S430 www.mercedesbenz.com 325i www.bmw
.com 523i www.bmw.com 530i www.bmw.com
42
Natural Join

• Equi-join of two relations R and S over all
  common attributes x
• One occurrence of each common attribute is
  eliminated from the result

R S
43
Natural Join
(? model, make (Cars)) cars.makemanufacturer
s .make (? make, URL(Manufacturers))
Cars
Manufacturers
LS340 Lexus GS300 Lexus GS430 Lexus C320 MB E320 M
B S430 MB 325i BMW 523i BMW 530i BMW
Lexus www.lexus.com MB www.mercedesbenz.com BMW ww
w.bmw.com
LS340 Lexus www.lexus.com GS300 Lexus www.lexus.co
m GS430 Lexus www.lexus.com C320 MB www.mercedesbe
nz.com E320 MB www.mercedesbenz.com S430 MB www.me
rcedesbenz.com 325i BMW www.bmw.com 523i BMW www.b
mw.com 530i BMW www.bmw.com
Make not repeated
44
Outer Join

• A join in which tuples from R that do not have
  matching values in the common attributes of S are
  also included in the result relation.
• Missing values in the second relation are set to
  null
• Right outer join (all tuples on right kept) vs.
  left outer join (all tuples on the left kept)
  vs. full outer join (all tuples on both sides
  kept)

R S
45
Left Outer Join
? model (Cars) cars.makemanufacturers.make
? URL(Manufacturers)
Cars
Manufacturers
LS340 Lexus GS300 Lexus GS430 Lexus C320 MB E320 M
B S430 MB 325i BMW 523i BMW 530i BMW S8 Audi
Lexus www.lexus.com MB www.mercedesbenz.com BMW ww
w.bmw.com Porsche www.porsche.com
Match all the models in Cars to the URLs in
Manufacturers
LS340 www.lexus.com GS300 www.lexus.com GS430 www.
lexus.com C320 www.mercedesbenz.com E320 www.merce
desbenz.com S430 www.mercedesbenz.com 325i www.bmw
.com 523i www.bmw.com 530i www.bmw.com S8 null
No match found
46
Right Outer Join
? model (Cars) cars.makemanufacturers.make
? URL(Manufacturers)
Cars
Manufacturers
LS340 Lexus GS300 Lexus GS430 Lexus C320 MB E320 M
B S430 MB 325i BMW 523i BMW 530i BMW S8 Audi
Lexus www.lexus.com MB www.mercedesbenz.com BMW ww
w.bmw.com Porsche www.porsche.com
Match all the URLs in Manufacturers to the models
in Cars
LS340 www.lexus.com GS300 www.lexus.com GS430 www.
lexus.com C320 www.mercedesbenz.com E320 www.merce
desbenz.com S430 www.mercedesbenz.com 325i www.bmw
.com 523i www.bmw.com 530i www.bmw.com null
www.porsche.com
No match found
47
Division

• Consists of the set of tuples from R defined over
  the attributes C that match the combination of
  every tuple in S

????attribute-x(R) ????attribute-x(?
predicate(S))
Select
Project R ??T1
Project of Select ??T2
Occurrences in project T1 that match the pattern
in T2
48
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))(Identify all renters who have viewed all
properties with 3 rooms)
Viewing
Rno Pno Date Comment CR56 PA14 5/24/95 Too
small CR76 PG4 4/20/95 Too remote CR56 PG4 5/26/95
CR62 PA14 5/14/95 No dining room CR56 PG36 4/28/
95
Rno Renter Number
Property_for_Rent
Pno Street Area City Pcode Type Rooms Rent Ono Sno
Bno PA14 6 PL94
4 PG4 3 PG36 3 PG21
5 PG16 4
?????
49
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))(Select Property_for_Rent with rooms 3)
Property_for_Rent
Pno Street Area City Pcode Type Rooms Rent Ono Sno
Bno PA14 6 PL94
4 PG4 3 PG36 3 PG21
5 PG16 4
?????
(? rooms3(Property_for_Rent)
Pno Street Area City Pcode Type Rooms Rent Ono Sno
Bno PG4 3 PG36 3
?????
50
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))(Project proper number)
Select(Property_for_Rent)
Pno Street Area City Pcode Type Rooms Rent Ono Sno
Bno PG4 3 PG36 3
?????
??pno(? rooms3(Property_for_Rent)
Pno PG4 PG36
All properties for rent with 3 rooms.
51
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))(Project renter number and property number)
Viewing
Rno Pno Date Comment CR56 PA14 5/24/95 Too
small CR76 PG4 4/20/95 Too remote CR56 PG4 5/26/95
CR62 PA14 5/14/95 No dining room CR56 PG36 4/28/
95
Rno Pno CR56 PA14 CR76 PG4 CR56 PG4 CR62 PA14
CR56 PG36
??rno,pno
52
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))
All rental properties with 3 rooms
Renters who have viewed properties
Rno Pno CR56 PA14 CR76 PG4 CR56 PG4 CR62 PA14
CR56 PG36
Pno PG4 PG36
Who has viewed all rental properties with 3 rooms?
53
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))
All rental properties with 3 rooms
Renters who have viewed properties
Rno Pno CR56 PA14 CR76 PG4 CR56 PG4 CR62 PA14
CR56 PG36
Pno PG4 PG36
Who has viewed all rental properties with 3 rooms?
Renter CR76 has viewed PG4, but not PG36
54
??rno,pno(Viewing) ????pno(? rooms3(Property_for_
Rent))(Identify all renters who have viewed all
properties with three rooms)
All rental properties with 3 rooms
Renters who have viewed properties
Pno PG4 PG36

Who has viewed all rental properties with 3 rooms?
Renter number CR56 has viewed all properties with
3 rooms
Rno CR56
55
Relational Calculus

• Specifies what is to be retrieved rather than how
  to retrieve it
• Predicate (truth-value function arguments)
• Proposition (arguments replaced with values
  including the predicate)For all x, if P is
  true (e.g., for all x if predicate (x gt 100) is
  true)

x P(x)
Where P is the predicate
56
Tuple Oriented vs. Domain Oriented Relational
Calculus

• Tuple oriented (entities or rows)Finding tuples
  for which a predicate is true
• Tuple variables (ranges over a named relation)
• Quantifiers
• Existential (?) there exists (one instance)
• Universal (?) for all
• Domain oriented (attributes or columns)Test for
  membership condition
• Variables that take their values from domains

Number of tuples to be applied to
57
SQL (pronounced sequel)

• Projection
• Selection

58
Structured Query Language (SQL)

• Transform-oriented language
• DDL (data definition language)
• DML (data manipulation language)
• Non-procedural (no logic structure required)
• Specify what information is required, rather than
  how that information should be retrieved
• Format-free (not bound to columns)
• English-like (although abbreviated)
• First standard database language to gain wide
  acceptance
• ISO 1992 standards (today ISO 2003)

59
SQL Terminology

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

column
Row
60
SELECT Basic Syntax

• SELECT
• DISTINCT ALL column-list AS alias
  , ...
• FROM table-name alias , ...
• WHERE condition
• GROUP BY column-list HAVING condition
• ORDER BY column-list

optional select one option
61
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)

62
ProjectionPerformed on a Single Table

• ? 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
63
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.
64
SelectionPerformed on a Single Table

• ? 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)
65
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
  

66
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

67
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

All columns
68
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.
69
Projection on a SelectionPerformed on a Single
Table
? product_code, product_description (?
manufacturer_code SON or PAN (Products))
Column list
Predicate (condition)

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

Only one condition has to be true
70
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.
71
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.
Both conditions must be true
72
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
73
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.
74
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
75
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.
76
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.

77
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.
78
Pattern Match with LIKE and UPPER Function

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

Changes to upper case
79
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.
80
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
81
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
82
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.
83
Using DISTINCT

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

84
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.
85
(No Transcript)