Chapter5 - SQL:Queries,Programming, Triggers Chapter5

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

View Full Document Right Arrow Icon
Database Management Systems, R. Ramakrishnan and J. Gehrke 1 SQL:  Queries, Programming,  Triggers Chapter 5
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, 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 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 would the  semantics differ?
Background image of page 2
Database Management Systems, R. Ramakrishnan and J. Gehrke 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 < = ≤ ≥ ≠ , , , , ,
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, R. Ramakrishnan and J. Gehrke 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 . – 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 .
Background image of page 4
Database Management Systems, 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 bid day 22 dustin 7 45.0 22 101 10/ 10/ 96 22 7 45.0 58 103 11/ 12/ 96 31 lubber 8 55.5 22 101 10/ 10/ 96 31 8 55.5 58 103 11/ 12/ 96 58 rusty 10 35.0 22 101 10/ 10/ 96 58 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, R. Ramakrishnan and J. Gehrke 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 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 04/06/2010 for the course CS COP 6371 taught by Professor Dr.hua during the Spring '10 term at University of Central Florida.

Page1 / 45

Chapter5 - SQL:Queries,Programming, Triggers Chapter5

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

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