[PDF] - SQL Queries Principal form: desired attributes SELECT tuple PDF Document

SLIDE 1 SQL Queries

Principal

form: SELECT desired attributes FROM tuple v ariables | range

v

er relations WHERE condition ab

ut

t.v.'s; Running example relation sc hema: Beers(name , manf) Bars(name , addr, license) Drinkers(name , addr, phone) Likes(drinker , beer ) Sells(bar , beer , price) Frequents(drinker , bar) 1

SLIDE 2 Example What b eers are made b y Anheuser-Busc h? Beers(name , manf) SELECT name FROM Beers WHERE manf = 'Anheuser-Busch' ;

Note

single quotes for strings. name Bud Bud Lite Mic helob 2

SLIDE 3 F

rmal

Seman tics

f

Single-Relati

n

SQL Query 1. Start with the relation in the FROM clause. 2. Apply (bag)

,

using condition in WHERE clause. 3. Apply (extended, bag)

using

terms in SELECT clause. Equiv alen t Op erational Seman tics Imagine a tuple variable ranging

v

er all tuples

f

the relation. F

r

eac h tuple:

Chec

k if it satises the WHERE clause.

Prin

t the v alues

f

terms in SELECT, if so. 3

SLIDE 4 Star as List

f

All A ttributes Beers(name , manf) SELECT * FROM Beers WHERE manf = 'Anheuser-Busch' ; name manf Bud Anheuser-Busc h Bud Lite Anheuser-Busc h Mic helob Anheuser-Busc h 4

SLIDE 5 Renaming columns Beers(name , manf) SELECT name AS beer FROM Beers WHERE manf = 'Anheuser-Busch' ; b eer Bud Bud Lite Mic helob 5

SLIDE 6 Expressions as V alues in Columns Sells(bar , beer , price) SELECT bar, beer, price*120 AS priceInYen FROM Sells; bar b eer priceInY en Jo e's Bud 300 Sue's Miller 360

Note

no WHERE clause OK. 6

SLIDE 7

T

ric k: If y

u

w an t an answ er with a particular string in eac h ro w, use that constan t as an expression. Likes(drinker , beer ) SELECT drinker, 'likes Bud' AS whoLikesBud FROM Likes WHERE beer = 'Bud'; drink er whoLik esBud Sally lik es Bud F red lik es Bud

7

SLIDE 8 Example Find the price Jo e's Bar c harges for Bud. Sells(bar , beer , price) SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud';

Note:

t w

single-quotes

in a c haracter string represen t

ne

single quote.

Conditions

in WHERE clause can use logical

p

erators AND, OR, NOT and paren theses in the usual w a y .

Remem

b er: SQL is c ase insensitive. Keyw

rds

lik e SELECT

r

AND can b e written upp er/lo w er case as y

u

lik e.

✦

Only inside quoted strings do es case matter. 8

SLIDE 9 P atterns

%

stands for an y string.

stands

for an y

ne

c haracter.

\A

ttribute LIKE pattern" is a condition that is true if the string v alue

f

the attribute matc hes the pattern.

✦

Also NOT LIKE for negation. Example Find drink ers whose phone has exc hange 555. Drinkers(name , addr, phone) SELECT name FROM Drinkers WHERE phone LIKE '%555- ';

Note

patterns m ust b e quoted, lik e strings. 9

SLIDE 10 Nulls In place

f

a v alue in a tuple's comp

nen

t.

In

terpretation is not exactly \missing v alue."

There

could b e man y reasons wh y no v alue is presen t, e.g., \v alue inappropriate." Comparing Nulls to V alues

3rd

truth v alue UNKNOWN.

A

query

nly

pro duces tuples if the WHERE- condition ev aluates to TRUE (UNKNOWN is not sucen t). 10

SLIDE 11 Example bar b eer price Jo e's bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00;

UNKNOWN

UNKNOWN

UNKNOWN
Jo

e's Bar is not pro duced, ev en though the WHERE condition is a tautology . 11

SLIDE 12 3-V alued Logic Think

f

true = 1; false = 0, and unkno wn = 1/2. Then:

AND

= min.

OR

= max.

NOT(x)

= 1

x.

Some Key La ws F ail to Hold Example: La w

f

the excluded middle, i.e., p OR NOT p = TRUE

F
r

3-v alued logic: if p = unkno wn, then left side = max(1/2,(1-1/2)) = 1/2 6= 1.

Lik

e bag algebra, there is no w a y kno wn to mak e 3-v alued logic conform to all the la ws w e exp ect for sets/2-v alued logic, resp ectiv ely . 12

SLIDE 13 Multirelatio n Queries

List
f

relations in FROM clause.

Relation-dot-att

ribut e disam biguates attributes from sev eral relations. Example Find the b eers that the frequen ters

f

Jo e's Bar lik e. Likes(drinker , beer ) Frequents(drinker , bar) SELECT beer FROM Frequents, Likes WHERE bar = 'Joe''s Bar' AND Frequents.drinke r = Likes.drinker; 13

SLIDE 14 F

rmal

Seman tics

f

Multirelation Queries Same as for single relation, but start with the pro duct

f

all the relations men tioned in the FROM clause. Op erational Seman tics Consider a tuple v ariable for eac h relation in the FROM.

Imagine

these tuple v ariables eac h p

in

ting to a tuple

f

their relation, in all com binations (e.g., nested lo

ps).
If

the curren t assignmen t

f

tuple-v ariabl es to tuples mak es the WHERE true, then

utput

the terms

f

the SELECT. 14

SLIDE 15 bar F requen ts drink er b eer drink er Lik es f l Sally Sally Jo e's 15

SLIDE 16 Explicit T uple V ariables Sometimes w e need to refer to t w

r

more copies

f

a relation.

Use

tuple variables as aliases

f

the relations. Example Find pairs

f

b eers b y the same man ufacturer. Beers(name , manf) SELECT b1.name, b2.name FROM Beers b1, Beers b2 WHERE b1.manf = b2.manf AND b1.name < b2.name;

SQL

p ermits AS b et w een relation and its tuple v ariable; Oracle do es not.

Note

that b1.name < b2.name is needed to a v

id

pro ducing (Bud, Bud) and to a v

id

pro ducing a pair in b

th
rders.

16

SLIDE 17 Sub queries Result

f

a select-from-where query can b e used in the where-clause

f

another query . Simplest Case: Sub query Returns a Single, Unary T uple Find bars that serv e Miller at the same price Jo e c harges for Bud. Sells(bar , beer , price) SELECT bar FROM Sells WHERE beer = 'Miller' AND price = (SELECT price FROM Sells WHERE bar = 'Joe''s Bar' AND beer = 'Bud' );

Notice

the sc

ping

rule: an attribute refers to the most closely nested relation with that attribute.

P

aren theses around sub query are essen tial. 17

SLIDE 18 The IN Op erator \T uple IN relation" is true i the tuple is in the relation. Example Find the name and man ufacturer

f

b eers that F red lik es. Beers(name , manf) Likes(drinker , beer ) SELECT * FROM Beers WHERE name IN (SELECT beer FROM Likes WHERE drinker = 'Fred' );

Also:

NOT IN. 18

SLIDE 19 EXISTS \EXISTS(relati

n)"

is true i the relation is nonempt y . Example Find the b eers that are the unique b eer b y their man ufacturer. Beers(name , manf) SELECT name FROM Beers b1 WHERE NOT EXISTS( SELECT * FROM Beers WHERE manf = b1.manf AND name <> b1.name );

Note

scoping rule: to refer to

uter

Beers in the inner sub query , w e need to giv e the

uter

a tuple v ariable, b1 in this example.

A

sub query that refers to v alues from a surrounding query is called a c

rr

elate d sub query. 19

SLIDE 20 Quan tiers ANY and ALL b eha v e as existen tial and univ ersal quan tiers, resp ectiv ely .

Bew

are: in common parlance, \an y" and \all" seem to b e synon yms, e.g., \I am fatter than an y

f

y

u"

vs. \I am fatter than all

f

y

u."

But in SQL: Example Find the b eer(s) sold for the highest price. Sells(bar , beer , price) SELECT beer FROM Sells WHERE price >= ALL( SELECT price FROM Sells ); Class Problem Find the b eer(s) not sold for the lo w est price. 20