Product Standard Price

Use the Pine Valley database to answer each of the questions. Each question within deliverable 2 must begin on a new page and be sure to document the questions the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and then provide a screen shot of each query. The screen shots must include both the SQL statement and the results for each item below based on the data entered in task 1. The screen shots must be large enough for the instructor to clearly read the results without a magnifying glass!

Caution: Read the instructions carefully! Each question is based on a single SQL statement, and the single SQL statement might contain sub-queries (additional SELECT statements) within the statement.

  • Provide a list all of the Customer ID, Customer Names, and States and sort the list in alphabetical order by Customer Name.
  • Provide a list of all of the Customer ID, Customer Names, and States, and sort the list by state with the Customer Names in alphabetical order within each state.
  • What is the full address of the customer named Kaneohe Homes? That includes the street, city, state, and zip code
  • Provide a list of the product name, product ID, and unit price for all of the products in the Product table, sorted in alphabetical order by product name
  • What is the standard price and standard price if increased by 15% for every product?
  • What is the average standard price for the products with a product finish of natural ash?
  • Provide a listing of all of the products that includes the difference between its standard price and the overall average standard price of all products.
  • What is the product name, product ID and price of most expensive product in the product table?
  • Which orders have been placed since October 20, 2005?
  • List the product name, standard price, and finish for all tables that cost more than $300 and all desks that cost more than $300.
  • Provide a listing of the unique product IDs for the products in the Order Line table.
  • Which states have more than one customer? List the states and the number of customers for those states

Book Pine Valley Furniture Company (PVFC) Datasets

Customer_T

CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode

1, ‘Contemporary Casuals’, ‘1355 S Hines Blvd’, ‘Gainesville’, ‘FL’, ‘32601-2871’

2, ‘Value Furniture’, ‘15145 S.W. 17th St.’, ‘Plano’, ‘TX’, ‘75094-7743’

3, ‘Home Furnishings’, ‘1900 Allard Ave.’, ‘Albany’, ‘NY’, ‘12209-1125’

4, ‘Eastern Furniture’, ‘1925 Beltline Rd.’, ‘Carteret’, ‘NJ’, ‘07008-3188’

5, ‘Impressions’, ‘5585 Westcott Ct.’, ‘Sacramento’, ‘CA’, ‘94206-4056’

6, ‘Furniture Gallery’, ‘325 Flatiron Dr.’, ‘Boulder’, ‘CO’, ‘80514-4432’

7, ‘Period Furniture’, ‘394 Rainbow Dr.’, ‘Seattle’, ‘WA’, ‘97954-5589’

8, ‘California Classics’, ‘816 Peach Rd.’, ‘Santa Clara’, ‘CA’, ‘96915-7754’

9, ‘M and H Casual Furniture’, ‘3709 First Street’, ‘Clearwater’, ‘FL’, ‘34620-2314’

10, ‘Seminole Interiors’, ‘2400 Rocky Point Dr.’, ‘Seminole’, ‘FL’, ‘34646-4423’

11, ‘American Euro Lifestyles’, ‘2424 Missouri Ave N.’, ‘Prospect Park’, ‘NJ’, ‘07508-5621’

12, ‘Battle Creek Furniture’, ‘345 Capitol Ave. SW’, ‘Battle Creek’, ‘MI’, ‘49015-3401’

13, ‘Heritage Furnishings’, ‘66789 College Ave.’, ‘Carlisle’, ‘PA’, ‘17013-8834’

14, ‘Kaneohe Homes’, ‘112 Kiowai St.’, ‘Kaneohe’, ‘HI’, ‘96744-2537’

15, ‘Mountain Scenes’, ‘4132 Main Street’, ‘Ogden’, ‘UT’, ‘84403-4432’

Order_T.

OrderID, OrderDate, CustomerID

1001, ’21/Oct/10′, 1

1002, ’21/Oct/10′, 8

1003, ’22/Oct/10′, 15

1004, ’22/Oct/10′, 5

1005, ’24/Oct/10′, 3

1006, ’24/Oct/10′, 2

1007, ’27/Oct/10′, 11

1008, ’30/Oct/10′, 12

1009, ’05/Nov/10′, 4

1010, ’05/Nov/10′, 1

Product_T.

ProductID, ProductDescription, ProductFinish, ProductStandardPrice, ProductLineID

1, ‘End Table’, ‘Cherry’, 175, 1

2, ‘Coffee Table’, ‘Natural Ash’, 200, 2

3, ‘Computer Desk’, ‘Natural Ash’, 375, 2

4, ‘Entertainment Center’, ‘Natural Maple’, 650, 3

5, ‘Writers Desk’, ‘Cherry’, 325, 1

6, ‘8-Drawer Desk’, ‘White Ash’, 750, 2

7, ‘Dining Table’, ‘Natural Ash’, 800, 2

8, ‘Computer Desk’, ‘Walnut’, 250, 3

OrderLine_T.

OrderID, ProductID, OrderedQuantity

1001, 1, 2

1001, 2, 2

1001, 4, 1

1002, 3, 5

1003, 3, 3

1004, 6, 2

1004, 8, 2

1005, 4, 4

1006, 4, 1

1006, 5, 2

1006, 7, 2

1007, 1, 3

1007, 2, 2

1008, 3, 3

1008, 8, 3

1009, 4, 2

1009, 7, 3

1010, 8, 10

Do you need help with this assignment? Or a different one? We got you covered.

Quality Guaranteed

Any Deadline

No Plagiarism