1SQL 2 – The SequelR&G, Chapter 5Lecture 10Administrivia• Homework 2 assignment now available– Due a week from Sunday• Midterm exam will be evening of 10/14Review – Query Optimization• External Sorting with Merge sort– It is possible to sort most tables in 2 passes• Join Algorithms– Nested Loops– Indexed Nested Loops– Sort-Merge Join– Hash Join• Relational Algebra EquivalencesReview – Query Optimization (cont.)• DBMSs keep statistics in system catalogs– Example from Postgres• Access Paths: scans and indexes• Query Plans a la System R– Consider all left-deep query trees– Consider different access plans for selections– Consider different algorithms for joinsQuery Optimization – Some Resources• Animation of Join Algorithmshttp://www.animal.ahrgr.de/en/AnimList_algo.html• System CatalogsPostGres catalogs: http://www.postgres.org/docs/7.2/interactive/pg-system-catalogs.html• Query Optimization“explain” command, type “explain <sql query>”Review - SQL• Data Definiton Language (DDL)– “create schema”, constraints, etc.• Data Manipulation Language (DML)– Range variables in Select clause– Expressions in Select, Where clauses– Set operators between queries:• Union, Intersect, Except/Minus– Set operators in nested queries:• In, Exists, Unique, <op> Any, <op> All– Aggregates: Count, Sum, Avg, Min, Max– Group By– Group By/Having2Today – Further SQL Features• Insert• Delete• Update• Null Values – Outer Joins• Views• Order By• Access Control• Integrity ConstraintsINSERT INSERT INTO Boats VALUES ( 105, ‘Clipper’, ‘purple’)INSERT INTO Boats (bid, color) VALUES (99, ‘yellow’)“bulk insert” from one table to another (must be type compatible):INSERT INTO TEMP(bid)SELECT r.bid FROM Reserves R WHERE r.sid = 22;“bulk insert” from files (in Postgres)Copy 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• Values 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. E.g.:– Special operators needed to check if value is/is not null. – “rating>8” - true or false when rating is null? What about AND, OR and NOT connectives?– 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.Null Values – 3 Valued LogicNULLFTNullFTAND(null > 0)(null + 1)(null = 0)null AND trueis nullis nullis nullis nullNULLFTNullFTORTF NullT FF FNullNullFFT TTT NullNullNullWhat about joins where one value is Null?• Join is comparing (attr1 = attr2)• What to do if attr1 is null?• By default, don’t join the tuples (INNER join).• Sometimes, though, do want to join tuples.3Joins Explicit 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_nameON qualification_listWHERE …Inner JoinOnly rows that match search conditions are returned.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 attributes with 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 age22 Dustin 7 45.031 Lubber 8 55.595 Bob 3 63.5sid bid day22 101 10/10/9695 103 11/12/96Left Outer JoinLeft Outer Join returns all matched rows, plus all unmatched rows from the table on the left of the 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 they have 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 age22 Dustin 7 45.031 Lubber 8 55.595 Bob 3 63.5sid bid day22 101 10/10/9695 103 11/12/96Right Outer JoinRight Outer Join returns all matched rows, plus all unmatched rows from the table on the right of 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 ones are reserved.4SELECT 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 Marinesid bid day22 101 10/10/9695 103 11/12/96bid bname color101 Interlake blue102 Interlake red103 Clipper green104 Marine redFull Outer JoinFull Outer Join returns all (matched or unmatched) rows from the tables on both sides of the join clause SELECT r.sid, b.bid, b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid = b.bidReturns all boats & all information on reservationsSELECT 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.sid bid day22 101 10/10/9695 103 11/12/96bid bname color101 Interlake blue102 Interlake red103 Clipper green104 Marine redViews CREATE VIEW view_nameAS select_statementMakes development simplerOften used for securityOften not instantiated - otherwise updates trickyCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scountFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’GROUP BY B.bidCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scountFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’GROUP BY B.bidb.bid scount102 1RedsSELECT bname, scountFROM Reds R, Boats BWHERE R.bid=B.bidAND scount < 10b.bid scount102 1RedsCREATE VIEW RedsAS SELECT B.bid, COUNT (*) AS scountFROM Boats B, Reserves RWHERE
View Full Document
Unlocking...