SQL Queries Principal form: desired attributes SELECT tuple - PDF document

SQL Queries Principal form: � desired attributes SELECT tuple v ariables | FROM range o v er relations condition ab out t.v.'s; WHERE 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

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

F ormal Seman tics of Single-Relati on SQL Query 1. Start with the relation in the clause. FROM 2. Apply (bag) , using condition in WHERE � clause. 3. Apply (extended, bag) using terms in � clause. SELECT Equiv alen t Op erational Seman tics Imagine a ranging o v er all tuples of tuple variable the relation. F or eac h tuple: Chec k if it satis�es the clause. � WHERE Prin t the v alues of terms in SELECT , if so. � 3

Star as List of 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

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

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 clause OK. � WHERE 6

T ric k: If y ou 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

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 o single-quotes in a c haracter string � represen t one single quote. Conditions in clause can use logical � WHERE op erators AND , OR , and paren theses in the NOT usual w a y . Remem b er: SQL is insensitive . Keyw ords � c ase lik e or can b e written upp er/lo w er SELECT AND case as y ou lik e. ✦ Only inside quoted strings do es case matter. 8

P atterns stands for an y string. � % stands for an y one c haracter. � � \A ttribute pattern" is a condition that LIKE is true if the string v alue of the attribute matc hes the pattern. ✦ Also for negation. NOT LIKE 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

Nulls In place of a v alue in a tuple's comp onen 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 only pro duces tuples if the WHERE - � condition ev aluates to ( UNKNOWN is not TRUE su�cen t). 10

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 � condition is a tautology . WHERE 11

3-V alued Logic Think of 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 of the excluded middle, i.e., OR NOT = TRUE p p F or 3-v alued logic: if = unkno wn, then left � p 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

Multirelatio n Queries List of relations in clause. � FROM Relation-dot-att ribut e disam biguates � attributes from sev eral relations. Example Find the b eers that the frequen ters of 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

F ormal Seman tics of Multirelation Queries Same as for single relation, but start with the pro duct of 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 oin ting to � a tuple of their relation, in all com binations (e.g., nested lo ops). If the curren t assignmen t of tuple-v ariabl es to � tuples mak es the true, then output the WHERE terms of the SELECT . 14

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

Explicit T uple V ariables Sometimes w e need to refer to t w o or more copies of a relation. Use as aliases of the relations. � tuple variables Example Find pairs of 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 b et w een relation and its tuple � AS v ariable; Oracle do es not. Note that is needed to � b1.name < b2.name a v oid pro ducing (Bud, Bud) and to a v oid pro ducing a pair in b oth orders. 16

Sub queries Result of a select-from-where query can b e used in the where-clause of 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 rule : an attribute refers � sc oping to the most closely nested relation with that attribute. P aren theses around sub query are essen tial. � 17

The IN Op erator \T uple relation" is true i� the tuple is in the IN relation. Example Find the name and man ufacturer of 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: IN . � NOT 18

EXISTS \ EXISTS (relati on)" 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 outer in � Beers the inner sub query , w e need to giv e the outer a tuple v ariable, in this example. b1 A sub query that refers to v alues from a � surrounding query is called a c orr elate d query . sub 19

Quan ti�ers and b eha v e as existen tial and univ ersal ANY ALL quan ti�ers, 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 of y ou" vs. \I am fatter than all of y ou." 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