Wednesday, September 29, 2010

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

60) select * from customer where rating>any (select rating from customer where city='rome');

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


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

61) SELECT * FROM order2 WHERE AMOUNT > ANY(SELECT AMOUNT FROM orders WHERE ODATE='6-OCT-97');

no rows selected

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

62)

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

63) SELECT C.* FROM CUSTomer C WHERE C.RATING > ALL(SELECT RATING FROM CUSTomer WHERE CITY='ROME');


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


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

64) SELECT * FROM CUSTOMER WHERE NOT RATING = ANY(SELECT RATING FROM CUSTOMER WHERE CITY='SURAT');

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

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