Transcript Exam 1 Review - Fox Valley Technical College

Mid-Term Review
What is a database?
structure containing categories of
information and relationships
between these categories
What is a Database Management
System (DBMS)?
software that lets you
create a database and
maintain the structures
and data within
What is open source software?
software whose source
code is freely and
publicly available
What does SQL stand for?
Structured Query Language
• What is another name for a table?
– Entity
• What is a relationship
– An association between entities
What are some reasons for
database normalization?
• minimize duplication of data
• help safeguard against data anomalies
• improve performance for certain activities
What is a primary key?
• A primary key uniquely identifies a row in a
table. A primary key can be a single column,
or a group of columns. When a primary key is
made up of multiple columns it may also be
referred to as a composite key or a
concatenated key, both of which mean
“consisting of more than one column.”
Tell me about null values
Shops
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
Revenues
shop_id
1
1
1
2
2
2
year quarter revenues
2008
1
10000
2008
2
7500
2008
3
6300
2008
1
4800
2008
2
7300
2008
3
null
Workers
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
What will the following query return?
select
*
from
Shops
Results
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
Write a query that will return all records
that have the letter “L” in their shop
name
select
*
from
Shops
where
shop_name like '%L%'
Results
shop_id
shop_name
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
Write a query that will return a count of
all records in the shops table
select
count(*)
from
Shops
Results
Count(*)
3
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
What will the following query return?
select
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
shop_name
Wisconsin Coffee Shop
shop_name
from
Shops s
join
workers w on
w.shop_id = s.shop_id and
w.name = 'Kris'
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
shop_name
worker name
College Coffee Shop
Jill
College Coffee Shop
Erika
Write a query that will return
shop name and worker name for
people working over 30 hours
select
s.shop_name,
w.name as 'worker name'
from
Shops s
join
workers w on
w.shop_id = s.shop_id and
w.weekly_hrs > 30
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Write a query that will return
shop name and worker name for
people working over 30 hours
select
s.shop_name,
w.name as 'worker name'
from
Shops s
join
workers w on
w.shop_id = s.shop_id
where
w.weekly_hrs > 30
Results
shop_name
worker name
College Coffee Shop
Jill
College Coffee Shop
Erika
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Write a query that will return
shop name and worker name for
people working over 30 hours
select
s.shop_name,
w.name as 'worker name'
from
Shops s,
workers w
where
w.shop_id = s.shop_id and
w.weekly_hrs > 30
Results
shop_name
worker name
College Coffee Shop
Jill
College Coffee Shop
Erika
What will the following query
return?
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
select
avg(pay_rt) as 'Average Pay Rate'
from
workers
Results
Average Pay Rate
8.666667
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
shop_name
Avg Pay Rt
College Coffee Shop
8.75
Northland Coffee Shop
9
Wisconsin Coffee Shop
8.5
Write a query that will return
average pay rate by shop name
select
s.shop_name,
avg(w.pay_rt) as 'Avg Pay Rt'
from
Shops s
join
workers w on
w.shop_id = s.shop_id
group by
s.shop_name
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
shop_name
Avg Pay Rt
College Coffee Shop
8.75
Northland Coffee Shop
9
Write a query that will return
average pay rate by shop name.
Only display shops with an
average pay rate over 8.50
select
s.shop_name,
avg(w.pay_rt) as 'Avg Pay Rt'
from
Shops s
join
workers w on
w.shop_id = s.shop_id
group by
s.shop_name
having
avg(w.pay_rt) > 8.5
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Write a query that will return average
pay rate by shop name. Don’t include
workers making less then 8.50 and
only display shops with an average
pay rate over 8.50
select
s.shop_name,
avg(w.pay_rt) as 'Avg Pay Rt'
from
Shops s
join
Results
shop_name
Avg Pay Rt
College Coffee Shop
8.75
Northland Coffee Shop
9
Wisconsin Coffee Shop
8.75
workers w on
w.shop_id = s.shop_id and
w.pay_rt >= 8.5
group by
s.shop_name
having
avg(w.pay_rt) > 8.5
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
name
Kris
Jill
Erika
Kim
Bill
What will the following query return?
select distinct
name
from
workers
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
What will the following query return?
Results
ERROR
Sort by isn’t a valid clause
select distinct
name
from
workers
sort by
1
worker_id shop_id name pay_rt weekly_hrs
1
1
Kris
8
25
2
2
Jill
8.5
40
3
2
Erika
9
40
4
1
Kim 8.5
30
5
1
Bill
9
25
6
3
Bill
9
15
Results
name
Bill
Erika
Jill
Kim
Kris
What will the following query return?
select distinct
name
from
workers
order by
1
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
1
1
1
2
2
2
year quarter revenues
2008
1
10000
2008
2
7500
2008
3
6300
2008
1
4800
2008
2
7300
2008
3
null
What will the following query return?
select
s.shop_name,
sum(r.revenues)
from
shops s
left outer join
revenues r on
r.shop_id = s.shop_id
Results
ERROR
Query is missing a group by
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
1
1
1
2
2
2
year quarter revenues
2008
1
10000
2008
2
7500
2008
3
6300
2008
1
4800
2008
2
7300
2008
3
null
What will the following query return?
select
s.shop_name,
sum(r.revenues)
from
shops s
left outer join
revenues r on
r.shop_id = s.shop_id
group by
s.shop_name
Results
shop_name
sum(r.revenues)
College Coffee Shop
12100
Northland Coffee Shop
Wisconsin Coffee Shop
23800
Explain left outer joins
Left outer join:
Returns data from the table on the left
regardless if it has a match in the table on
the right. If there is not a match on the right,
null values are returned.
Explain right outer joins
right outer join:
Returns data from the table on the right
regardless if it has a match in the table on
the left. If there is not a match on the left,
null values are returned.
All operator
condition is true only if it satisfies all values
What does T-SQL stand for?
• Transact Structured Query Language
What does PL/SQL stand for?
• Procedural Language/Structured Query
Language
Any operator
condition is true only if it satisfies any value
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
1
1
1
2
2
2
year quarter revenues
2008
1
10000
2008
2
7500
2008
3
6300
2008
1
4800
2008
2
7300
2008
3
2000
Results
shop_name
Northland Coffee Shop
Write a query that will list shops that
do not have any recorded revenues
(use IN in your query)
select
shop_name
from
shops
where
shop_id not in
(
select
shop_id
from
revenues
)
shop_id
shop_name
1
Wisconsin Coffee Shop
2
College Coffee Shop
3
Northland Coffee Shop
shop_id
1
1
1
2
2
2
year quarter revenues
2008
1
10000
2008
2
7500
2008
3
6300
2008
1
4800
2008
2
7300
2008
3
2000
Results
shop_name
Northland Coffee Shop
Write a query that will list shops that
do not have any recorded revenues
(use EXISTS in your query)
select
shop_name
from
shops s
where
not exists
(
select
shop_id
from
revenues r
where
r.shop_id = s.shop_id
)
What is a correlated subquery?
A subquery that involves a table
listed in outer query
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
What will the following query return?
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
Results
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
36
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
What will the following query return?
select
name,
car
from
car_dealer c
Results
name
Kris Windorski
Al Pacino
Al Pacino
car
Cavalier
Ferrari
Lexus
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
37
Why do these queries produce different results?
select
name,
car
from
car_dealer c
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
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.
However, the same cannot be said when dealing with
outer joins
Let’s take a closer
look
38
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
A left outer join returns data from the table
on the left regardless if it has a match in
the table on the right.
select
name,
car
from
car_dealer c
In this query in order for there to be a
match between the two tables the join
criteria must be met, which includes the
following :
- Matching car dealer ids
- A cost greater then
50,000
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
39
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
The car_dealer table is the table on the left, so we know every
car dealer’s name will be returned regadless if it satisfies the
join criteria
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
Results
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
40
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
If the join condition is satisfied the information from the right
(in this case the sales table) will also be returned. If the
condition is not met null values will be returned from the right.
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id and
s.cost > 50000
Results
name
Kris Windorski
Al Pacino
Al Pacino
Paris Hilton
Jessica Simpson
A.J. Hawk
car
Cavalier
Ferrari
Lexus
null
null
null
41
Car_Dealer_ID
Name
1
Kris Windorski
2
Al Pacino
3
Paris Hilton
4
Jessica Simpson
5
A.J. Hawk
Sale_ID
1
2
3
4
5
6
7
Car
Porsche
Cavalier
Mustang
Vibe
BMW
Ferrari
Lexus
Cost
40,000
60,000
30,000
19,000
42,000
65,000
61,000
Car_Dealer_ID
1
1
3
4
4
2
2
A left outer join returns data from the table
on the left regardless if it has a match in
the table on the right.
select
name,
car
from
car_dealer c
left outer join
sales s on
s.car_dealer_id = c.car_dealer_id
where
s.cost > 50000
In this query in order for there to be a
match between the two tables the join
criteria must be met, which includes the
following :
- Matching car dealer ids
Note: the “where” clause also
contains join/filter criteria, but
logically it is applied after the
join/filter criteria in the “from”
clause have been applied
42
Union vs. Union all
Instructor_ID
Name
1
Doug Waterman
2
Terri Keane
3
Glen Orsburn
4
Kris Windorski
Class_NBR Instructor_ID
Name
1111
4
SQL
4444
1
VB.net
2222
3
SQL
5555
2
C++
99999
4
Accounting
7777
4
SQL
What will the following
query return?
Results
Instructor Name
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Doug Waterman
Terri Keane
Glen Orsburn
Kris Windorski
Class Name
SQL
SQL
SQL
SQL
VB.net
VB.net
VB.net
VB.net
SQL
SQL
SQL
SQL
C++
C++
C++
C++
Accounting
Accounting
Accounting
Accounting
SQL
SQL
SQL
SQL
select
i.name as 'Instructor Name',
c.name as 'Class Name'
from
instructor i,
class c
A join is not specified for
the tables
SQL returns every possible
combination
Referred to as a Cartesian
Product
44
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