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

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 DocumentRight 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 DocumentRight 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 DocumentRight 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 DocumentRight 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.

Page1 / 38

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

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