Session5

Session5 - SQL: Queries, Constraints, Triggers Session 5...

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

View Full Document Right Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1 SQL: Queries, Constraints, Triggers Session 5 (CSCI-585)
Background image of page 1

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

View Full DocumentRight Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2 Example Instances sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid sname rating age 28 yuppy 9 35.0 31 lubber 8 55.5 44 guppy 5 35.0 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96 R1 S1 S2 x We will use these instances of the Sailors and Reserves relations in our examples. x If the key for the Reserves relation contained only the attributes sid and bid , how would the semantics differ?
Background image of page 2
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3 Basic SQL Query x relation-list A list of relation names (possibly with a range-variable after each name). x target-list A list of attributes of relations in relation-list x qualification Comparisons (Attr op const or Attr1 op Attr2, where op is one of ) combined using AND, OR and NOT . x DISTINCT is an optional keyword indicating that the answer should not contain duplicates. Default is that duplicates are not eliminated! SELECT [DISTINCT] target-list FROM relation-list WHERE qualification < > = ≤ ≥ ≠ , , , , ,
Background image of page 3

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

View Full DocumentRight Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4 Conceptual Evaluation Strategy x Semantics of an SQL query defined in terms of the following conceptual evaluation strategy: s Compute the cross-product of relation-list . s Discard resulting tuples if they fail qualifications . s Delete attributes that are not in target-list . s If DISTINCT is specified, eliminate duplicate rows. x This strategy is probably the least efficient way to compute a query! An optimizer will find more efficient strategies to compute the same answers .
Background image of page 4
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5 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 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
Background image of page 5

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

View Full DocumentRight Arrow Icon
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6 A Note on Range Variables x Really needed only if the same relation appears twice in the FROM clause. The previous query can also be written as: SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND bid=103 SELECT sname FROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103 It is good style, however, to use range variables always! OR
Background image of page 6
Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7 Find sailors who’ve reserved at least one boat x Would adding DISTINCT to this query make a difference? x
Background image of page 7

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

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

Page1 / 35

Session5 - SQL: Queries, Constraints, Triggers Session 5...

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

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