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.

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

8.

SELECT WAREHOUSE_NUMBER,

AVG(UNIT_PRICE)

FROM PART

WHERE NOT WAREHOUSE_NUMBER=2

GROUP BY WAREHOUSE_NUMBER;

9.

SELECT * FROM PART

WHERE WAREHOUSE_NUMBER IN(2,3)

AND ITEM_CLASS= 'SG';

10.

SELECT PART_NUMBER, PART_DESCRIPTION

FROM PART

WHERE PART_DESCRIPTION LIKE '%D%'

ORDER BY ITEM_CLASS ASC,

UNITS_ON_HAND DESC;

EXPLANATION:

PART_DESCRIPTION LIKE '%D%'

Means that Those part numbers and description will be printed where part_description contains character 'D' or 'd' at any position.

11.

SELECT PART_NUMBER,

AVG(UNIT_PRICE) AS "AVG_UNIT_PRICE"

FROM PART

GROUP BY PART_NUMBER;

12.

SELECT PART_NUMBER, PART_DESCRIPTION

FROM PART

WHERE PART_DESCRIPTION LIKE '_________' OR '__________'

ORDER BY PART_NUMBER;

EXPLANATION:

To select part numbers where part_description is 9 or 10 characters long, we use LIKE operator:

LIKE PART_DESCRIPTION '_________' OR '__________'

i.e.,

9 times underscore OR 10 times underscore.

No. Of Underscores denotes the number of characters.

13.

SELECT ITEM_CLASS, COUNT(PART_NUMBER) AS "No. Of Parts"

FROM PART

GROUP BY ITEM_CLASS

HAVING "No. Of Parts" >= 3;