Using SQL Server to create the queries below:

You should copy/paste the SQL code, followed by the data/results onto this Word Document. NOTE: It is easier if you use a clipping tool for the data set (such as Snipping Tool) as it does not copy/paste well.

Be sure to use good indention form on coding.

If your result is more than 10 records ONLY show the 1^st ten!

The sample that follows will give you an example of how your code and data should look.

Sample:

Write an SQL statement to select the WarehouseCity and WarehouseState from the WAREHOUSE table where the square footage is more than 130,000 square feet.

Code:

SELECT WarehouseCity, WarehouseState

FROM WAREHOUSE

WHERE SquareFeet > 130000;

Data Result:

Tables and Fields:

Single Table Queries

1. Write a SQL statement to show all the fields in the WAREHOUSE table. Sort by the WarehouseCity field. Use the * wildcard character.

Code:

Data Result:

2. Write a SQL statement to show all the fields in the SKU_DATA table. Do NOT use the * wildcard character.

Code:

Data Result:

3. Write a SQL statement to show a unique SKU, and the SKU_Description for all products having Mask within the SKU description.

Code:

Data Result:

4. Write a SQL statement to show only the TOP 4 order items from the ORDER_ITEM table. Show all fields and sort by Price in descending order.

Code:

Data Result:

5. Write a SQL statement to show the total number of Small Clear Dive Masks (SKU = 101100) on hand across all warehouse locations. Make the column name in the output TotalSmallClearMasks.

Code:

Data Result:

6. Write an SQL statement to show all catalog items in the 2018 catalog that was NOT in the 2016 catalog. Show only the SKU and SKU Description fields.

Code:

Data Result:

7. Write an SQL statement to show all items that were included in the 2016, 2017, and 2018 catalog. Show only the SKU and SKU-Description fields and sort by the SKU_Description.

Code:

Data Result:

8. Write an SQL statement to show a unique SKU and SKU_Description (from INVENTORY table) for all products having an f in the fourth position from the left in SKU_Description.

Code:

Data Result:

9. Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by WarehouseID. Omit all SKU items that have less than 50 items on hand and name the sum TotalItemsOnHandMT49 and display the results in descending order of TotalItemsOnHandMT49.

Code:

Data Result:

10. Write an SQL statement to produce a single column called ManagerWarehouse that combines the Manager, Warehouse City, and WarehouseState together so that each row will look like Dave Jones manages a warehouse in Atlanta, GA. Be sure to remove any extra spaces.

Code:

Data Result:

11. Write an SQL statement to show any items added to the catalog (CATALOG_SKU_2016) on July 1^st of 2016. Include all fields.

Code:

Data Result:

12. Write an SQL statement to display the total square footage for the warehouses in Bangor, Seattle, and San Francisco. Name the field TotalSqFootage.

Code:

Data Result:

13. Write an SQL statement to display each item in inventory where the QuantityOnOrder is 250 or more but less than750. Include the WarehouseID, SKU, SKU_Description and the QuantityOnOrder.

Code:

Data Result:

Multiple Table Queries

14. Show all the inventory available in the Bangor warehouse. Include the SKU, SKU_Description, and QuantityOnHand. Use a subquery and sort by QuantityOnHand in descending order.

Code:

Data Result:

15. What product(s) did Store Number 10 order during December of 2017? Include SKU, SKU_Description, Quantity, Price, and ExtendedPrice and do not show duplicates.

Code:

Data Result:

16. Create the same query above in #14 using a JOIN.

Code:

Data Result:

17. Write a SQL statement to create to list of items that are *available* in inventory. The list should have the following columns: SKU_Description, WarehouseCity, QuantityOnHand.

Use an implicit join. Sort the list by the SKU_Description.

Code:

Data Result:

Using SQL Server to create the queries below: Single Table Queries - You should copy/paste the SQL code, followed by the data/results onto this Word Document. 1. Write a SQL statement to show all the fields in the WAREHOUSE table. Sort by the WarehouseCity NOTE: It is easier if you use a clipping tool for the data set (such as Snipping Tool) as it does not field. Use the * wildcard character. copy/paste well. - Be sure to use good indention form on coding. Code: - If your result is more than 10 records ONLY show the 1st ten! - The sample that follows will give you an example of how your code and data should look. Sample: Write an SQL statement to select the WarehouseCity and WarehouseState from the Data Result: WAREHOUSE table where the square footage is more than 130,000 square feet. Code: SELECTFROMWHEREWarehousecity,WarehousestateWAREHOUSESquareFeet>130000; 2. Write a SQL statement to show all the fields in the SKU_DATA table. Do NOT use the * wildcard character. Data Result: Code: Tables and Fields: Data Result: Cape Codd Database Table Structure: (Tables \& Fields) Data Result: 3. Write a SQL statement to show a unique SKU, and the SKU Description for all products having 'Mask' within the SKU description. Code: Data Result: 4. Write a SQL statement to show only the TOP 4 order items from the ORDER_ITEM table. Show all 7. Write an SQL statement to show all items that were included in the 2016, 2017, and 2018 catalog. fields and sort by Price in descending order. Show only the SKU and SKU-Description fields and sort by the SKU Description. Code: Code: Data Result: Data Result: 8. Write an SQL statement to show a unique SKU and SKU Description (from INVENTORY table) for all products having an ' f ' in the fourth position from the left in SKU Description. 5. Write a SQL statement to show the total number of Small Clear Dive Masks (SKU=101100) on hand Code: across all warehouse locations. Make the column name in the output 'TotalSmallClearMasks'. Code: Data Result: Data Result: 6. Write an SQL statement to show all catalog items in the 2018 catalog that was NOT in the 2016 9. Write an SQL statement to display the WarehouseID and the sum of QuantityOnHand, grouped by catalog. Show only the SKU and SKU Description fields. WarehouselD. Omit all SKU items that have less than 50 items on hand and name the sum TotalltemsOnHandMT49 and display the results in descending order of TotalitemsOnHandMT49. Code: Code: Data Result: 10. Write an SQL statement to produce a single column called ManagerWarehouse that combines the 13. Write an SQL statement to display each item in inventory where the QuantityOnOrder is 250 or Manager, Warehouse City, and WarehouseState together so that each row will look like "Dave Jones more but less than750. Include the WarehouselD, SKU, SKU Description and the QuantityOnOrder. manages a warehouse in Atlanta, GAN.. Be sure to remove any extra spaces. Code: Code: Data Result: Data Result: Multiple Table Queries 11. Write an SQL statement to show any items added to the catalog (CATALOG_SKU_2016) on July 1st of 14. Show all the inventory available in the Bangor warehouse. Include the SKU, SKU, Description, and 2016. Include all fields. QuantityQnHand. Use a subquery and sort by QuantitwQnHand in descending order. Code: Code: Data Result: Data Result: 12. Write an SQL statement to display the total square footage for the warehouses in Bangor, Seattle, and San Francisco. Name the field TotalSgFootage. 15. What product(s) did Store Number 10 order during December of 2017 ? Include SKU, SKU Desccriptipo, Quantity, Price, and ExtendedRrice and do not show duplicates. Code: Code: 17. Write a SQL statement to create to list of items that are available in inventory. The list should have the following columns: SKU Description, WarehouseCity, QuantityOnHand. Use an implicit join. Sort the list by the SKU Description. Code