Select query, Insert query, Delete Query, update query

SQL> select TO_CHAR(odate,'dd/mm/yy'), count(odate) from order2 group by odate;

TO_CHAR( COUNT(ODATE)
-------- ------------
10/03/99            4
10/04/99            2
10/05/99            2
--------------------------------------------------------------------------------------------
(27)

SQL> select onum,snum,amount from order34 where snum in (select snum from salesman where commission=
12);

     ONUM      SNUM    AMOUNT
--------- --------- ---------
     3002      1001    767.19
     3008      1001      4723
     3010      1001   9898.87
----------------------------------------------------------------------------------------------
(28)

SQL> SELECT CITY,MAX(RATING) FROM CUSTomer GROUP BY CITY;

CITY                 MAX(RATING)
-------------------- -----------
bombay                       300
london                       100
rome                         200
surat                        300
-----------------------------------------------------------------------------------------------
(29)
SQL> SELECT *FROM CUSTomer  ORDER BY RATING DESC;

     CNUM CNAME                CITY                    RATING      SNUM
--------- -------------------- -------------------- --------- ---------
     2004 govind               bombay                     300      1002
     2006 champak              surat                      300      1007
     2008 priyank              surat                      300      1004
     2002 gita                 rome                       200      1003
     2003 laxit                surat                      200      1002
     2001 hardik               london                     100      1001
     2007 pratik               rome                       100      1004

7 rows selected.
---------------------------------------------------------------------------------------------------
(30)
SQL> SELECT ODATE,SUM(AMOUNT)FROM ORDER34 GROUP BY ODATE;

ODATE     SUM(AMOUNT)
--------- -----------
10-MAR-99     7846.43
10-APR-99     2811.37
10-MAY-99      6108.7
10-JUN-99     9898.87
------------------------------------------------------------------------------------------------------------
(31)

SQL> select c.cname,s.sname from customer c, salesman s where c.snum=s.snum;

CNAME                SNAME
-------------------- ---------------
hardik               piyush
gita                 miti
laxit                niraj
govind               niraj
champak              laxman
pratik               rajesh
priyank              rajesh

7 rows selected.
----------------------------------------------------------------------------------------------
(32)

SQL> SELECT C.CNAME,S.SNAME,c.city FROM CUSTomer C, SALESMAN S WHERE C.CITY=S.City;

CNAME                SNAME           CITY              
-------------------- --------------- --------
govind               laxman          bombay            
hardik               piyush          london             
hardik               miti            london             
chandu               piyush          londan              
chandu               miti            londan
laxit                niraj           surat              
champak              niraj           surat              

--------------------------------------------------------------------------------------------
(33)

SQL> SELECT O.ONUM,C.CNAME,S.SNAME FROM ORDER34 O,CUSTomer C,SALESMAN S
  2  WHERE O.CNUM=C.CNUM AND O.SNUM=S.SNUM
  3  ;

     ONUM CNAME                SNAME
--------- -------------------- ---------------
     3001 pratik               laxman
     3002 hardik               piyush
     3003 pratik               rajesh
     3004 laxit                niraj
     3005 priyank              laxman
     3006 gita                 miti
     3007 govind               niraj
     3008 champak              piyush
     3009 govind               niraj
     3010 champak              piyush

10 rows selected.
-----------------------------------------------------------------------------------------
(34)

SQL> SELECT O.ONUM,C.CNAME,S.SNAME,s.city,c.city FROM ORDER34 O,CUSTomer C,SALESMAN S WHERE O.CNUM=C
.CNUM AND O.SNUM=S.SNUM AND C.CITY!=S.City;

     ONUM CNAME                SNAME           CITY            CITY
--------- -------------------- --------------- --------------- --------------------
     3001 pratik               laxman          bombay          rome
     3003 pratik               rajesh          baroda          rome
     3005 priyank              laxman          bombay          surat
     3006 gita                 miti            london          rome
     3007 govind               niraj           surat           bombay
     3008 champak              piyush          london          surat
     3009 govind               niraj           surat           bombay
     3010 champak              piyush          london          surat

8 rows selected.

-----------------------------------------------------------------------------------------------
(35)
SQL> select c.* from customer c, salesman s where s.snum=c.snum and c.cnum >12;

     CNUM CNAME                CITY                    RATING      SNUM
--------- -------------------- -------------------- --------- ---------
     2001 hardik               london                     100      1001
     2002 gita                 rome                       200      1003
     2003 laxit                surat                      200      1002
     2004 govind               bombay                     300      1002
     2006 champak              surat                      300      1007
     2007 pratik               rome                       100      1004
     2008 priyank              surat                      300      1004

7 rows selected.
--------------------------------------------------------------------------------------------
36)

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

37)
SQL> select distinct a.cname,b.rating from customer a,customer b where a.rating=b.rating;

CNAME         RATING
---------- ---------
chandu           100
hardik           100
pratik           100
gita             200
laxit            200
champak          300
govind           300

7 rows selected.