IS675 – Database Design

Bilal Gonen

My answers for SQL-2

 

 

 

#1. List the names of all customers located in the state of Nevada.  Sort the output by customer last name.

 

#1. Code

SELECT           firstname + ' ' + lastname "Customer Name",

                        city "City",

                        state "State"

FROM              TblCustomer

WHERE           LOWER(State) = 'nv'

ORDER BY     lastname

 

#1. Output

dbo.SQL2_1

Customer Name

City

State

Margaret Barrington

reno

nv

Lian Chen

REno

Nv

Brittany Cranston

Sparks

NV

Janice Jackson

Sparks

NV

Martin Jones

reno

nv

Guadalupe Martinez

Reno

NV

Kendall Phillips

SPARKS

nv

Tiffany Polanski

RENO

NV

 

 

 

#2. Change the format of the query to include the customer's phone number.  Change the structure of the customer name.  Change the format of the city so that it displays the first character as upper case and the rest as lower case.  Change the state so that it displays in all upper case.

 

#2. Code

SELECT           lastname + ', ' + SUBSTRING(firstname,1,1)+'.' "Customer Name",

                        '('+SUBSTRING(Phone,1,3)+') '+ SUBSTRING(Phone,5,12) "Phone Number",

                        UPPER(SUBSTRING(city,1,1))+LOWER(SUBSTRING(city,2,20)) "City",

                        UPPER(state) "State"

FROM              TblCustomer

WHERE           LOWER(State) = 'nv'

ORDER BY     lastname

 

#2. Output

dbo.SQL2_2

Customer Name

Phone Number

City

State

Barrington, M.

(775) 746-4561

Reno

NV

Chen, L.

(775) 721-8991

Reno

NV

Cranston, B.

(775) 331-2199

Sparks

NV

Jackson, J.

(775) 331-7188

Sparks

NV

Jones, M.

(775) 331-4838

Reno

NV

Martinez, G.

(775) 883-7612

Reno

NV

Phillips, K.

(775) 332-4636

Sparks

NV

Polanski, T.

(775) 746-5771

Reno

NV

 

#3. List the orders in the order table that do NOT have a shipaddress.  Sort the output in ascending order by orderdate.

 

#3. Code

SELECT           CONVERT(VARCHAR, OrderDate, 107) "Date of Order",

                        OrderID "Order Number",

                        CustomerID "Customer Number",

                        DiscountCode "Discount Code"

FROM              TblOrder

WHERE           ShipAddress IS NULL

ORDER BY     OrderDate

 

#3. Output

dbo.SQL2_3

Date of Order

Order Number

Customer Number

Discount Code

Dec 28, 2007

892211

00625

 

Jan 22, 2008

980001

78112

04

Jan 29, 2008

651222

12006

 

Jan 30, 2008

300221

12006

03

Feb 02, 2008

123000

00405

A1

Feb 02, 2008

671100

32018

 

Feb 09, 2008

223344

21142

 

Feb 15, 2008

445511

32018

02

Feb 29, 2008

400001

32018

B3

 

 

 

#4. List order information placed for itemID A23441.  Include the orderID, itemID, quantity ordered, price paid, and calculate the extended price (price * quantity).  Sort the output by orderID. 

 

#4. Code

SELECT           OrderID "Order Number",

                        ItemID "Item Number",

                        Quantity "Quantity Ordered",

                        ListPrice "Price Paid",

                        Quantity * ListPrice "Extended Price"

FROM              tblOrderLine

WHERE           ItemID = 'A23441'

ORDER BY     OrderID

 

#4. Output

dbo.SQL2_1

Order Number

Item Number

Quantity Ordered

Price Paid

Extended Price

123000

A23441

8

$29.95

$239.60

200335

A23441

1

$29.95

$29.95

223344

A23441

55

$29.95

$1,647.25

 

 

#5. Which order lines have an extended price greater than $500?  Sort the output by orderID.

 

#5. Code

SELECT           OrderID "Order Number",

                        ItemID "Item Number",

                        Quantity "Quantity Ordered",

                        ListPrice "Price Paid",

                        (Quantity * ListPrice) "Extended Price"

FROM              tblOrderLine

WHERE           Quantity * ListPrice > 500

ORDER BY     OrderID

 

#5. Output

dbo.SQL2_1

Order Number

Item Number

Quantity Ordered

Price Paid

Extended Price

123000

A34665

30

$37.95

$1,138.50

223344

A23441

55

$29.95

$1,647.25

223344

A23771

15

$53.99

$809.85

223344

A34665

100

$23.95

$2,395.00

223344

B67466

15

$40.95

$614.25

445511

C34122

3

$269.95

$809.85

450137

C34122

6

$167.95

$1,007.70

651222

B78244

21

$31.65

$664.65

892211

C26133

15

$380.00

$5,700.00

892211

C29179

10

$259.95

$2,599.50

892211

C34122

8

$200.00

$1,600.00

980001

C29179

3

$275.99

$827.97

 

 

 

 

#6. Which order lines placed for itemID C34122 have an extended price greater than $1000?

 

#6. Code

SELECT           OrderID "Order Number",

                        ItemID "Item Number",

                        Quantity "Quantity Ordered",

                        ListPrice "Price Paid",

                        (Quantity * ListPrice) "Extended Price"

FROM              tblOrderLine

WHERE           Quantity * ListPrice > 1000 AND ItemID='C34122'

ORDER BY     OrderID

 

#6. Output

dbo.SQL2_1

Order Number

Item Number

Quantity Ordered

Price Paid

Extended Price

450137

C34122

6

$167.95

$1,007.70

892211

C34122

8

$200.00

$1,600.00

 

 

#7. Which customers have a firstbuydate in the 1900’s?

 

#7. Code

SELECT           FirstName + ' ' + LastName "Customer Name",

                        CONVERT(VARCHAR, FirstBuyDate, 107) "First Buy Date"

FROM              TblCustomer

WHERE           DATEPART(yyyy, FirstBuyDate) < 2000

ORDER BY     FirstName

 

#7. Output

dbo.SQL2_1

Customer Name

First Buy Date

Ben Foster

Aug 15, 1989

Bethany Twillers

Apr 01, 1999

Kendall Phillips

Aug 12, 1999

Lian Chen

Aug 30, 1998

Margaret Barrington

Jul 12, 1999

 

 

 

#8. What is the latest (most current) firstbuydate for a customer in the CUSTOMER table?  You do not have to display any other information – just the value of the most current firstbuydate – for the answer to this query.

 

#8. Code

SELECT           CONVERT(VARCHAR, MAX(FirstBuyDate), 107) "The Most Current First Buy Date"

FROM              TblCustomer

 

#8. Output

dbo.SQL2_1

The Most Current First Buy Date

Feb 12, 2007

 

 

 

 

#9. What is the average listprice for items in the database? Use the ITEM table for this query.

 

#9. Code

SELECT           AVG(ListPrice) "Average List Price"

FROM              tblItem

 

#9. Output

dbo.SQL2_1

Average List Price

$97.22

 

 

 

#10. What is the average amount in qtyshipped when a package is shipped via the method of FedEx?

 

#10. Code

SELECT           AVG(QtyShipped) "Average Quantity Shipped via FedEx"

FROM              tblShipLine

WHERE           LOWER(MethodShipped) = 'fedex'

 

#10. Output

dbo.SQL2_1

Average Quantity Shipped via FedEx

7

 

#11. Create a grouped report that summarizes data and provides information about each item that is on an order in the tblorderline.  The report should include the itemid, the total quantity on order for that item, the minimum price that the item sold for, the maximum price that the item sold for, and the average price that the item sold for.  The report should be ordered by itemid.

 

#11. Code

SELECT           ItemID "Item Number",

                        SUM(Quantity) "Total Quantity Sold",

                        MIN(ListPrice) "Minimum Price",

                        MAX(ListPrice) "Maximum Price",

                        AVG(ListPrice) "Average Price"

FROM              TblOrderLine

GROUP BY     ItemID

ORDER BY     ItemID

 

#11. Output

dbo.SQL2_1

Item Number

Total Quantity Sold

Minimum Price

Maximum Price

Average Price

A23441

64

$29.95

$29.95

$29.95

A23771

16

$53.99

$65.95

$59.97

A34665

147

$23.95

$37.95

$33.63

A34882

102

$7.95

$11.95

$10.20

B67123

53

$14.95

$19.95

$16.35

B67466

17

$40.95

$43.95

$42.95

B78244

22

$31.65

$32.95

$32.30

C26133

18

$380.00

$398.95

$392.71

C29179

14

$25.95

$275.99

$187.30

C34122

19

$167.95

$269.95

$201.96

 

 

 

#12. Modify query #11 so that it only displays the rows where the maxprice is greater than 25% more than the minprice.  Add a column to the output that displays the percentage difference between the minimum and maximum price.  (Hint:  There is no percentage data type or formatting capability in SQL.)

 

#12. Code

SELECT           ItemID "Item Number",

                        SUM(Quantity) "Total Quantity Sold",

                        MIN(ListPrice) "Minimum Price",

                        MAX(ListPrice) "Maximum Price",

                        AVG(ListPrice) "Average Price",

                        CAST((MAX(ListPrice)/MIN(ListPrice)-1)*100 AS VARCHAR)+ ' %' "Percent Difference"

FROM              TblOrderLine

GROUP BY     ItemID

HAVING         MAX(ListPrice)/MIN(ListPrice) > 1.25

ORDER BY     ItemID

 

#12. Output

dbo.SQL2_1

Item Number

Total Quantity Sold

Minimum Price

Maximum Price

Average Price

Percent Difference

A34665

147

$23.95

$37.95

$33.63

58.45 %

A34882

102

$7.95

$11.95

$10.20

50.31 %

B67123

53

$14.95

$19.95

$16.35

33.44 %

C29179

14

$25.95

$275.99

$187.30

963.54 %

C34122

19

$167.95

$269.95

$201.96

60.73 %

 

 

#13. The purpose of this report is to look for orders that are overdue.  The company usually ships all orders within 40 days of the date that the order was placed.  Which orders in tblorder have an orderdate that  is less than the current date, once 40 days are added to the orderdate?

 

#13. Code

SELECT           OrderID "Order Number",

                        CustomerID "Customer Number",

                        CONVERT(VARCHAR, OrderDate, 107) "Date Ordered",

                        CONVERT(VARCHAR, DATEADD(day, 40, OrderDate), 107) "Date 40 Days After Date Ordered",

                        DATEDIFF(day, OrderDate, getdate()) "Number of Days Difference"

FROM              TblOrder

WHERE           DATEDIFF(day, OrderDate, getdate()) > 40

ORDER BY     OrderID

 

#13. Output

dbo.SQL2_1