SLPRS CUSTOMER SLPRS ID SLPRS FNAME SLPRS LNAME SLPRS TELEPHONE SLPRS_HIRE _DATE

Question

SLPRS CUSTOMER 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 return the maximum and the minimum expected amount for customers based in the state of California 2. Write an SQL query to return the maximum and the minimum expected value for customers in each state 3. Write an SQL query to produce a list of salespersons with more than 2 leads per customer Write an SQL query to produce the average possibility of leads with status "I" for each salesperson 4.

Explanation / Answer

Hi Student,

Below are the MySQL queries, kindly let me know if you have any doubts!

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, Lead where SALES.SLPRS_ID=Leads.SLPRS_ID
GROUP BY Lead.SLPRS_ID, Customer_ID having count(*)>2;

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

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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