1SQL: The Query LanguagePart 2R & G - Chapter 5The important thing is not tostop questioning.Albert EinsteinExample Database633Bob95558Lubber31457Dustin22ageratingsnamesidSailors11/12/061039510/10/0610122daybidsidReservesredMarine104greenClipper103redInterlake102blueInterlake101colorbnamebidBoatsConceptual SQL EvaluationSELECT [DISTINCT] target-listFROM relation-listWHERE qualificationGROUP BY grouping-listHAVING group-qualificationSELECTRelationcross-productApply selections(eliminate rows)Project away columns(just keep those used inSELECT, GBY, HAVING)WHEREFROM GROUP BYForm groups& aggregateHAVINGEliminategroups[DISTINCT]EliminateduplicatesSorting the Results of a Query• ORDER BY column [ ASC | DESC] [, ...]• Can order by any column in SELECT list,including expressions or aggs:SELECT S.rating, S.sname, S.ageFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sidAND R.bid=B.bid AND B.color=‘red’ORDER BY S.rating, S.sname;SELECT S.sid, COUNT (*) AS redrescntFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sidAND R.bid=B.bid AND B.color=‘red’GROUP BY S.sidORDER BY redrescnt DESC;Null Values• Field values in a tuple are sometimes unknown (e.g., arating has not been assigned) or inapplicable (e.g., nospouse’s name).– SQL provides a special value null for such situations.• The presence of null complicates many issues. E.g.:– Special operators needed to check if value is/is not null.– Is rating>8 true or false when rating is equal to null? Whatabout AND, OR and NOT connectives?– We need a 3-valued logic (true, false and unknown).– Meaning of constructs must be defined carefully. (e.g.,WHERE clause eliminates rows that don’t evaluate to true.)– New operators (in particular, outer joins) possible/needed.JoinsExplicit join semantics needed unless it is an INNER join(INNER is default)SELECT (column_list)FROM table_name [INNER | {LEFT |RIGHT | FULL } OUTER] JOIN table_name ON qualification_listWHERE …2Inner JoinOnly the rows that match the search conditions arereturned.SELECT s.sid, s.name, r.bidFROM Sailors s INNER JOIN Reserves rON s.sid = r.sidReturns only those sailors who have reserved boatsSQL-92 also allows:SELECT s.sid, s.name, r.bidFROM Sailors s NATURAL JOIN Reserves r“NATURAL” means equi-join for each pair of attributeswith the same nameSELECT s.sid, s.name, r.bidFROM Sailors s INNER JOIN Reserves rON s.sid = r.sids.sid s.name r.bid22 Dustin 10195 Bob 103sid sname rating age 22 Dustin 7 45.0 31 Lubber 8 55.5 95 Bob 3 63.5 sid bid day 22 101 10/10/96 95 103 11/12/96 Left Outer JoinLeft Outer Join returns all matched rows, plus allunmatched rows from the table on the left ofthe join clause(use nulls in fields of non-matching tuples)SELECT s.sid, s.name, r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid = r.sidReturns all sailors & information on whether theyhave reserved boatsSELECT s.sid, s.name, r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid = r.sids.sid s.name r.bid22 Dustin 10195 Bob 10331 Lubbersid sname rating age 22 Dustin 7 45.0 31 Lubber 8 55.5 95 Bob 3 63.5 sid bid day 22 101 10/10/96 95 103 11/12/96 Right Outer JoinRight Outer Join returns all matched rows, plusall unmatched rows from the table on the rightof the join clauseSELECT r.sid, b.bid, b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid = b.bidReturns all boats & information on which onesare reserved.SELECT r.sid, b.bid, b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid = b.bidr.sid b.bid b.name22 101 Interlake102 Interlake95 103 Clipper104 Marinebid bname color 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red sid bid day 22 101 10/10/96 95 103 11/12/963Full Outer JoinFull Outer Join returns all (matched orunmatched) rows from the tables on bothsides of the join clauseSELECT r.sid, b.bid, b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid = b.bidReturns all boats & all information onreservationsSELECT r.sid, b.bid, b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid = b.bidr.sid b.bid b.name22 101 Interlake102 Interlake95 103 Clipper104 MarineNote: in this case it is the same as the ROJ becausebid is a foreign key in reserves, so all reservations musthave a corresponding tuple in boats.bid bname color 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red sid bid day 22 101 10/10/96 95 103 11/12/96 Views: Defining External DBSchemasCREATE VIEW view_nameAS select_statementMakes development simplerOften used for securityNot instantiated - makes updates trickyCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bidSELECT bname, scount FROM Reds R, Boats B WHERE R.bid=B.bidAND scount < 10b.bid scount102 1RedsCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bidViews Instead of Relations in QueriesDiscretionary Access ControlGRANT privileges ON object TO users[WITH GRANT OPTION]• Object can be a Table or a View• Privileges can be:• Select• Insert• Delete• References (cols) – allow to create a foreignkey that references the specified column(s)• All• Can later be REVOKEd• Users can be single users or groups• See Chapter 17 for more details.Two more important topics• Constraints• SQL embedded in other languages4Integrity Constraints (Review)• An IC describes conditions that every legal instanceof a relation must satisfy.– Inserts/deletes/updates that violate IC’s are disallowed.– Can be used to ensure application semantics (e.g., sid isa key), or prevent inconsistencies (e.g., sname has to bea string, age must be < 200)•Types of IC’s: Domain constraints, primary keyconstraints, foreign key constraints, generalconstraints.–Domain constraints: Field values must be of right type.Always enforced.–Primary key and foreign key constraints: you know them.GeneralConstraints• Useful whenmore general ICsthan keys areinvolved.• Can use queriesto expressconstraint.• Checked on insertor update.• Constraints canbe named.CREATE TABLE Sailors( sid INTEGER,sname CHAR(10),rating INTEGER,age REAL,PRIMARY KEY (sid),CHECK ( rating >= 1 AND rating <= 10 )) CREATE TABLE Reserves( sname CHAR(10),bid INTEGER,day DATE,PRIMARY KEY (bid,day),CONSTRAINT noInterlakeResCHECK (`Interlake’ <>( SELECT B.bnameFROM Boats BWHERE
View Full Document
Unlocking...