SLPRS CUSTOMER 1-7 CUST-ID SLPRS ID SLPRS FNAME SLPRS LNAME SLPRS TELEPHONE SLPR

Question

SLPRS CUSTOMER 1-7 CUST-ID SLPRS ID SLPRS FNAME SLPRS LNAME SLPRS TELEPHONE SLPRS_HIRE_DATE CUST TITLE CUST TELEPHONE CUST ADDRESS CUST CITY CUST STATE CUST PC Lead Lead_ID SLPRS ID Customer ID Date Expected_date Amount Possibility Status 1. Write an SQL query to produce a list least with the average amount and the difference from the average amount, using a SELECT subquery 2. Write an SQL query to produce an efficiency rate per salesperson. That is the (total amount *average possibility) /100 3. Write an SQL query to produce a list of salespersons that sell to customer 1005 using a FROM subquery

Explanation / Answer

1. select max(amount),min(amount) from Lead,Customer
where Lead.Customer_ID=CUSTOMER.CUST_ID and CUST_State="California";

2. select CUST_STATE, max(amount),min(amount) from Lead,Customer
where Lead.Customer_ID=CUSTOMER.CUST_ID group by CUST_STATE;

3. Select DISTINCT(SLPRS.SLPR) from SLPRS NATURAL JOIN Lead
GROUP BY Lead.SLPRS_ID, Customer_ID having count(*)>2;

4. Select SLPRS.SLPRS_ID,AVERAGE(Possibility) from SLPRS NATURAL JOIN Lead where Status='I' group by Lead.SLPRS_ID;

Hi, Please find the solution below:

1. select (Select AVG(Amount) from Lead) as avg, amount- (select AVG(AMOUNT) from Lead) as difference from Lead;
2. Select SLPRS_ID, SUM(Amount)*AVG(Possibility)*0.01 as efficiency_rate from Lead group by SLPRS_ID;
3. Select DISTINCT(SLPRS_ID) from
(Select * from SLPRS_ID where Customere_ID=1005) as a;

Kindly let me know if you have any doubts!

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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