IS675 – Database Design
Bilal Gonen
SQL-4
#1. Create a report listing the shipping status of each
order line. Include the columns
shown on the sample result table below. The sample
below is not especially well formatted, but it is provided to give you a
general idea of the required information.
The shipping status is as follows:
Completed:
The quantity shipped equals the quantity ordered.
Over Shipped:
The quantity shipped is greater than the quantity ordered.
Partially Shipped: The quantity shipped is greater than zero,
but does not equal the quantity ordered.
Not Shipped: No items have been shipped.
Sort the output by itemID within orderID. I recommend using a SQL view to summarize the total quantity shipped from the shipline table so that the main query does not have to use an aggregate function. I recommend using the CASE statement to determine the shipping status.
#1. Code
CREATE VIEW
SELECT item.itemID,
item.description,
item.ListPrice,
h1.LastCost,
h1.LastCostDate
FROM TblItem item
LEFT JOIN tblitemCostHistory
h1
ON h1.itemID = item.itemID
WHERE h1.LastCostDate = (
SELECT
FROM tblitemCostHistory h2
WHERE h2.itemID = h1.itemID
)
CREATE VIEW
SELECT ol.orderID "OrderID",
ord.OrderDate,
cust.LastName + ',
'+ SUBSTRING(cust.firstname,1,1)+'.' "CustomerName",
ol.itemID "ItemID",
ol.quantity "QuantityOrdered",
ISNULL(SUM(sl.QtyShipped), 0) "TotalShipped",
ol.quantity - ISNULL(SUM(sl.QtyShipped), 0)
"quantityremaining"
FROM TblOrderLine ol
LEFT JOIN TblOrder ord
ON ord.orderID = ol.OrderID
LEFT JOIN TblShipLine sl
ON sl.ItemID = ol.ItemID AND
sl.OrderID = ol.OrderID
LEFT JOIN TblCustomer cust
ON ord.customerID = cust.customerID
GROUP BY ol.orderID,
cust.LastName,
cust.FirstName,
ol.ItemID,
ord.OrderDate,
ol.quantity
SELECT v.orderid "Order ID",
CONVERT(VARCHAR, v.OrderDate, 101) "Order Date",
v.CustomerName
"Customer Name",
v.ItemID "Item
ID",
item.description
"Item Name",
v.QuantityOrdered
"Quantity Ordered",
v.TotalShipped
"Total Shipped",
v.quantityremaining
"Quantity Left to Ship",
CASE
WHEN v.TotalShipped
= 0
THEN 'Not Shipped'
WHEN v.QuantityOrdered
= v.TotalShipped
THEN 'Completed'
WHEN v.TotalShipped
> v.QuantityOrdered
THEN 'Over Shipped'
ELSE 'Partially Shipped'
END "Ship Status"
FROM VShippedQty v
LEFT JOIN tblItem item
ON v.itemID = item.itemID
#1. Output
|
dbo.SQL4 |
||||||||
|
Order ID |
Order Date |
Customer Name |
Item ID |
Item Name |
Quantity Ordered |
Total Shipped |
Quantity Left to Ship |
Ship Status |
|
123000 |
|
|
A23441 |
New York City Monopoly Game Collector’s Edition |
8 |
8 |
0 |
Completed |
|
123000 |
|
|
A34665 |
Boggle Deluxe 5x5 |
30 |
32 |
-2 |
Over Shipped |
|
123000 |
|
|
B67123 |
Cranium WOW Edition |
5 |
5 |
0 |
Completed |
|
200335 |
|
Phillips, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
A34665 |
Boggle Deluxe 5x5 |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67123 |
Cranium WOW Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
223344 |
|
Candriller, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
55 |
28 |
27 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
A23771 |
Catch Phrase Game Music Edition |
15 |
15 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
A34665 |
Boggle Deluxe 5x5 |
100 |
110 |
-10 |
Over Shipped |
|
223344 |
|
Candriller, K. |
A34882 |
Perudo |
35 |
35 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
B67123 |
Cranium WOW Edition |
25 |
13 |
12 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
15 |
0 |
15 |
Not Shipped |
|
300221 |
|
|
A23771 |
Catch Phrase Game Music Edition |
1 |
1 |
0 |
Completed |
|
300221 |
|
|
A34665 |
Boggle Deluxe 5x5 |
1 |
1 |
0 |
Completed |
|
300221 |
|
|
A34882 |
Perudo |
1 |
1 |
0 |
Completed |
|
300221 |
|
|
B67123 |
Cranium WOW Edition |
1 |
1 |
0 |
Completed |
|
300221 |
|
|
B78244 |
Chicks Battle the Dudes Board Game |
1 |
1 |
0 |
Completed |
|
400001 |
|
Jones, M. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
445511 |
|
Jones, M. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
3 |
3 |
0 |
Completed |
|
450137 |
|
Rodriguez, K. |
A34665 |
Boggle Deluxe 5x5 |
10 |
10 |
0 |
Completed |
|
450137 |
|
Rodriguez, K. |
A34882 |
Perudo |
50 |
15 |
35 |
Partially Shipped |
|
450137 |
|
Rodriguez, K. |
B67123 |
Cranium WOW Edition |
21 |
29 |
-8 |
Over Shipped |
|
450137 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
450137 |
|
Rodriguez, K. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
6 |
0 |
6 |
Not Shipped |
|
567123 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
651222 |
|
|
A34665 |
Boggle Deluxe 5x5 |
5 |
5 |
0 |
Completed |
|
651222 |
|
|
A34882 |
Perudo |
16 |
16 |
0 |
Completed |
|
651222 |
|
|
B78244 |
Chicks Battle the Dudes Board Game |
21 |
21 |
0 |
Completed |
|
671100 |
|
Jones, M. |
C29179 |
Managing Change: The Game for an Executive Retreat |
1 |
1 |
0 |
Completed |
|
781206 |
|
Argiento, B. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
892211 |
|
Dao, P. |
C26133 |
Knowledge Management: Create a Learning Organization |
15 |
0 |
15 |
Not Shipped |
|
892211 |
|
Dao, P. |
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
6 |
4 |
Partially Shipped |
|
892211 |
|
Dao, P. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
8 |
5 |
3 |
Partially Shipped |
|
980001 |
|
Guili, M. |
C29179 |
Managing Change: The Game for an Executive Retreat |
3 |
3 |
0 |
Completed |
|
980001 |
|
Guili, M. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
2 |
2 |
0 |
Completed |
#2. Have any orders been over shipped? List the name of the customer, orderid, itemid, description of
the item and the quantity overshipped for any orders
where more items have been shipped for that order than were actually ordered.
#2. Code
CREATE VIEW
SELECT ol.orderID "OrderID",
ord.OrderDate,
cust.LastName + ',
'+ SUBSTRING(cust.firstname,1,1)+'.' "CustomerName",
ol.itemID "ItemID",
ol.quantity "QuantityOrdered",
ISNULL(SUM(sl.QtyShipped), 0) "TotalShipped",
ol.quantity - ISNULL(SUM(sl.QtyShipped), 0)
"quantityremaining"
FROM TblOrderLine ol
LEFT JOIN TblOrder ord
ON ord.orderID = ol.OrderID
LEFT JOIN TblShipLine sl
ON sl.ItemID = ol.ItemID AND
sl.OrderID = ol.OrderID
LEFT JOIN TblCustomer cust
ON ord.customerID = cust.customerID
GROUP BY ol.orderID,
cust.LastName,
cust.FirstName,
ol.ItemID,
ord.OrderDate,
ol.quantity
SELECT v.orderid "Order ID",
v.CustomerName
"Customer Name",
v.ItemID "Item
ID",
item.description
"Item Name",
v.QuantityOrdered
"Quantity Ordered",
v.TotalShipped
"Total Shipped",
v.TotalShipped-v.QuantityOrdered
"Quantity Overshipped"
FROM VShippedQty v
LEFT JOIN tblItem item
ON v.itemID = item.itemID
WHERE v.TotalShipped > v.QuantityOrdered
#2. Output
|
dbo.SQL4 |
||||||
|
Customer Name |
Order ID |
Item ID |
Item Name |
Quantity Ordered |
Total Shipped |
Quantity Overshipped |
|
|
123000 |
A34665 |
Boggle Deluxe 5x5 |
30 |
32 |
2 |
|
Candriller, K. |
223344 |
A34665 |
Boggle Deluxe 5x5 |
100 |
110 |
10 |
|
Rodriguez, K. |
450137 |
B67123 |
Cranium WOW Edition |
21 |
29 |
8 |
#3. Which customer paid the least expensive price for
the game called “Knowledge Management:
Create a Learning Organization”?
Display the customer name, orderid, orderdate, itemID, item
description, selling price of the item, the most current lastcost
of the item and the most current lastcostdate for
that item. If the most current lastcost is null, display it as zero. Make sure that your
query uses the name of the game and not the itemid
for the actual search in the “where” clause.
#3. Code
CREATE
VIEW
SELECT item.itemID,
item.description,
item.ListPrice,
h1.LastCost,
h1.LastCostDate
FROM TblItem
item
LEFT
JOIN tblitemCostHistory
h1
ON h1.itemID = item.itemID
WHERE h1.LastCostDate = (
SELECT MAX(h2.LastCostDate)
FROM tblitemCostHistory h2
WHERE
h2.itemID = h1.itemID
)
SELECT cust.lastname
+ ', ' + cust.firstName "Customer Name",
ol.orderid
"Order ID",
CONVERT(VARCHAR,
OrderDate, 101) "Date of Order",
ol.ItemID
"Item ID",
v.description
"Item Name",
ol.listprice
"Price Paid",
v.LastCost,
CONVERT(VARCHAR,
v.LastCostDate, 101) "Last Cost Date"
FROM TblOrderLine ol
LEFT
JOIN tblOrder
ord
ON ol.orderID
= ord.orderID
LEFT
JOIN tblCustomer
cust
ON cust.customerID
= ord.customerID
LEFT
JOIN VItemCostHistory
v
ON ol.itemID
= v.itemID
WHERE ol.listprice
= (
SELECT MIN(ol2.listprice)
FROM TblOrderLine ol2
LEFT JOIN TblItem item
ON ol2.itemID = item.itemID
WHERE item.description
= 'Knowledge Management: Create a
Learning Organization'
)
#3. Output
|
dbo.SQL4 |
|||||||
|
Customer Name |
Order ID |
Date of Order |
Item ID |
Item Name |
Price Paid |
LastCost |
Last Cost Date |
|
Dao, Phong |
892211 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
$380.00 |
$198.00 |
|
#4. Which customer ordered the game “Cranium WOW
Edition” most recently? List the
customer name, orderid, orderdate,
itemID, item description, selling price of the item,
the most current lastcost of the item and the most
current lastcostdate for that item. Make sure that your query uses the name
of the game and not the itemid for the actual search
in the “where” clause.
#4. Code
CREATE
VIEW
SELECT item.itemID,
item.description,
item.ListPrice,
h1.LastCost,
h1.LastCostDate
FROM TblItem
item
LEFT
JOIN tblitemCostHistory
h1
ON h1.itemID = item.itemID
WHERE h1.LastCostDate = (
SELECT
FROM tblitemCostHistory h2
WHERE
h2.itemID = h1.itemID
)
create view
SELECT cust.lastname,
cust.firstName,
cust.customerID,
ol.orderid,
ord.OrderDate,
ol.ItemID,
ol.quantity
"QuantityOrdered",
ol.listprice "PricePaid",
item.description,
item.listprice
"itemlistprice"
FROM TblOrderLine ol
LEFT
JOIN tblOrder
ord
ON ol.orderID
= ord.orderID
LEFT
JOIN tblCustomer
cust
ON cust.customerID
= ord.customerID
LEFT
JOIN tblItem
item
ON ol.itemID
= item.itemID
SELECT v1.lastname + ', ' + v1.firstName
"Customer Name",
v1.orderid "Order
ID",
CONVERT(VARCHAR,
v1.orderdate, 101) "Order Date",
v1.itemID "Item
ID",
v1.description
"Item Name",
v1.itemlistprice
"Item List Price",
vhist.LastCost
"Most Current Last Cost",
CONVERT(VARCHAR,
vhist.LastCostDate, 101) "Last Cost Date"
FROM VOrderLine v1
LEFT
JOIN VItemCostHistory
vhist
ON v1.itemID = vhist.itemID
WHERE orderdate =
(
select
from
VOrderLine
v2
where
v2.description = 'Cranium WOW Edition'
)
AND v1.description = 'Cranium WOW
Edition'
#4. Output
|
dbo.SQL4 |
|||||||
|
Customer Name |
Order ID |
Order Date |
Item ID |
Item Name |
Item List Price |
Most Current Last
Cost |
Last Cost Date |
|
Candriller, Kathy |
223344 |
|
B67123 |
Cranium WOW Edition |
$15.95 |
$14.00 |
|
#5. Which order in the database is the most profitable
for the company? Determine the profit of
the order by calculating the difference between the price and the cost for each
item on the order by multiplying the quantity ordered by the price and
subtracting the quantity ordered by the most current lastcost
of the item. Then sum those differences
for the entire order to determine the overall profitability of the order.
#5. Code
CREATE
VIEW
SELECT item.itemID,
item.description,
item.ListPrice,
h1.LastCost,
h1.LastCostDate
FROM TblItem
item
LEFT
JOIN tblitemCostHistory
h1
ON h1.itemID = item.itemID
WHERE h1.LastCostDate = (
SELECT MAX(h2.LastCostDate)
FROM tblitemCostHistory h2
WHERE
h2.itemID = h1.itemID
)
create view
SELECT cust.lastname,
cust.firstName,
cust.customerID,
ol.orderid,
ord.OrderDate,
ol.ItemID,
ol.quantity
"QuantityOrdered",
ol.listprice
"PricePaid",
item.description,
item.listprice
"itemlistprice"
FROM TblOrderLine ol
LEFT
JOIN tblOrder
ord
ON ol.orderID
= ord.orderID
LEFT
JOIN tblCustomer
cust
ON cust.customerID
= ord.customerID
LEFT
JOIN tblItem
item
ON ol.itemID
= item.itemID
CREATE
VIEW
SELECT v1.orderid,
SUM((v1.PricePaid
- vhist.LastCost) * v1.QuantityOrdered ) "TotalOrderProfit"
FROM VOrderLine v1
LEFT
JOIN VItemCostHistory
vhist
ON v1.itemID = vhist.itemID
GROUP
BY v1.orderid
SELECT v1.orderid "Order ID",
CONVERT(VARCHAR,
v1.orderdate, 101) "Order Date",
v1.lastname
"Customer Last Name",
v1.itemID "Item
ID",
v1.description
"Item Name",
v1.QuantityOrdered,
v1.PricePaid,
vhist.LastCost
"Most Current Last Cost",
v1.PricePaid - vhist.LastCost "pricediff",
(v1.PricePaid - vhist.LastCost) * v1.QuantityOrdered "extendeddiff"
FROM VOrderLine v1
LEFT
JOIN VItemCostHistory
vhist
ON v1.itemID = vhist.itemID
WHERE v1.orderID = (
SELECT v1.orderID
FROM Vtotalorderprofit v1
WHERE v1.totalorderprofit = (
SELECT MAX(totalorderprofit)
FROM Vtotalorderprofit
)
)
#5. Output
|
dbo.SQL4 |
|||||||||
|
Order ID |
Order Date |
Customer Last Name |
Item ID |
Item Name |
Quantity Ordered |
Price Paid |
Most Current Last
Cost |
Unit Profit |
Total Item Profit |
|
892211 |
|
Dao |
C26133 |
Knowledge Management: Create a Learning Organization |
15 |
$380.00 |
$198.00 |
$182.00 |
$2,730.00 |
|
892211 |
|
Dao |
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
$259.95 |
$150.00 |
$109.95 |
$1,099.50 |
|
892211 |
|
Dao |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
8 |
$200.00 |
$51.00 |
$149.00 |
$1,192.00 |
#6. Which customer has ordered the most
items from Life's a Game? List information about the owner (name, formatted
phone number, state) as well as the total sum of the items ordered by that
customer. This question is not asking
you to determine which customer ordered the most different items, but is
instead asking which customer ordered the most items in total. Thus, calculate the sum of the quantity of
items ordered in the orderline table to determine the
number of items ordered. The question is
asking for the
#6. Code
CREATE
VIEW
SELECT cust.lastname,
cust.firstName,
cust.customerID,
cust.phone,
cust.state,
ol.orderid,
ord.OrderDate,
SUM(ol.quantity) "TotalItemQuantity"
FROM TblOrderLine ol
LEFT
JOIN tblOrder
ord
ON ol.orderID
= ord.orderID
LEFT
JOIN tblCustomer
cust
ON cust.customerID
= ord.customerID
LEFT
JOIN tblItem
item
ON ol.itemID
= item.itemID
GROUP
BY cust.lastname,
cust.firstName,
cust.customerID,
cust.phone,
cust.state,
ol.orderid,
ord.OrderDate
SELECT lastname +
', ' + firstname "Customer Name",
'('+SUBSTRING(Phone,1,3)+')
'+ SUBSTRING(Phone,5,12) "Phone Number",
UPPER(state)
"State",
TotalItemQuantity
"Sum of Items Ordered"
FROM VTotalItemQuantity
WHERE totalitemquantity
= (
SELECT MAX(totalitemquantity)
FROM VTotalItemQuantity
)
#6. Output
|
dbo.SQL4 |
|||
|
Customer Name |
Phone Number |
State |
Sum of Items Ordered |
|
Candriller, Kathy |
(619) 881-3929 |
CA |
245 |
#7. Make a report listing all open orders
sorted by order date. Feel free to use
the same format of the result table as that for questions #1 and #2. An open
order is any order in the database that has not yet been completely
shipped. List the order_id,
the order_date, the customer name, the names and
quantities of all items on the order, the shipped quantity of any of the items,
and the number of items that remain to be shipped. Sort the output by order date.
This problem could be interpreted two different ways: (1) Report only those order lines of all
orders that have not been fully shipped or shipped at all; or (2) Report each
and every order line of any order that has any orderline
that has not been shipped at all, or has not been fully shipped at this
time. The second interpretation means
that you would display those order lines that have been fully shipped as well
as those order lines that have not been shipped or have been partially shipped
for those orders that have not been fully shipped. I want your answer to the second
interpretation, not the first. This
interpretation means that you must have a way of determining whether or not an
entire order has been fully shipped. I
recommend creating a view to help with this determination.
#7. Code
CREATE
VIEW
orderdate
FROM VShippedQty
WHERE quantityremaining
> 0
SELECT v.orderid
"Order ID",
CONVERT(VARCHAR,
v.OrderDate, 101) "Order Date",
v.CustomerName
"Customer Name",
v.ItemID
"Item ID",
item.description
"Item Name",
v.QuantityOrdered
"Quantity Ordered",
v.TotalShipped
"Total Shipped",
v.quantityremaining
"Quantity Left to Ship",
CASE
WHEN v.TotalShipped = 0
THEN
'Not Shipped'
WHEN v.QuantityOrdered = v.TotalShipped
THEN
'Completed'
WHEN v.TotalShipped > v.QuantityOrdered
THEN
'Over Shipped'
ELSE 'Partially Shipped'
END "Ship
Status"
FROM VShippedQty
v
LEFT
JOIN tblItem
item
ON v.itemID
= item.itemID
WHERE v.orderID
IN (
SELECT OrderID
FROM VOpenOrders
)
#7. Output
|
dbo.SQL4 |
||||||||
|
Order ID |
Order Date |
Customer Name |
Item ID |
Item Name |
Quantity Ordered |
Total Shipped |
Quantity Left to Ship |
Ship Status |
|
200335 |
|
Phillips, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
A34665 |
Boggle Deluxe 5x5 |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67123 |
Cranium WOW Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
223344 |
|
Candriller, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
55 |
28 |
27 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
A23771 |
Catch Phrase Game Music Edition |
15 |
15 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
A34665 |
Boggle Deluxe 5x5 |
100 |
110 |
-10 |
Over Shipped |
|
223344 |
|
Candriller, K. |
A34882 |
Perudo |
35 |
35 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
B67123 |
Cranium WOW Edition |
25 |
13 |
12 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
15 |
0 |
15 |
Not Shipped |
|
400001 |
|
Jones, M. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
450137 |
|
Rodriguez, K. |
A34665 |
Boggle Deluxe 5x5 |
10 |
10 |
0 |
Completed |
|
450137 |
|
Rodriguez, K. |
A34882 |
Perudo |
50 |
15 |
35 |
Partially Shipped |
|
450137 |
|
Rodriguez, K. |
B67123 |
Cranium WOW Edition |
21 |
29 |
-8 |
Over Shipped |
|
450137 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
450137 |
|
Rodriguez, K. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
6 |
0 |
6 |
Not Shipped |
|
567123 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
781206 |
|
Argiento, B. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
892211 |
|
Dao, P. |
C26133 |
Knowledge Management: Create a Learning Organization |
15 |
0 |
15 |
Not Shipped |
|
892211 |
|
Dao, P. |
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
6 |
4 |
Partially Shipped |
|
892211 |
|
Dao, P. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
8 |
5 |
3 |
Partially Shipped |
#8. Which order (or orders) in the
database is/are the oldest open order(s)?
“Oldest” means the open order (as determined in question #7) with the
earliest order date. List the same
information as displayed for question #7 for the oldest open order.
#8. Code
CREATE
VIEW
SELECT ol.orderID
"OrderID",
ord.OrderDate,
cust.LastName
+ ', '+ SUBSTRING(cust.firstname,1,1)+'.' "CustomerName",
ol.itemID
"ItemID",
ol.quantity
"QuantityOrdered",
ISNULL(SUM(sl.QtyShipped), 0) "TotalShipped",
ol.quantity
- ISNULL(SUM(sl.QtyShipped),
0) "quantityremaining"
FROM TblOrderLine
ol
LEFT
JOIN TblOrder ord
ON ord.orderID
= ol.OrderID
LEFT
JOIN TblShipLine
sl
ON sl.ItemID
= ol.ItemID AND
sl.OrderID
= ol.OrderID
LEFT
JOIN TblCustomer
cust
ON ord.customerID
= cust.customerID
GROUP
BY ol.orderID,
cust.LastName,
cust.FirstName,
ol.ItemID,
ord.OrderDate,
ol.quantity
SELECT v.orderid
"Order ID",
CONVERT(VARCHAR,
v.OrderDate, 101) "Order Date",
v.CustomerName
"Customer Name",
v.ItemID
"Item ID",
item.description
"Item Name",
v.QuantityOrdered
"Quantity Ordered",
v.TotalShipped
"Total Shipped",
v.quantityremaining
"Quantity Left to Ship",
CASE
WHEN v.TotalShipped = 0
THEN
'Not Shipped'
WHEN v.QuantityOrdered = v.TotalShipped
THEN
'Completed'
WHEN v.TotalShipped > v.QuantityOrdered
THEN 'Over Shipped'
ELSE 'Partially Shipped'
END "Ship
Status"
FROM VShippedQty
v
LEFT
JOIN tblItem
item
ON v.itemID
= item.itemID
WHERE v.orderID
IN (
SELECT OrderID
FROM VOpenOrders
)
AND v.OrderDate
= (
SELECT MIN(OrderDate)
FROM VOpenOrders
)
#8. Output
|
dbo.SQL4 |
||||||||
|
Order ID |
Order Date |
Customer Name |
Item ID |
Item Name |
Quantity Ordered |
Total Shipped |
Quantity Left to Ship |
Ship Status |
|
200335 |
|
Phillips, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
A34665 |
Boggle Deluxe 5x5 |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67123 |
Cranium WOW Edition |
1 |
0 |
1 |
Not Shipped |
|
200335 |
|
Phillips, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
223344 |
|
Candriller, K. |
A23441 |
New York City Monopoly Game Collector’s Edition |
55 |
28 |
27 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
A23771 |
Catch Phrase Game Music Edition |
15 |
15 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
A34665 |
Boggle Deluxe 5x5 |
100 |
110 |
-10 |
Over Shipped |
|
223344 |
|
Candriller, K. |
A34882 |
Perudo |
35 |
35 |
0 |
Completed |
|
223344 |
|
Candriller, K. |
B67123 |
Cranium WOW Edition |
25 |
13 |
12 |
Partially Shipped |
|
223344 |
|
Candriller, K. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
15 |
0 |
15 |
Not Shipped |
|
400001 |
|
Jones, M. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
450137 |
|
Rodriguez, K. |
A34665 |
Boggle Deluxe 5x5 |
10 |
10 |
0 |
Completed |
|
450137 |
|
Rodriguez, K. |
A34882 |
Perudo |
50 |
15 |
35 |
Partially Shipped |
|
450137 |
|
Rodriguez, K. |
B67123 |
Cranium WOW Edition |
21 |
29 |
-8 |
Over Shipped |
|
450137 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
450137 |
|
Rodriguez, K. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
6 |
0 |
6 |
Not Shipped |
|
567123 |
|
Rodriguez, K. |
C26133 |
Knowledge Management: Create a Learning Organization |
1 |
0 |
1 |
Not Shipped |
|
781206 |
|
Argiento, B. |
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
1 |
0 |
1 |
Not Shipped |
|
892211 |
|
Dao, P. |
C26133 |
Knowledge Management: Create a Learning Organization |
15 |
0 |
15 |
Not Shipped |
|
892211 |
|
Dao, P. |
C29179 |
Managing Change: The Game for an Executive Retreat |
10 |
6 |
4 |
Partially Shipped |
|
892211 |
|
Dao, P. |
C34122 |
A Game of Strategy, Negotiation and Excitement for Office Retreats |
8 |
5 |
3 |
Partially Shipped |
#9. Which orders have been fully shipped? List the orderid, orderdate, customerid, customer
name, the first date that anything was shipped for that order and the last date
that anything was shipped for that order for all orders that have been fully
shipped. Include those orders that have
items that have been overshipped in this query. DO NOT list the items on the order – only
list the information shown on the sample output.
#9. Code
CREATE
VIEW
SELECT cust.lastname,
cust.firstName,
cust.customerID,
cust.phone,
cust.state,
ol.orderid,
ord.OrderDate,
SUM(ol.quantity) "TotalItemQuantity"
FROM TblOrderLine ol
LEFT
JOIN tblOrder
ord
ON ol.orderID
= ord.orderID
LEFT
JOIN tblCustomer
cust
ON cust.customerID
= ord.customerID
LEFT
JOIN tblItem
item
ON ol.itemID
= item.itemID
GROUP
BY cust.lastname,
cust.firstName,
cust.customerID,
cust.phone,
cust.state,
ol.orderid,
ord.OrderDate
CREATE
VIEW
SELECT DISTINCT(orderID)
orderdate
FROM VShippedQty
WHERE quantityremaining
> 0
SELECT v.orderID
"Order ID",
v.orderdate
"Date of Order",
v.customerID
"Customer ID",
v.lastname
+', '+ UPPER(SUBSTRING(v.firstname,1,1))
"Customer Name",
(SELECT CONVERT(VARCHAR,
MIN(dateshipped), 101)
FROM TblShipline t
WHERE t.orderID =
v.orderID
) "First Date
Shipped",
(SELECT CONVERT(VARCHAR,
MAX(dateshipped), 101)
FROM TblShipline t
WHERE t.orderID =
v.orderID
) "Last Date
Shipped"
FROM VTotalItemQuantity v
WHERE orderID NOT
IN (
SELECT orderID
FROM VOpenOrders
)
ORDER
BY v.orderID
#9. Output
|
dbo.SQL4 |
|||||
|
Order ID |
Date of Order |
Customer ID |
Customer Name |
First Date Shipped |
Last Date Shipped |
|
123000 |
|
00405 |
|
|
|
|
300221 |
|
12006 |
|
|
|
|
445511 |
|
32018 |
Jones, M |
|
|
|
651222 |
|
12006 |
|
|
|
|
671100 |
|
32018 |
Jones, M |
|
|
|
980001 |
|
78112 |
Guili, M |
|
|
#10. Which items in the database have an insufficient
quantity on hand to fill all outstanding orders for that item? Remember, some of the orders have already
been filled, so make sure that you use only the open orders and partially
filled orders for this query.
#10. Code
insert into tblitemlocation values
('B67466','30',4)
insert into tblitemlocation values
('B67466','20',3)
CREATE
VIEW
SELECT itemID,
SUM(quantityremaining) "totalleft"
FROM VShippedQty
GROUP
BY itemID
HAVING SUM(quantityremaining) > 0
SELECT TblItem.ItemID
"Item ID",
TblItem.description
"Item Name",
v.totalleft
"Total Left to Ship",
SUM(TblItemLocation.QtyOnHand) "Total On Hand",
v.totalleft
- SUM(TblItemLocation.QtyOnHand)
"Total Short"
FROM TblItemLocation
LEFT
JOIN TblItem
ON TblItemLocation.itemID
= tblitem.itemID
LEFT
JOIN Vitemtotalleft
v
ON TblItemLocation.itemID
= v.itemID
GROUP
BY TblItem.ItemID,
TblItem.description,
v.totalleft
HAVING v.totalleft
> SUM(TblItemLocation.QtyOnHand)
#10. Output
|
dbo.SQL4 |
||||
|
Item ID |
Item Name |
Total Left to Ship |
Total On Hand |
Total Short |
|
B67466 |
Diplomacy: Game of Negotiation, Cunning and Deceit. |
17 |
7 |
10 |
|
C26133 |
Knowledge Management: Create a Learning Organization |
18 |
0 |
18 |
#11. Display the names of the customers who did not
order an item during the month of January of the current year and the names of
the items that did not sell during the month of January of the current
year. Use the GETDATE()
function to determine the current year.
Combine the customer names and item names into a single column of the
result table. This is done most easily
with the use of the SQL UNION command. I
also recommend using the “NOT IN” statement.
The answer for this question is a bit difficult to visualize, so the
complete result table is provided below to help in your coding process. I capitalized the customer name to help
differentiate it from the name of the item and also concatenated the words
‘CUSTOMER’ and ‘ITEM’ to improve readability of the output.
#11. Code
create view
SELECT cust.lastname,
cust.firstName,
cust.customerID,
ol.orderid,
ord.OrderDate,
ol.ItemID,
ol.quantity "QuantityOrdered",
ol.listprice "PricePaid",
item.description,
item.listprice
"itemlistprice"
FROM TblOrderLine ol
LEFT JOIN tblOrder ord
ON ol.orderID = ord.orderID
LEFT JOIN tblCustomer cust
ON cust.customerID = ord.customerID
LEFT JOIN tblItem item
ON ol.itemID = item.itemID
SELECT 'CUSTOMER: '++ UPPER(lastname+', '+SUBSTRING(firstname,1,1)+'.')
"Customer or Item Not Purchased in
January"
FROM TblCustomer
WHERE customerID NOT IN (
SELECT DISTINCT(customerID)
FROM VOrderLine
WHERE DATEPART(mm, OrderDate)
= 1
AND DATEPART(yyyy,
OrderDate) = DATEPART(yyyy,
GETDATE())
)
SELECT 'ITEM: '+Description
FROM TblItem
WHERE itemID NOT IN (
SELECT DISTINCT(itemID)
FROM VOrderLine
WHERE DATEPART(mm, OrderDate)
= 1
AND DATEPART(yyyy,
OrderDate) = DATEPART(yyyy,
GETDATE())
)
ORDER BY 1
#11. Output
|
dbo.SQL4 |
|
Customer or Item Not
Purchased in January |
|
CUSTOMER: ARGIENTO, B. |
|
CUSTOMER: |
|
CUSTOMER: CANDRILLER, K. |
|
CUSTOMER: CHEN, L. |
|
CUSTOMER: |
|
CUSTOMER: DAO, P. |
|
CUSTOMER: FOSTER, B. |
|
CUSTOMER: JACKSON, J. |
|
CUSTOMER: JONES, M. |
|
CUSTOMER: POLANSKI, T. |
|
CUSTOMER: TWILLERS, B. |
|
ITEM: How to Host a Murder - An Affair to Dismember |
|
ITEM: You Might be a Redneck if...Jeff Foxworthy Board Game |