Write SQL codes for these requirements based off of the five tables below. I hav

Question

Write SQL codes for these requirements based off of the five tables below. I have attached five pictures of the five tables there are with what each of them contain.
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 item class 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 item classes and the number of parts under each class. Only show those classes with at least 3 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 most 3 parts. SELECT FRON ORDER LNE ORDER NUMBER PART NUMBER NUMBER ORDERED 21 05 22.95 4999

Explanation / Answer

I have implemented SQL queries based on the requirement asked in the question, please read the follow answers

Question 7

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.

SQL CODE

SELECT
O.Order_Date,
SUM(Number_Ordered) As NumberOfOrders,
RANK() OVER (ORDER BY SUM(Number_Ordered)) AS RankOrder
FROM Orders As O
Inner Join Order_Line As OL On OL.Order_Number = O.Order_Number
Group By O.Order_Date

Question 8

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

SQL CODE

SELECT Warehouse_Number, AVG(Unit_Price) As AverageUnitPrice FROM Part
Where Warehouse_Number <> 2
Group By Warehouse_Number

Question 9

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

SQL CODE

Select
Part_Number,
Part_Description,
Units_On_Hand,
Item_Class,
Warehouse_Number,
Unit_Price
From Part
Where Item_Class = 'SG' And Warehouse_Number IN (2, 3)

Question 10

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 item class and then descending order on units_on_hand.

SQL CODE

Select
part_number,
part_description,
RANK() OVER (ORDER BY ITEM_CLASS ASC) AS RankItem_Class
from part
where part_description Like '%d%'
Order By Units_On_Hand DESC

Question 11

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

SQL CODE

SELECT AVG(Units_On_Hand) AS AVG_UNIT_PRICE FROM PART

Question 12

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.

SQL CODE

Select
part_number,
part_description,
RANK() OVER (ORDER BY part_number ASC) AS RankPart_Number
from part
where LEN(part_description) = 9 OR LEN(part_description) = 10

Question 13

13.Find the item classes and the number of parts under each class. Only show those classes with at least 3 types of parts.

SQL CODE

Select Top 2 Item_Class, COUNT(Part_Number) As NumberOfParts From Part
Group By Item_Class
Having COUNT(Part_Number) > 2

Question 14

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

SQL CODE

SELECT
Part_Number,
Part_Description,
Units_On_Hand,
Item_Class,
Warehouse_Number,
Unit_Price
From Part
Where (Item_Class = 'SG'
Or Units_On_Hand > 60)
And Warehouse_Number = 3

Question 15

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

SQL CODE

Select
Count(Part_Number) As NumberOfParts,
Warehouse_Number
From Part
Group By Warehouse_Number
Having Count(Part_Number) = 3

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.