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()) AND

                        DATEPART(mm, OrderDate) = '01'

ORDER BY     OrderDate DESC

 

#1. Output

dbo.SQL2_3

Date of Order

orderID

Customer Name

Phone Number

01/30/2008

300221

Martinez, G.

(775) 883-7612

01/29/2008

651222

Martinez, G.

(775) 883-7612

01/26/2008

567123

Rodriguez, K.

(819) 382-1828

01/22/2008

980001

Guili, M.

(619) 562-1334

01/15/2008

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()) AND

                                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

01/30/2008

300221

Martinez, G.

(775) 883-7612

A23771

Catch Phrase Game Music Edition

1

$65.95

$65.95

01/30/2008

300221

Martinez, G.

(775) 883-7612

A34665

Boggle Deluxe 5x5

1

$35.95

$35.95

01/30/2008

300221

Martinez, G.

(775) 883-7612

A34882

Perudo

1

$10.95

$10.95

01/30/2008

300221

Martinez, G.

(775) 883-7612

B67123

Cranium WOW Edition

1

$15.95

$15.95

01/30/2008

300221

Martinez, G.

(775) 883-7612

B78244

Chicks Battle the Dudes Board Game

1

$32.95

$32.95

01/29/2008

651222

Martinez, G.

(775) 883-7612

A34665

Boggle Deluxe 5x5

5

$37.95

$189.75

01/29/2008

651222

Martinez, G.

(775) 883-7612

A34882

Perudo

16

$11.95

$191.20

01/29/2008

651222

Martinez, G.

(775) 883-7612

B78244

Chicks Battle the Dudes Board Game

21

$31.65

$664.65

01/26/2008

567123

Rodriguez, K.

(819) 382-1828

C26133

Knowledge Management: Create a Learning Organization

1

$395.95

$395.95

01/22/2008

980001

Guili, M.

(619) 562-1334

C29179

Managing Change: The Game for an Executive Retreat

3

$275.99

$827.97

01/22/2008

980001

Guili, M.

(619) 562-1334

C34122

A Game of Strategy, Negotiation and Excitement for Office Retreats

2

$169.95

$339.90

01/15/2008

200335

Phillips, K.

(775) 332-4636

A23441

New York City Monopoly Game Collector’s Edition

1

$29.95

$29.95

01/15/2008

200335

Phillips, K.

(775) 332-4636

A34665

Boggle Deluxe 5x5

1

$34.95

$34.95

01/15/2008

200335

Phillips, K.

(775) 332-4636

B67123

Cranium WOW Edition

1

$19.95

$19.95

01/15/2008

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

Bethany Twillers

San jose

CA

98123

Apr 01, 1999

12001

Brittany Cranston

Sparks

NV

89431

Apr 12, 2006

21143

Janice Jackson

Sparks

NV

89431-0112

May 06, 2006

29188

Tiffany Polanski

Reno

NV

89507

Aug 23, 2005

30192

Lian Chen

Reno

NV

89503-0113

Aug 30, 1998

32817

Ben Foster

San diego

CA

92381

Aug 15, 1989

 

#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 MAX(ListPrice)

                        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           MAX(ListPrice)

                        FROM  TblOrderLine

                        )

AND                lastCostDate =

                        (

                        SELECT           MAX(lastCostDate)

                        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

02/15/2008