1 / 29

SQL 2 – The Sequel

SQL 2 – The Sequel. R&G, Chapter 5 Lecture 10. Administrivia. Homework 2 assignment now available Due a week from Sunday Midterm exam will be evening of 10/14. Review – Query Optimization. External Sorting with Merge sort It is possible to sort most tables in 2 passes Join Algorithms

tasha-slater
Download Presentation

SQL 2 – The Sequel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 2 – The Sequel R&G, Chapter 5 Lecture 10

  2. Administrivia • Homework 2 assignment now available • Due a week from Sunday • Midterm exam will be evening of 10/14

  3. Review – 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 Equivalences

  4. Review – 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 joins

  5. Query Optimization – Some Resources • Animation of Join Algorithms http://www.animal.ahrgr.de/en/AnimList_algo.html • System Catalogs PostGres catalogs: http://www.postgres.org/docs/7.2/interactive/pg-system-catalogs.html • Query Optimization “explain” command, type “explain <sql query>”

  6. 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/Having

  7. Today – Further SQL Features • Insert • Delete • Update • Null Values – Outer Joins • Views • Order By • Access Control • Integrity Constraints

  8. INSERT INSERT [INTO] table_name [(column_list)] VALUES ( value_list) INSERT [INTO] table_name [(column_list)] <select statement> 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

  9. DELETE & UPDATE DELETE [FROM] table_name [WHERE qualification] 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 Boats SET Color = “green” WHERE bid = 103;

  10. 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.

  11. Null Values – 3 Valued Logic (null > 0) (null + 1) (null = 0) null AND true is null is null is null is null T F Null T T T F F F T F Null Null F Null T Null Null

  12. What 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.

  13. Joins SELECT (column_list) FROM table_name [INNER | {LEFT |RIGHT | FULL } OUTER] JOIN table_name ON qualification_list WHERE … Explicit join semantics needed unless it is an INNER join (INNER is default)

  14. Inner Join Only rows that match search conditions are returned. SELECT s.sid, s.name, r.bid FROM Sailors s INNER JOIN Reserves r ON s.sid = r.sid Returns only those sailors who have reserved boats SQL-92 also allows: SELECT s.sid, s.name, r.bid FROM Sailors s NATURAL JOIN Reserves r “NATURAL” means equi-join for each pair of attributes with the same name

  15. SELECT s.sid, s.name, r.bidFROM Sailors s INNER JOIN Reserves rON s.sid = r.sid

  16. Left Outer Join Left 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.bid FROM Sailors s LEFT OUTER JOIN Reserves r ON s.sid = r.sid Returns all sailors & information on whether they have reserved boats

  17. SELECT s.sid, s.name, r.bidFROM Sailors s LEFT OUTER JOIN Reserves rON s.sid = r.sid

  18. Right Outer Join Right Outer Join returns all matched rows, plus all unmatched rows from the table on the right of the join clause SELECT r.sid, b.bid, b.name FROM Reserves r RIGHT OUTER JOIN Boats b ON r.bid = b.bid Returns all boats & information on which ones are reserved.

  19. SELECT r.sid, b.bid, b.nameFROM Reserves r RIGHT OUTER JOIN Boats bON r.bid = b.bid

  20. Full Outer Join Full Outer Join returns all (matched or unmatched) rows from the tables on both sides of the join clause SELECT r.sid, b.bid, b.name FROM Reserves r FULL OUTER JOIN Boats b ON r.bid = b.bid Returns all boats & all information on reservations

  21. SELECT r.sid, b.bid, b.nameFROM Reserves r FULL OUTER JOIN Boats bON r.bid = b.bid Note: in this case it is the same as the ROJ because bid is a foreign key in reserves, so all reservations must have a corresponding tuple in boats.

  22. Views CREATE VIEW view_name AS select_statement Makes development simpler Often used for security Often not instantiated - otherwise updates tricky CREATE VIEW Reds AS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid

  23. CREATE VIEW Reds AS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid Reds

  24. Views Instead of Relations in Queries CREATE VIEW Reds AS SELECT B.bid, COUNT (*) AS scount FROM Boats B, Reserves R WHERE R.bid=B.bid AND B.color=‘red’ GROUP BY B.bid Reds SELECT bname, scount FROM Reds R, Boats B WHERE R.bid=B.bid AND scount < 10

  25. Sorting the Results of a Query SELECT S.rating, S.sname, S.age FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ ORDER BY S.rating, S.sname; • ORDER BY column [ ASC | DESC] [, ...] • Can order by any column in SELECT list, including expressions or aggs: SELECT S.sid, COUNT (*) AS redrescnt FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ GROUP BY S.sid ORDER BY redrescnt DESC;

  26. Discretionary Access Control GRANT 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 foreign key that references the specified column(s) • All • Can later be REVOKEd • Users can be single users or groups • See Chapter 17 for more details.

  27. Integrity Constraints (Review) • An IC describes conditions that every legal instance of a relation must satisfy. • Inserts/deletes/updates that violate IC’s are disallowed. • Can be used to ensure application semantics (e.g., sid is a key), or prevent inconsistencies (e.g., sname has to be a string, age must be < 200) • Types of IC’s: Domain constraints, primary key constraints, foreign key constraints, general constraints. • Domain constraints: Field values must be of right type. Always enforced. • Primary key and foreign key constraints: you know them.

  28. CREATE TABLE Reserves ( sname CHAR(10), bid INTEGER, day DATE, PRIMARY KEY (bid,day), CONSTRAINTnoInterlakeRes CHECK (`Interlake’ <> ( SELECT B.bname FROM Boats B WHERE B.bid=bid))) CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( rating >= 1 AND rating <= 10 )) General Constraints • Useful when more general ICs than keys are involved. • Can use queries to express constraint. • Checked on insert or update. • Constraints can be named.

  29. Constraints Over Multiple Relations CREATE TABLE Sailors ( sid INTEGER, sname CHAR(10), rating INTEGER, age REAL, PRIMARY KEY (sid), CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 ) Number of boats plus number of sailors is < 100 • Awkward and wrong! • Only checks sailors! • Only required to hold if the associated table is non-empty. • ASSERTION is the right solution; not associated with either table. • Unfortunately, not supported in many DBMS. • Triggers are another solution. CREATE ASSERTION smallClub CHECK ( (SELECT COUNT (S.sid) FROM Sailors S) + (SELECT COUNT (B.bid) FROM Boats B) < 100 )

More Related