Select query, Insert query, Delete Query, update query

SQL> SELECT *FROM SALESMAN WHERE City='LONDON' AND COMmISSION>'10%';

     SNUM SNAME      CITY       COMMIS
--------- ---------- ---------- ------
     1001 PIYUSH     LONDON     12%
     1003 MITI       LONDON     11%

--------------------------------------------------------------------------------------------
8)

SQL> SELECT *FROM CUSTOMER WHERE RATING<=100 AND CITY='ROME';

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2007 PRATIK          ROME                  100      1004
--------------------------------------------------------------------------------------------
9)

SQL>  select *from order2 where amount>1000 and not(snum = 1006 and odate='10/mar/99');

     ONUM    AMOUNT ODATE          CNUM      SNUM
--------- --------- --------- --------- ---------
     3003    1900.1 10-MAR-99      2007      1004
     3004   5160.45 10-MAR-99      2003      1002
     3005   1098.25 10-APR-99      2008      1007
     3006   1713.12 10-APR-99      2002      1003
     3008      4723 10-MAY-99      2006      1001
--------------------------------------------------------------------------------------------
10)

SQL> SELECT *FROM ORDER34 WHERE ODATE='10/mar/99' OR ODATE='10/apr/99'
  2  OR ODATE='10/jun/99';

     ONUM    AMOUNT ODATE          CNUM      SNUM ITEM_NAME
--------- --------- --------- --------- --------- --------------------
     3001     18.69 10-MAR-99      2007      1007
     3002    767.19 10-MAR-99      2001      1001
     3003    1900.1 10-MAR-99      2007      1004
     3004   5160.45 10-MAR-99      2003      1002
     3005   1098.25 10-APR-99      2008      1007
     3006   1713.12 10-APR-99      2002      1003
     3010   9898.87 10-JUN-99      2006      1001

7 rows selected.
--------------------------------------------------------------------------------------------
11)

SQL> SELECT *FROM CUSTOMER WHERE CNAME LIKE 'C%';

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2005 CHAND           LONDON                100      1001
     2006 CHAMPAK         SURAT                 300      1007
--------------------------------------------------------------------------------------------
12)

SQL> SELECT *FROM CUSTOMER WHERE CNAME BETWEEN 'A'AND 'G';

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2005 CHAND           LONDON                100      1001
     2006 CHAMPAK         SURAT                 300      1007
--------------------------------------------------------------------------------------------
13)

SQL>  SELECT *FROM ORDER2 WHERE AMOUNT=NULL OR AMOUNT=0;

no rows selected
--------------------------------------------------------------------------------------------
14)

SQL> SELECT SNUM,MAX(AMOUNT) FROM ORDER2 GROUP BY SNUM HAVING SNUM=1002 OR SNUM=1007;

     SNUM MAX(AMOUNT)
--------- -----------
     1002     5160.45
     1007     1098.25
--------------------------------------------------------------------------------------------
15)

SQL> SELECT COUNT(ODATE) FROM ORDER2 WHERE ODATE='10/MAR/99';

COUNT(ODATE)
------------
           4
--------------------------------------------------------------------------------------------
16)

SQL> SELECT SUM(AMOUNT) FROM ORDER2;

SUM(AMOUNT)
-----------
   15456.55
--------------------------------------------------------------------------------------------
17)

SQL> SELECT AVG(AMOUNT) FROM ORDER2;

AVG(AMOUNT)
-----------
  1932.0688
--------------------------------------------------------------------------------------------
18)

SQL> select count(distinct snum) from order2;

COUNT(DISTINCTSNUM)
-------------------
                  5
--------------------------------------------------------------------------------------------
19)

SQL>  SELECT ODATE,MAX(AMOUNT) FROM ORDER2 GROUP BY ODATE;

ODATE     MAX(AMOUNT)
--------- -----------
10-MAR-99     5160.45
10-APR-99     1713.12
10-MAY-99        4723
--------------------------------------------------------------------------------------------
20)

SQL> SELECT ODATE,MAX(AMOUNT) FROM ORDER2 GROUP BY ODATE HAVING ODATE='10/MAR/99';

ODATE     MAX(AMOUNT)
--------- -----------
10-MAR-99     5160.45
--------------------------------------------------------------------------------------------
21)

SQL> SELECT COUNT(CITY) FROM CUSTOMER WHERE CITY=NULL;

COUNT(CITY)
-----------
          0
--------------------------------------------------------------------------------------------
22)

SQL> SELECT CNUM,MIN(AMOUNT) FROM ORDER2 GROUP BY CNUM;

     CNUM MIN(AMOUNT)
--------- -----------
     2001      767.19
     2002     1713.12
     2003     5160.45
     2004       75.75
     2006        4723
     2007      1900.1
     2008       18.69

7 rows selected.
--------------------------------------------------------------------------------------------
23)

SQL> SELECT * FROM CUSTOMER WHERE CNAME LIKE 'G%' ORDER BY CNAME;

     CNUM CNAME           CITY               RATING      SNUM
--------- --------------- --------------- --------- ---------
     2002 GITA            ROME                  200      1003
     2004 GOVIND          BOMBAY                300      1002
--------------------------------------------------------------------------------------------
24)

SQL> SELECT COUNT(SNUM) FROM ORDER2 GROUP BY odate;

COUNT(SNUM)
-----------
          4
          2
          2
--------------------------------------------------------------------------------------------
25)

SQL> select snum, commission from salesman;

     SNUM COMMISSION
--------- ----------
     1001         12
     1002         13
     1003         11
     1004         15
     1005         10
     1006         10
     1007          9

7 rows selected.