New version page

# Berkeley COMPSCI 186 - SQL: The Query Language Part 2

Pages: 28
Documents in this Course

11 pages

20 pages

4 pages

34 pages

28 pages

16 pages

3 pages

26 pages

16 pages

3 pages

24 pages

62 pages

38 pages

39 pages

55 pages

8 pages

22 pages

33 pages

29 pages

5 pages

5 pages

47 pages

7 pages

11 pages

30 pages

25 pages

44 pages

2 pages

5 pages

2 pages

2 pages

8 pages

5 pages

5 pages

5 pages

3 pages

28 pages

5 pages

5 pages

6 pages

19 pages

5 pages

3 pages

30 pages

15 pages

4 pages

6 pages

3 pages

3 pages

22 pages

26 pages

5 pages

5 pages

40 pages

40 pages

30 pages

7 pages

34 pages

5 pages

5 pages

34 pages

5 pages

47 pages

6 pages

52 pages

2 pages

5 pages

6 pages

33 pages

27 pages

36 pages

## This preview shows page 1-2-3-26-27-28 out of 28 pages.

View Full Document

End of preview. Want to read all 28 pages?

View Full Document
Unformatted text preview:

SQL: The Query Language Part 2Example InstancesQueries With GROUP BYGroup By ExamplesConceptual EvaluationFind the number of reservations for each red boat.Slide 8Queries With GROUP BY and HAVINGSlide 10Find the age of the youngest sailor with age  18, for each rating with at least 2 such sailorsRelational Division Re-visitedSlide 13SELECT S.name, S.sid FROM Sailors S, reserves R WHERE S.sid = r.sid GROUP BY S.name, S.sid HAVING COUNT(DISTINCT R.bid) = Select COUNT (*) FROM BoatsINSERTDELETE & UPDATENull ValuesJoinsInner JoinSELECT s.sid, s.name, r.bid FROM Sailors s INNER JOIN Reserves r ON s.sid = r.sidLeft Outer JoinSELECT s.sid, s.name, r.bid FROM Sailors s LEFT OUTER JOIN Reserves r ON s.sid = r.sidRight Outer JoinSELECT r.sid, b.bid, b.name FROM Reserves r RIGHT OUTER JOIN Boats b ON r.bid = b.bidFull Outer JoinSELECT r.sid, b.bid, b.name FROM Reserves r FULL OUTER JOIN Boats b ON r.bid = b.bidViewsSlide 28SQL: The Query LanguagePart 2CS186, Fall 2005R & G - Chapter 5 The important thing is not tostop questioning.Albert EinsteinExample Instancessidsnameratingage22Dustin745.031Lubber855.595Bob363.5bidbnamecolor101Interlakeblue102Interlakered103Clippergreen104Marineredsidbidday2210110/10/969510311/12/96ReservesSailorsBoatsQueries With GROUP BYThe target-list contains (i) list of column names & (ii) terms with aggregate operations (e.g., MIN (S.age)).– column name list (i) can contain only attributes from the grouping-list. SELECT [DISTINCT] target-listFROM relation-list[WHERE qualification]GROUP BY grouping-list•To generate values for a column based on groups of rows, use aggregate functions in SELECT statements with the GROUP BY clauseGroup By ExamplesSELECT S.rating, AVG (S.age)FROM Sailors SGROUP BY S.rating For each rating, find the average age of the sailorsFor each rating find the age of the youngestsailor with age  18SELECT S.rating, MIN (S.age)FROM Sailors SWHERE S.age >= 18GROUP BY S.ratingConceptual Evaluation•The cross-product of relation-list is computed, tuples that fail qualification are discarded, `unnecessary’ fields are deleted, and the remaining tuples are partitioned into groups by the value of attributes in grouping-list. •One answer tuple is generated per qualifying group.•If DISTINCT is specified: drop duplicate answer tuples.SELECT [DISTINCT] target-listFROM relation-list[WHERE qualification]GROUP BY grouping-listSELECT S.rating, MIN (S.age)FROM Sailors SWHERE S.age >= 18GROUP BY S.rating sidsnameratingage22dustin745.031lubber855.571zorba1016.064horatio735.029brutus133.058rusty1035.01. Form cross product ratingage133.0745.0735.0855.51035.02. Delete unneeded columns, rows; form groups3. Perform Aggregationratingage133.0735.0855.01035.0Answer TableFind the number of reservations for each red boat.•Grouping over a join of two relations.SELECT B.bid, COUNT(*) AS numresFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’GROUP BY B.bidSELECT B.bid, COUNT (*) AS scountFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’GROUP BY B.bid1b.bid b.color r.bid101 blue 101102 red 101103 green 101104 red 101101 blue 102102 red 102103 green 102104 red 102b.bid b.color r.bid102 red 1022b.bid scount102 1answerQueries With GROUP BY and HAVING•Use the HAVING clause with the GROUP BY clause to restrict which group-rows are returned in the result setSELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualificationConceptual Evaluation•Form groups as before.•The group-qualification is then applied to eliminate some groups. –Expressions in group-qualification must have a single value per group!–That is, attributes in group-qualification must be arguments of an aggregate op or must also appear in the grouping-list. (SQL does not exploit primary key semantics here!)•One answer tuple is generated per qualifying group.Find the age of the youngest sailor with age  18, for each rating with at least 2 such sailorsSELECT S.rating, MIN (S.age)FROM Sailors SWHERE S.age >= 18GROUP BY S.ratingHAVING COUNT (*) > 1rating735.0Answer relationratingage133.0745.0735.0855.51035.02ratingm-agecount133.01735.02855.011035.013sidsnameratingage22Dustin745.031lubber855.571zorba1016.064horatio735.029brutus133.058rusty1035.0Relational Division Re-visitedSELECT S.snameFROM Sailors SWHERE NOT EXISTS (SELECT B.bid FROM Boats B WHERE NOT EXISTS (SELECT R.bid FROM Reserves R WHERE R.bid=B.bid AND R.sid=S.sid))Sailors S such that ...there is no boat B without ...a Reserves tuple showing S reserved BFind sailors who’ve reserved all boats.•Can you do this using Group By and Having?SELECT S.nameFROM Sailors S, reserves RWHERE S.sid = R.sid GROUP BY S.name, S.sid HAVING COUNT(DISTINCT R.bid) = ( Select COUNT (*) FROM Boats) Find sailors who’ve reserved all boats.Note: must have both sid and name in the GROUP BYclause. Why? Relational Division Re-visitedSELECT S.name, S.sidFROM Sailors S, reserves RWHERE S.sid = r.sid GROUP BY S.name, S.sid HAVING COUNT(DISTINCT R.bid) = Select COUNT (*) FROM Boats s.name s.sid r.sid r.bidDustin 22 22 101Lubber 31 22 101Bob 95 22 101Dustin 22 95 102Lubber 31 95 102Bob 95 95 102s.name s.sid bcountDustin 22 1Bob 95 1bidbnamecolor101Interlakeblue102Interlakered103Clippergreen104MarineredCount (*) from boats = 4Apply having clause to groupss.name s.sidINSERT INSERT INTO Boats VALUES ( 105, ‘Clipper’, ‘purple’)INSERT INTO Boats (bid, color) VALUES (99, ‘yellow’)You can also do a “bulk insert” of values from onetable into another:INSERT INTO TEMP(bid)SELECT r.bid FROM Reserves R WHERE r.sid = 22;(must be type compatible)INSERT [INTO] table_name [(column_list)]VALUES ( value_list)INSERT [INTO] table_name [(column_list)]<select statement>DELETE & UPDATE DELETE FROM Boats WHERE color = ‘red’ DELETE FROM Boats b WHERE b. bid = (SELECT r.bid FROM Reserves R WHERE r.sid = 22)Can also modify tuples using UPDATE statement.UPDATE BoatsSET Color = “green”WHERE bid = 103;DELETE [FROM] table_name[WHERE qualification]Null Values•Field values in a tuple are sometimes unknown (e.g., a rating has not been assigned) or inapplicable (e.g., no spouse’s name). –SQL provides a special value null for such situations.•The presence of null complicates many issues.

View Full Document
Unlocking...