{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Chapter5 - SQL:Queries,Programming Triggers Chapter5...

Info icon This 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
Image of page 1

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

View Full Document Right 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?
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 < = ≤ ≥ ≠ , , , , ,
Image of page 3

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

View Full Document Right 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 .
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 (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
Image of page 5

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

View Full Document Right 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!
Image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern