rama-cap4 - R1 Example 58 103 age 45.0 55.5 35.0 sid bid...

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

View Full Document Right Arrow Icon
1 SQL: The Query Language Module 2, Lectures 1 and 2 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 ± We will use these instances of the Sailors and Reserves relations in our examples. ± If the key for the Reserves relation contained only the attributes sid and bid ,how ±wouldthe semantics differ? 3 Basic SQL Query ± relation-list A list of relation names (possibly with a range-variable after each name). ± target-list A list of attributes of relations in relation-list ± qualification Comparisons (Attr op const or Attr1 op Attr2, where op is one of ) combined using AND, OR and NOT. ± 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 <>=≤≥≠ ,,,,, 4 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 qualifications . – Delete attributes that are not in target-list . –I f 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 . 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 6 A Note on Range Variables ± 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!
Background image of page 1

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

View Full Document Right Arrow Icon
7 Find sailors who’ve reserved at least one boat ± Would adding DISTINCT to this query make a difference? ± What is the effect of replacing S.sid by S.sname in the SELECT clause? Would adding DISTINCT to this variant of the query make a difference? SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid 8 Expressions and Strings ± Illustrates use of arithmetic expressions and string pattern matching: Find triples (of ages of sailors and two fields defined by expressions) for sailors whose names begin and end with B and contain at least three characters. ± AS
Background image of page 2
Image of page 3
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 6

rama-cap4 - R1 Example 58 103 age 45.0 55.5 35.0 sid bid...

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

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