TA SectionApril 8, 20102Modifying the DatabaseThree kinds of modifications• Insertions• Deletions• UpdatesSometimes they are all called “updates”InsertionsGeneral form:INSERT INTO R(A1,…., An) VALUES (v1,…., vn)InsertionsProduct(name, listPrice, category)Purchase(buyer, seller, product, price)Missing attribute NULL.May drop attribute names if give them in order.INSERT INTO Purchase(buyer, seller, product, price)VALUES („Joe‟, „Fred‟, „wakeup-clock-espresso-machine‟,„The Sharper Image‟)Example: Insert a new purchase to the database:5InsertionsINSERT INTO PRODUCT(name)SELECT DISTINCT Purchase.productFROM PurchaseWHERE Purchase.date > “10/26/01”The query replaces the VALUES keyword.Here we insert many tuples into PRODUCT6DeletionsDELETE FROM PURCHASEWHERE seller = „Joe‟ ANDproduct = „Brooklyn Bridge‟Example:7UpdatesUPDATE PRODUCTSET price = price/2WHERE Product.name IN (SELECT productFROM PurchaseWHERE Date =„Oct, 25, 1999‟);Example:Aggregate Queries• Our SchemaAUTHOR(aid, name)AUTH_DOC(aid, did)DOCUMENT (did, title)DOC_WORD (did, word)WORD(word)AUTHOR DOCUMENT WORDAUTH_DOC DOC_WORDS• Find authors who wrote more than 20 docsSELECT name FROM AUTHOR aWHERE(SELECT COUNT(*) FROM AUTH_DOC ad WHERE ad.aid = a.aid) > 20SELECT name FROM AUTHOR a, AUTH_DOC ad WHERE a.aid = ad.aid GROUP BY a.aid, a.name HAVING COUNT(*) > 20• Find authors who have a vocabulary of more than 10,000 wordsSELECT name FROM AUTHOR WHERE (SELECT COUNT(DISTINCT word) FROM …) > 10000SELECT name FROM AUTHOR a, AUTH_DOC ad, DOC_WORDS dw WHERE a.aid = ad.aid AND ad.did = dw.did GROUP BY a.aid, a.name HAVING COUNT(DISTINCT word) > 10000• Find authors who have written a total 10,000 words(same queries as on previous slide, but drop keyword DISTINCT)• For each author, report the total number of wordsSELECT aid, COUNT(*) num FROM AUTHOR a, AUTH_DOC ad, DOC_WORDS dw WHERE a.aid = ad.aid AND ad.did = dw.did GROUP BY aid.• For each author, report average number of words per paper.SELECT aid, AVG(num) FROM (SELECT aid, did, COUNT(*) num FROM AUTHOR a, AUTH_DOC ad, DOC_WORDS dw WHERE … GROUP BY aid, did) t GROUP BY aid• Find author with highest average number of words per paperSELECT …WHERE NOT EXISTS (…)• Find words used by at least 10 authorsSELECT word FROM DOC_WORDS NATURAL JOIN AUTH_DOCGROUP BY wordHAVING COUNT(DISTINCT aid) >= 10• Find most frequently used wordSELECT word FROM DOC_WORDSGROUP BY wordHAVING (COUNT(*)) >= ALL(…)• Find the largest documentSELECT did FROM DOC_WORDSGROUP BY didHAVING COUNT(*) >= ALL(…)orHAVING NOT EXISTS(…)• Find authors who have written the largest documentSELECT nameFROM author aWHERE (SELECT COUNT(word) FROM DOC_WORDS dw, AUTH_DOC ad WHERE dw.did = ad.did AND ad.aid = a.aid) = (SELECT …)Existential and UniversialQuantifiers• Our SchemaLIKES(drinker, beer)FREQUENTS(drinker, bar)SERVES(bar, beer)• Find all drinkers that like some beer that is not served by the bar “Black Cat”SELECT l.drinkerFROM LIKES lWHERE l.beer NOT IN (SELECT s.beer FROM SERVES sWHERE S.bar = “Black Cat”)• Find drinkers that frequent some bar that serves some beer they likeSELECT f.drinker FROM FREQUENTS f, LIKES l, SERVES sWHERE l.drinker = f.drinkerAND l.beer = s.beer AND s.bar = f.barSELECT f.drinkerFROM FREQUENT fWHERE f.bar IN (SELECT bar FROM SERVES WHERE (drinker, beer) in LIKES)• Find drinkers that frequent only bars that serves some beer they likeSELECT drinkerFROM FREQUENTS fWHERE NOT EXISTS(SELECT beer FROM SERVES sWHERE s.bar = f.bar ANDNOT EXISTS(SELECT drinker FROM LIKES l WHERE l.drinker = f.drinker AND l.beer = s.beer)• Find drinkers that frequent some bar that serves only beers they like.SELECT f.drinkerFROM FREQUENTS fWHERE EXISTS(SELECT beer FROM SERVES sWHERE s.bar = f.bar ANDNOT EXISTS (SELECT beer FROM Serves s2 WHERE s2.bar = s.bar AND beer NOT IN (SELECT beer FROM Likes WHERELikes.drinker = f.drinker)))Can you improve this one?• Find drinkers that frequent only bars that serve some beer they likeSELECT drinkerFROM FREQUENTS fWHERE NOT EXISTS (SELECT beer FROM SERVES sWHERE s.bar = f.bar AND beer NOT IN(SELECT beer FROM Likes lWHERE l.drinker =
View Full Document
Unlocking...