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 Write an SQL query to produce the average possibility of leads for each salesperson listing only those salesperson with average possibility more than 0.6 1. 2. Write an SQL query to produce the average possibility of leads with status “1" for each salesperson listing only those salesperson with average possibility more than 0.6 3. Write an SQL query to display the salesperson ID that has leads in city of 'San Diego'. It can be done with the use of subquery and without it. Write a query that will find the number of l' leads per customer for the states of California, Texas and Florida, using an IN subquery 4.

Explanation / Answer

create table SLPRS
(
SLPRS_ID int primary key,
SLPRS_FNAME nvarchar(max),
SLPRS_LNAME nvarchar(max),
SLPRS_TELEPHONE nvarchar(max),
SLPRS_HIRE_DATE date
)

create table CUSTOMER
(
CUST_ID int primary key,
CUST_TITLE nvarchar(max),
CUST_TELEPHONE nvarchar(max),
CUST_ADDRESS nvarchar(max),
CUST_CITY nvarchar(max),
CUST_STATE nvarchar(max),
CUST_PC nvarchar(max)
)

CREATE TABLE Lead
(
Lead_ID int primary key,
SLPRS_ID int foreign key REFERENCES SLPRS(SLPRS_ID),
Customer_ID int foreign key REFERENCES CUSTOMER(CUST_ID),
[Date] date,
Expected_Date date,
Amount nvarchar(max),
Possibility float,
[Status] nvarchar(max)

)

---------- 1)-----------------
SELECT avg(Possibility) as [AveragePossibility], S.SLPRS_FNAME , S.SLPRS_LNAME
FROM Lead L join SLPRS S on L.SLPRS_ID = S.SLPRS_ID
GROUP BY S.SLPRS_FNAME , S.SLPRS_LNAME
HAVING avg(Possibility) > 0.6;

---------- 2)-----------------
SELECT avg(Possibility) as [AveragePossibility], S.SLPRS_FNAME , S.SLPRS_LNAME
FROM Lead L join SLPRS S on L.SLPRS_ID = S.SLPRS_ID
where L.Status = 'l'
GROUP BY S.SLPRS_FNAME , S.SLPRS_LNAME
HAVING avg(Possibility) > 0.6;

---------- 3)-----------------
SELECT S.SLPRS_ID, S.SLPRS_FNAME , S.SLPRS_LNAME
FROM Lead L join SLPRS S on L.SLPRS_ID = S.SLPRS_ID
where L.Customer_ID in (select CUST_ID from CUSTOMER where CUST_CITY = 'San Diego')

-- -- or with join ---------

SELECT S.SLPRS_ID, S.SLPRS_FNAME , S.SLPRS_LNAME
FROM Lead L
join SLPRS S on L.SLPRS_ID = S.SLPRS_ID
join CUSTOMER C on L.Customer_ID = C.CUST_ID
where C.CUST_CITY = 'San Diego'

---------- 4)-----------------
Select Count(Lead_ID) , C.CUST_STATE
from Lead L
join CUSTOMER C on L.Customer_ID = C.CUST_ID
where C.CUST_STATE in ('Califiornia', 'Texas' , 'Florida') and L.Status = 'l'
Group by C.CUST_STATE

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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