L06 - Relational Query Languages SQL Queries SQL Structured...

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

View Full Document Right Arrow Icon
Relational Query Languages SQL Queries
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
CS174A: SQL 2 ± Evolved from S E Q UE L (System R, IBM) ± ANSI/ISO SQL-92 (SQL-2), SQL-99 (SQL-3) ± Basic form: ( A 1 : x 1 ,..., A n : x n ) y 1 L y m R 1 ( y 1 )∧ L R m ( y m )∧ ϕ x 1 = y i 1 , j 1 L x n = y i n , j n π A 1 ,..., A n σ ϕ ( R 1 × R 2 × L × R m ) ± Based on bag (multiset) semantics ± DDL/DML ± Can be embedded into PLs (C, Fortran, etc.) / JDBC SELECT A 1 , A 2 , . .., A n FROM R 1 , R 2 , . .., R m WHERE ϕ SQL — Structured Query Language
Background image of page 2
CS174A: SQL 3 ± 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: Att θ const or Att 1 Att 2 , where 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 Basic SQL Query
Background image of page 3

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

View Full Document Right Arrow Icon
CS174A: SQL 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 qualification 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
CS174A: SQL 5 ± 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? Example Instances 55.5 8 lubber 31 35.0 9 yuppy 28 35.0 10 rusty 58 35.0 5 guppy 44 age rating sname sid S2 45.0 7 dustin 22 35.0 10 rusty 58 55.5 8 lubber 31 age rating sname sid S1 10/10/96 101 22 11/12/96 103 58 day bid sid R1
Background image of page 5

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

View Full Document Right Arrow Icon
CS174A: SQL 6 10/10/96 101 22 35.0 10 rusty 58 10/10/96 101 22 55.5 8 lubber 31 11/12/96 103 58 45.0 7 dustin 22 58 58 22 ( sid ) 103 103 101 bid 35.0 55.5 45.0 age 10/10/96 7 dustin 22 11/12/96 10 rusty 58 11/12/96 8 lubber 31 day rating sname ( sid ) Example of Conceptual Evaluation SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid = R.sid AND R.bid = 103
Background image of page 6
CS174A: SQL 7 ± Really needed only if the same relation appears twice or more 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 R.bid = 103 SELECT sname FROM Sailors, Reserves WHERE Sailors.sid = Reserves.sid AND bid = 103 or Range Variables It is good style to use range variables always!
Background image of page 7

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

View Full Document Right Arrow Icon
CS174A: SQL 8 SELECT S.sid FROM Sailors S, Reserves R WHERE S.sid=R.sid Find sailors who’ve reserved at least one boat 45.0 7 dustin 22 35.0 10 rusty 58 55.5 8 lubber 31 age rating sname sid Sailors 5/6/99 101 22 4/30/99 101 22 5/2/99 103 58 day bid sid Reserves
Background image of page 8
Image of page 9
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page1 / 38

L06 - Relational Query Languages SQL Queries SQL Structured...

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

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