cmpt 258 database systems sql queries (chapter 5)

CMPT 258 Database SystemsSQL Queries(Chapter 5)

2

Agenda

•Introduction•Basic SQL Query•Union, Intersection and Except•Nested Queries•Aggregate Operations

Examples

sid sname rating age

22 dustin 7 45.0

31 lubber 8 55.5 58 rusty 10 35.0

bid bname color101 Interlake Blue102 Interlake Red103 Clipper Green104 Marine Red

sid bid day

22 101 10/10/14 58 103 11/12/14

Reserves

Sailors

Boats

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 and OR .

SELECT target-listFROM relation-listWHERE qualification

, , , , ,

•The query uses the optional keyword AS to introduce a range variable.

5

Basic SQL Query

• DISTINCT is an optional keyword indicating that the answer should not contain duplicates. ▫Default is that duplicates are not eliminated!

SELECT [DISTINCT] target-listFROM relation-listWHERE qualification

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.

SELECT [DISTINCT] target-listFROM relation-listWHERE qualification


22 dustin 7 45.0

31 lubber 8 55.558 rusty 10 35.0

sid bid day

22 101 10/10/14 58 103 11/12/14

Reserves

Sailors


Boats

(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

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.snameFROM Sailors S, Reserves RWHERE S.sid=R.sid AND R.bid=103

SELECT snameFROM Sailors, Reserves WHERE Sailors.sid=Reserves.sid AND bid=103

It is good style,however, to userange variablesalways!

OR

13

Query Examples•Find the sids of sailors who have reserved

a red boat

14


a red boat

•Find the name of sailors who have reserved a red boat

SELECT R.sidFROM Reserves R, Boats BWHERE R.bid=B.bid AND B.color = ‘red’

15


a red boat

•Find the name of sailors who have reserved a red boat

SELECT R.sidFROM Reserves R, Boats BWHERE R.bid=B.bid AND B.color = ‘red’

SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.sid=R.sid AND R.bid = B.bid AND B.color = ‘red’

•Find the color of the boats reserved by Lubber

16

•Find the color of the boats reserved by Lubber

17

SELECT B.colorFROM Sailors S, Reserves R, Boats BWHERE S.sid=R.sid AND R.bid = B.bid AND S.name = ‘Lubber’

Find the names of sailors who’ve reserved at least one boat

Find the names of sailors who’ve reserved at least one boat

SELECT DISTINCT S.nameFROM Sailors S, Reserves RWHERE S.sid=R.sid

20

•Find the names of sailors who have sailed two different boats on the same day.

21

•Find the names of sailors who have sailed two different boats on the same day.

SELECT S.snameFROM Sailors S, Reserves R1, Reserves R2WHERE S.sid= R1.sid and R1.sid=R2.sid

AND R1.day = R2.day AND R1.bid <> R2.bid

Expressions and Strings• Find ages of sailors whose names begin and end

with B and contain at least three characters.

• LIKE is used for string matching. ‘_’ stands for any one character and ‘%’ stands for 0 or more arbitrary characters (including blanks).

• ‘_AB%’• Comparison operators (=, <, >, etc.) can be used

for string comparison

Expressions and Strings• Find ages of sailors whose names begin and end

with B and contain at least three characters.

SELECT S.ageFROM Sailors SWHERE S.sname LIKE ‘B_%B’

Query FROM More than One Table Using JOINs

• A join combines the rows of two tables, based on a rule called a join condition; this compares values from the rows of both tables to determine which rows should be joined.

• Basic types of join:▫inner join▫outer join

LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN

24

Inner Join

• select a, b• from A, B• where a=b

26


22 dustin 7 45.0

31 lubber 8 55.558 rusty 10 35.0

sid bid day

22 101 10/10/14 58 103 11/12/14

Reserves

Sailors


Boats

•Union•Intersect•Except

31

Find sid’s of sailors who’ve reserved a red or a green boat

Find sid’s of sailors who’ve reserved a red or a green boat• UNION: Can be used

to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).

SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)

Find sid’s of sailors who’ve reserved a red or a green boat• UNION: Can be used

to compute the union of any two union-compatible sets of tuples (which are themselves the result of SQL queries).

• If we replace OR by AND in the first version, what do we get?

SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND (B.color=‘red’ OR B.color=‘green’)

SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’UNIONSELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘green’

35

Find the sids of sailors who have a rating of 10 or reserved boat 104

36

Find the sids of sailors who have a rating of 10 or reserved boat 104

•The default for UNION queries is that duplicates are eliminated.

•To retain duplicates, use UNION ALL

SELECT S.sidFROM Sailors SWHERE S.rating = 10 UNIONSELECT R.sidFROM Reserves RWHERE R.bid=104

Find sid’s of sailors who’ve reserved a red and a green boat

• INTERSECT: Can be used to compute the intersection of any two union-compatible sets of tuples.

• Some systems don’t support it.

SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’INTERSECTSELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘green’

38

Find the sids of all sailors who have reserved red boats but not green boats

SELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘red’EXCEPTSELECT R.sidFROM Boats B, Reserves RWHERE R.bid=B.bid AND B.color=‘green’

39

Find the names of sailors who’ve reserved a red and a green boat

• Is this query correct? Why

SELECT S.snameFROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’INTERSECTSELECT S.snameFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

40Find the names of sailors who’ve reserved a red and a green boat• Is this query correct?

WhySELECT S.snameFROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘red’INTERSECTSELECT S.snameFROM Sailors S, Boats B, Reserves RWHERE S.sid=R.sid AND R.bid=B.bid AND B.color=‘green’

There are two sailors with the same name Horatio!

Readings

•Chapter 5

cmpt 258 database systems sql queries (chapter 5)

Documents