Transcript Multiple Table Queries Part 2

Multiple Table Queries Part 2
1
Instructor Table
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class Table
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
2
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List Instructor names and the
names of the classes they teach
Ask yourself:
- Which tables contain the
information I need?
- How are the tables related to one
another?
3
List Instructor names and the
names of the classes they teach
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
select
i.name as ‘Instructor Name’,
c.name as ‘Class Name’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
from
instructor i
table aliases
join
class c on
c.instructor_id = i.instructor_id
Results
Instructor Name
Kris Windorski
Doug Waterman
Glen Orsburn
Terri Keane
Kris Windorski
Kris Windorski
Class Name
SQL
VB.net
SQL
C++
Accounting
SQL
4
List Instructor names and the
names of the classes they teach
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
select
i.name as ‘Instructor Name’,
c.name as ‘Class Name’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
from
instructor i,
class c
where
c.instructor_id = i.instructor_id
Results
Instructor Name
Kris Windorski
Doug Waterman
Glen Orsburn
Terri Keane
Kris Windorski
Kris Windorski
Class Name
SQL
VB.net
SQL
C++
Accounting
SQL
5
Joining in the “from” clause
select
Joining in the “where” clause
select
i.name as ‘Instructor Name’,
c.name as ‘Class Name’
from
i.name as ‘Instructor Name’,
c.name as ‘Class Name’
from
instructor i
instructor i,
class c
join
class c on
c.instructor_id = i.instructor_id
where
c.instructor_id = i.instructor_id
6
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
List the names of instructors
teaching SQL
select
i.name as ‘Instructor Name’
from
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
instructor i
join
class c on
c.instructor_id = i.instructor_id
where
c.name = ‘SQL’
Results
Instructor Name
Kris Windorski
Glen Orsburn
Kris Windorski
How can I get rid of the duplicate Instructor Name?
7
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors
teaching SQL (distinct list)
select distinct
i.name as ‘Instructor Name’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
where
c.name = ‘SQL’
Results
Instructor Name
Kris Windorski
Glen Orsburn
8
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors
teaching SQL (distinct list)
select distinct
i.name as ‘Instructor Name’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id and
c.name = ‘SQL’
Results
Instructor Name
Kris Windorski
Glen Orsburn
9
Filtering in the where clause
select
Filtering in the from clause
select
i.name as ‘Instructor Name’
from
i.name as ‘Instructor Name’
from
instructor i
join
instructor i
join
class c on
c.instructor_id = i.instructor_id
where
class c on
c.instructor_id = i.instructor_id and
c.name = ‘SQL’
c.name = ‘SQL’
When a query only has inner joins a filter placed in the “where”
clause will yield the same result as placing the filter in the “from”
clause
10
List the names of instructors and
a count of classes they are
teaching
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
Results
ERROR!!
Query is missing a “group by” clause
11
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
List the names of instructors and
a count of classes they are
teaching
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
group by
i.name
Results
Instructor Name Class Count
Doug Waterman
1
Glen Orsburn
1
Kris Windorski
3
Terri Keane
1
12
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors and
a count of classes they are
teaching. Do not include
Accounting classes in your class
counts.
What needs to be added to this query?
- a filter excluding Accounting classes in a
“where” clause or in the “join”
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
group by
i.name
13
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
Results
Instructor Name Class Count
Doug Waterman
1
Glen Orsburn
1
Kris Windorski
2
Terri Keane
1
List the names of instructors and
a count of classes they are
teaching. Do not include
Accounting classes in your class
counts.
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id
where
c.name <> ‘Accounting’
group by
i.name
14
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors and
a count of classes they are
teaching. Do not include
Accounting classes in your class
counts.
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id and
c.name <> ‘Accounting’
Results
Instructor Name Class Count
Doug Waterman
1
Glen Orsburn
1
Kris Windorski
2
Terri Keane
1
group by
i.name
15
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors and a count
of classes they are teaching. Do not
include Accounting classes in your class
counts, and only display instructors
teaching more then 1 class
What needs to be added to this query?
- a “having” clause excluding groups that
do not have a class count greater then 1
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id and
c.name <> ‘Accounting’
group by
i.name
16
Instructor
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List the names of instructors and a count
of classes they are teaching. Do not
include Accounting classes in your class
counts, and only display instructors
teaching more then 1 class
select
i.name as ‘Instructor Name’,
count(*) as ‘Class Count’
from
instructor i
join
class c on
c.instructor_id = i.instructor_id and
c.name <> ‘Accounting’
Results
Instructor Name Class Count
Kris Windorski
2
group by
i.name
having
count(*) > 1
17
Let’s add another table!
Department Table
Dept_ID Name
1
IT
2
Adjunct
Instructor Table
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class Table
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
18
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
List department names and the
names of the classes they offer
Ask yourself:
- Which tables contain the
information I need?
- How are the tables related to one
another?
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
19
Department
List department names and the
names of the classes they offer
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
select distinct
d.name as ‘Department Name’,
c.name as ‘Class Name’
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
Results
Department Name Class Name
Adjunct
SQL
IT
VB.net
IT
SQL
IT
C++
Adjunct
Accounting
20
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List department names and the
names of the classes they offer
select distinct
d.name as ‘Department Name’,
c.name as ‘Class Name’
from
department d,
instructor i,
class c
where
i.dept_id = d.dept_id and
c.instructor_id = i.instructor_id
Results
Department Name Class Name
Adjunct
SQL
IT
VB.net
IT
SQL
IT
C++
Adjunct
Accounting
21
Department
List department names and count
of classes they offer
Dept_ID Name
1
IT
2
Adjunct
Instructor
select
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
Results
ERROR!!
Query is missing a “group by” clause
22
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List department names and count
of classes they offer
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
group by
d.name
Results
Department Name Class Count
Adjunct
3
IT
3
23
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
List department names and count of
classes they offer. Only list departments
offering more then 3 classes.
What needs to be added to this query?
- a “having” clause excluding groups that
do not have a class count greater then 3
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
group by
d.name
24
Department
List department names and count of
classes they offer. Only list departments
offering more then 3 classes.
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
group by
d.name
having
count(*) > 3
Results
Department Name Class Count
25
Department
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
List department names and count of classes they
offer. Do not include Accounting in your class
counts, and only list departments offering more
then 2 classes.
What needs to be added to this query?
- a filter excluding Accounting classes in a
“where” clause or in the “join”
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
from
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
group by
d.name
having
count(*) > 2
26
Department
List department names and count of classes they
offer. Do not include Accounting in your class
counts, and only list departments offering more
then 2 classes.
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id and
c.name <> ‘Accounting’
group by
d.name
having
Results
Department Name Class Count
IT
3
count(*) > 2
27
Department
List department names and count of classes they
offer. Do not include Accounting in your class
counts, and only list departments offering more
then 2 classes.
Dept_ID Name
1
IT
2
Adjunct
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
select
d.name as ‘Department Name’,
count(*) as ‘Class Count’
from
department d
join
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
where
c.name <> ‘Accounting’
group by
d.name
Results
Department Name Class Count
IT
3
having
count(*) > 2
28
Department
What will the following query return?
Dept_ID Name
1
IT
2
Adjunct
select
d.name as ‘Department Name’,
i.name as ‘Instructor Name’,
c.name as ‘Class Name’
Instructor
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
from
department d
join
instructor i on
i.dept_id = d.dept_id and
i.name = ‘Kris Windorski’
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
join
class c on
c.instructor_id = i.instructor_id
Results
Department Name Instructor Name Class Name
Adjunct
Kris Windorski
SQL
Adjunct
Kris Windorski Accounting
Adjunct
Kris Windorski
SQL
29
Department
What will the following query return?
Dept_ID Name
1
IT
2
Adjunct
select
Instructor
from
Instructor_ID
Name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
Class
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
*
department d
join
instructor i on
i.dept_id = d.dept_id
join
class c on
c.instructor_id = i.instructor_id
where
c.class_nbr = ‘99999’
Results
dept_id name instructor_id
name
Dept_ID class_nbr instructor_id
name
2
Adjunct
4
Kris Windorski
2
99999
4
Accounting
30
For every boat, list the marina number, slip
number, boat name, owner number, owner’s
first name, and owner’s last name
• Which tables contain the information I need?
– a_marina_slip (marina_num, slip_num,
boat_name, & owner_num)
– a_owner (owner_num, first_name, & last_name)
• How are the tables related to one another?
– by owner_num
(a_marina_slip.owner_num = a_owner.owner_num)
31
For every boat, list the marina number, slip
number, boat name, owner number, owner’s
first name, and owner’s last name
select
marina_num,
slip_num,
boat_name,
a_marina_slip.owner_num,
first_name,
last_name
from
a_marina_slip
join
a_owner on
a_marina_slip.owner_num = a_owner.owner_num
32
33
For every completed or open service request with a
category description of “routine engine maintenance”,
list the slip id, request description, and status
• Which tables contain the information I need?
– a_service_request (slip_id, description, staus)
– a_service_category (category_description)
• How are the tables related to one another?
– by category_num
(a_service_request.category_num = a_service_category.category_num)
34
For every completed or open service request with a
category description of “routine engine maintenance”,
list the slip id, request description, and status
select
slip_id,
description,
status
from
a_service_request r
join
a_service_category c on
r.category_num = c.category_num and
category_description = 'routine engine maintenance'
35