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 VItemCostHistory AS

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 VShippedQty AS

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

02/02/2008

Barrington, M.

A23441

New York City Monopoly Game Collector’s Edition

8

8

0

Completed

123000

02/02/2008

Barrington, M.

A34665

Boggle Deluxe 5x5

30

32

-2

Over Shipped

123000

02/02/2008

Barrington, M.

B67123

Cranium WOW Edition

5

5

0

Completed

200335

01/15/2008

Phillips, K.

A23441

New York City Monopoly Game Collector’s Edition

1

0

1

Not Shipped

200335

01/15/2008

Phillips, K.

A34665

Boggle Deluxe 5x5

1

0

1

Not Shipped

200335

01/15/2008

Phillips, K.

B67123

Cranium WOW Edition

1

0

1

Not Shipped

200335

01/15/2008

Phillips, K.

B67466

Diplomacy: Game of Negotiation, Cunning and Deceit.

1

0

1

Not Shipped

223344

02/09/2008

Candriller, K.

A23441

New York City Monopoly Game Collector’s Edition

55

28

27

Partially Shipped

223344

02/09/2008

Candriller, K.

A23771

Catch Phrase Game Music Edition

15

15

0

Completed

223344

02/09/2008

Candriller, K.

A34665

Boggle Deluxe 5x5

100

110

-10

Over Shipped

223344

02/09/2008

Candriller, K.

A34882

Perudo

35

35

0

Completed

223344

02/09/2008

Candriller, K.

B67123

Cranium WOW Edition

25

13

12

Partially Shipped

223344

02/09/2008

Candriller, K.

B67466

Diplomacy: Game of Negotiation, Cunning and Deceit.

15

0

15

Not Shipped

300221

01/30/2008

Martinez, G.

A23771

Catch Phrase Game Music Edition

1

1

0

Completed

300221

01/30/2008

Martinez, G.

A34665

Boggle Deluxe 5x5

1

1

0

Completed

300221

01/30/2008

Martinez, G.

A34882

Perudo

1

1

0

Completed

300221

01/30/2008

Martinez, G.

B67123

Cranium WOW Edition

1

1

0

Completed

300221

01/30/2008

Martinez, G.

B78244

Chicks Battle the Dudes Board Game

1

1

0

Completed

400001

02/29/2008

Jones, M.

C26133

Knowledge Management: Create a Learning Organization

1

0

1

Not Shipped

445511

02/15/2008

Jones, M.

C34122

A Game of Strategy, Negotiation and Excitement for Office Retreats

3

3

0

Completed

450137

12/29/2007

Rodriguez, K.

A34665

Boggle Deluxe 5x5

10

10

0

Completed

450137

12/29/2007

Rodriguez, K.

A34882

Perudo

50

15

35

Partially Shipped

450137

12/29/2007

Rodriguez, K.

B67123

Cranium WOW Edition

21

29

-8

Over Shipped

450137

12/29/2007

Rodriguez, K.

C26133

Knowledge Management: Create a Learning Organization

1

0

1

Not Shipped

450137

12/29/2007

Rodriguez, K.

C34122

A Game of Strategy, Negotiation and Excitement for Office Retreats

6

0

6

Not Shipped

567123

01/26/2008

Rodriguez, K.

C26133

Knowledge Management: Create a Learning Organization

1

0

1

Not Shipped

651222

01/29/2008

Martinez, G.

A34665

Boggle Deluxe 5x5

5

5

0

Completed

651222

01/29/2008

Martinez, G.

A34882

Perudo

16

16

0

Completed

651222

01/29/2008

Martinez, G.

B78244

Chicks Battle the Dudes Board Game

21

21

0

Completed

671100

02/02/2008

Jones, M.

C29179

Managing Change: The Game for an Executive Retreat

1

1

0

Completed

781206

02/15/2008

Argiento, B.

B67466

Diplomacy: Game of Negotiation, Cunning and Deceit.

1

0

1

Not Shipped

892211

12/28/2007

Dao, P.

C26133

Knowledge Management: Create a Learning Organization

15

0

15

Not Shipped

892211

12/28/2007

Dao, P.

C29179

Managing Change: The Game for an Executive Retreat

10

6

4

Partially Shipped

892211

12/28/2007

Dao, P.

C34122

A Game of Strategy, Negotiation and Excitement for Office Retreats

8

5

3

Partially Shipped

980001

01/22/2008

Guili, M.

C29179

Managing Change: The Game for an Executive Retreat

3

3

0

Completed

980001

01/22/2008

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 VShippedQty AS

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

Barrington, M.

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