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

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

IN SQL 2014.. Please read the questions carefully. Thank You.

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

1A)Which sales representative has the highest dollar amount in total sales? Hint: Provide the name and

the total sales amount for that rep only. Do not include the information for other people, and do not list
details about orders.

1B)List all of the sales reps and their total commission amounts in order of highest sales commission
dollar amount first. Hint: Provide all of sales reps’ names and their total commission dollar amounts –
imagine that you were going to write one check to each sales rep to pay their total commission amount,
so be sure to produce that dollar amount. Extra Hint: Not all of the sales reps have sold any orders.

1C)Which sales representative has sold the most products? Hint: Name of the sales rep and a number
representing the total number of distinct products sold by that rep. This is the number of different
products they have sold, not the quantity of any products.

Explanation / Answer

1.a.

select SalesRepName from CUSTOMER_t c,Product_Supplier_t p where c.SalesRep=p.SupplierID(FK) where

productcost>=(select sum(ProductCost) from Product_Supplier_t)

1. select SalesRepID,sum(ComissionRate) from CUSTOMER_t, c SALES_REPRESENTATIVE_t s

where c.SalesRepID=s.EmpoyeeID

1C.SELECT distinct SUM(OrderID) FROM ORDER_LINE_t O,CUSTOMER_t C,Product_Supplier_t WHERE O.ProductID=p.SupplierID

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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