Transcript Slide 1

Multiple Table Queries Part 4
Before starting to write your query
what are some good questions to ask
yourself?
- Which tables contain the
information I need?
- How are the tables related
to one another?
2
Student_Names
Student_Phones
Return phone type and number for all of
Will Ferrell’s phones
Student_Names
select
home.phone_type,
home.phone_number,
busn.phone_type,
busn.phone_number,
cell.phone_type,
cell.phone_number
Student_Phones
from
student_names sn
join
student_phones home on
home.student_id = sn.student_id and
home.phone_type = 'home'
join
student_phones cell on
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
join
student_phones busn on
busn.student_id = sn.student_id and
busn.phone_type = 'busn'
where
Results
sn.first_name = 'Will' and
sn.last_name = 'Ferrell'
Return phone type and number for all of
Will Ferrell’s phones
Student_Names
select
home.phone_number as "Home Phone",
busn.phone_number as "Business Phone",
cell.phone_number as "Cell Phone"
from
student_names sn
Student_Phones
join
student_phones home on
home.student_id = sn.student_id and
home.phone_type = 'home'
join
student_phones cell on
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
join
student_phones busn on
busn.student_id = sn.student_id and
busn.phone_type = 'busn'
where
sn.first_name = 'Will' and
sn.last_name = 'Ferrell’
Results
Return student names and all their
phones
Student_Names
select
sn.first_name,
sn.last_name,
home.phone_number as "Home Phone",
busn.phone_number as "Business Phone",
cell.phone_number as "Cell Phone"
from
Student_Phones
student_names sn
join
student_phones home on
home.student_id = sn.student_id and
home.phone_type = 'home'
join
student_phones cell on
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
join
student_phones busn on
busn.student_id = sn.student_id and
busn.phone_type = 'busn'
Results
Need to use outer joins
Return student names and all their
phones
Student_Names
select
sn.first_name,
sn.last_name,
home.phone_number as "Home Phone",
busn.phone_number as "Business Phone",
cell.phone_number as "Cell Phone"
from
Student_Phones
student_names sn
left outer join
student_phones home on
home.student_id = sn.student_id and
home.phone_type = 'home'
left outer join
student_phones cell on
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
left outer join
Results
student_phones busn on
busn.student_id = sn.student_id and
busn.phone_type = 'busn'
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.phone_type = 'cell'
)
Results
Given the data we have the
subquery will always return
something, so the existence
check will always prove true.
Need a correlated subquery.
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
)
Results
Return names of students who have cell
phones
Student_Names
select
sn.first_name,
sn.last_name
from
student_names sn
where
exists
(
select
Student_Phones
*
from
student_phones cell
where
cell.student_id = sn.student_id and
cell.phone_type = 'cell'
)
Results
Now with the correlated
subquery we get back only
those students with a cell
phone
What does T-SQL stand for?
• Transact Structured Query Language
• Microsoft SQL Server
What does PL/SQL stand for?
• Procedural Language/Structured Query
Language
• Oracle
In Class Lab
Using 3 different approaches write 3 different queries that will
each answer the following data request:
List the boat names for all boats that do not
have any service requests
• Approach 1: utilize the "IN" operator
hint: subquery
• Approach 2: utilize the "exists" operator
hint: correlated subquery
• Approach 3: utilize an outer join
hint: if a match is not found null is returned
Approach 1: utilize the "IN" operator
hint: subquery
select
boat_name
from
a_marina_slip
where
slip_id not in
(
select
slip_id
from
a_service_request
)
Approach 2: utilize the "exists"
operator
hint: correlated subquery
select
boat_name
from
a_marina_slip m
where
not exists
(
select
*
from
a_service_request s
where
s.slip_id = m.slip_id
)
Approach 3: utilize an outer join
hint: if a match is not found null is
returned
select
boat_name
from
a_marina_slip m
left outer join
a_service_request s on
s.slip_id = m.slip_id
where
s.service_id is null
A working query performing the joins in the “where” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m,
a_owner o,
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Let’s change it to perform the joins in the “From” clause!
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m,
a_owner o,
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Essentially we’re going to
take the join conditions
from the “where” clause
and move them
into the “from”
clause
This is still the original query from the first slide; I simply added
some space between the tables for us to work in
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m,
a_owner o,
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
We no longer need the commas delimiting the tables
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m,
Get rid of commas
a_owner o,
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Add the word “join” between each set of tables
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o
join
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Add the word “on” after each table name that is preceded by a
“join”
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o
join
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Add the word “on” after each table name that is preceded by a
“join”
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o on
Note: the word “on” does
not go after the first table
in your query. It goes
after the tables that are
preceded by a “join”
join
a_marina_slip s on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
There isn’t a join we can perform directly that makes sense between
a_marina and a_owner because those two tables are not directly
related
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o on
join
a_marina_slip s on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Note: when joining in the “from” clause you can only join a
table to another table(s) that has been stated earlier in the
query
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o on
join
a_marina_slip s on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
a_marina and a_marina_slip are directly related so we can perform
a join between them. Lets switch a_marina_slip and a_owner
around
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o on
join
a_marina_slip s on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
a_marina and a_marina_slip are directly related so we can perform
a join between them. Lets switch a_marina_slip and a_owner
around
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_owner o on
join
a_marina_slip s on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
a_marina and a_marina_slip are directly related so we can perform
a join between them. Lets switch a_marina_slip and a_owner
around
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
join
a_owner o on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Now we’re ready to move the join conditions from the “where”
clause to the “from” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
join
a_owner o on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Our first join is between a_marina and a_marina_slip
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
First join is between
a_marina and
a_marina_slip
join
a_owner o on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Joining a_marina_slip to a_marina
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
We find the join condition(s)
between these two tables in the
“where” clause and move it up
into the “from” clause
join
a_owner o on
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Now we need to join a_owner to something
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
where
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
Now we need to join a_owner to something
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
where
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
We can join a_owner to
any of the tables stated
earlier in the query. a
join to a_marina_slip is
the only one that makes
sense because it is
directly related to
a_owner
Joining a_owner to a_marina_slip
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
where
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
We find the join
condition(s) between
these two tables in the
“where” clause and move
it up into the “from”
clause
Joining a_owner to a_marina_slip
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
o.owner_num = s.owner_num
where
s.boat_type like 'Dolphin%'
Filter on boat_type in the “where” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
o.owner_num = s.owner_num
where
s.boat_type like 'Dolphin%'
Because this query
involves only inner joins
we can filter in the
“where” clause (like we
are here) and the query
will yield the same
results as filtering in the
“from” clause.
This is a valid query that
performs the joins in the
“from” clause.
Filter on boat_type in the “from” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
o.owner_num = s.owner_num
where
s.boat_type like 'Dolphin%'
Alternatively we can
move the filter on
boat_type from the
“where” clause up into
the “from” clause in
either of these two spots
Filter on boat_type in the “from” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_owner o on
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
or
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num and
s.boat_type like 'Dolphin%'
join
a_owner o on
o.owner_num = s.owner_num
Both of these are valid queries performing the joins and filtering in the “from” clause
These 4 queries yield the same result set
Original query joining in the “where” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m,
a_owner o,
a_marina_slip s
where
m.marina_num = s.marina_num and
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
New queries joining in the “from” clause
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
Select
m.name,
s.boat_name,
o.first_name,
o.last_name
from
a_marina m
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_marina_slip s on
m.marina_num = s.marina_num
join
a_marina_slip s on
m.marina_num = s.marina_num and
s.boat_type like 'Dolphin%'
join
a_owner o on
o.owner_num = s.owner_num
join
a_owner o on
o.owner_num = s.owner_num and
s.boat_type like 'Dolphin%'
where
s.boat_type like 'Dolphin%'
join
a_owner o on
o.owner_num = s.owner_num