BMGT402
Spring
2013
Lab
#3
SQL
DML
Due:
11:59pm,
Monday,
March
4,
2013
For
this
lab,
you
will
be
creating
queries
based
on
the
SQL
Server
database,
AdventureWorks.
This
database
is
visible
in
Management
Studio
(MS)
when
you
sign
on
to
your
student
database
area.
Expand
Databases
and
you
will
see
AdventureWorks
at
the
top
of
the
database
list.
It
is
NOT
in
your
space
but
is
a
public
database,
visible
to
all
student
users.
Your
deliverable
for
this
lab
will
be
a
document
containing
your
SQL
DML
statements
and
the
output
from
executing
the
statements.
For
each
query
assignment
below,
please
cut
and
paste
your
SQL
code
from
MS
into
Microsoft
Word
and
type
a
statement
indicating
how
many
rows
were
returned.
Depending
on
questions,
you
may
need
to
copy
and
paste
several
rows
from
the
output
table.
Be
sure
to
name
your
document
yourLastName_yourFirstName_Lab3.docx
(or
yourLastName_yourFirstName_Lab3.doc).
Failing
to
follow
this
naming
convention
will
be
considered
that
you
fail
to
satisfy
one
requirement.
When
you
complete
this
lab
assignment,
submit
your
Word
document
via
Canvas.
Using
the
AdventureWorks
database,
please
perform
the
following
tasks:
1. Using
the
query
design
tool
in
MS,
create
a
query
that
lists
all
columns
from
the
[Link]
table.
How
many
rows
returned?
SELECT * FROM [Link] ; -- 395 rows
2. Using
the
query
design
tool
in
MS,
create
a
query
that
lists
all
Products
from
the
[Link]
table
where
the
color
is
Silver.
How
many
rows
returned?
SELECT * FROM [Link] WHERE Color = 'Silver' ; -- 43 rows
3. Using
the
query
design
tool
in
MS,
create
a
query
that
lists
only
the
VendorID,
AccountNumber,
Name,
and
CreditRating
in
alphabetical
order
by
Name
from
the
[Link]
table.
How
many
rows
returned?
SELECT VendorID, AccountNumber, Name, CreditRating FROM [Link] ORDER BY Name ; -- 104 rows
4. Using
the
query
design
tool
in
MS,
create
an
Employee
Directory
by
executing
a
query
that
lists
employees
LastName,
FirstName,
BirthDate,
MaritalStatus,
Gender,
and
HireDate
from
the
[Link]
and
[Link]
tables.
Order
the
output
by
LastName.
How
many
rows
returned?
BMGT402
Lab
3
SELECT [Link], [Link], [Link], [Link], [Link], [Link] FROM [Link] c, [Link] e WHERE [Link] = [Link] ORDER BY [Link] ; -- 290 rows
5. Using
the
query
design
tool
in
MS,
create
a
query
that
lists
LastName
and
FirstName
(from
[Link]),
OrderDate
(from
[Link]),
and
ItemValue
(created
from
OrderQty
*
UnitPrice
from
[Link])
AND
where
OrderDate
>=
July
1,
2004.
Order
the
output
by
LastName.
How
many
rows
returned?
(HINT:
This
is
a
3-table
query.)
SELECT [Link], [Link], [Link], [Link]*[Link] AS Item_Value FROM [Link] c, [Link] s, [Link] d WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] >= '7/01/2004' ORDER BY [Link] ; -- 2209 rows
6. Using
the
query
design
tool
in
MS,
create
a
query
that
computes
the
average
of
LineTotal
(label
the
result
as
Avg_Item_Value)
from
the
[Link]
table.
What
is
the
Avg_Item_Value
returned?
SELECT AVG (LineTotal) AS Avg_Item_Value FROM [Link] ; -- Avg_Item_Value =
905.449206
7. Using
the
query
design
tool
in
MS,
compute
the
total
(use
TotalDue
column
and
label
the
SUM
as
Sum_of_Orders)
of
all
orders
from
the
[Link]
table
from
12/1/2003
to
and
including
12/31/2003.
What
is
the
Sum_of_Orders
returned?
SELECT SUM (TotalDue) AS Sum_of_Orders FROM [Link] WHERE OrderDate >= '12/01/2003' AND OrderDate <= '12/31/2003' ; -- Sum_of_Orders = 6582833.0438
8. 'Sheela
Word'
changed
departments
multiple
times.
Using
the
query
design
tool
in
MS,
list
the
name
of
the
departments
'Sheela
Word'
worked
for,
and
also
list
the
date
'Sheela
Word'
started
to
work
for
each
department.
How
many
departments
did
'Sheela
Word'
work
for?
SELECT [Link], [Link] FROM [Link] h, [Link] e, [Link] d, [Link] c
BMGT402
Lab
3
WHERE [Link] = [Link] AND [Link] = [Link] AND [Link] = [Link] AND [Link] = 'Word' AND [Link] = 'Sheela' ; -- 3 rows
9. Using
the
query
design
tool
in
MS,
list
the
total
number
of
sales
orders
(use
[Link])
that
is
not
paid
with
a
credit
card.
What
is
the
total
number
returned?
SELECT COUNT (*) AS Num_of_Orders FROM [Link] h WHERE [Link] IS NULL ; -- Num_of_Orders = 1131
10. Using
the
query
design
tool
in
MS,
count
the
number
of
addresses
(use
[Link])
that
contain
'Santa'
as
part
of
any
attribute
used
to
represent
the
addresses.
What
is
the
number
returned?
SELECT COUNT (*) AS Num_of_Addresses FROM [Link] a WHERE AddressLine1 like '%Santa%' OR AddressLine2 like '%Santa%' OR City like '%Santa%' -- Num_of_Addresses = 292
BMGT402
Lab
3
