Filetype PDF | Posted on 07 Feb 2023 | 2 years ago
Authentication
digital resources
115x Filetype PDF File size 0.05 MB Source: www.cs.toronto.edu
R1 sid bid day
Example Instances 22 101 10/10/96
58 103 11/12/96
v We will use these S1 sid sname rating age
SQL: Queries, Programming, instances of the 22 dustin 7 45.0
Sailors and
Triggers Reserves relations 31 lubber 8 55.5
in our examples. 58 rusty 10 35.0
v If the key for the
Reserves relation S2 sid sname rating age
contained only the 28 yuppy 9 35.0
attributes sid and 31 lubber 8 55.5
bid, how would the 44 guppy 5 35.0
semantics differ?
58 rusty 10 35.0
CSC343 – Introduction to Databases - A. Vaisman 1 CSC343 – Introduction to Databases - A. Vaisman 2
SELECT [DISTINCT] target-list
Basic SQL Query FROM relation-list Conceptual Evaluation Strategy
WHERE qualification
vrelation-list A list of relation names (possibly with a v Semantics of an SQL query defined in terms of the
range-variableafter each name). following conceptual evaluation strategy:
vtarget-list A list of attributes of relations in relation-list § Compute the cross-product of relation-list.
§ Discard resulting tuples if they fail qualifications.
vqualification Comparisons (Attrop const or Attr1 op § Delete attributes that are not in target-list.
Attr2, where op is one of <,>,=,<=,>==,<, >, =, ≤, ≥,like≠ ) § If DISTINCT is specified, eliminate duplicate rows.
combined using AND, OR and NOT. vThis strategy is probably the least efficient way to
v DISTINCTis an optional keyword indicating that the compute a query! An optimizer will find more
answer should not contain duplicates. Default is that efficient strategies to compute the same answers.
duplicates are not eliminated!
CSC343 – Introduction to Databases - A. Vaisman 3 CSC343 – Introduction to Databases - A. Vaisman 4
Conceptual Evaluation Strategy Example of Conceptual Evaluation
v Semantics of an SQL query based on R.A: SELECT S.sname
FROM Sailors S, Reserves R ---->range variable
SELECT R.A,S.B WHERE S.sid=R.sid ANDR.bid=103
FROM R, S (sid) sname rating age (sid) bid day
WHERE R.C=S.C 22 dustin 7 45.0 22 101 10/10/96
==============> 22 dustin 7 45.0 58 103 11/12/96
Π σ (R x S) 31 lubber 8 55.5 22 101 10/10/96
R.A,S.B R.C=S.C 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
CSC343 – Introduction to Databases - A. Vaisman 5 CSC343 – Introduction to Databases - A. Vaisman 6
A Note on Range Variables Find sailors who’ve reserved at least one boat
vReally needed only if the same relation SELECT S.sid
appears twice in the FROMclause. The FROM Sailors S, Reserves R
WHERE S.sid=R.sid
previous query can also be written as:
SELECT S.sname It is good style, vWould adding DISTINCT to this query make a
FROM Sailors S, Reserves R however, to use difference?
WHERE S.sid=R.sid ANDbid=103 range variables vWhat is the effect of replacing S.sid by S.snamein
OR SELECT sname always! the SELECT clause? Would adding DISTINCT to
FROM Sailors, Reserves this variant of the query make a difference?.
WHERE Sailors.sid=Reserves.sid
ANDbid=103
CSC343 – Introduction to Databases - A. Vaisman 7 CSC343 – Introduction to Databases - A. Vaisman 8
Expressions and Strings Find sid’s of sailors who’ve reserved a red or a green boat
SELECT S.age, age1=S.age-5, 2*S.age AS age2 v UNION: Can be used to SELECT S.sid
FROM Sailors S compute the union of any FROM Sailors S, Boats B, Reserves R
WHERE S.sname LIKE ‘B_%B’ two union-compatible sets of WHERE S.sid=R.sid ANDR.bid=B.bid
vIllustrates use of arithmetic expressions and string tuples (which are AND(B.color=‘red’ OR B.color=‘green’)
pattern matching: Find triples (of ages of sailors and themselves the result of
two fields defined by expressions) for sailors whose names SQL queries). SELECT S.sid
begin and end with B and contain at least three characters. v If we replace ORby ANDin FROM Sailors S, Boats B, Reserves R
the first version, what do WHERES.sid=R.sid ANDR.bid=B.bid
v AS and = are two ways to name fields in result. we get? ANDB.color=‘red’
v Also available: EXCEPT UNION
v LIKE is used for string matching. `_’ stands for any (What do we get if we SELECT S.sid
one character and `%’ stands for 0 or more arbitrary FROM Sailors S, Boats B, Reserves R
characters. replace UNION by EXCEPT?) WHERES.sid=R.sid ANDR.bid=B.bid
ANDB.color=‘green’
CSC343 – Introduction to Databases - A. Vaisman 9 CSC343 – Introduction to Databases - A. Vaisman 10
Find sid’s of sailors who’ve reserved a red and a green boat Nested Queries
SELECT S.sid Find names of sailors who’ve reserved boat #103:
FROM Sailors S, Boats B1, Reserves R1, SELECT S.sname
v INTERSECT: Can be used to Boats B2, Reserves R2 FROM Sailors S
compute the intersection WHERE S.sid=R1.sid ANDR1.bid=B1.bid WHERE S.sid IN (SELECT R.sid
of any two union- AND S.sid=R2.sid ANDR2.bid=B2.bid FROM Reserves R
compatible sets of tuples. AND(B1.color=‘red’ ANDB2.color=‘green’) WHERE R.bid=103)
v Included in the SQL/92 SELECT S.sid Key field! vA very powerful feature of SQL: a WHERE clause can
standard, but some FROM Sailors S, Boats B, Reserves R itself contain an SQL query! (Actually, so can FROM
systems don’t support it. WHERES.sid=R.sid ANDR.bid=B.bid and HAVING clauses, not supported by all systems.)
ANDB.color=‘red’
INTERSECT vTo find sailors who’ve notreserved #103, use NOT IN.
SELECT S.sid
FROM Sailors S, Boats B, Reserves R vTo understand semantics of nested queries, think of a
WHERES.sid=R.sid ANDR.bid=B.bid nested loopsevaluation: For each Sailors tuple, check the
ANDB.color=‘green’ qualification by computing the subquery.
CSC343 – Introduction to Databases - A. Vaisman 11 CSC343 – Introduction to Databases - A. Vaisman 12
Nested Queries with Correlation More on Set-Comparison Operators
Find names of sailors who’ve reserved boat #103:
SELECT S.sname
FROM Sailors S vWe’ve already seen IN, EXISTS and UNIQUE. Can also
WHERE EXISTS (SELECT * use NOT IN, NOT EXISTS and NOT UNIQUE.
FROM Reserves R >,<,=,≥,≤,≠
WHERE R.bid=103 ANDS.sid=R.sid) vAlso available: op ANY, op ALL, op IN
v EXISTS is another set comparison operator, like IN. vFind sailors whose rating is greater than that of some
vIf UNIQUEis used, and * is replaced by R.bid, finds sailor called Horatio:
sailors with at most one reservation for boat #103. SELECT *
(UNIQUEchecks for duplicate tuples; * denotes all FROM Sailors S
attributes. Why do we have to replace * by R.bid?) WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
vIllustrates why, in general, subquerymust be re- WHERE S2.sname=‘Horatio’)
computed for each Sailors tuple.
CSC343 – Introduction to Databases - A. Vaisman 13 CSC343 – Introduction to Databases - A. Vaisman 14
(1) SELECT S.sname
Rewriting INTERSECT Queries Using IN FROM Sailors S
Division in SQL WHERE NOT EXISTS
Find sid’sof sailors who’ve reserved both a red and a green boat: ((SELECT B.bid
FROM Boats B)
SELECT S.sid Find sailors who’ve reserved all boats. EXCEPT
FROM Sailors S, Boats B, Reserves R (SELECT R.bid
WHERE S.sid=R.sid ANDR.bid=B.bid ANDB.color=‘red’ vLet’s do it the hard FROM Reserves R
ANDS.sid IN (SELECT S2.sid way, without EXCEPT: WHERE R.sid=S.sid))
FROM Sailors S2, Boats B2, Reserves R2 (2) SELECT S.sname
WHERE S2.sid=R2.sid ANDR2.bid=B2.bid FROM Sailors S
AND B2.color=‘green’) WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
vSimilarly, EXCEPT queries re-written using NOT IN. Sailors S such that ... WHERE NOT EXISTS (SELECT R.bid
vTo find names (not sid’s) of Sailors who’ve reserved FROM Reserves R
both red and green boats, just replaceS.sid by S.sname there is no boat B without ... WHERE R.bid=B.bid
in SELECT clause. (What about INTERSECT query?) AND R.sid=S.sid))
a Reserves tuple showing S reserved B
CSC343 – Introduction to Databases - A. Vaisman 15 CSC343 – Introduction to Databases - A. Vaisman 16
no reviews yet
Please Login to review.
