Need help with this SQL question thanks. CUSTOMER_T CustomerID (PK) CustomerName

Question

Need help with this SQL question thanks.

CUSTOMER_T

CustomerID (PK)

CustomerName

CustomerStreet

CustomerCity

CustomerState

CustomerZip

CreditLimit

SalesRepID (FK) of EMPLOYEE_T

ORDER_T

OrderID (PK)

CustomerID (FK) of CUSTOMER_T

CustomerPONumber

OrderDate

DueDate

ShipDate

ORDER_LINE_T

OrderID composite (PK), (FK) of ORDER_T

ProductID composite (PK), (FK) of PRODUCT_T

OrderQuantity

PRODUCT_T

ProductID (PK)

ProductDescription

StockQuantity

FABRICATED_T

ProductID composite (PK), (FK) of PRODUCT_T.ProductID

PartID composite(PK), (FK) of PRODUCT_T.ProductID

PartQuantity quantity of PartID that goes into ProductID (example, 2 tires

In one bicycle)

PRICE_HISTORY_T

ProductID(FK)
StartPrice(PK)

EndDate

UnitPrice

PRODUCT_SUPPLIER_T

ProducID composite (PK), (FK) of PRODUCT_T

SupplierID composite (PK), (FK) of SUPPLIER_T

VendorPartID this is the ID the Vendor (i.e., Supplier) uses in their system), similar to

CustomerPONumber in the ORDER_t.

ProductCost

PurchasedQuantity

SUPPLIER_T

SupplierID (PK)

SupplierName

SupplierStreet

SupplierCity

SupplierState

SupplierZip

EMPLOYEE_T

EmployeeID (PK)

EmployeeName

EmployeeStreet

EmployeeCity

EmployeeState

EmployeeZip

ManagerID (FK) unary

EMPLOYEE_COURSE_T

EmployeeID combination (PK), (FK) of EMPLOYEE_T

CourseID combination (PK), (FK) of COURSE_T

CompletionDate

COURSE_T

CourseID (PK)

CourseDescription

SALES_COMMISSION_RATE_T

EmployeeID (PK), (FK) of EMPLOYEE_T

CommissionRate

A.Which sales representative has generated the most profit for the company? Hint: Provide the name of the sales rep and the total profit amount only. This one SQL statement will have subqueries. You must determine the profitability of a product by subtracting its cost from its selling price and multiplying the difference by the quantity sold. Regarding the cost, many products are supplied by more than one suppler, and each supplier may provide a different cost, so when this occurs, use the least expensive cost in measuring your profitability.

in this format

SELECT
FROM EMPLOYEE, SALESREP,CUSTOMER,ORDER,ORDERLINE, PRODUCT , PRICEHISTORY ,
PRODUCT SUPPLIER

(/* DETERMINE THE PROFIT FOR EACH PRODUCT

SELECT*
FROM PriceHistory PH,
)

(/* DETERMINE LOWERST COST OF EACH PRODUC T*/

SELECT ProductID, AS MinCost
FROM ProductSupplier
) AS MT

WHERE PH.ProductID = MT

GROUP BY SalesRep. EmployeeID TOP 1

Explanation / Answer

SELECT SalesRepID
FROM CUSTOMER_T(SELECT (Ph.StartPrice - ps.ProductCost)*ps.PurchasedQuantity*pt.PartQuantity )

FROM PriceHistory Ph, PRODUCT_SUPPLIER_T ps,PRODUCT_T pt
)SELECT ProductID, AS MinCost

FROM ProductSupplier
) AS MT

WHERE Ph.ProductID = MT

GROUP BY SalesRep. EmployeeID

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

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