Structured Query Language –ContinuedRose-Hulman Institute of TechnologyCurt CliftonThe Story Thus Far SELECT … FROM … WHERE SELECT * … SELECT Foo AS Bar … SELECT expression … SELECT … FROM … WHERE … LIKE … SELECT … FROM Foo, Bar … SELECT … FROM Foo f1, Foo f2 …Next Up: Subqueries As values As relationsSubqueries as Values Only allowed when subquery evaluates tosingle value Run-time error otherwise Example: Find the restaurants that sell Slicefor the price the Joe's charges for PepsiSubqueries as Relations – in FROM SELECT 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 WHERE value IN relation Evaluates to true if relation contains value SELECT *FROM SodaWHERE name IN (SELECT sodaFROM LikesWHERE customer = 'Fred')Subqueries as Relations – in WHERE EXISTS relation Evaluates to true if relation is non-empty Find every soda where its manufacturer does notmake anything else SELECT nameFROM Soda s1WHERE NOT EXISTS (SELECT *FROM Soda s2WHERE s2.manf = s1.manfAND s2.name <> s1.name)Subqueries as Relations – in WHERE ANY x comp ANY(relation) comp can be <, >, =, <>, >=, <= Evaluates to true if comparison holds for any tuple inrelation ALL x comp ALL(relation) comp can be <, >, =, <>, >=, <= Evaluates to true if comparison holds for every tuple inrelationExample SELECT sodaFROM SellsWHERE price >= ALL(SELECT priceFROM Sells)Subqueries Summary As values As relations in FROM clause As relations in WHERE clause IN EXISTS ANY ALLCombining Relations Union, Intersection, Difference JoinsUnion, Intersection, and Difference Union (subquery) UNION (subquery ) Intersection (subquery) INTERSECT (subquery) Difference (subquery) EXCEPT (subquery)SQL Goofiness – Sets vs. Bags Bags by default SELECT Sets by default UNION INTERSECT EXCEPT Overriding defaults SELECT DISTINCT UNION ALL Cannot override Cannot overrideExample Find all the different prices charged for sodasExample Find all the different prices charged for sodas SELECT DISTINCT priceFROM SellsTheta Join Syntax: SELECT …FROM table1 JOIN table2 ON condition…Example Give name and phone number of allcustomers that frequent Joe's SushiExample SELECT name, phoneFROM Customer JOIN FrequentsON name = customerWHERE rest = 'Joe''s Sushi' Compare: SELECT name, phoneFROM Customer, FrequentsWHERE name = customerAND rest = 'Joe''s Sushi'Natural Join Not in SQL Server But some DBMS allow: SELECT …FROM table1 NATURAL JOIN table2Outer Joins Recall: solution to dangling tuple problem Make sure every tuple shows up, even if no“mate”, by inserting nulls if needed Three basic forms: SELECT … FROM t1 LEFT OUTER JOIN t2 SELECT … FROM t1 RIGHT OUTER JOIN t2 SELECT … FROM t1 OUTER JOIN t2Cross Product Possible, though less common SELECT …FROM table1 CROSS JOIN table2 Or just write: SELECT …FROM table1, table2Reporting Aggregation GroupingAggregation Calculations over rows Example: SELECT AVG(price)FROM SellsWHERE soda = 'Pepsi' Other aggregations: SUM AVG COUNT, COUNT(*) MIN, MAX“Let me explain. No, would taketoo long. Let me sum up.”Aggregation and Duplicates Can use DISTINCT inside an aggregation Example – Find the number of different pricescharged for PepsiAggregation and Duplicates Can use DISTINCT inside an aggregation Example – Find the number of different pricescharged for Pepsi SELECT COUNT(DISTINCT price)FROM SellsWHERE soda = 'Pepsi'Grouping For aggregatingsubsections of result SELECT …FROM …WHERE …GROUP BY attr,…Example: Grouping Find the average price for each sodaExample: Grouping Find the average price for each soda SELECT soda, AVG(price)FROM SellsGROUP BY sodaHaving Like a WHERE clause for groups SELECT …FROM …WHERE … -- filter rowsGROUP BY … -- group rowsHAVING … -- filter groupsExample: Having Find the average price of those sodas that areserved by at least three restaurantsExample: Having Find the average price of those sodas that areserved by at least three restaurants SELECT soda, AVG(price)FROM SellsGROUP BY sodaHAVING COUNT(rest) >= 3Modifying the Database Insert Delete UpdateInsertion Single tuple, quick and dirty: INSERT INTO tableVALUES (value1, …) Single tuple, more robust: INSERT INTO table(attr1, …)VALUES (value1, …) Many tuples: INSERT INTO table (subquery)Deletion Single tuple: DELETE FROM table WHERE condition All tuples (zoinks!): DELETE FROM tableUpdates Syntax: UPDATE tableSET attr1 = expr1, … -- attributes, new valuesWHERE condition -- rows to changeExample Change Fred's phone number to 555-1212Example Change Fred's phone number to 555-1212 UPDATE CustomerSET phone = '555-1212'WHERE name = 'Fred'Example Raise all prices by 10%Example Raise all prices by 10% UPDATE SellsSET price = (price *