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.
6.List stored information for all the orders placed between August 3rd and August 6th of 2013. (not including August 3rd 2013 and August 6th 2013).
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.

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

Explanation / Answer

1.

Select distinct Item_Class from Part;

2.

Select Warehouse_Number, count(Part_Number) from Part group by Warehouse_Number having count(Part_Number) > 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;

5.

Select Item_Class, sum(Units_On_Hand) from Part group by Item_Class order by sum(Units_On_Hand) desc;

6.

Select * from Orders where Order_Date between #03-Aug-2013# and #06-Aug-2013#;

7.

Select Order_date,count(Order_Number) from Orders group by Order_Date order by count(Order_Number) desc;

Do ask if any doubt. Please upvote

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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