Using SQLServer to create the queries below:

• You should copy/paste the SQL code, followed by the data onto this Word Document. If the data set is more than 10 records, just copy the first ten records. Do NOT use Print Screen. NOTE: It is easier if you use a clipping tool such as SnagIt for the data set as it does not copy/paste well.

NOTE: Be sure to use the attached Table Structure sheet that will show you the fields in each table to reference for the following questions!

Sample:

Write an SQL statement to select both the SKU and the SKU_Description fields from the inventory table.

SELECT SKU, SKU_Description

FROM INVENTORY

NOTE: There were 32 records returned when executing this query but I only copy and pasted the first 10 records as required. Be sure you limit your data to only 10 records.

1. Write an SQL statement to show only the first 10 orders from the RETAIL_ORDER table. Show all fields and sort by OrderTotal.

Copy your code here:

2. Write an SQL statement to show all the fields in the INVENTORY table. Sort by the QuantityOnOrder in Ascending order. Use the * wildcard character.

3. Write an SQL statement to display the total square footage for the warehouses in Bangor, Chicago, and Seattle. Name the field SqFootageSum.

4. Write an SQL statement to show a unique SKU_Description and Buyer for all buyers having an e in the second position from the right in their name.

5. Write an SQL statement to show all catalog items in the 2017 catalog that was NOT in the 2018 catalog. Show only the SKU, SKU Description and Department fields.

Cape Codd Database Table Structure: (Tables & Fields) Catalog_SKU_2016 CatalogID (PK, int, not null) SKU (int, not null) SKU_Description (char(35), not null) Department (char(30), not null) CatalogPage (int, null) DateOnWeb Site (date, null) Catalog_SKU_2017 CatalogID (PK, int, not null) SKU (int, not null) SKU_Description (char(35), not null) Department (char(30), not null) CatalogPage (int, null) DateOnWeb Site (date, null) Catalog_SKU_2018 CatalogID (PK, int, not null) SKU (int, not null) SKU_Description (char(35), not null) Department (char(30), not null) CatalogPage (int, null) DateOnWebSite (date, null) INVENTORY WarehouseID (PK, FK, int, not null) SKU (PK, FK, int, not null) SKU_Description (char(35), not null) QuantityOnHand (int, null) QuantityOnOrder (int, null) ORDER_ITEM RETAIL_ORDER OrderNumber (PK, FK, int, not null) OrderNumber (PK, int, not null) SKU (PK, FK, int, not null) StoreNumber (int, null) Quantity (int, not null) Store Zip (char(9), null) Price (money, not null) OrderMonth (char(12), not null) Extended Price (money, not null) OrderYear (int, not null) Order Total (money, null) WAREHOUSE WarehouseID (PK, int, not null) WarehouseCity (char(30), not null) WarehouseState (char(2), not null) Manager (char(35), null) SquareFeet (int, null) SKU_DATA SKU (PK, int, not null) SKU_Description (char(35), not null) Department (char(30), not null) Buyer (char(35), null) Pirate Toy Store Tables (Tables & Fields) INVOICE_ITEM CUSTOMER CustomerID (PK, int, not null) FirstName (char(25), not null) LastName (char(25), not null) Phone (char(12), not null) Email (char(100), null) INVOICE InvoiceNumber (PK, int, not null) CustomerNumber (FK, int, not null) SaleDate (datetime, not null) TotalAmount (numeric(8,2), null) InvoiceNumber (PK, FK, int, not null) Item Number (PK, int, not null) Item (char(50), not null) Quantity (int, not null) UnitPrice (numeric(8.2), null) 1 2 3 OrderNumber StoreNumber StoreZIP Order Month Order Year Order Total 2000 20 02335 December 2017 310.00 1000 10 98110 December 2017 445.00 3000 10 98110 January 2018 480 00 1 2 3 4 5 6 7 8 WarehouseID 100 100 200 300 300 300 300 100 400 400 100 200 SKU SKU_Descnption QuantityOnHand QuantityOnOrder 100100 Std. Scuba Tank, Yelow 250 0 302000 Locking Carabiner, Oval 1000 0 302000 Locking Carabiner, Oval 1250 0 100100 Std Scuba lank Yelow 10x1 0 101200 Dive Mask, Med Clear 475 0 201000 Hait-dome Tent 250 0 202000 Half dome Tent vestibulo 100 0 100100 Std. Scuba Tank, Yelow 200 0 100200 Std Scuba Tank, Magenta 250 0 101100 Dive Mask, Smal Clear 450 0 100200 Std Scuba lank, Magenta 200 30 1000 SIH Scuba Tank Yol 100 50 9 10 11 12 SqFootageSum 380000 1 1 2 3 4 5 5 6 SKU_Description Buyer Std. Scuba Tank, Dark Blue Pete Hansen Std Scuba Tank, Dark Green Pete Hansen Std. Scuba Tank, Light Blue Pete Hansen Std Scuba Tank, Light Green Pete Hansen Std. Scuba Tank, Magenta Pete Hansen Std. Scuba Tank, Yellow Pete Hansen 5 1 SKU SKU_Description Department 100300 Std. Scuba Tank Light Blue Water Sports 100400 Std. Scuba Tank, Dark Blue Water Sports 2