IS675 – Database Design
Bilal Gonen
My answers for SQL-3
#1. Create a listing of all the orders in the ORDER
table with an orderdate in January in the current
year. Make sure your query uses the
current year from GETDATE, rather than hard-coding 2008 into the WHERE
clause. Include the orderdate,
orderid, customer name (last name and first initial),
and customer phone number. Sort the
orders by date so that the most current orderdate is
first and the oldest orderdate is last in the list.
#1. Code
SELECT CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
orderID,
lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",
'('+SUBSTRING(Phone,1,3)+')
'+ SUBSTRING(Phone,5,12) "Phone Number"
FROM TblOrder ord
LEFT JOIN TblCustomer cust
ON ord.CustomerID = cust.CustomerID
WHERE DATEPART(yyyy,
OrderDate) = DATEPART(yyyy,
getdate())
DATEPART(mm, OrderDate) = '01'
ORDER BY OrderDate DESC
#1. Output
|
dbo.SQL2_3 |
|||
|
Date of Order |
orderID |
Customer Name |
Phone Number |
|
|
300221 |
|
(775) 883-7612 |
|
|
651222 |
|
(775) 883-7612 |
|
|
567123 |
Rodriguez, K. |
(819) 382-1828 |
|
|
980001 |
Guili, M. |
(619) 562-1334 |
|
|
200335 |
Phillips, K. |
(775) 332-4636 |
#2. Expand
the query created for question #1 to include information about each item on
order. Include the same parameters as
requested in question #1, but expand the output columns to include the item
number on order, the description, quantity on order, price, and extended price
(quantity * price).
#2. Code
SELECT CONVERT(VARCHAR, OrderDate, 101) "Date of Order",
ord.orderID "Order Number",
lastname + ', ' + SUBSTRING(firstname,1,1)+'.'
"Customer Name",
'('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone
Number",
item.ItemID "Item Number",
item.description "Item Name",
ol.quantity "Qty on Order",
ol.ListPrice "Price",
ol.quantity * ol.ListPrice
"Extended Price"
FROM TblOrder ord
LEFT
JOIN TblCustomer
cust
ON ord.CustomerID
= cust.CustomerID
LEFT
JOIN TblOrderLine
ol
ON ord.orderID
= ol.OrderID
LEFT
JOIN TblItem
item
ON ol.ItemID
= item.ItemID
WHERE DATEPART(yyyy, OrderDate)
= DATEPART(yyyy, getdate())
DATEPART(mm, OrderDate) = '01'
ORDER
BY OrderDate
DESC
#2. Output
|
dbo.SQL2_3 |
||||||||
|
Date of Order |
Order Number |
Customer Name |
Phone Number |
Item Number |
Item Name |
Qty on Order |
Price |
Extended Price |
|
|
300221 |
|
(775) 883-7612 |
A23771 |
Catch Phrase Game Music Edition |
1 |
$65.95 |
$65.95 |
|
|
300221 |
|
(775) 883-7612 |
A34665 |
Boggle Deluxe 5x5 |
1 |
$35.95 |
$35.95 |
|
|
300221 |
|
(775) 883-7612 |
A34882 |
Perudo |
1 |
$10.95 |
$10.95 |
|
|
300221 |
|
(775) 883-7612 |
B67123 |
Cranium WOW Edition |
1 |
$15.95 |
$15.95 |
|
|
300221 |
|
(775) 883-7612 |
B78244 |
Chicks Battle the Dudes Board Game |
1 |
$32.95 |
$32.95 |
|
|
651222 |
|
(775) 883-7612 |
A34665 |
Boggle Deluxe 5x5 |
5 |
$37.95 |
$189.75 |
|
|
651222 |
|
(775) 883-7612 |
A34882 |
Perudo |
16 |
$11.95 |
$191.20 |
|
|
651222 |
|
(775) 883-7612 |
B78244 |
Chicks Battle the Dudes Board Game |
21 |
$31.65 |
$664.65 |
|
|
567123 |
Rodriguez, K. |
(819) 382-1828 |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
$395.95 |
$395.95 |
|
|
980001 |
Guili, M. |
(619) 562-1334 |
C29179 |
Managing Change: The Game for an Executive Retreat |
3 |
$275.99 |
$827.97 |
|
|
980001 |
Guili, M. |
(619) 562-1334 |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
2 |
$169.95 |
$339.90 |
|
|
200335 |
Phillips, K. |
(775) 332-4636 |
A23441 |
New York City Monopoly Game Collector’s Edition |
1 |
$29.95 |
$29.95 |
|
|
200335 |
Phillips, K. |
(775) 332-4636 |
A34665 |
Boggle Deluxe 5x5 |
1 |
$34.95 |
$34.95 |
|
|
200335 |
Phillips, K. |
(775) 332-4636 |
B67123 |
Cranium WOW Edition |
1 |
$19.95 |
$19.95 |
|
|
200335 |
Phillips, K. |
(775) 332-4636 |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
$43.95 |
$43.95 |
#3. Create
a report detailing information about items in inventory at each location. The report should contain the itemID, item description, location ID and qtyonhand. For items
that don’t have a qtyonhand, substitute the value
zero.
#3. Code
SELECT item.ItemID
"Item Number",
item.description
"Item Name",
ISNULL(LocationID, '**') "Location Number",
ISNULL(QtyOnHand, 0) "Quantity on Hand"
FROM TblItem
item
LEFT
JOIN TblItemLocation
loc
ON item.ItemID
= loc.ItemID
#3. Output
|
dbo.SQL2_3 |
|||
|
Item Number |
Item Name |
Location Number |
Quantity on Hand |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
10 |
11 |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
20 |
23 |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
30 |
25 |
|
A23771 |
Catch Phrase Game Music Edition |
10 |
6 |
|
A23771 |
Catch Phrase Game Music Edition |
20 |
4 |
|
A23771 |
Catch Phrase Game Music Edition |
30 |
5 |
|
A34665 |
Boggle Deluxe 5x5 |
10 |
141 |
|
A34882 |
Perudo |
10 |
40 |
|
A34882 |
Perudo |
30 |
55 |
|
A45111 |
How to Host a Murder - An Affair to Dismember |
** |
0 |
|
B67123 |
Cranium WOW Edition |
10 |
22 |
|
B67123 |
Cranium WOW Edition |
20 |
28 |
|
B67123 |
Cranium WOW Edition |
30 |
6 |
|
B67123 |
Cranium WOW Edition |
40 |
4 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
10 |
0 |
|
B78244 |
Chicks Battle the Dudes Board Game |
20 |
22 |
|
B78500 |
You Might be a Redneck if...Jeff Foxworthy Board Game |
20 |
8 |
|
B78500 |
You Might be a Redneck if...Jeff Foxworthy Board Game |
30 |
1 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
10 |
0 |
|
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
15 |
|
C29179 |
Managing Change: The Game for an Executive Retreat |
20 |
15 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
10 |
16 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
20 |
21 |
#4. Which customers do not have any orders currently in
the order table? List
information about those customers such as customerID,
customer first and last name, customer state and zip, and firstbuydate.
#4. Code
SELECT CustomerID
"Customer Number",
firstname + ' ' + lastname "Customer Name",
UPPER(SUBSTRING(city,1,1))+LOWER(SUBSTRING(city,2,20))
"City",
UPPER(state)
"State",
Zip,
CONVERT(VARCHAR,
FirstBuyDate, 107) "First Buy Date"
FROM TblCustomer
WHERE CustomerID NOT IN
(
SELECT CustomerID
FROM
TblOrder
)
ORDER
BY CustomerID
#4. Output
|
dbo.SQL2_3 |
|||||
|
Customer Number |
Customer Name |
City |
State |
Zip |
First Buy Date |
|
08892 |
|
|
CA |
98123 |
|
|
12001 |
Brittany Cranston |
|
NV |
89431 |
|
|
21143 |
Janice Jackson |
|
NV |
89431-0112 |
|
|
29188 |
Tiffany Polanski |
|
NV |
89507 |
|
|
30192 |
Lian Chen |
|
NV |
89503-0113 |
|
|
32817 |
Ben Foster |
|
CA |
92381 |
|
#5. What is the most expensive item that the company
currently has on order? (Use the price in tblorderline
for this determination.) List the orderID, itemID, item
description, selling price and listprice.
#5. Code
SELECT ol.orderID
"Order Number",
ol.itemID
"Item Number",
item.description
"Item Name",
ol.ListPrice
"Selling Price",
item.ListPrice
"List Price"
FROM TblOrderLine
ol
LEFT
JOIN tblitem
item
ON ol.itemID
= item.itemID
WHERE ol.ListPrice
=
(
SELECT
FROM TblOrderLine
)
#5. Output
|
dbo.SQL2_3 |
||||
|
Order Number |
Item Number |
Item Name |
Selling Price |
List Price |
|
450137 |
C26133 |
Knowledge Management: Create a Learning Organization |
$398.95 |
$395.95 |
#6. Expand the query created for #5 to also include the
most recent lastcost and the most recent lastcostdate for that item from the tblitemcosthistory
table. Calculate the difference between
the list price and the most recent lastcost. There should still be only one row in the
result table. This query is accomplished
most easily by adding a correlated sub-query to the existing SQL code.
#6. Code
SELECT ol.itemID
"Item Number",
item.description
"Item Name",
ol.orderID
"Order Number",
ol.ListPrice
"Selling Price",
item.ListPrice
"List Price",
lastCost "Most Recent Cost",
item.ListPrice
- lastCost "Difference Between
Price and Cost",
CONVERT(VARCHAR,
lastCostDate, 101) "Most Recent Purchase
Date"
FROM TblOrderLine
ol
LEFT
JOIN tblitem
item
ON ol.itemID
= item.itemID
LEFT
JOIN tblitemCostHistory
ON tblitemCostHistory.itemID
= item.itemID
WHERE ol.ListPrice
=
(
SELECT
FROM TblOrderLine
)
(
SELECT
FROM tblItemCostHistory
)
#6. Output
|
dbo.SQL2_3 |
|||||||
|
Item Number |
Item Name |
Order Number |
Selling Price |
List Price |
Most Recent Cost |
Difference Between
Price and Cost |
Most Recent Purchase
Date |
|
C26133 |
Knowledge Management: Create a Learning Organization |
450137 |
$398.95 |
$395.95 |
$198.00 |
$197.95 |
|
#7. Create a report summarizing information about all
the orders in the ORDER table. Include
the orderid, orderdate,
customer name, a count of the number of different items on the order, a sum of
the quantity of the items on the order and the total extended price of the order
(sum of quantity * price). The result
table should have one row per order.
#7. Code
SELECT ord.orderID
"Order Number",
CONVERT(VARCHAR,
OrderDate, 101) "Order Date",
lastname + ', ' + firstname
"Customer Name",
COUNT(ol.ItemID) "Number of Items",
SUM(ol.quantity) "Total Quantity Ordered",
SUM(ol.quantity * ol.ListPrice)
"Total Extended Price"
FROM TblOrder ord
LEFT
JOIN TblCustomer
cust
ON ord.CustomerID
= cust.CustomerID
LEFT
JOIN TblOrderLine
ol
ON ord.orderID
= ol.OrderID
LEFT
JOIN TblItem
item
ON ol.ItemID
= item.ItemID
GROUP
BY ord.orderID,
cust.lastname,
cust.firstname,
ord.OrderDate
#7. Output
|
dbo.SQL2_3 |
|||||
|
Order Number |
Order Date |
Customer Name |
Number of Items |
Total Quantity
Ordered |
Total Extended Price |
|
123000 |
|
Barrington, Margaret |
3 |
43 |
$1,457.85 |
|
200335 |
|
Phillips, Kendall |
4 |
4 |
$128.80 |
|
223344 |
|
Candriller, Kathy |
6 |
245 |
$6,118.35 |
|
300221 |
|
Martinez, Guadalupe |
5 |
5 |
$161.75 |
|
400001 |
|
Jones, Martin |
1 |
1 |
$395.95 |
|
445511 |
|
Jones, Martin |
1 |
3 |
$809.85 |
|
450137 |
|
Rodriguez, Karen |
5 |
88 |
$2,528.10 |
|
567123 |
|
Rodriguez, Karen |
1 |
1 |
$395.95 |
|
651222 |
|
Martinez, Guadalupe |
3 |
42 |
$1,045.60 |
|
671100 |
|
Jones, Martin |
1 |
1 |
$25.95 |
|
781206 |
|
Argiento, Bud |
1 |
1 |
$43.95 |
|
892211 |
|
Dao, Phong |
3 |
33 |
$9,899.50 |
|
980001 |
|
Guili, Mary Anne |
2 |
5 |
$1,167.87 |
#8. Modify the query created for question #7 to display
only those orders with an order total (total extended price) > $3,500.
#8. Code
SELECT ord.orderID
"Order Number",
CONVERT(VARCHAR,
OrderDate, 101) "Order Date",
lastname + ', ' + firstname
"Customer Name",
COUNT(ol.ItemID) "Number of Items",
SUM(ol.quantity) "Total Quantity Ordered",
SUM(ol.quantity * ol.ListPrice)
"Total Extended Price"
FROM TblOrder ord
LEFT
JOIN TblCustomer
cust
ON ord.CustomerID
= cust.CustomerID
LEFT
JOIN TblOrderLine
ol
ON ord.orderID
= ol.OrderID
LEFT
JOIN TblItem
item
ON ol.ItemID
= item.ItemID
GROUP
BY ord.orderID,
cust.lastname,
cust.firstname,
ord.OrderDate
HAVING SUM(ol.quantity * ol.ListPrice) >
3500
#8. Output
|
dbo.SQL2_3 |
|||||
|
Order Number |
Order Date |
Customer Name |
Number of Items |
Total Quantity
Ordered |
Total Extended Price |
|
223344 |
|
Candriller, Kathy |
6 |
245 |
$6,118.35 |
|
892211 |
|
Dao, Phong |
3 |
33 |
$9,899.50 |
#9. Create a report summarizing information about the
items in the ITEM table. Include the itemID, name of the item, the list price, and the most
recent last cost (from tblitemcosthistory). Determine the average selling price, the
minimum selling price and the maximum selling price for that item from the
ORDERLINE table. There should be one row
in the result table per row in the ITEM table.
#9. Code
SELECT item.itemID
"Item ID",
item.description
"Item Name",
item.ListPrice
"List Price",
h1.LastCost "Most
Recent Cost",
AVG(ol.ListPrice) "Average Selling Price",
MIN(ol.ListPrice) "Minimum Selling Price",
MAX(ol.ListPrice) "Maximum Selling Price"
FROM TblItem
item
LEFT
JOIN tblitemCostHistory
h1
ON h1.itemID = item.itemID
LEFT
JOIN TblOrderLine
ol
ON ol.ItemID
= item.ItemID
WHERE h1.LastCostDate = (
SELECT MAX(h2.LastCostDate)
FROM tblitemCostHistory h2
WHERE h2.itemID = h1.itemID
)
GROUP
BY item.itemID,
item.description,
item.ListPrice,
h1.LastCost
ORDER
BY item.itemID
#9. Output
|
dbo.SQL2_3 |
||||||
|
Item ID |
Item Name |
List Price |
Most Recent Cost |
Average Selling Price |
Minimum Selling Price |
Maximum Selling Price |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
$29.95 |
$10.35 |
$29.95 |
$29.95 |
$29.95 |
|
A23771 |
Catch Phrase Game Music Edition |
$32.95 |
$8.50 |
$59.97 |
$53.99 |
$65.95 |
|
A34665 |
Boggle Deluxe 5x5 |
$34.95 |
$15.00 |
$33.63 |
$23.95 |
$37.95 |
|
A34882 |
Perudo |
$10.95 |
$6.50 |
$10.20 |
$7.95 |
$11.95 |
|
B67123 |
Cranium WOW Edition |
$15.95 |
$14.00 |
$16.35 |
$14.95 |
$19.95 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
$43.95 |
$31.00 |
$42.95 |
$40.95 |
$43.95 |
|
B78244 |
Chicks Battle the Dudes Board Game |
$38.95 |
$14.50 |
$32.30 |
$31.65 |
$32.95 |
|
B78500 |
You Might be a Redneck if...Jeff Foxworthy Board Game |
$35.95 |
$18.00 |
|
|
|
|
C26133 |
Knowledge Management: Create a Learning Organization |
$395.95 |
$198.00 |
$392.71 |
$380.00 |
$398.95 |
|
C29179 |
Managing Change: The Game for an Executive Retreat |
$259.95 |
$150.00 |
$187.30 |
$25.95 |
$275.99 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
$169.95 |
$51.00 |
$201.96 |
$167.95 |
$269.95 |
#10. Which customer(s) have orders that have been shipped
FedEx? List the customer’s name, orderid, itemid and name of the
item(s) that have been shipped FedEx.
Make sure that the result table does not repeat a given combination of
customer name plus orderid plus item name.
#10. Code
SELECT lastname +
', ' + firstname "Customer Name",
ord.orderID
"Order Number",
ol.itemID
"Item Number",
item.description
"Item Name"
FROM TblCustomer
cust
LEFT
JOIN TblOrder ord
ON ord.CustomerID
= cust.CustomerID
LEFT
JOIN TblOrderLine
ol
ON ord.orderID
= ol.OrderID
LEFT
JOIN TblItem
item
ON ol.ItemID
= item.ItemID
LEFT
JOIN TblShipLine
sl
ON sl.ItemID
= ol.ItemID
sl.OrderID
= ol.OrderID
WHERE LOWER(MethodShipped) = 'fedex'
#10. Output
|
dbo.SQL2_3 |
|||
|
Customer Name |
Order Number |
Item Number |
Item Name |
|
Rodriguez, Karen |
450137 |
A34665 |
Boggle Deluxe 5x5 |
|
Martinez, Guadalupe |
651222 |
A34882 |
Perudo |
|
Candriller, Kathy |
223344 |
A34882 |
Perudo |
|
Jones, Martin |
445511 |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
|
Guili, Mary Anne |
980001 |
C29179 |
Managing Change: The Game for an Executive Retreat |
|
Guili, Mary Anne |
980001 |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
#11. What is/are the name of the item(s) and the price of
that item on an order where the item sold for less than the average selling price for that item? Calculate the average selling price for a
given item using the data in the ORDERLINE table. You must calculate the average selling price
separately for each item – the average selling price is for an item, not for
all items in the table. It will be
possible to see an item more than once on this output. Order the output by two sort keys – the first
is itemid (in ascending order) and the second is
actual order unit price of the item on an order (in descending order). I
recommend using a correlated sub-query in the WHERE clause to assist with
question #11. I also recommend including
a correlated sub-query in the SELECT list.
#11. Code
SELECT olOuter.itemID "Item
Number",
item.description
"Item Name",
ord.orderID
"Order Number",
CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
olOuter.ListPrice
"Actual Order Unit Price",
(
SELECT AVG(olSel.ListPrice)
FROM TblOrderLine olSel
WHERE olSel.itemID = olOuter.ItemID
) "Average Order Price"
FROM TblOrderLine olOuter
LEFT JOIN TblOrder ord
ON ord.orderID = olOuter.OrderID
LEFT JOIN TblItem item
ON olOuter.ItemID = item.ItemID
WHERE olOuter.ListPrice < (SELECT AVG(olInner.ListPrice)
FROM TblOrderLine
olInner
WHERE olInner.itemID
= olOuter.ItemID)
GROUP BY olOuter.ItemID,
item.description,
ord.orderID,
ord.OrderDate,
olOuter.ListPrice
ORDER BY olOuter.itemID,
olOuter.ListPrice
DESC
#11. Output
|
dbo.SQL2_3 |
|||||
|
Item Number |
Item Name |
Order Number |
Order Date |
Actual Order Unit
Price |
Average Order Price |
|
A23771 |
Catch Phrase Game Music Edition |
223344 |
|
$53.99 |
$59.97 |
|
A34665 |
Boggle Deluxe 5x5 |
450137 |
|
$31.00 |
$33.63 |
|
A34665 |
Boggle Deluxe 5x5 |
223344 |
|
$23.95 |
$33.63 |
|
A34882 |
Perudo |
450137 |
|
$9.95 |
$10.20 |
|
A34882 |
Perudo |
223344 |
|
$7.95 |
$10.20 |
|
B67123 |
Cranium WOW Edition |
123000 |
|
$15.95 |
$16.35 |
|
B67123 |
Cranium WOW Edition |
300221 |
|
$15.95 |
$16.35 |
|
B67123 |
Cranium WOW Edition |
223344 |
|
$14.95 |
$16.35 |
|
B67123 |
Cranium WOW Edition |
450137 |
|
$14.95 |
$16.35 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
223344 |
|
$40.95 |
$42.95 |
|
B78244 |
Chicks Battle the Dudes Board Game |
651222 |
|
$31.65 |
$32.30 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
892211 |
|
$380.00 |
$392.71 |
|
C29179 |
Managing Change: The Game for an Executive Retreat |
671100 |
|
$25.95 |
$187.30 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
892211 |
|
$200.00 |
$201.96 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
980001 |
|
$169.95 |
$201.96 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
450137 |
|
$167.95 |
$201.96 |
#12. List the orderid, orderdate, itemid, quantity on
order for an item on an order (in the ORDERLINE table), the total amount that has been shipped for
a given item on a given order and the difference between the quantity on order
and the total quantity shipped. Make
sure that your report displays all items on all orders – if an item has not had
any shipments, it should still show up on this report.
#12. Code
SELECT ol.orderID "Order ID",
CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
ol.itemID
"Item ID",
ol.quantity
"Quantity Ordered",
ISNULL(SUM(sl.QtyShipped), 0) "Total Quantity Shipped",
ol.quantity - ISNULL(SUM(sl.QtyShipped), 0)
"Quantity Left to Ship"
FROM TblOrderLine ol
LEFT JOIN TblOrder ord
ON ord.orderID = ol.OrderID
LEFT JOIN TblShipLine sl
ON sl.ItemID = ol.ItemID
sl.OrderID = ol.OrderID
GROUP BY ol.orderID,
ol.ItemID,
ord.OrderDate,
ol.quantity
ORDER BY ol.orderID,
ol.ItemID
#12. Output
|
dbo.SQL2_3 |
|||||
|
Order ID |
Order Date |
Item ID |
Quantity Ordered |
Total Quantity
Shipped |
Quantity Left to Ship |
|
123000 |
|
A23441 |
8 |
8 |
0 |
|
123000 |
|
A34665 |
30 |
32 |
-2 |
|
123000 |
|
B67123 |
5 |
5 |
0 |
|
200335 |
|
A23441 |
1 |
0 |
1 |
|
200335 |
|
A34665 |
1 |
0 |
1 |
|
200335 |
|
B67123 |
1 |
0 |
1 |
|
200335 |
|
B67466 |
1 |
0 |
1 |
|
223344 |
|
A23441 |
55 |
28 |
27 |
|
223344 |
|
A23771 |
15 |
15 |
0 |
|
223344 |
|
A34665 |
100 |
110 |
-10 |
|
223344 |
|
A34882 |
35 |
35 |
0 |
|
223344 |
|
B67123 |
25 |
13 |
12 |
|
223344 |
|
B67466 |
15 |
0 |
15 |
|
300221 |
|
A23771 |
1 |
1 |
0 |
|
300221 |
|
A34665 |
1 |
1 |
0 |
|
300221 |
|
A34882 |
1 |
1 |
0 |
|
300221 |
|
B67123 |
1 |
1 |
0 |
|
300221 |
|
B78244 |
1 |
1 |
0 |
|
400001 |
|
C26133 |
1 |
0 |
1 |
|
445511 |
|
C34122 |
3 |
3 |
0 |
|
450137 |
|
A34665 |
10 |
10 |
0 |
|
450137 |
|
A34882 |
50 |
15 |
35 |
|
450137 |
|
B67123 |
21 |
29 |
-8 |
|
450137 |
|
C26133 |
1 |
0 |
1 |
|
450137 |
|
C34122 |
6 |
0 |
6 |
|
567123 |
|
C26133 |
1 |
0 |
1 |
|
651222 |
|
A34665 |
5 |
5 |
0 |
|
651222 |
|
A34882 |
16 |
16 |
0 |
|
651222 |
|
B78244 |
21 |
21 |
0 |
|
671100 |
|
C29179 |
1 |
1 |
0 |
|
781206 |
|
B67466 |
1 |
0 |
1 |
|
892211 |
|
C26133 |
15 |
0 |
15 |
|
892211 |
|
C29179 |
10 |
6 |
4 |
|
892211 |
|
C34122 |
8 |
5 |
3 |
|
980001 |
|
C29179 |
3 |
3 |
0 |
|
980001 |
|
C34122 |
2 |
2 |
0 |
#13. Modify the answer to question #12 so that only those
order lines that have not been fully shipped are displayed. An item on an order is considered “fully
shipped” if the total quantity shipped is greater than or equal to the quantity
ordered. Add a column in the result table
for the description of the item.
#13. Code
SELECT ol.orderID "Order ID",
CONVERT(VARCHAR, ord.OrderDate, 101) "Order Date",
ol.itemID
"Item ID",
item.description
"Item Name",
ISNULL(SUM(sl.QtyShipped), 0) "Total Quantity Shipped",
FROM TblOrderLine ol
LEFT JOIN TblOrder ord
ON ord.orderID = ol.OrderID
LEFT JOIN TblShipLine sl
ON sl.ItemID = ol.ItemID
sl.OrderID = ol.OrderID
LEFT JOIN TblItem item
ON ol.ItemID = item.ItemID
GROUP BY ol.orderID, ol.ItemID, item.description, ord.OrderDate
HAVING
ORDER BY ol.orderID,
ol.ItemID
#13. Output
|
dbo.SQL2_3 |
||||||
|
Order ID |
Order Date |
Item ID |
Item Name |
Quantity Ordered |
Total Quantity
Shipped |
Quantity Left to Ship |
|
200335 |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
1 |
0 |
1 |
|
200335 |
|
A34665 |
Boggle Deluxe 5x5 |
1 |
0 |
1 |
|
200335 |
|
B67123 |
Cranium WOW Edition |
1 |
0 |
1 |
|
200335 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
|
223344 |
|
A23441 |
New York City Monopoly Game Collector’s Edition |
55 |
28 |
27 |
|
223344 |
|
B67123 |
Cranium WOW Edition |
25 |
13 |
12 |
|
223344 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
15 |
0 |
15 |
|
400001 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
|
450137 |
|
A34882 |
Perudo |
50 |
15 |
35 |
|
450137 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
|
450137 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
6 |
0 |
6 |
|
567123 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
|
781206 |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
|
892211 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
15 |
0 |
15 |
|
892211 |
|
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
6 |
4 |
|
892211 |
|
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
8 |
5 |
3 |
#14. Perform the three updates below. These updates should be done to the relevant
source table – these are not queries. Do not write individual UPDATE statements to
separately modify each row individually in the table; the three required
updates below should be done with three UPDATE statements. Parts a and b are
easy. Part c is more difficult and
requires the use of correlated sub-queries.
#14. Code
a-
Update the values in the state column of the customer table to have all uppercase characters. For example, nv should be NV and ca should be CA. Do not include the name of the state in the UPDATE statement – make one generic UPDATE statement that is capable of working for all states.
UPDATE TblCustomer
SET State = UPPER(State)
b-
Update the values in the city column of the customer table
to have all uppercase characters. For
example,
UPDATE TblCustomer
SET City = UPPER(City)
c-
Update the shipping data in the order table. If the shipname is null, then update the shipname with a concatenation of the first and last name of the customer (found in the customer table – use the format of “lastname, firstname”), update the shipaddress with the address from the customer table, update the shippostalcode with the zip from the customer table, update the shipcountry with the country from the customer table, and update the shipphone with the phone from the customer table. If the shipname is NOT null, then no updating should occur.
UPDATE ord
SET ShipName = lastname +
', ' + firstname,
ShipAddress = Address,
ShipZip = Zip,
ShipCountry = Country,
ShipPhone = Phone
FROM TblOrder ord
LEFT JOIN TblCustomer cust
ON cust.CustomerID = ord.CustomerID
WHERE ShipName IS NULL