Question

Please write queries based on the following requirements using DML In-Class Data Set. For each question, you are required to submit 1) SQL query code; 2) a screen shot of your query result.You should copy and paste your SQL query code to the word document instead of taking a screenshot of your code. Missing either part for each question will result in 0 for this question.

1.List unique itemclasses stored in my database.

2.List the warehouse number and the number of different partsstored in each warehouse, only include those warehouse with more than 2 different parts.

3.List the warehouse number and the maximum number of units on hand for parts stored in each of the warehouse. Rename the new column Max_UOH.

4.List the part number, total dollar amount for each part stored, and the warehouse number for the part.Renamethe calculatedcolumn TOTAL_AMOUNT. TOTAL_AMOUNT=Units_ON_HAND*UNIT_PRICE

5.List theclass and totalnumber of units on hand for each class. Rank your resultsin descending order on the total number of units on hand.

6.List stored information for all the orders placed between August 3rdand August 6thof 2013.(not including August 3rd2013 and August 6th2013).

7.List all the order date and the number of orders placed on each date. Rank your results in descending order on the number of orders.

8.List the warehouse number and the average unit price for warehouses other than number 2.

9.List the stored part information for SG parts stored in warehouse 2 or 3.

10.List the part number and part description for each part with letter D(or d) in the part_description. Rank your results in ascending order on itemclass and then descending order on units_on_hand.

11.List the average unit price among all the parts. Rename the column as AVG_UNIT_PRICE

12.List the part number and part description for each part with nine characters or ten characters in the part_description. Rank your results in ascending order on part number.

13.Find the itemclasses and the number of parts under each class. Only show those classes with at least3 types of parts.

14.List the stored part information for SG parts or parts with more than 60 units stored in warehouse 3.

15.List the number of different part stored in each warehouse, only include those warehouse with at most3parts.

Answer 1

1. SELECT DISTINCT(ITEM_CLASS) FROM PART;

2.

SELECT WAREHOUSE_NUMBER,

COUNT(PART_NUMBER) AS "NO. OF PARTS"

FROM PART

GROUP BY WAREHOUSE_NUMBER

HAVING "NO. OF PARTS" > 2;

3.

SELECT WAREHOUSE_NUMBER,

MAX(UNITS_ON_HAND) AS "Max_UOH"

FROM PART

GROUP BY WAREHOUSE_NUMBER;

4.

SELECT PART_NUMBER, (UNITS_ON_HAND *

UNIT_PRICE) AS "TOTAL AMOUNT" ,

WAREHOUSE_NUMBER

FROM PART

GROUP BY PART_NUMBER;

5.

SELECT ITEM_CLASS,

SUM(UNITS_ON_HAND) AS "Total No. Of Units"

FROM PART

GROUP BY ITEM_CLASS

ORDER BY "Total No. Of Units" DESC;

6.

SELECT * FROM ORDERS

WHERE ORDER_DATE > 03-AUG-2013

AND

ORDER_DATE < 06-AUG-2013;

7.

SELECT ORDER_DATE, COUNT (ORDER_NUMBER) AS "NO. OF ORDERS"

FROM ORDERS

GROUP BY ORDER_DATE

ORDER BY "NO. OF ORDERS" DESC;

8.

SELECT WAREHOUSE_NUMBER, AVG(UNIT_PRICE)

FROM PART

WHERE WAREHOUSE_NUMBER NOT IN (2)

GROUP BY WAREHOUSE_NUMBER;