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.
1.List unique item classes stored in my database.
2.List the warehouse number and the number of different parts stored 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. Rename the calculated column TOTAL_AMOUNT. TOTAL_AMOUNT=Units_ON_HAND*UNIT_PRICE
5.List the class and total number of units on hand for each class. Rank your results in descending order on the total number of units on hand.

SELECT FRON ORDER LNE ORDER NUMBER PART NUMBER NUMBER ORDERED 21 05 22.95 4999

Explanation / Answer

Please find my answer:

Question a)

List unique item classes stored in your database.

SQL CODE

SELECT DISTINCT item_class As ItemClass FROM Part

Question b)

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

SQL CODE

select
warehouse_number As WareHouseNumber,
count(part_number) NumberOfDifferentParts
from part
group by warehouse_number
having count(part_number) > 2

Question c)

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

SQl CODE

select
warehouse_number As WareHouse_Number,
max(units_on_hand) As Max_UOH from part
Group By warehouse_number

Question d)

List the part number, total dollar amount for each part stored, and the warehouse number for the part. Rename the calculated column TOTAL_AMOUNT. TOTAL_AMOUNT=Units_ON_HAND*UNIT_PRICE

SQL CODE

select
part_number,
(units_on_hand * unit_price) As Total_Amount,
warehouse_number
from part

Question e)

List the item class and total number of units on hand for each item class. Rank your results in descending order on the total number of units on hand.

SQL CODE

select
RANK() OVER (ORDER BY Sum(units_on_hand) DESC) As RankTotal,
item_class,
Sum(units_on_hand) As total_number_of_units
from part
Group By Item_Class

Please rate my answer!!

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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