Structured Query Language – ContinuedThe Story Thus FarNext Up: SubqueriesSubqueries as ValuesSubqueries as Relations – in FROMSubqueries as Relations – in WHERESlide 7Slide 8ExampleSubqueries SummaryCombining RelationsUnion, Intersection, and DifferenceSQL Goofiness – Sets vs. BagsSlide 14Slide 15Theta JoinSlide 17Slide 18Natural JoinOuter JoinsCross ProductReportingAggregationAggregation and DuplicatesSlide 25GroupingExample: GroupingSlide 28HavingExample: HavingSlide 31Modifying the DatabaseInsertionDeletionUpdatesSlide 36Slide 37Slide 38Slide 39Structured Query Language – ContinuedRose-Hulman Institute of TechnologyCurt CliftonThe Story Thus FarSELECT … FROM … WHERESELECT * …SELECT Foo AS Bar …SELECT expression …SELECT … FROM … WHERE … LIKE …SELECT … FROM Foo, Bar …SELECT … FROM Foo f1, Foo f2 …Next Up: SubqueriesAs valuesAs relationsSubqueries as ValuesOnly allowed when subquery evaluates to single valueRun-time error otherwiseExample: Find the restaurants that sell Slice for the price the Joe's charges for PepsiSubqueries as Relations – in FROMSELECT Likes.customer, mix.soda1, mix.soda2FROM Likes,(SELECT s1.name AS soda1, s2.name AS soda2FROM Soda s1, Soda s2WHERE s1.manf = s2.manfAND s1.name < s2.name)AS mixWHERE Likes.soda = mix.soda1Subqueries as Relations – in WHEREvalue IN relationEvaluates to true if relation contains valueSELECT *FROM SodaWHERE name IN (SELECT sodaFROM LikesWHERE customer = 'Fred')Subqueries as Relations – in WHEREEXISTS relationEvaluates to true if relation is non-emptyFind every soda where its manufacturer does not make anything elseSELECT nameFROM Soda s1WHERE NOT EXISTS (SELECT *FROM Soda s2WHERE s2.manf = s1.manf AND s2.name <> s1.name)Subqueries as Relations – in WHEREANYx comp ANY(relation)comp can be <, >, =, <>, >=, <=Evaluates to true if comparison holds for any tuple in relationALLx comp ALL(relation)comp can be <, >, =, <>, >=, <=Evaluates to true if comparison holds for every tuple in relationExampleSELECT sodaFROM SellsWHERE price >= ALL(SELECT priceFROM Sells)Subqueries SummaryAs valuesAs relations in FROM clauseAs relations in WHERE clauseINEXISTSANYALLCombining RelationsUnion, Intersection, DifferenceJoinsUnion, Intersection, and DifferenceUnion(subquery) UNION (subquery )Intersection(subquery) INTERSECT (subquery)Difference(subquery) EXCEPT (subquery)SQL Goofiness – Sets vs. BagsBags by defaultSELECTSets by defaultUNIONINTERSECTEXCEPTOverriding defaultsSELECT DISTINCTUNION ALLCannot overrideCannot overrideExampleFind all the different prices charged for sodasExampleFind all the different prices charged for sodasSELECT DISTINCT price FROM SellsTheta JoinSyntax:SELECT …FROM table1 JOIN table2 ON condition…ExampleGive name and phone number of all customers that frequent Joe's SushiExampleSELECT name, phoneFROM Customer JOIN FrequentsON name = customerWHERE rest = 'Joe''s Sushi'Compare:SELECT name, phoneFROM Customer, FrequentsWHERE name = customerAND rest = 'Joe''s Sushi'Natural JoinNot in SQL ServerBut some DBMS allow:SELECT …FROM table1 NATURAL JOIN table2Outer JoinsRecall: solution to dangling tuple problemMake sure every tuple shows up, even if no “mate”, by inserting nulls if neededThree basic forms:SELECT … FROM t1 LEFT OUTER JOIN t2SELECT … FROM t1 RIGHT OUTER JOIN t2SELECT … FROM t1 OUTER JOIN t2Cross ProductPossible, though less commonSELECT …FROM table1 CROSS JOIN table2Or just write:SELECT …FROM table1, table2ReportingAggregationGroupingAggregationCalculations over rowsExample:SELECT AVG(price)FROM SellsWHERE soda = 'Pepsi'Other aggregations:SUMAVGCOUNT, COUNT(*)MIN, MAX“Let me explain. No, would take too long. Let me sum up.”Aggregation and Duplicates Can use DISTINCT inside an aggregationExample – Find the number of different prices charged for PepsiAggregation and Duplicates Can use DISTINCT inside an aggregationExample – Find the number of different prices charged for PepsiSELECT COUNT(DISTINCT price)FROM SellsWHERE soda = 'Pepsi'GroupingFor aggregating subsections of resultSELECT …FROM …WHERE …GROUP BY attr,…Example: GroupingFind the average price for each sodaExample: GroupingFind the average price for each sodaSELECT soda, AVG(price)FROM SellsGROUP BY sodaHavingLike a WHERE clause for groupsSELECT …FROM …WHERE … -- filter rowsGROUP BY … -- group rowsHAVING … -- filter groupsExample: HavingFind the average price of those sodas that are served by at least three restaurantsExample: HavingFind the average price of those sodas that are served by at least three restaurantsSELECT soda, AVG(price)FROM SellsGROUP BY sodaHAVING COUNT(rest) >= 3Modifying the DatabaseInsertDeleteUpdateInsertionSingle tuple, quick and dirty:INSERT INTO tableVALUES (value1, …)Single tuple, more robust:INSERT INTO table(attr1, …)VALUES (value1, …)Many tuples:INSERT INTO table (subquery)DeletionSingle tuple:DELETE FROM table WHERE conditionAll tuples (zoinks!):DELETE FROM tableUpdatesSyntax:UPDATE tableSET attr1 = expr1, … -- attributes, new valuesWHERE condition -- rows to changeExampleChange Fred's phone number to 555-1212ExampleChange Fred's phone number to 555-1212UPDATE CustomerSET phone = '555-1212'WHERE name = 'Fred'ExampleRaise all prices by 10%ExampleRaise all prices by 10%UPDATE SellsSET price = (price *
View Full Document