Write SQL code that could be used from these five tables below 6. List the full

Question

Write SQL code that could be used from these five tables below
6. List the full name of the sales representatives, order number, and total quoted price for each order (rename to TQP) handled by each sales representative. 7. List the part number and part description of parts with units on hand higher than the average units on hand across all parts. 8. List the full name and credit limit of customer who bought a Gas Grill. 9. List the warehouse number and total dollar amount of all items sold from that warehouse. Rename the calculated column TOTAL_VALUE. TOTAL_VALUE=SUM(NUMBER_ORDERED*QUOTED_PRICE). 10. List the part description of the part with the most units on hand.

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

Explanation / Answer

If you have doubts or small errors, please give me a comment...

-- 6. List the full name of the sales representatives, order number, and total quoted price for each order (rename to TQP) handled by each sales representative.

SELECT SRFIRST, SRLAST, ORDER_NUMBER, SUM(QUOTED_PRICE)

FROM ((SELES_REP NATURAL JOIN CUSTOMER) NATURAL JOIN ORDERS) NATURAL JOIN ORDER_LINE

GROUP BY SRFIRST, SRLAST, ORDER_NUMBER;

-- 7. List the part number and part description of parts with units on hand higher than the average units on hand across all parts.

SELECT PART_NUMBER, PART_DESCRIPTION

FROM PART

WHERE UNITS_ON_HAND > (

SELECT AVG(UNITS_ON_HAND)

FROM PART

);

-- List the full name and credit limit of customer who bought a Gas Grill.

SELECT CFIRST, CLAST

FROM (((CUSTOMER NATURAL JOIN ORDERS) NATURAL JOIN ORDER_LINE) NATURAL JOIN PART)

WHERE PART_DESCRIPTION = 'Gas Grill';

-- 9) List the warehouse number and total dollar amount of all items sold from that warehouse.

-- Rename the calculated column TOTAL_VALUE.

-- TOTAL_VALUE=SUM(NUMBER_ORDERED*QUOTED_PRICE).

SELECT WAREHOUSE_NUMBER, (NUMBER_ORDRED * QUOTED_PRICE) AS TOTAL_VALUE

FROM PART NATURAL JOIN ORDER_LINE

GROUP BY WAREHOUSE_NUMBER;

-- 10. List the part description of the part with the most units on hand.

SELECT PART_DESCRIPTION

FROM PART

WHERE UNITS_ON_HAND = (

SELECT MAX(UNITS_ON_HAND)

FROM PART

);

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

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