√2011-09-12-SQL

√2011-09-12-SQL - SQL Database Management...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: SQL Database Management Systems, R. Ramakrishnan and J. Gehrke 1 Basic SQL Query SELECT [DISTINCT] target-list FROM relation-list [WHERE condition ] Database Management Systems, R. Ramakrishnan and J. Gehrke 2 • Default is that duplicates are not eliminated! – Need to explicitly say “DISTINCT” SELECT S.Name FROM Sailors S WHERE S.Age > 25 SELECT DISTINCT S.Name FROM Sailors S WHERE S.Age > 25 SQL Query SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 Database Management Systems, R. Ramakrishnan and J. Gehrke 3 sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96 Sailors Reserves Conceptual Evaluation Strategy • Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: – Compute the cross-product of relation-list – Discard resulting tuples if they fail condition . Database Management Systems, R. Ramakrishnan and J. Gehrke 4 – Delete attributes that are not in target-list – If DISTINCT is specified, eliminate duplicate rows. • This strategy is probably the least efficient way to compute a query! – An optimizer will find more efficient strategies to compute the same answers . Example of Conceptual Evaluation SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103 (sid) sname rating age (sid) bid day Database Management Systems, R. Ramakrishnan and J. Gehrke 5 22 dustin 7 45.0 22 101 10/10/96 22 dustin 7 45.0 58 103 11/12/96 31 lubber 8 55.5 22 101 10/10/96 31 lubber 8 55.5 58 103 11/12/96 58 rusty 10 35.0 22 101 10/10/96 58 rusty 10 35.0 58 103 11/12/96 A Slightly Modified Query SELECT S.sid FROM Sailors S, Reserves R Database Management Systems, R. Ramakrishnan and J. Gehrke 6 • Would adding DISTINCT to this query make a difference? WHERE S.sid=R.sid AND R.bid=103 Find sid’s of sailors who’ve reserved a red or a green boat SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’) Database Management Systems, R. Ramakrishnan and J. Gehrke 7 SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’ What does this query compute? SELECT S.sid Database Management Systems, R. Ramakrishnan and J. Gehrke 8 FROM Sailors S, Boats B1, Reserves R1, Boats B2, Reserves R2 WHERE S.sid=R1.sid AND R1.bid=B1.bid AND S.sid=R2.sid AND R2.bid=B2.bid AND B1.color=‘red’ AND B2.color=‘green’ Find sid’s of sailors who’ve reserved a red and a green boat SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’ INTERSECT Key field!...
View Full Document

This note was uploaded on 12/07/2011 for the course CS 4410 taught by Professor Vollset during the Spring '07 term at Cornell.

Page1 / 12

√2011-09-12-SQL - SQL Database Management...

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

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