1 / 36

SQL

. SQL. . The important thing is not to stop questioning. Albert Einstein. CS 186, Spring 2007, Lecture 7 R&G, Chapter 5 Mary Roth. Life is just a bowl of queries. -Anon (not Forrest Gump). Administrivia. Homework 1 due Thursday, Feb 8 10 p.m.

diazt
Download Presentation

SQL

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  The important thing is not to stop questioning. Albert Einstein CS 186, Spring 2007, Lecture 7 R&G, Chapter 5 Mary Roth Life is just a bowl of queries. -Anon (not Forrest Gump)

  2. Administrivia • Homework 1 due Thursday, Feb 8 10 p.m. • Source code for diskmgr and global are available on class web site • Coming up: • Homework 2 handed out Feb 13 • Midterm 1: in class February 22 • Questions?

  3. Review • Query languages provide 2 key advantages: • Less work for user asking query • More opportunities for optimization • Algebra and safe calculus are simple and powerful models for query languages for relational model • Have same expressive power • Algebra is more operational; calculus is more declarative • SQL can express every query that is expressible in relational algebra/calculus. (and more)

  4. Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Lectures 3 &4 Disk Space Management DB Review: Where have we been? Theory Practice Relational Calculus Lecture 6 Relational Algebra Lecture 5 Relational Model Lecture 2

  5. Query Optimization and Execution Relational Operators Files and Access Methods Buffer Management Disk Space Management DB Where are we going next? This week SQL Practice After the midterm Next week

  6. Review: Relational Calculus Example Find names, ages and reservation dates of sailors rated > 7 who’ve reserved boat #103 S 3 quantifiers, but only 1 is free. The free quantifier defines the shape of the result. S S R R {S1 | SSailors  S.rating > 7   R(RReserves  R.bid = 103  R.sid = S.sid)  (S1.sname = S.name  S1.age = S.age  S1.day = R.day)} 35.0 rusty 11/12/96 S1

  7. Review: The SQL Query Language • The most widely used relational query language. • Standardized (although most systems add their own “special sauce” -- including PostgreSQL) • We will study SQL92 -- a basic subset

  8. Review: SQL • Two sublanguages: • DDL – Data Definition Language • Define and modify schema (at all 3 levels) • DML – Data Manipulation Language • Queries and IUD (insert update delete) • DBMS is responsible for efficient evaluation. • Relational completeness means we can define precise semantics for relational queries. • Optimizer can re-order operations, without affecting query answer. • Choices driven by “cost model”

  9. Sailors Review: DDL CREATE TABLE Sailors (sid INTEGER, sname CHAR(20), rating INTEGER, age REAL, PRIMARY KEY sid) CREATE TABLE Boats (bid INTEGER, bname CHAR (20), color CHAR(10) PRIMARY KEY bid) CREATE TABLE Reserves (sid INTEGER, bid INTEGER, day DATE, PRIMARY KEY (sid, bid, day), FOREIGN KEY sid REFERENCES Sailors, FOREIGN KEY bid REFERENCES Boats) NOT NULL, Boats NOT NULL, NOT NULL, Reserves NOT NULL, NOT NULL,

  10. Integrity Constraints (ICs) • A foreign key constraint is anIntegrity Constraint: • a condition that must be true for anyinstance of the database; • Specified when schema is defined. • Checked when relations are modified. • Primary/foreign key constraints; but databases support more general constraints as well. • e.g. domain constraints like: • Rating must be between 1 and 10 ALTER TABLE SAILORS ADD CONSTRAINT RATING CHECK (RATING >= 1 AND RATING < 10) • Or even more complex (and potentially nonsensical): ALTER TABLE SAILORS ADD CONSTRAINT RATING CHECK (RATING*AGE/4 <= SID)

  11. DBMSs have fairly sophisticated support for constraints! • Specify them on CREATE or ALTER TABLE statements • Column Constraints: expressions for column constraint must produce boolean results and reference the related column’s value only. NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK (expression) FOREIGN KEY (column) referenced_table [ ON DELETE action ] [ ON UPDATE action ] } action is one of: NO ACTION, CASCADE, SET NULL, SET DEFAULT

  12. DBMSs have fairly sophisticated support for constraints! • Table Constraints: UNIQUE ( column_name [, ... ] ) PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ON DELETE action ] [ ON UPDATE action ] } Here, expressions, keys, etc can include multiple columns

  13. Integrity Constraints can help prevent data consistency errors • …but they have drawbacks: • Expensive • Can’t always return a meaningful error back to the application. e.g: What if you saw this error when you enrolled in a course online? “A violation of the constraint imposed by a unique index or a unique constraint occurred”. • Can be inconvenient e.g. What if the ‘Sailing Class’ application wants to register new (unrated) sailors with rating 0? • So they aren’t widely used • Software developers often prefer to keep the integrity logic in applications instead

  14. Intermission

  15. sid name login age gpa 53666 Jones jones@cs 18 3.4 53688 Smith smith@ee 18 3.2 53650 Smith smith@math 19 3.8 SQL DML • DML includes 4 main statements: SELECT (query), INSERT, UPDATE and DELETE • e.g: To find the names of all 19 year old students: We’ll spend a lot of time on this one PROJECT SELECT S.name FROM Students S WHERE S.age=19 SELECT

  16. Querying Multiple Relations SELECT S.name, E.cid FROM Students S, Enrolled E WHERE S.sid=E.sid AND E.grade=‘B' PROJECT • Can specify a join over two tables as follows: SELECT JOIN S.name E.cid Jones History105 result =

  17. Basic SQL Query • DISTINCT: optional keyword indicating answer should not contain duplicates. • In SQL, default is that duplicates are not eliminated! (Result is called a “multiset”) target-list : A list of attributes of tables in relation-list SELECT [DISTINCT] target-list FROMrelation-list WHERE qualification qualification : Comparisons combined using AND, OR and NOT. Comparisons are Attr op const or Attr1 op Attr2, where op is one of ,,,, etc. relation-list : A list of relation names, possibly with a range-variable after each name

  18. Query Semantics • Semantics of an SQL query are defined in terms of the following conceptual evaluation strategy: • FROM clause: compute cross-product of all tables • WHERE clause: Check conditions, discard tuples that fail. (called “selection”). 3. SELECT clause: Delete unwanted fields. (called “projection”). 4. If DISTINCT specified, eliminate duplicate rows. • Probably the least efficient way to compute a query! • An optimizer will find more efficient strategies to get the same answer.

  19. Query Semantics Example SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 Boats X Sailors Reserves

  20. Step 1: Compute the cross product Sailors Reserves ... SailorsXReserves

  21. Step 1: How big? Sailors Reserves Question: If |S| is cardinality of Sailors, and |R| is cardinality of Reserves, What is the cardinality of Sailors X Reserves? Answer: |S| * |R| |Sailors X Reserves| = 3X2 = 6

  22. Step 2: Check conditions in where clause SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 SailorsXReserves

  23. Step 3: Delete unwanted fields SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 SailorsXReserves

  24. Range Variables • Used for short hand • Needed when ambiguity could arise • e.g two tables with the same column name: • SELECT sname • FROM Sailors, Reserves • WHERE Sailors.sid=Reserves.sid AND Reserves.bid=103 • SELECT sname • FROM Sailors S, Reserves R • WHERE S.sid=R.sid AND R.bid=103 Question: do range variables remind you of anything? • Variables in relational calculus

  25. Sometimes you need a range variable e.g a Self-join: SELECT R1.bid, R1.date FROM Reserves R1, Reserves R2 WHERE R1.bid = R2.bid and R1.date = R2.date and R1.sid != R2.sid Reserves Reserves R1 R2 R1 R2 R2 R2 R1

  26. Sometimes you need a range variable SELECT R1.bid, R1.day FROM Reserves R1, Reserves R2 WHERE R1.bid = R2.bid and R1.day = R2.day and R1.sid != R2.sid What are we computing? Boats reserved on the same day by different sailors

  27. SELECT Clause Expressions • Can use “*” if you want all columns: SELECT * FROM Sailors x WHERE x.age > 20 • Can use arithmetic expressions (add other operations we’ll discuss later) SELECT S.age, S.age-5 AS age1, 2*S.age AS age2 FROM Sailors S WHERE S.sname = ‘Dustin’ • Can use AS to provide column names SELECT S1.sname AS name1, S2.sname AS name2 FROM Sailors S1, Sailors S2 WHERE 2*S1.rating = S2.rating - 1

  28. WHERE Clause Expressions • Can also have expressions in WHERE clause: SELECT S1.sname AS name1, S2.sname AS name2 FROM Sailors S1, Sailors S2 WHERE 2*S1.rating = S2.rating - 1 `_’ stands for any one character and `%’ stands for 0 or more arbitrary characters. • “LIKE” is used for string matching. SELECT S.age, S.age-5 AS age1, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘B_l%o’

  29. SELECT DISTINCT Sailors Reserves Find sailors that have reserved at least one boat SELECT DISTINCT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid

  30. SELECT DISTINCT SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid • How about:

  31. Sailors SELECT DISTINCT SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid How about: Reserves vs: SELECT DISTINCT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid Do we find all sailors that reserved at least one boat?

  32. ANDs, ORs, UNIONs and INTERSECTs Find sids of sailors who’ve reserved a red or a green boat Boats SELECT R.sid FROM Boats B,Reserves R WHERE(B.color=‘red’ OR B.color=‘green’) AND R.bid=B.bid Sailors Reserves X

  33. ANDs and ORs Find sids of sailors who’ve reserved a red and a green boat Boats SELECT R.sid FROM Boats B,Reserves R WHERE(B.color=‘red’ AND B.color=‘green’) AND R.bid=B.bid X Sailors Reserves

  34. Use INTERSECT instead of AND Exercise: try to rewrite this query using a self join instead of INTERSECT! SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘red’ AND R.bid=B.bid INTERSECT SELECT R.sid FROM Boats B,Reserves R WHERE B.color = ‘green’ AND R.bid=B.bid Boats Reserves  =

  35. Could also use UNION for the OR query SELECT R.sid FROM Boats B, Reserves R WHERE B.color = ‘red’ AND R.bid=B.bid UNION SELECT R.sidFROM Boats B, Reserves R WHERE B.color = ‘green’ AND R.bid=B.bid Boats Reserves = 

  36. Sailors EXCEPT: Set Difference Find sids of sailors who have not reserved a boat SELECT S.sid FROM Sailors S EXCEPT SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid Reserves First find the set of sailors who have reserved a boat… and then compare it with the rest of the sailors

More Related