Question

Write an SQL statement that will:

List the salesmen that have sold autos with rebate amounts greater than $3500.

Use the following database schema to write your SQL statement.

AUTOS (AutoID, Make, Model, Year, MSRP, RebateID)
SALESMAN (SMID, SMFName, SMLName, MgrID)
MANAGER (MgrID, MgrFName, MgrLName)
REBATE (RebateID, Description, RebateAmount)
AUTOSALES (AutoID, CustomerID, SalesDate)
CUSTOMER (CustomerID, CustFName, CustLName, Email, SMID)

Answer 1

Solution

Select SALESMAN.SMID,SALESMAN.SMFName,SALESMAN.SMLName

From SALESMAN,CUSTOMER,AUTOSALES , AUTOS, REBATE

Where SALESMAN.SMID=CUSTOMER.SMID And CUSTOMER.CustomerID=AUTOSALES.CustomerId And AUTOSALES.AutoID= AUTOS.AutoID AND AUTOS.RebateID= REBATE.RebateID And REBATE.RebateAmount>3500

Explanation

We are doing the cross product of tuples from SALESMAN,CUSTOMER,AUTOSALES , AUTOS and REBATE tables and in the where condition we choose the correct tuples by equating respective attributes from different tables and selecting RebateAmount>3500. For these set of tuples we are printing the SMID,SalesmanFirstName and SalesmanLastName.