The%20Oracle%20Database%20System%20Querying%20the%20Data

1
The Oracle Database SystemQuerying the Data

• Database Course
• The Hebrew University of Jerusalem

2
Basic SQL Query
SELECT Distinct target-list FROM
from-list WHERE condition

• from-list A list of relation names (possibly
  with a range-variable after each name)
• target-list A list of fields of relations in
  relation-list
• condition A boolean condition
• DISTINCT Optional keyword to delete duplicates

3
Basic SQL Query (continues)
SELECT Distinct target-list FROM
from-list WHERE condition

• The result
  evaluation
• Compute the cross product of the tables in
  from-list.
• Delete all rows that do not satisfy condition.
• Delete all columns that do not appear in
  target-list.
• If Distinct is specified eliminate duplicate
  rows.

4
Basic SQL Query (continues)
SELECT Distinct A1,,An FROM R1,,Rm WHERE C
This translates to the expression in relational
algebra ?A1,,An (?C(R1 xx Rm))
5
Example Tables Used
Sailors Sailors Sailors Sailors
sid sname rating age
22 31 58 Dustin Lubber Rusty 7 8 10 45.0 55.5 35.0
Boats Boats Boats
bid bname color
101 103 Nancy Gloria red green
Reserves Reserves Reserves
sid bid Day
22 58 101 103 10/10/02 11/12/02
Key
Key
6
Boat Names and Reservation Dates

• SELECT bname,day
• FROM Boats,Reserves
• WHERE Boats.bid Reserves.bid

7
Boat Names and Reservation Dates

• SELECT bname,day
• FROM Boats,Reserves
• WHERE Boats.bid Reserves.bid

Reserves Reserves Reserves
sid bid day
22 22 101 101 10/10/02 10/10/02
58 58 103 103 11/12/02 11/12/02
Boats Boats Boats
bid bname color
101 103 Nancy Gloria red green
101 103 Nancy Gloria red green
8
Boat Names and Reservation Dates

• SELECT bname,day
• FROM Boats,Reserves
• WHERE Boats.bid Reserves.bid

Reserves Reserves Reserves
sid bid day
22 101 10/10/02
58 103 11/12/02
Boats Boats Boats
bid bname color
101 Nancy red
103 Gloria green
9
Boat Names and Reservation Dates

• SELECT bname,day
• FROM Boats,Reserves
• WHERE Boats.bid Reserves.bid

Reserves
day
10/10/02
11/12/02
Boats
bname
Nancy
Gloria
10
Sailors Who Reserved Boat 103
SELECT sname FROM Sailors, Reserves WHERE
Sailors.sid Reserves.sid and bid 103
?sname(?Sailors.sid Reserves.sid ? bid 103
(Sailors x Reserves))
11
Sailors Who Reserved Boat 103
Sailors x Reserves
Sailors Sailors Sailors Sailors Reserves Reserves Reserves
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/02
22 Dustin 7 45.0 58 103 11/12/02
31 Lubber 8 55.5 22 101 10/10/02
31 Lubber 8 55.5 58 103 11/12/02
58 Rusty 10 35.0 22 101 10/10/02
58 Rusty 10 35.0 58 103 11/12/02
12
Sailors Who Reserved Boat 103
?Sailors.sid Reserves.sid ? bid 103
Sailors Sailors Sailors Sailors Reserves Reserves Reserves
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/02
22 Dustin 7 45.0 58 103 11/12/02
31 Lubber 8 55.5 22 101 10/10/02
31 Lubber 8 55.5 58 103 11/12/02
58 Rusty 10 35.0 22 101 10/10/02
58 Rusty 10 35.0 58 103 11/12/02
13
Sailors Who Reserved Boat 103
?sname
Sailors Sailors Sailors Sailors Reserves Reserves Reserves
sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/02
22 Dustin 7 45.0 58 103 11/12/02
31 Lubber 8 55.5 22 101 10/10/02
31 Lubber 8 55.5 58 103 11/12/02
58 Rusty 10 35.0 22 101 10/10/02
58 Rusty 10 35.0 58 103 11/12/02
14
Range Variables
SELECT S.sname FROM Sailors S, Reserves R
WHERE S.sid R.sid and R.bid 103

• Range variables are good style
• They are necessary if the same relation appears
  twice in the FROM clause

15
A Few SELECT Options

• Select all columns
• SELECT
• FROM Sailors S
• Rename selected columns
• SELECT S.sname AS Sailors_Name
• FROM Sailors S

16
A Few SELECT Options

• Applying functions
• Mathematical manipulations
• SELECT (age-5)2
• FROM Sailors S
• Aggregate functions
• SELECT COUNT()
• FROM Sailors S
• SELECT MAX(age)
• FROM Sailors S

17
The WHERE Clause

• Numerical and string comparison
• !,ltgt,, lt, gt, gt, lt, between(between val1 AND
  val2)
• String comparisson is according to the
  alphabetical order!
• Logical components AND, OR
• Null verification IS NULL, IS NOT NULL
• Example
• SELECT sname
• FROM Sailors
• WHERE agegt40 AND rating IS NOT NULL

18
The WHERE Clause (continues)

• The LIKE operator
• A pattern matching operator
• Basic format colname LIKE pattern
• Example
• _ is a single character
• is 0 or more characters

SELECT sid FROM Sailors WHERE sname LIKE
B_g
19
Sailors Whove Reserved a Boat
SELECT sname FROM Sailors S, Reserves R WHERE
S.sid R.sid
20
Exercise

• Formulate a query that finds the names of
  sailors who reserved a yellow boat.

Sailors Sailors Sailors Sailors
sid sname rating age
22 31 58 Dustin Lubber Rusty 7 8 10 45.0 55.5 35.0
Boats Boats Boats
bid bname color
101 103 Nancy Gloria red green
Reserves Reserves Reserves
sid bid Day
22 58 101 103 10/10/02 11/12/02
21
A Harder Exercise

• Formulate a query that finds the bid of boats
  that are reserved in at least two different days
• Fix the query you formulated to find the names of
  these boats
• Hint A relation may appear more than once in the
  FROM list

22
Union, Intersect and Except

• Sqlplus supports the union, intersection and
  difference operators
• The syntax
• query1 UNION query2
• query1 INTERSECT query2
• query1 MINUS query2

23
Sailors whove reserved a red or green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and (B.color red or B.color
green)
?sname(?color red ? color green (Sailors
? Reserves ? Boats))
24
Sailors whove reserved red or green boat
SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red UNION SELECT S.sname
FROM Sailors S, Boats B, Reserves R WHERE S.sid
R.sid and R.bid B.bid and B.color
green
25
The Second Version in Relational Algebra
?sname(?color red (Sailors ? Reserves ?
Boats)) ? ?sname(?color green (Sailors ?
Reserves ? Boats))
26
Nested Queries

• A query may be nested within another through the
  following operators
• (NOT) IN
• (NOT) EXISTS
• ANY
• ALL
• A query with nested queries is computed using
  nested loops

27
The IN Operator
Names of sailors whove reserved boat 103
SELECT S.sname FROM Sailors S WHERE S.sid IN
(SELECT R.sid FROM Reserves R WHERE
R.bid 103)
What would happen if we wrote NOT IN?
28
The EXISTS Operator
Names of sailors whove reserved boat
103
SELECT S.sname FROM Sailors S WHERE EXISTS
(SELECT FROM Reserves R WHERE R.bid
103 and S.sid R.sid)
What would happen if we wrote NOT EXISTS?
Notice the correlation between the examined row
and the inner query
29
Set-Comparison Queries ANY,ALL
Sailors who are not the youngest
SELECT FROM Sailors S1 WHERE S1.age gt ANY
(SELECT S2.age FROM Sailors S2)
We can also use op ALL (op is gt, lt, , gt, lt, or
ltgt)
30
Another Exercise

• What does the following query compute?
• Using IN and INTERSECT operators, formulate a
  query that finds the names of the sailors who
  reserved both green and red boats.

SELECT S.sname FROM Sailors S, Boats B,
Reserves R WHERE S.sid R.sid and R.bid B.bid
and B.color red INTERSECT SELECT
S.sname FROM Sailors S, Boats B, Reserves R
WHERE S.sid R.sid and R.bid B.bid and
B.color green
31
Another Exercise (continues)

• What does the following query compute?
• SELECT S.sname
• FROM Sailors S
• WHERE NOT EXISTS
• (
• (SELECT B.bid FROM Boats B)
• MINUS
• (SELECT R.bid FROM Reserves R
• WHERE R.sidS.sid)
• )

32
For The Brave Ones

• Formulate a query that computes the sid of all
  sailors who have not reserved a green boat.
• Prove formally that in the Sailors-Reserves-Boats
  database, the query of question 1 cannot be as
  simple as a query of the form
• SELECT Vj.sid
• FROM R1 V1, R2 V2Rn Vn
• WHERE C
• Where C is a simple condition (contains only
  comparison clauses, AND and OR, and does not
  contain nested queries)
• Notice that the records in each table are
  arbitrary!