Practical 5

1 SELECT * FROM orders_aj WHERE snum IN
(SELECT snum FROM salesman_aj WHERE city LIKE 'London');

ONUM    AMOUNT  ODATE   CNUM    SNUM
3011    9891.88 10-JUN-97   2006    1001
3008    4723    10-MAY-97   2006    1001
3003    767.19  10-MAR-97   2001    1001
3002    1900.1  10-MAR-97   2007    1004

2 SELECT sname,commision FROM salesman_aj WHERE snum IN
(SELECT snum FROM customer_aj WHERE city LIKE 'London');

SNAME   COMMISION
Piyush  12

3 SELECT cnum FROM customer_aj WHERE snum IN (SELECT snum FROM salesman_aj WHERE snum>1002);

CNUM
2007
2008
2002

4 SELECT COUNT(cnum) FROM customer_aj WHERE rating > (SELECT avg(rating) FROM customer_aj WHERE city LIKE 'Surat' );

COUNT(CNUM)
2

5 SELECT * FROM orders_aj WHERE cnum IN (SELECT cnum FROM customer_aj WHERE cname = 'Chirag' );

ONUM    AMOUNT  ODATE   CNUM    SNUM
3008    4723    10-MAY-97   2006    1001
3011    9891.88 10-JUN-97   2006    1001

6 SELECT cname,rating FROM customer_aj WHERE cnum IN
(SELECT cnum FROM orders_aj WHERE amount>
(SELECT avg (amount) FROM orders_aj));

CNAME   RATING
Chirag  100
Lalit   200

--select cname,rating,cnum from customer_aj where cnum in (select avg(amount) as average,cnum from orders_aj where amount>=avg);
-- error   too many values


7 SELECT SUM(amount),snum FROM orders_aj  GROUP BY snum HAVING amount > (SELECT MAX(amount) FROM orders_aj );
-- not a group by statement

8 SELECT * FROM orders_aj WHERE odate IN (SELECT odate FROM orders_aj WHERE odate='10-Mar-97');

ONUM    AMOUNT    ODATE     CNUM    SNUM
3006    1098.16 10-MAR-97   2008    1007
3005    5160.45 10-MAR-97   2003    1002
3002    1900.1  10-MAR-97   2007    1004
3003    767.19  10-MAR-97   2001    1001
3001    18.69   10-MAR-97   2008    1007

--  correct one
-- select cname from customer_aj where cnum in (select cnum from orders_aj where odate='10-Mar-97');

-- CNAME
-- Harsh
-- Lalit
-- Pratik
-- Chinmay

9 SELECT sname,snum FROM salesman_aj WHERE snum IN (SELECT snum FROM customer_aj GROUP BY snum HAVING COUNT(snum)>1);

SNAME   SNUM
Piyush  1001
Sejal   1002