complex query, Select query, Insert query, Delete Query, update query

SQL> select s.sname,c.cname,c.city from salesman s,customer c where s.sname='niraj' and s.snum=c.snu
m;

SNAME           CNAME           CITY
--------------- --------------- ---------------
niraj           laxit           surat
niraj           govind          bombat

-----------------------------------------------------------------------------------------------------------------------------

39)
SQL> select a.cname,b.cname,c.snum,c.sname from salesman c,customer b,customer a where a.snum=b.snum
 and b.snum=c.snum and a.snum=b.snum and a.cnum<b.cnum;

CNAME           CNAME                SNUM SNAME
--------------- --------------- --------- ---------------
hardik          chandu               1001 piyush
laxit           govind               1002 niraj


-----------------------------------------------------------------------------------------------------------------------------

40)
SQL> select distinct a.sname,b.sname from salesman a,salesman b where a.city=b.city and a.snum<b.snu
m;

SNAME           SNAME
--------------- ---------------
piyush          miti

-----------------------------------------------------------------------------------------------------------------------------

41)SQL> SELECT CNAME,CITY FROM CUSTOMER WHERE RATING IN(SELECT RATING FROM CUSTOMER WHERE CNAME='Hardik
');

no rows selected

-----------------------------------------------------------------------------------------------------------------------------

42)SQL> SELECT * FROM ORDER2 WHERE SNUM IN(SELECT SNUM FROM SALESMAN WHERE SNAME='MITI');

     ONUM     AMOUT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3006   1713.12 10-APR-99      2002      1003

-----------------------------------------------------------------------------------------------------------------------------

43)SQL> SELECT * FROM ORDER2 WHERE SNUM IN(SELECT SNUM FROM SALESMAN WHERE CITY='BARODA');

     ONUM     AMOUT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3003    1900.1 10-MAR-99      2007      1004

-----------------------------------------------------------------------------------------------------------------------------

44)44)SQL> select * from order2 where snum in(select snum from customer where cname='hardik');

     ONUM    AMOUNT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3002       767 03-OCT-99      2001      1001
     3008      4723 05-OCT-99      2006      1001
     3010      9899 06-OCT-99      2006      1001

-----------------------------------------------------------------------------------------------------------------------------

45)SQL> select onum,amount,odate from order2 where odate>'10-apr-99';

     ONUM    AMOUNT ODATE
--------- --------- ---------
     3007        76 10-MAY-99
     3008      4723 10-MAY-99
     3009      1310 10-MAY-99
     3010      9899 10-JUN-99

-----------------------------------------------------------------------------------------------------------------------------

46)SQL> select * from order2 where snum in(select snum from customer where city='london');

     ONUM    AMOUNT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3002       767 03-OCT-99      2001      1001
     3008      4723 05-OCT-99      2006      1001
     3010      9899 06-OCT-99      2006      1001

-----------------------------------------------------------------------------------------------------------------------------

47)SQL> select sname,commission from salesman where snum in(select snum from customer where city='londo
n');

SNAME                COMMISSION
-------------------- --------------------
piyush               12%

-----------------------------------------------------------------------------------------------------------------------------

48)SQL> select * from customer where snum in(select snum from salesman where sname='niraj' and cnum>snum+1000);

     CNUM CNAME      CITY          RATING      SNUM
--------- ---------- ---------- --------- ---------
     2003 laxit      surat            200      1002
     2004 govind     bombay           300      1002

-----------------------------------------------------------------------------------------------------------------------------

49)SQL> select count(cnum) from customer where rating>(select avg(rating) from customer where city='sur
at');

COUNT(CNUM)
-----------
          2

-----------------------------------------------------------------------------------------------------------------------------

50)SQL> select * from pratik where cnum in(select cnum from customer where cname='champak');

     ONUM    AMOUNT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3008      4723 05-OCT-99      2006      1001
     3010      9899 06-OCT-99      2006      1001

-----------------------------------------------------------------------------------------------------------------------------

51)SQL> select b.cnum,a.cname,a.rating from customer a,order2 b where a.cnum=b.cnum and b.amount>(select avg(amount)
 from order2);

     CNUM CNAME         RATING
--------- ---------- ---------
     2003 laxit            200
     2006 champak          300
     2006 champak          300

-----------------------------------------------------------------------------------------------------------------------------

52)SQL> select c.cname,o.odate,amount from customer c,order3 o where c.cnum=o.cnum and odate='10-mar-99';

CNAME           ODATE        AMOUNT
--------------- --------- ---------
pratik          10-MAR-99        19
hardik          10-MAR-99       767
pratik          10-MAR-99      1900
laxit           10-MAR-99      5160

-----------------------------------------------------------------------------------------------------------------------------

53)

-----------------------------------------------------------------------------------------------------------------------------

54)SQL> SELECT ODATE,SUM(AMOUNT),MAX(AMOUNT) FROM ORDER2 GROUP BY ODATE HAVING SUM(AMOUNT)>MAX(AMOUNT)+
2000;

ODATE     SUM(AMOUNT) MAX(AMOUNT)
--------- ----------- -----------
10-MAR-99        7846        5160

-----------------------------------------------------------------------------------------------------------------------------

55)SQL> SELECT CNUM,CNAME,RATING FROM CUSTOMER WHERE RATING IN(SELECT MAX(RATING) FROM CUSTOMER GROUP BY
 CITY);

     CNUM CNAME              RATING
--------- --------------- ---------
     2001 hardik                100
     2005 chandu                100
     2007 pratik                100
     2002 gita                  200
     2003 laxit                 200
     2004 govind                300
     2006 champak               300

7 rows selected.

-----------------------------------------------------------------------------------------------------------------------------

56)SQL> SELECT S.SNUM,S.SNAME,S.CITY,C.CNUM,C.CNAME,C.CITY FROM SALESMAN S,CUSTOMER C WHERE S.CITY=C.CI
TY AND S.SNUM=C.SNUM;

     SNUM SNAME           CITY                 CNUM CNAME           CITY
--------- --------------- --------------- --------- --------------- ---------------
     1001 piyush          london               2001 hardik          london
     1002 niraj           surat                2003 laxit           surat
     1001 piyush          london               2005 chandu          london

-----------------------------------------------------------------------------------------------------------------------------

57) select s.snum,s.sname,s.city,c.rating from salesman s,customer c where s.snum=c.snum and c.rating>500;

no rows selected

-----------------------------------------------------------------------------------------------------------------------------

58) select s.*,c.* from salesman s,customer c where s.snum=c.snum and s.city=c.city;

     SNUM SNAME                CITY            COMMISSION      CNUM CNAME
--------- -------------------- --------------- ---------- --------- --------------------
CITY               RATING      SNUM
--------------- --------- ---------
     1001 piyush               london                  12      2001 hardik
london                100      1001

     1002 niraj                surat                   13      2003 laxit
surat                 200      1002

     1001 piyush               london                  12      2005 chandu
london                100      1001


-----------------------------------------------------------------------------------------------------------------------------

59)