Transcript Single Table Queries Part 2

Single Table Queries
Part 2
Using Functions
A Guide to MySQL
2
Tourism_Revenue Table
State_Code
WI
MN
CA
FL
WI
Year
1999
1999
1997
1997
2000
Revenues
10,000,000
10,000,000
20,100,000
30,000,000
12,000,000
Select
min(Revenues) as ‘Minimum Revenues’
from
Tourism_Revenue
Result Set
Minimum Revenues
10,000,000
Tourism_Revenue Table
State_Code
WI
MN
CA
FL
WI
Year
1999
1999
1997
1997
2000
Revenues
10,000,000
10,000,000
20,100,000
30,000,000
12,000,000
Select
count(*) as ‘Record Count’
from
Tourism_Revenue
Result Set
Record Count
5
NFL_Fans Table
Name
Bill
Kathy
Kris
Judy
Jake
NFL_Team
Packers
Vikings
Packers
Packers
Vikings
Age
35
42
25
47
32
select
NFL_Team,
avg(Age) as 'Avg Fan Age'
from
NFL_Fans
group by
NFL_Team
Result Set
NFL_Team Avg Fan Age
Packers
35
Vikings
37
NFL_Fans Table
Name
Bill
Kathy
Kris
Judy
Jake
NFL_Team
Packers
Vikings
Packers
Packers
Vikings
Age
35
42
25
47
32
select
NFL_Team,
count(*) as 'Fan Count'
from
NFL_Fans
group by
NFL_Team
Result Set
NFL_Team
Packers
Vikings
Fan Count
3
2
NFL_Fans Table
Name
Bill
Kathy
Kris
Judy
Jake
NFL_Team
Packers
Vikings
Packers
Packers
Vikings
Age
35
42
25
47
32
select
NFL_Team,
count(*) as 'Fan Count'
from
NFL_Fans
group by
NFL_Team
having
count(*) > 2
Result Set
NFL_Team
Packers
Fan Count
3
where vs. having
• where clause
– filters out data not meeting a certain criteria
– data that is not filtered out can then be placed
into groups
• having clause
– filters out groups not meeting a certain criteria
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
WI Population: 5,363,675
Would it make a whole lot of sense for
us to have everyone in the U.S. get
into groups based on Political Party if
we really only want to put Wisconsin
people into groups?
group by
Political_Party
No.
having
count(*) > 10000
order by
1
We only care about 5.3 million
people, so there’s no reason to
have the other 300 million +
people get into groups.
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
WI Population: 5,363,675
Would it make a whole lot of sense for
us to have everyone in the U.S. get
into groups based on Political Party if
we really only want to put Wisconsin
people into groups?
group by
Political_Party
No.
having
count(*) > 10000
order by
1
We only care about 5.3 million
people, so there’s no reason to
have the other 300 million +
people get into groups.
So we filter out people from the
other 49 states before we even
create groups (where clause)
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
WI Population: 5,363,675
Can we have Political Party groups
with 10,000 or less members go home
before we even place the people of
Wisconsin into groups?
State = 'WI'
group by
Political_Party
No.
having
count(*) > 10000
order by
1
We need to put people into
groups so that we can count
how many are in each group.
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
WI Population: 5,363,675
Can we have Political Party groups
with 10,000 or less members go home
before we even place the people of
Wisconsin into groups?
No.
We need to put people into
groups so that we can count
how many are in each group.
So we place the people from
Wisconsin into groups based on
Political Party (group by clause)
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
WI Population: 5,363,675
Let’s say we have 20 political parties in
Wisconsin, but only 2 have member
counts greater then 10,000. Would it
make sense to have all 20 parties line
up in alphabetical order if we only care
about seeing in order the 2 that have
more then 10,000 members?
No.
I struggle enough with the
alphabet, so I’d much rather
order just the 2 groups instead
of all 20.
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
WI Population: 5,363,675
Let’s say we have 20 political parties in
Wisconsin, but only 2 have member
counts greater then 10,000. Would it
make sense to have all 20 parties line
up in alphabetical order if we only care
about seeing in order the 2 that have
more then 10,000 members?
No.
I struggle enough with the
alphabet, so I’d much rather
order just the 2 groups instead
of all 20.
So we get rid of the groups that
do not have more then 10,000
members (having clause)
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
WI Population: 5,363,675
Now that we only have Wisconsin
people in our groups, and we only
have the groups with more then
10,000 members being returned, let’s
sort the data!
U.S. Population: 305,709,250
select
Political_Party,
count(*) as 'Member count'
from
USA
where
State = 'WI'
group by
Political_Party
having
count(*) > 10000
order by
1
WI Population: 5,363,675
Now that we only have Wisconsin
people in our groups, and we only
have the groups with more then
10,000 members being returned, let’s
sort the data! (Order by clause)
Instructor Table
What will the following query return?
instructor_id
name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
select
dept_id,
count(*)
from
instructor
Error
Query is missing a “group by” clause
Instructor Table
What will the following query return?
instructor_id
name
Dept_ID
1
Doug Waterman
1
2
Terri Keane
1
3
Glen Orsburn
1
4
Kris Windorski
2
select
dept_id,
count(*)
from
instructor
group by
dept_id
Results
dept_id count(*)
1
3
2
1
Nesting Queries
• Query results require two or more steps
• Subquery: an inner query placed inside another
query
• Outer query uses subquery results
A Guide to MySQL
20
A_Owner Table
Let’s say we want to find slip
information for all boats
owned by people with a first
name of Mary
We know first name
information does not exist in
the Marina Slip table, which
is where the slip information
exists, but it does exist in the
Owner table
We also know the Owner
table and the Marina Slip
table are related to each
other by OWNER_NUM
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
We can write a query against
the Owner table using the
filter information provided
(First_Name = ‘Mary’) to
bring back Owner_Num
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
select
owner_num
from
a_owner
where
first_name = 'Mary'
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
We can write a query against
the Owner table using the
filter information provided
(First_Name = ‘Mary’) to
bring back Owner_Num
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
select
owner_num
from
a_owner
where
first_name = 'Mary'
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
We can use the result set
from the query against the
Owner table in a query
against the Marina Slip table
Select
*
from
a_marina_slip
where
owner_num in
(
select
owner_num
from
a_owner
where
first_name = 'Mary'
)
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
We can use the result set
from the query against the
Owner table in a query
against the Marina Slip table
Select
*
from
a_marina_slip
where
owner_num in
(
select
owner_num
from
a_owner
where
first_name = 'Mary'
)
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
Result Set
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
9
2
4
30
2500
Mermaid
Dolphin 28
BL72
Outer query
Subquery
Select
*
from
a_marina_slip
where
owner_num in
(
select
owner_num
from
a_owner
where
first_name = 'Mary'
)
The result of the subquery is used by the outer query
A_Owner Table
Let’s say we want to find
owner information for people
who own a Sprite 4000
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
A_Owner Table
Select
*
from
a_owner
where
owner_num =
(
select
owner_num
from
a_marina_slip
where
boat_type = 'Sprite 4000'
)
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
Query will error because the subquery returns more then one record.
A_Owner Table
Select
*
from
a_owner
where
owner_num in
(
select
owner_num
from
a_marina_slip
where
boat_type = 'Sprite 4000'
)
OWNER_NUM LAST_NAME FIRST_NAME
ADDRESS
CITY
STATE ZIP
AD57
Adney
Bruce and Jean
208 Citrus
Bowton
FL 31313
AN75
Anderson
Bill
18 Wilcox
Glander Bay FL 31044
BL72
Blake
Mary
2672 Commodore Bowton
FL 31313
EL25
Elend
Sandy and Bill
462 Riverside
Rivard
FL 31062
FE82
Feenstra
Daniel
7822 Coventry
Kaleva
FL 32521
JU92
Juarez
Maria
8922 Oak
Rivard
FL 31062
KE22
Kelly
Alyssa
5271 Waters
Bowton
FL 31313
NO27
Norton
Peter
2811 Lakewood
Lewiston
FL 32765
SM72
Smeltz
Becky and Dave
922 Garland
Glander Bay FL 31044
TR72
Trent
Ashton
922 Crest
Bay Shores
FL 30992
A_Marina_Slip Table
SLIP_ID MARINA_NUM SLIP_NUM LENGTH RENTAL_FEE BOAT_NAME BOAT_TYPE OWNER_NUM
1
1
A1
40
3800
Anderson II Sprite 4000
AN75
2
1
A2
40
3800
Our Toy
Ray 4025
EL25
3
1
A3
40
3600
Escape
Sprite 4000
KE22
4
1
B1
30
2400
Gypsy
Dolphin 28
JU92
5
1
B2
30
2600
Anderson III Sprite 3000
AN75
6
2
1
25
1800
Bravo
Dolphin 25
AD57
7
2
2
25
1800
Chinook
Dolphin 22
FE82
8
2
3
25
2000
Listy
Dolphin 25
SM72
9
2
4
30
2500
Mermaid Dolphin 28
BL72
10
2
5
40
4200
Axxon II
Dolphin 40
NO27
11
2
6
40
4200
Karvel
Ray 4025
TR72
Result Set
OWNER_NUM LAST_NAME FIRST_NAME ADDRESS
CITY
STATE ZIP
AN75
Anderson
Bill
18 Wilcox Glander Bay FL 31044
KE22
Kelly
Alyssa
5271 Waters Bowton
FL 31313
Null
• A value of NULL indicates that the value is
unknown. A value of NULL is different from an
empty or zero value. No two null values are
equal. Comparisons between two null values,
or between a NULL and any other value,
return unknown because the value of each
NULL is unknown.
select
slip_id,
next_service_date
from
a_service_request
slip_id next_service_date
1
2007-07-12
5
2007-07-12
4
2007-07-16
1
2007-07-13
3
2007-07-13
11
null
6
2007-07-13
6
2007-07-17
7
2007-07-16
2
2007-07-17
2
null
4
2007-07-16
8
2007-07-12
7
2007-07-13
11
2007-07-17
select
slip_id,
next_service_date
from
a_service_request
where
next_service_date is null
slip_id next_service_date
11
null
2
null
select
slip_id,
next_service_date
from
a_service_request
where
next_service_date is not null
slip_id
1
5
4
1
3
6
6
7
2
4
8
7
11
next_service_date
2007-07-12
2007-07-12
2007-07-16
2007-07-13
2007-07-13
2007-07-13
2007-07-17
2007-07-16
2007-07-17
2007-07-16
2007-07-12
2007-07-13
2007-07-17
Using the tables in Alexamara Marina
Return the marina number, slip number, and
boat name of every slip who’s rental fee is
greater then average of all rental fees.
What does ETL stand for?
• Extract, Transform, and Load
• ETL is a term/concept frequently associated with
Data Warehousing
• Examples of ETL Tools
– Informatica PowerCenter
– SSIS (SQL Server Integration Services; MS SQL Server
2005 and above)
– DTS (Data Transformation Services; MS SQL Server
2000)
– IBM Cognos DecisionStream