lec08 - SQL Queries Chapter 5.1-5.8 2/6/11 EECS 484:...

Info iconThis preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
1 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 1 SQL Queries Chapter 5.1-5.8 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 2 SQL Query Language Implements relational algebra / calculus And more… Subqueries, Correlated subqueries Ordering of results Aggregate queries (e.g., SUM, MAX, AVG) Three-valued logic for NULL values
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
2 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 3 Key Topics (Next Two Classes) Basic SQL queries Aggregate queries Nested queries NULL values and SQL queries Complex constraints and triggers Examples 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 4 Basic SQL Query SELECT [DISTINCT] attr-list FROM relation-list WHERE qualification Optional List of relations Attr1 op Attr2 OPS: <, >, =, <=, >=, <> Combine using AND, OR, NOT Attributes from input relations (Conceptual) Evaluation: 1. Take cross-product of relation-list 2. Select rows satisfying qualification 3. Project columns in attr-list (eliminate duplicates only if DISTINCT) Optimizer chooses efficient plan!!
Background image of page 2
3 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 5 Example of Basic Query Schema: Sailors (sid , sname, rating, age) Boats (bid , bname, color) Reserves (sid , bid, day ) Find the names of sailors who have reserved boat #103 SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid = R.sid AND R.bid = 103 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 6 Example of Basic Query SELECT DISTINCT sname FROM Sailors S, Reserves R WHERE S.sid = R.sid AND R.bid = 103 π sname ( σ bid=103 (Sailors  Reserves)) Equivalent Relational Algebra {T | S Sailors R Reserves (S.sid = R.sid R.bid = 103 T.name = S.sname)} Equivalent Relational Calculus
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
4 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 7 Another Example Schema: Sailors (sid , sname, rating, age) Boats (bid , bname, color) Reserves (sid , bid, day ) Find the colors of boats reserved by a sailor named rusty SELECT B.color FROM Sailors S, Reserves R, Boats B WHERE S.sid = R.sid AND R.bid = B.bid AND S.sname = ‘rusty’ 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 8 Note on Range Variables (“Aliases”) Needed when same relation appears twice in FROM clause SELECT S1.sname, S2.sname FROM Sailors S1, Sailors S2 WHERE S1.age > S2.age Good style to always use range variables anyway…
Background image of page 4
5 2/6/11 EECS 484: Database Management Systems, Kristen LeFevre 9 ORDER BY clause In relational algebra and calculus, there is no way to specify ordering of results SQL has an extra ORDER BY clause SELECT S.sname, S.age FROM Sailors S ORDER BY S.age [ASC] SELECT S.sname, S.age FROM Sailors S ORDER BY S.age DESC Find the names and ages of all sailors, in increasing order of age of all sailors, in decreasing Attribute(s) in ORDER BY clause must be in SELECT list.
Background image of page 5

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 6
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 12/08/2011 for the course EECS 484 taught by Professor Staff during the Winter '08 term at University of Michigan.

Page1 / 20

lec08 - SQL Queries Chapter 5.1-5.8 2/6/11 EECS 484:...

This preview shows document pages 1 - 6. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online