Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
A portion of a university course on sql queries and semantics taught by arthur keller during the winter 2002 semester. It includes lecture notes on sql query structure, formal semantics, star schema, renaming columns, expressions as values, patterns, nulls, 3-valued logic, multi-relation queries, explicit tuple variables, subqueries, and the in operator. Students are expected to read specific sections, complete assignments, and work on projects related to these topics.
Typology: Study notes
1 / 21
This page cannot be seen from the preview
Don't miss anything!
Winter 2002^
Arthur Keller – CS 180
-^ Today: Jan. 22 (T)^ ◆^ SQL Queries.^ ◆^ Read Sections 6.1-6.2. Assignment 2 due.•^ Jan. 24 (TH)^ ◆^ Subqueries, Grouping and Aggregation.^ ◆^ Read Sections 6.3-6.4. Project Part 2 due.•^ Jan. 29 (T)^ ◆^ Modifications, Schemas, Views.^ ◆^ Read Sections 6.5-6.7. Assignment 3 due.•^ Jan. 31 (TH)^ ◆^ Constraints.^ ◆^ Read Sections 7.1-7.3, 7.4.1. Project Part 3 due.
Winter 2002^
Arthur Keller – CS 180
-^ Principal form:^ SELECT
desired attributes FROM^ tuple variables –– range over relations WHERE condition about tuple variables; Running example relation schema:^ Beers(name, manf)Bars(name, addr, license)Drinkers(name, addr, phone)Likes(drinker, beer)Sells(bar, beer,
price)
Frequents(drinker,
bar)
Winter 2002^
Arthur Keller – CS 180
clause.
2.^ Apply (bag)
σ, using condition in
clause.
3.^ Apply (extended, bag)
π^ using attributes in
SELECT
clause.
tuple variable
ranging over all tuples of
the relation. For each tuple:• Check if it satisfies the
clause.
-^ Print the values of terms in
, if so.
Winter 2002^
Arthur Keller – CS 180
name^
manf Bud^
Anheuser-Busch Bud Lite
Anheuser-Busch Michelob
Anheuser-Busch
Winter 2002^
Arthur Keller – CS 180
price*120 AS
priceInYen
FROM Sells; bar^
beer^
priceInYen Joe’s^
Bud^
Sue’s^
Miller^
-^ Note: no
clause is OK.
Winter 2002^
Arthur Keller – CS 180
-^ Trick: If you want an answer with a particularstring in each row, use that constant as anexpression.^ Likes(drinker, beer)SELECT drinker, 'likes Bud' AS
whoLikesBud
FROM LikesWHERE beer = 'Bud';^ drinker
whoLikesBud Sally^
likes Bud Fred^
likes Bud …^
Winter 2002^
Arthur Keller – CS 180
-^ % stands for any string.•^ _ stands for any one character.•^ “Attribute
pattern” is a condition that is true if the string value of the attribute matches thepattern.^ ◆^ Also
NOT LIKE
Find drinkers whose phone has exchange 555.^ Drinkers(name, addr, phone)SELECT nameFROM DrinkersWHERE phone LIKE '%555-_ _ _ _’; •^ Note patterns must be quoted, like strings.
Winter 2002^
Arthur Keller – CS 180
In place of a value in a tuple's component.•^ Interpretation is not exactly “missing value.”•^ There could be many reasons why no value ispresent,
e.g ., “value inappropriate.”
-^ A query only produces tuples if the
condition evaluates to
is not sufficient).
Winter 2002^
Arthur Keller – CS 180
Think of true = 1; false = 0, and unknown = 1/2. Then:•^ AND = min.•^ OR = max.•^ NOT(
x ) = 1 –
x.
i.e .,
p^ OR^ NOT
p^ =^ TRUE
-^ For 3-valued logic: if
p^ = unknown, then left side = max(1/2,(1–1/2)) = 1/
≠^ 1.
-^ Like bag algebra, there is no way known to make 3-valuedlogic conform to all the laws we expect for sets/2-valuedlogic, respectively.
Winter 2002^
Arthur Keller – CS 180
-^ List of relations in
clause.
bar)
SELECT beerFROM Frequents, LikesWHERE bar =
'Joe''s
Bar' AND
Frequents.drinker = Likes.drinker;
Winter 2002^
Arthur Keller – CS 180 bar Frequents
drinker^
beer
drinker
Likes
f^
l
Sally^
Sally Joe’s
Winter 2002^
Arthur Keller – CS 180
Sometimes we need to refer to two or more copies of a relation.•^ Use^ tuple variables
as aliases of the relations.
manf) SELECT b1.name,
b2.name FROM^ Beers
b1,^ Beers
b
WHERE^ b1.manf
=^ b2.manf
AND
b1.name
<^ b2.name;
-^ SQL permits
AS^ between relation and its tuple variable; Oracle does not.• Note that
b1.name < b2.name
is needed to avoid producing
(Bud, Bud) and to avoid producing a pair in both orders.
Winter 2002^
Arthur Keller – CS 180
“Tuple
IN^ relation” is true iff the tuple is in the relation.
(SELECT beerFROM LikesWHERE drinker = 'Fred’);
-^ Also:
Winter 2002^
Arthur Keller – CS 180 EXISTS
“EXISTS
(relation)” is true iff the relation is nonempty. ExampleFind the beers that are the unique beer by their manufacturer.^ Beers(name,
manf) SELECT name FROM^ Beers
b WHERE^ NOT^ EXISTS(SELECT^ * FROM^ BeersWHERE^ manf
=^ b1.manf
AND name^ <>
b1.name);
-^ Note scoping rule: to refer to outer
Beers
in the inner subquery,
we need to give the outer a tuple variable,
b1^ in this example.
-^ A subquery that refers to values from a surrounding query iscalled a
correlated subquery
.
