Program 2 (QUERIES)

Queries (Program 2)

1.  Count the customers with grades above Bengaluru’s average.

SELECT Grade, COUNT(customer_id)

FROM  CUSTOMER

GROUP BY Grade

HAVING Grade > (SELECT AVG(Grade)

                             FROM CUSTOMER

                             WHERE City='Bengaluru');

2.  Find the name and numbers of all salesman who had more than one customer.

SELECT S.Salesman_id,Name

FROM SALESMAN S

WHERE 1 < (SELECT COUNT(*)

                        FROM CUSTOMER C               

                        WHERE C.Salesman_id=S.Salesman_id);

OR

SELECT S.Salesman_id,Name

FROM SALESMAN S

WHERE (SELECT COUNT(*)

              FROM CUSTOMER C

            WHERE C.Salesman_id=S.Salesman_id)>1;

OR

SELECT s.salesman_id,name

FROM salesman s, customer c

WHERE s.salesman_id=c.salesman_id

GROUP BY s.salesman_id,name

HAVING count(customer_id)>1;

3.  List all the salesman and indicate those who have and don’t have customers in their cities (Use UNION operation).

SELECT S.Salesman_id,Name,Cust_name,S.City

FROM SALESMAN S,CUSTOMER C

WHERE S.City=C.City and S.Salesman_id=C.Salesman_id

UNION               

SELECT S.Salesman_id,Name,Cust_name,'no match'

FROM SALESMAN S,CUSTOMER C

WHERE S.city!=C.city and S.Salesman_id=C.Salesman_id

ORDER BY 1;

4.  Create  a  view  that  finds  the  salesman  who  has  the  customer  with  the  highest order of a day.

CREATE VIEW LUCKY_SALESMAN

AS SELECT Ord_date,S.Salesman_id,Name

FROM SALESMAN S,ORDERS O

WHERE S.Salesman_id=O.Salesman_id AND O.Purchase_amt=

(SELECT MAX(Purchase_amt)

                                                                                                  FROM ORDERS O2

                                                                                                WHERE O2.Ord_date=O.Ord_date);

                                                   OR    

CREATE VIEW LUCKY_SALESMAN

AS SELECT Ord_date,S.Salesman_id,Name,purchase_amt

FROM SALESMAN S,ORDERS O

WHERE S.Salesman_id=O.Salesman_id AND O.Purchase_amt=

(SELECT MAX(Purchase_amt)

                                                                                                  FROM ORDERS O2

                                                                                                WHERE O2.Ord_date=O.Ord_date);

5.  Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be deleted.

DELETE

FROM SALESMAN

WHERE Salesman_id=1000;