****Please write queries based on the following requirements using the DML In-Cl
ID: 3737992 • Letter: #
Question
****Please write queries based on the following requirements using the DML In-Class Data Set.
****I do not have the Data Set to post, but if you could please provide the SQL queries for each question, Thank You!
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.
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.
Explanation / Answer
1. List unique item classes stored in my database
SELECT DISTINCT(CLASS_NAME) FROM MY_CLASSES;
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.
SELECT WAREHOUSE_NO, COUNT(DISTINCT(PARTS)) FROM WAREHOUSE GROUP BY WAREHOUSE_NO HAVING COUNT(DISTINCT(PARTS)) > 2;
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
SELECT WAREHOUSE_NO, MAX(PARTS) AS Max_UOH FROM WAREHOUSE GROUP BY WAREHOUSE_NO;
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.
SELECT PART_NO, WAREHOUSE_NO, Units_ON_HAND*UNIT_PRICE AS TOTAL_AMOUNT FROM WAREHOUSE;
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 CLASS_NAME, SUM(UNITS) AS TOTAL_UNITS FROM WAREHOUSE GROUP BY CLASS_NAME ORDER BY TOTAL_UNITS DESC;
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).
SELECT * FROM ORDERS WHERE ORDER_DATE > TO_DATE('03-AUG-2013','DD-MON-YYYY') AND ORDER_DATE < TO_DATE ('06-AUG-2013','DD-MON-YYYY');
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 ORDER_DATE, COUNT(ORDER_ID) AS ORDERS_PLACED FROM ORDERS GROUP BY ORDER_DATE ORDER BY ORDERS_PLACED DESC;
8. List the warehouse number and the average unit price for warehouses other than number 2.
SELECT WAREHOUSE_NO, AVG(UNIT_PRICE) FROM WAREHOUSE WHERE WAREHOUSE_NO <> 2 GROUP BY WAREHOUSE_NO;
9. List the stored part information for SG parts stored in warehouse 2 or 3
SELECT PART_ID, PART_NAME, WAREHOUSE_NO FROM WAREHOUSE WHERE WAREHOUSE_NO IN (2,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.
SELECT PART_NUMBER, PART_DESCRIPTION, CLASS_NAME, UNIT_ON_HAND FROM WAREHOUSE WHERE PART_DESCRIPTION LIKE 'd%' OR PART_DESCRIPTION LIKE 'D%' GROUP BY CLASS_NAME ASC, UNITS_ON_HAND DESC;
Please let me know in case of any clarifications required. Thanks!
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.