Employee_t Employee_ID EmployeeFirstName EmployeeLastName EmployeeJobTitle Manag

Question

Employee_t

Employee_ID

EmployeeFirstName

EmployeeLastName

EmployeeJobTitle

ManagerID

SALES_REPRESENTATIVE_t

EmployeeID(Fk)

ComissionRate

COURSE_t

CourseID(PK)

CourseDescription

EMPLOYEE_COURSE_t

EmployeeID(FK)

CourseID(FK)

CompletionDate

PRODUCT_t

ProductID(PK)

ProductDescription

StockQuantity

PRICE_HISTORY_t

ProductID(FK)

StartDate(PK)

DueDate

UnitPrice

FABRITCATED_T

PartID(PK)

ProductID(FK)

PartQuantity

Product_Supplier_t

ProductID(FK)

SupplierID(FK)

VendorPartID

ProductCost

PurchasedQuantity

Supplier_t

SupplierID(PK)

SUpplierName

CUSTOMER_t

CustomerID (PK)

CustomerName

CustomerStreet

CUstomerState

SalesRepID

ORDER_t

OrderID(PK)

CustomerPONumber

OrderDate

DueDate

ShipDate

CustomerID(PK)

ORDER_LINE_t

OrderID(FK)

ProductID(FK)

OrderQuantity

---------------

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.

--------------------------

sql 2014

Explanation / Answer

select e.EmployeeFirstName,profitAmount from Employee_t e inner join temp1 t1
(
select SalesRepID,sum(orderAmount) as profitAmount from (
select ct.SalesRepID,(olt.OrderQuantity*pht.UnitPrice) as orderAmount from CUSTOMER_t ct inner join
ORDER_t ot ON ot.CustomerID = ct.CustomerID
inner join ORDER_LINE_t on olt.OrderID = ot.OrderID
inner join PRICE_HISTORY_t pht on pht.ProductID = olt.ProductID
) temp
group by SalesRepID
)temp1
on t1.SalesRepID=e.Employee_ID
where profitAmount IN (select max(profitAmount) from temp1 group by SalesRepID)

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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