Structured Query Language - Practice Queries - CS430/630 Lecture 5 - - PowerPoint PPT Presentation

▶
structured query language practice queries
SMART_READER_LITE
LIVE PREVIEW

Structured Query Language - Practice Queries - CS430/630 Lecture 5 - - PowerPoint PPT Presentation

May 31, 2023 105 likes •199 views

Structured Query Language - Practice Queries - CS430/630 Lecture 5 Slides based on Database Management Systems 3 rd ed, Ramakrishnan and Gehrke Example Schema Sailors Boats sid sname rating age bid name color 22 dustin 7 45.0 101

slide-1
SLIDE 1

Structured Query Language

  • Practice Queries -

Slides based on “Database Management Systems” 3rd ed, Ramakrishnan and Gehrke

CS430/630 Lecture 5

slide-2
SLIDE 2

Example Schema

sid sname rating age 22 dustin 7 45.0 31 lubber 8 55.5 58 rusty 10 35.0 sid bid day 22 101 10/10/96 58 103 11/12/96

Reserves Sailors

bid name color 101 interlake red 103 clipper green

Boats

slide-3
SLIDE 3

Query Example 1

 “Find names of sailors who’ve reserved boat #103” SELECT S.sname FROM Sailors S, Reserves R WHERE S.sid=R.sid AND R.bid=103

sid sname rating age

sid bid day

Reserves Sailors

bid name color

Boats

slide-4
SLIDE 4

Query Example 2

 “Find names of sailors who’ve reserved a red boat” SELECT S.sname FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’;

sid sname rating age

sid bid day

Reserves Sailors

bid name color

Boats

slide-5
SLIDE 5

Query Example 3

 “Find sailor ids who’ve reserved a red or a green boat; list each

matching sailor id once”

sid sname rating age

sid bid day

Reserves Sailors

bid name color

Boats

SELECT DISTINCT S.sid FROM Sailors S, Reserves R, Boats B WHERE S.sid=R.sid AND R.bid=B.bid

AND (B.color=‘red’ OR B.color=‘green’);

slide-6
SLIDE 6

Same query with set operations

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’

UNION SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’

slide-7
SLIDE 7

Query Example 4

 “Find sailor ids who’ve reserved a red and a green boat”

sid sname rating age

sid bid day

Reserves Sailors

bid name color

Boats

SELECT S.sid FROM Sailors S, Boats B1, Reserves R1,

Boats B2, Reserves R2

WHERE S.sid=R1.sid AND R1.bid=B1.bid

AND S.sid=R2.sid AND R2.bid=B2.bid AND (B1.color=‘red’ AND B2.color=‘green’)

slide-8
SLIDE 8

Same query with set operations

SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘red’

INTERSECT SELECT S.sid FROM Sailors S, Boats B, Reserves R WHERE S.sid=R.sid AND R.bid=B.bid

AND B.color=‘green’

slide-9
SLIDE 9

LIKE with escape sequence

SELECT S.age, S.age-5 AS age1, 2*S.age AS age2 FROM Sailors S WHERE S.sname LIKE ‘d_!%n’ ESCAPE ‘!’;