Oracle Queries With Example 3

select round('16-may-07','mon') from dual;

select round(to_date('16-may-07'),'yy')) from dual;

select round(to_date('16-may-07'),'yy')
 from dual;

ROUND(TO_
---------
01-JAN-07

select round(to_date('16-jun-07'),'yy')
 from dual;


select round(to_date('16-jul-07'),'yy')
 from dual;


select round(to_date('15-jul-07'),'mon')
 from dual;

select trunc(to_date('16-jul-09'),'yy')
 from dual;



SQL> select sysdate ,
to_char(sysdate,'dd month year') from dual;

SYSDATE   TO_CHAR(SYSDATE,'DDMONTHYEAR')
--------- -------------------------------------------------------
29-OCT-09 29 october   two thousand nine



 select sysdate ,
to_char(sysdate,'day') from dual;

SQL> select sysdate ,
  2  to_char(sysdate,'day') from dual;

SYSDATE   TO
--------- --
29-OCT-09 44



select to_char(to_date('05-may-05'),'Q') from dual;








select to_char(sysdate,'dd-mon-yy HH24:MI:SS ')
from dual;

T
-
2

select to_char(sysdate,'dd-mon-yy HH:MI:SS AM')
from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
29-oct-09 03:01:44 PM


select to_char(sysdate,'dd-mon-yy HH24:MI:SS ')
from dual;

TO_CHAR(SYSDATE,'DD
-------------------
29-oct-09 15:02:20




select lname,salary ,commission_pct ,coalesce(commission_pct,salary,dailywages) income from edetails;

FNAME           LNAME                    SALARY     INCOME
--------------- -------------------- ---------- ----------
sai             kiran                     23000      23000
jai             krishnan                  27000      27000
ashok           kumar                     27000         .1
Geethu          Mohan                     27000         .3
Arafat          Aboobacker                27000         .2
Seetha          Raman                     24000         .2
Urmilla         Unni                      28000         .2
Sajesh          Kumar                     29000         .2
Rajesh          C                         25000         .2
Manu            Jayan                     29000         .2
Santhosh        Jayan                     22000         .2

FNAME           LNAME                    SALARY     INCOME
--------------- -------------------- ---------- ----------
hari            prasad                                 500

12 rows selected.


select lname,salary ,commission_pct ,dailywages,
coalesce(commission_pct,salary,dailywages) income from
edetails;

LNAME                    SALARY COMMISSION_PCT     INCOME
-------------------- ---------- -------------- ----------
kiran                     23000                     23000
krishnan                  27000                     27000
kumar                     27000             .1         .1
Mohan                     27000             .3         .3
Aboobacker                27000             .2         .2
Raman                     24000             .2         .2
Unni                      28000             .2         .2
Kumar                     29000             .2         .2
C                         25000             .2         .2
Jayan                     29000             .2         .2
Jayan                     22000             .2         .2

LNAME                    SALARY COMMISSION_PCT     INCOME
-------------------- ---------- -------------- ----------
prasad                                                500

12 rows selected.


SQL> select lname,salary ,commission_pct ,dailywages,
  2  coalesce(commission_pct,salary,dailywages) income from
  3  edetails;

LNAME                    SALARY COMMISSION_PCT DAILYWAGES     INCOME
-------------------- ---------- -------------- ---------- ----------
kiran                     23000                                23000
krishnan                  27000                                27000
kumar                     27000             .1                    .1
Mohan                     27000             .3                    .3
Aboobacker                27000             .2                    .2
Raman                     24000             .2                    .2
Unni                      28000             .2                    .2
Kumar                     29000             .2                    .2
C                         25000             .2                    .2
Jayan                     29000             .2                    .2
Jayan                     22000             .2                    .2

LNAME                    SALARY COMMISSION_PCT DAILYWAGES     INCOME
-------------------- ---------- -------------- ---------- ----------
prasad                                                500        500

12 rows selected.






  select deptid,salary from edetails;

DEPT     SALARY
---- ----------
D01       23000
D01       27000
D02       27000
D02       27000
D03       27000
D01       24000
D01       28000
D02       29000
D02       25000
D03       29000
D03       22000

select deptid,sum(salary) "departwise total" from edetails group by deptid;


select deptid,sum(salary) "departwise total" from edetails
group by deptid;

DEPT departwise total
---- ----------------

D01            102000
D03             78000
D02            108000

select deptid,sum(salary) "departwise total" from edetails
 group by deptid
 having sum(salary) > 100000;



  SQL> select rownum,eid, fname from edetails;

    ROWNUM EID  FNAME
---------- ---- ---------------
         1 e1   sai
         2 e2   jai
         3 e3   ashok
         4 e4   Geethu
         5 e5   Arafat
         6 e6   Seetha
         7 e7   Urmilla
         8 e9   Sajesh
         9 e8   Rajesh
        10 e10  Manu
        11 e11  Santhosh

    ROWNUM EID  FNAME
---------- ---- ---------------
        12 e12  hari

12 rows selected.


  SQL> select * from edetails where rownum <  8;

EID  FNAME           LNAME                HIREDATE      SALARY JOB_ID   DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1   sai             kiran                23-FEB-09      23000 IT_PROG  D01
e2   jai             krishnan             24-APR-09      27000 ST_CLERK D01
e3   ashok           kumar                21-APR-09      27000 SAEXE    D02              .1
e4   Geethu          Mohan                14-APR-09      27000 SA_MAN   D02              .3
e5   Arafat          Aboobacker           21-APR-09      27000 SA_REP   D03              .2
e6   Seetha          Raman                21-APR-09      24000 SA_REP   D01              .2
e7   Urmilla         Unni                 21-APR-09      28000 SA_REP   D01              .2


select rowid,rownum,eid,fname from edetails;

ROWID                  ROWNUM EID  FNAME
------------------ ---------- ---- ---------------
AAAM5RAABAAAPCaAAA          1 e1   sai
AAAM5RAABAAAPCaAAB          2 e2   jai
AAAM5RAABAAAPCaAAC          3 e3   ashok
AAAM5RAABAAAPCaAAD          4 e4   Geethu
AAAM5RAABAAAPCaAAE          5 e5   Arafat
AAAM5RAABAAAPCaAAF          6 e6   Seetha
AAAM5RAABAAAPCaAAG          7 e7   Urmilla
AAAM5RAABAAAPCaAAH          8 e9   Sajesh
AAAM5RAABAAAPCaAAI          9 e8   Rajesh
AAAM5RAABAAAPCaAAJ         10 e10  Manu
AAAM5RAABAAAPCaAAK         11 e11  Santhosh

ROWID                  ROWNUM EID  FNAME
------------------ ---------- ---- ---------------
AAAM5RAABAAAPCaAAL         12 e12  hari





    select sysdate , sysdate +4,sysdate -10 from dual;

SYSDATE   SYSDATE+4 SYSDATE-1
--------- --------- ---------
29-OCT-09 02-NOV-09 19-OCT-09

Oracle Queries With Example 2

create table transactiondemo
(
  col1 number(2),
  col2 char(4)
);

insert into transactiondemo values
  (1,'a');

insert into transactiondemo values
  (2,'b');

select * from transactiondemo;


      COL1 COL2
---------- ----
         1 a
         2 b

rollback;






Rollback complete.


select * from transactiondemo;
no rows selected


insert into transactiondemo values
  (1,'a');

SQL> savepoint save1;

Savepoint created.

insert into transactiondemo values
  (2,'b');

SQL> select * from transactiondemo;

      COL1 COL2
---------- ----
         1 a
         2 b


rollback to save1;

select * from transactiondemo;

      COL1 COL2
---------- ----
         1 a


SQL> rename transactiondemo to tdemo;

Table renamed.

SQL> select * from tdemo;

      COL1 COL2
---------- ----
         1 a

SQL> select * from transactiondemo;
select * from transactiondemo
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from cedetails;

EID  FNAME           LNAME                HIREDATE      SALARY JOB_ID   DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1   sai             kiran                23-FEB-09      23000 IT_PROG  D01
e2   jai             krishnan             24-APR-09      27000 ST_CLERK D01
e3   ashok           kumar                21-APR-09      27000 SAEXE    D02              .1
e4   Geethu          Mohan                14-APR-09      27000 SA_MAN   D02              .3
e5   Arafat          Aboobacker           21-APR-09      27000 SA_REP   D03              .2

SQL> alter table cedetails set unused (hiredate);

Table altered.

  SQL> select * from cedetails;

EID  FNAME           LNAME                    SALARY JOB_ID   DEPT COMMISSION_PCT DAILYWAGES
---- --------------- -------------------- ---------- -------- ---- -------------- ----------
e1   sai             kiran                     23000 IT_PROG  D01
e2   jai             krishnan                  27000 ST_CLERK D01
e3   ashok           kumar                     27000 SAEXE    D02              .1
e4   Geethu          Mohan                     27000 SA_MAN   D02              .3
e5   Arafat          Aboobacker                27000 SA_REP   D03              .2

SQL> alter table cedetails set unused (job_id);

Table altered.

SQL> select * from cedetails;

EID  FNAME           LNAME                    SALARY DEPT COMMISSION_PCT DAILYWAGES
---- --------------- -------------------- ---------- ---- -------------- ----------
e1   sai             kiran                     23000 D01
e2   jai             krishnan                  27000 D01
e3   ashok           kumar                     27000 D02              .1
e4   Geethu          Mohan                     27000 D02              .3
e5   Arafat          Aboobacker                27000 D03              .2

SQL> describe cedetails;
 Name                                                                                                
 ---------------------------------------------------------------------------------------------------
 EID                                                                                                  
 FNAME                                                                                                
 LNAME                                                                                                
 SALARY                                                                                              
 DEPTID                                                                                              
 COMMISSION_PCT                                                                                      
 DAILYWAGES                                                                                          

SQL>

  SQL> create table copyedetails as
  select * from edetails where  0 >1;

Table created.

SQL> select * from copyedetails;

no rows selected

  insert into copyedetails select * from edetails;


SQL> insert into copyedetails select * from edetails;

5 rows created.

SQL> select * from copyedetails;

EID  FNAME           LNAME                HIREDATE      SALARY JOB_ID   DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1   sai             kiran                23-FEB-09      23000 IT_PROG  D01
e2   jai             krishnan             24-APR-09      27000 ST_CLERK D01
e3   ashok           kumar                21-APR-09      27000 SAEXE    D02              .1
e4   Geethu          Mohan                14-APR-09      27000 SA_MAN   D02              .3
e5   Arafat          Aboobacker           21-APR-09      27000 SA_REP   D03              .2

SQL>


SQL> select fname ,salary from edetails e where salary >= (select avg(salary) from edetails ed where
 ed.deptid=e.deptid);

FNAME               SALARY
--------------- ----------
jai                  27000
ashok                27000
Geethu               27000
Arafat               27000
Urmilla              28000
Sajesh               29000
Manu                 29000

Oracle Queries with Example

SQL> select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e3   kiran           45000
e4   karthick        44444

SQL> select ename || ' 1 month salary = ' || salary from employdet;

ENAME||'1MONTHSALARY='||SALARY
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444

SQL> select ename || ' 1 month salary = ' || salary as "Monthly salary " from employdet;

Monthly salary
--------------------------------------------------------------------
sam 1 month salary = 35000
hari 1 month salary = 33000
kiran 1 month salary = 45000
karthick 1 month salary = 44444

SQL>


SQL> select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e3   kiran           45000
e4   karthick        44444



SQL>  select * from employdet where ename like 'k%';

EID  ENAME          SALARY
---- ---------- ----------
e3   kiran           45000
e4   karthick        44444

SQL> select * from employdet where ename like '_a%';

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e4   karthick        44444




 SQL> select * from employdet where ename like '__r%';

EID  ENAME          SALARY
---- ---------- ----------
e2   hari            33000
e3   kiran           45000
e4   karthick        44444

select 3+5*2 from dual;

3+5*2
-----
   13




SQL> select ename,salary,salary *1.4 "bonus" from employdet;

ENAME          SALARY      bonus
---------- ---------- ----------
sam             35000      49000
hari            33000      46200
kiran           45000      63000
karthick        44444    62221.6


select concat('Hello','World') combinestring from dual;



COMBINESTR
----------
HelloWorld


select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e3   kiran           45000
e4   karthick        44444

create table cemp as select * from employdet where 0>1





MERGE INTO cemp c
    USING employdet e
    ON (c.eId = e.eId)
    WHEN MATCHED THEN
    UPDATE SET c.salary = e.salary,c.ename=e.ename
    WHEN NOT MATCHED THEN
    INSERT (c.eId,c.ename,c.salary)
   VALUES(e.eId,e.ename,e.salary);


SQL> MERGE INTO cemp c
  2      USING employdet e
  3      ON (c.eId = e.eId)
  4      WHEN MATCHED THEN
  5      UPDATE SET c.salary = e.salary,c.ename
  6      WHEN NOT MATCHED THEN
  7      INSERT (c.eId,c.ename,c.salary)
  8     VALUES(e.eId,e.ename,e.salary);

4 rows merged.






SQL> select * from cemp;

no rows selected

SQL> select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e3   kiran           45000
e4   karthick        44444



SQL> select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            33000
e3   kiran           45000
e4   karthick        44444

SQL> update employdet set salary = 55555 where eid='e2';

1 row updated.

SQL> update employdet set ename='praksh' where eid='e3';

1 row updated.

SQL> select * from employdet;

EID  ENAME          SALARY
---- ---------- ----------
e1   sam             35000
e2   hari            55555
e3   praksh          45000
e4   karthick        44444

SQL> SQL> select * from cemp;

EID  ENAME          SALARY
---- ---------- ----------
e2   hari            33000
e4   karthick        44444
e3   kiran           45000
e1   sam             35000





SQL> select * from cemp;

EID  ENAME          SALARY
---- ---------- ----------
e2   hari            55555
e4   karthick        44444
e3   praksh          45000
e1   sam             35000



SQL> select next_day('26-oct-09','wednesday') from dual;

NEXT_DAY(
---------
28-OCT-09




SQL> select next_day('26-oct-09','friday') from dual;

NEXT_DAY(
---------
30-OCT-09


create table edetails
(
 eid char(4),
 fname varchar(15),
 lname varchar2(20),
 hiredate date,
 salary number(6),
 job_id char(8),
 deptid char(4),
 commission_pct number(4,2),
 dailywages number (4)
);
insert into edetails values('e1','sai','kiran','23-feb-09',
  23000,'IT_PROG','D01',null,null);

insert into edetails values('e2','jai','krishnan','24-apr-09'
,27000,'ST_CLERK','D01',null,null);

insert into edetails values('e3','ashok','kumar','21-apr-09'
,27000,'SAEXE','D02',.10,null);

insert into edetails values('e4','Geethu','Mohan','14-apr-09',
27000,'SA_MAN','D02',.30,null);

insert into edetails values('e5','Arafat','Aboobacker',
'21-apr-09',27000,'SA_REP','D03',.20,null);

insert into edetails values('e6','Seetha','Raman',
'21-apr-09',24000,'SA_REP','D01',.20,null);

insert into edetails values('e7','Urmilla','Unni',
'21-apr-09',28000,'SA_REP','D01',.20,null);

insert into edetails values('e8','Rajesh','C',
'21-apr-09',25000,'SA_REP','D02',.20,null);

insert into edetails values('e9','Sajesh','Kumar',
'21-apr-09',29000,'SA_REP','D02',.20,null);

insert into edetails values('e10','Manu','Jayan',
'21-apr-09',29000,'SA_REP','D03',.20,null);

insert into edetails values('e11','Santhosh','Jayan',
'21-apr-09',22000,'SA_REP','D03',.20,null);









SQL> select * from edetails;

EID  FNAME           LNAME                HIREDATE      SALARY JOB_ID   DEPT COMMISSION_PCT DAILYWAG
---- --------------- -------------------- --------- ---------- -------- ---- -------------- --------
e1   sai             kiran                23-FEB-09      23000 IT_PROG  D01
e2   jai             krishnan             14-APR-09      27000 SY_ANA   D01
e3   ashok           kumar                14-APR-09      27000 SAEXE    D02              .1
e4   Geethu          Mohan                14-APR-09      27000 SA_MAN   D02              .3
e5   Arafat          Aboobacker           14-APR-09      27000 SA_REP   D03              .2



SQL> select fname,salary , commission_pct from edetails;

FNAME               SALARY COMMISSION_PCT
--------------- ---------- --------------
sai                  23000
jai                  27000
ashok                27000             .1
Geethu               27000             .3
Arafat               27000             .2

SQL> select fname ,salary ,
nvl(to_char(commission_pct),' no commisn')
 commsiondet from edetails;

FNAME               SALARY COMMSIONDET
--------------- ---------- ----------------------------------------
sai                  23000  no commisn
jai                  27000  no commisn
ashok                27000 .1
Geethu               27000 .3
Arafat               27000 .2



 select fname ,salary ,
   nvl(commission_pct,0) commsiondet from edetails;

FNAME               SALARY COMMSIONDET
--------------- ---------- -----------
sai                  23000           0
jai                  27000           0
ashok                27000          .1
Geethu               27000          .3
Arafat               27000          .2


SQL> select lname,salary,commission_pct,
nvl2(commission_pct,salary+salary*commission_pct,salary)
 income from edetails;


LNAME                    SALARY COMMISSION_PCT     INCOME
-------------------- ---------- -------------- ----------
kiran                     23000                     23000
krishnan                  27000                     27000
kumar                     27000             .1      29700
Mohan                     27000             .3      35100
Aboobacker                27000             .2      32400



 SQL> select lname ,job_id from edetails
  2  ;

LNAME                JOB_ID
-------------------- --------
kiran                IT_PROG
krishnan             SY_ANA
kumar                SAEXE
Mohan                SA_MAN
Aboobacker           SA_REP

SQL> select lname , job_id from edetails
 where job_id like 'SA_%';

LNAME                JOB_ID
-------------------- --------
kumar                SAEXE
Mohan                SA_MAN
Aboobacker           SA_REP


SQL> select lname , job_id from edetails
 where job_id like 'SA\_%' escape '\';

LNAME                JOB_ID
-------------------- --------
Mohan                SA_MAN
Aboobacker           SA_REP

SQL> select lname , job_id from edetails
 where job_id like 'SA$_%' escape '$';

LNAME                JOB_ID
-------------------- --------
Mohan                SA_MAN
Aboobacker           SA_REP


select fname,lname , length(fname) "exp1" ,
 length(lname) "exp2" from edetails;


FNAME           LNAME                      exp1       exp2
--------------- -------------------- ---------- ----------
sai             kiran                         3          5
jai             krishnan                      3          8
ashok           kumar                         5          5
Geethu          Mohan                         6          5
Arafat          Aboobacker                    6         10

// nullif compares two expressions and returns null
 if both expressions are equal other wise returns
first expression

select fname,lname , length(fname) "exp1" ,
 length(lname) "exp2",
  nullif(length(fname),length(lname)) "result" from edetails;


FNAME           LNAME                      exp1       exp2     result
--------------- -------------------- ---------- ---------- ----------
sai             kiran                         3          5          3
jai             krishnan                      3          8          3
ashok           kumar                         5          5
Geethu          Mohan                         6          5          6
Arafat          Aboobacker                    6         10          6

case expression

 select lname,job_id,salary,
 case job_id when 'IT_PROG' then 1.20*salary
             when 'ST_CLERK' then 1.15*salary
             when 'SA_REP' then 1.10*salary
             else
              salary
              end
              revisedsalary
      from edetails;

LNAME                JOB_ID       SALARY REVISEDSALARY
-------------------- -------- ---------- -------------
kiran                IT_PROG       23000         27600
krishnan             ST_CLERK      27000         31050
kumar                SAEXE         27000         27000
Mohan                SA_MAN        27000         27000
Aboobacker           SA_REP        27000         29700







SQL> select fname,hiredate,
to_char(hiredate,'mm/yy') mmyy from edetails;

FNAME           HIREDATE  MMYY
--------------- --------- -----
sai             23-FEB-09 02/09
jai             14-APR-09 04/09
ashok           14-APR-09 04/09
Geethu          14-APR-09 04/09
Arafat          14-APR-09 04/09


SQL> select fname,hiredate,
   to_char(hiredate,'mon/yyyy') monthhired
 from edetails;

FNAME           HIREDATE  MONTHHIR
--------------- --------- --------
sai             23-FEB-09 feb/2009
jai             14-APR-09 apr/2009
ashok           14-APR-09 apr/2009
Geethu          14-APR-09 apr/2009
Arafat          14-APR-09 apr/2009


select fname,hiredate,
to_char(hiredate,'mon/year') year from edetails;

FNAME           HIREDATE  YEAR
--------------- --------- ----------------------------------------------
sai             23-FEB-09 feb/two thousand nine
jai             14-APR-09 apr/two thousand nine
ashok           14-APR-09 apr/two thousand nine
Geethu          14-APR-09 apr/two thousand nine
Arafat          14-APR-09 apr/two thousand nine


select fname,hiredate,
to_char(hiredate,'DD Month YYYY')
 as hireddate from edetails;
FNAME           HIREDATE  HIREDDATE
--------------- --------- -----------------
sai             23-FEB-09 23 February  2009
jai             14-APR-09 14 April     2009
ashok           14-APR-09 14 April     2009
Geethu          14-APR-09 14 April     2009
Arafat          14-APR-09 14 April     2009

select fname,hiredate,
to_char(hiredate,'fmDD Month YYYY')
 as hireddate from edetails;

FNAME           HIREDATE  HIREDDATE
--------------- --------- -----------------
sai             23-FEB-09 23 February 2009
jai             14-APR-09 14 April 2009
ashok           14-APR-09 14 April 2009
Geethu          14-APR-09 14 April 2009
Arafat          14-APR-09 14 April 2009

select lname,hiredate,
to_char(hiredate,'fmDdspth "of" Month yyyy') hiredate from
edetails;


LNAME                HIREDATE  HIREDATE
-------------------- --------- -------------------------------
kiran                23-FEB-09 Twenty-Third of February 2009
krishnan             14-APR-09 Fourteenth of April 2009
kumar                14-APR-09 Fourteenth of April 2009
Mohan                14-APR-09 Fourteenth of April 2009
Aboobacker           14-APR-09 Fourteenth of April 2009


select lname,hiredate,
to_char(hiredate,'fmDdth "of" Month yyyy') hiredate from
edetails;

LNAME                HIREDATE  HIREDATE
-------------------- --------- ----------------------
kiran                23-FEB-09 23rd of February 2009
krishnan             14-APR-09 14th of April 2009
kumar                14-APR-09 14th of April 2009
Mohan                14-APR-09 14th of April 2009
Aboobacker           14-APR-09 14th of April 2009



 create table timeexample1
(loanduration interval year(3) to month);



 insert into timeexample1 values(interval '120' month (3));

1 row created.

SQL> select * from timeexample1;

LOANDURATION
-------------------------------------------------------------------------
+010-00

SQL> insert into timeexample1 values(interval '155' month(3));

1 row created.

SQL> select * from timeexample1;

LOANDURATION
-------------------------------------------------------------------------
+010-00
+012-11





insert into timeexample1 values(interval '5-6' year to month);
insert into timeexample1 values(interval '13-11' year to month);

SQL> select * from timeexample1;

LOANDURATION
---------------------------------------------------------------------------
+010-00
+012-11
+005-06
+013-11

7 rows selected.

SQL> select to_char(sysdate+loanduration,'dd-mon-yyyy') from timeexample1;

TO_CHAR(SYS
-----------
26-oct-2019
26-sep-2022
26-apr-2015
26-sep-2023

7 rows selected.





SQL> create table timeexample2
  2  (
  3     dayduration interval day(3) to second);

Table created.

SQL> insert into timeexample2 values(interval '184' day(3));

1 row created.

SQL> select * from timeexample2;

DAYDURATION
----------------------------------------------------------------------
+184 00:00:00.000000

SQL> select sysdate +dayduration "Half year" from timeexample2;

Half year
---------
28-APR-10

Practical DBMS learning with example Queries

Practical Answer 1.

1)

SQL> create table dept1(deptno number primary key,dname varchar(6) not null,loc varchar(8));

Table created.

SQL> describe dept1;

 Name                                      Null?    Type

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

 DEPTNO                                    NOT NULL NUMBER

 DNAME                                     NOT NULL VARCHAR2(6)

 LOC                                                VARCHAR2(8)

SQL>  create table emp1(empno number primary key,ename varchar(8) not null,deptno number,

  2   job varchar(8)  check( job=UPPER(job)),hiredate date default sysdate,foreign key(deptno)

  3   references  dept1 (deptno));

Table created.

SQL> describe emp1

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER

 ENAME                                     NOT NULL VARCHAR2(8)

 DEPTNO                                             NUMBER

 JOB                                                VARCHAR2(8)

 HIREDATE                                           DATE

 2)

SQL> alter table emp1 add(sal number(8,2) check (sal>0),mgr number,comm number(5,2));

Table altered.

SQL> describe emp1;

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER

 ENAME                                     NOT NULL VARCHAR2(8)

 DEPTNO                                             NUMBER

 JOB                                                VARCHAR2(8)

 HIREDATE                                           DATE

 SAL                                                NUMBER(8,2)

 MGR                                                NUMBER

 COMM                                               NUMBER(5,2)

SQL> alter table dept1 modify(dname varchar(14),loc varchar(13));

Table altered.

SQL> insert into dept1  select * from dept;

4 rows created.

SQL> select * from dept1;

    DEPTNO DNAME          LOC

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

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

3)

SQL>  insert into emp1 values(7499,'ALLEN',30,'SALESMAN','20-feb-81',1600,7608,300);

1 row created.

Expriment no 2

1)

SQL> select * from tab

  2  ;

TNAME                          TABTYPE  CLUSTERID

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

BONUS                          TABLE

CLIENTMAST                     TABLE

CLIENTMASTER                   TABLE

DEPT                           TABLE

DEPT1                          TABLE

DEPTIT                         TABLE

EMP                            TABLE

EMP1                           TABLE

EMPIT                          TABLE

EMPLOYEE                       TABLE

LOGSTUD                        TABLE

TNAME                          TABTYPE  CLUSTERID

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

ORDERS                         TABLE

SALGRADE                       TABLE

STUD                           TABLE

SUPPLYMASTER                   TABLE

15 rows selected.

 2)

SQL> select dname from dept1;

DNAME

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

ACCOUNTING

RESEARCH

SALES

OPERATIONS

3)

SQL> select dname,loc from dept;

DNAME          LOC

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

ACCOUNTING     NEW YORK

RESEARCH       DALLAS

SALES          CHICAGO

OPERATIONS     BOSTON

4)

SQL> select job from emp;

JOB

---------

CLERK

SALESMAN

SALESMAN

MANAGER

SALESMAN

MANAGER

MANAGER

ANALYST

PRESIDENT

SALESMAN

CLERK

JOB

---------

CLERK

ANALYST

CLERK

14 rows selected.

5)

SQL> select * from emp where ename='smith';

no rows selected

SQL>  select * from emp where ename='SMITH';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7369 SMITH      CLERK           7902 17-DEC-80        800

        20

6)

SQL> SELECT ename,job from emp where deptno=20;

ENAME      JOB

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

SMITH      CLERK

JONES      MANAGER

SCOTT      ANALYST

ADAMS      CLERK

FORD       ANALYST

7)

SQL> select * from emp where deptno=30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300

        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500

        30

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400

        30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

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

    DEPTNO

----------

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850

        30

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0

        30

      7900 JAMES      CLERK           7698 03-DEC-81        950

        30

6 rows selected.

 8)

SQL> select empno,ename,job from emp where job='MANAGER';

     EMPNO ENAME      JOB

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

      7566 JONES      MANAGER

      7698 BLAKE      MANAGER

      7782 CLARK      MANAGER

 9)

SQL> select ename from emp where comm>sal;

ENAME

----------

MARTIN

10)

SQL> select ename,job,sal from emp where sal>2000;

ENAME      JOB              SAL

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

JONES      MANAGER         2975

BLAKE      MANAGER         2850

CLARK      MANAGER         2450

SCOTT      ANALYST         3000

KING       PRESIDENT       5000

FORD       ANALYST         3000

6 rows selected.

11)

SQL> select ename,sal as monthly,sal*12 as annual from emp;

ENAME         MONTHLY     ANNUAL

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

SMITH             800       9600

ALLEN            1600      19200

WARD             1250      15000

JONES            2975      35700

MARTIN           1250      15000

BLAKE            2850      34200

CLARK            2450      29400

SCOTT            3000      36000

KING             5000      60000

TURNER           1500      18000

ADAMS            1100      13200

ENAME         MONTHLY     ANNUAL

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

JAMES             950      11400

FORD             3000      36000

MILLER           1300      15600

14 rows selected.

12)

SQL> select ename,sal from emp where deptno=10;

ENAME             SAL

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

CLARK            2450

KING             5000

MILLER           1300

13)

SQL> select dname as department,deptno as department_no from dept;

DEPARTMENT     DEPARTMENT_NO

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

ACCOUNTING                10

RESEARCH                  20

SALES                     30

OPERATIONS                40

14)

SQL> select ename,mgr from emp where job='MANAGER';

ENAME             MGR

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

JONES            7839

BLAKE            7839

CLARK            7839

15)

SQL>  select empno,ename FROM EMP where job='ANALYST';

     EMPNO ENAME

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

      7788 SCOTT

      7902 FORD

Experiment No 3

1)

SQL> select ename from emp where empno>mgr;

ENAME

----------

SCOTT

TURNER

ADAMS

JAMES

FORD

MILLER

6 rows selected.

2)

SQL> select ename,job from emp where job='MANAGER' and deptno !=10;

ENAME      JOB

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

JONES      MANAGER

BLAKE      MANAGER

3)

SQL> select ename,job from emp where job not in('CLEARK','SALESMAN','ANALYST');

ENAME      JOB

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

SMITH      CLERK

JONES      MANAGER

BLAKE      MANAGER

CLARK      MANAGER

KING       PRESIDENT

ADAMS      CLERK

JAMES      CLERK

MILLER     CLERK

8 rows selected.

SQL> select ename from emp where deptno=20 and job!='clerk' and job!='salesman' and job!='analyst';

ENAME

----------

SMITH

JONES

SCOTT

ADAMS

FORD

6)

SQL> select ename,sal from emp where sal between 1000 and 2000;

ENAME             SAL

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

ALLEN            1600

WARD             1250

MARTIN           1250

TURNER           1500

ADAMS            1100

MILLER           1300

6 rows selected.

13)

SQL> select ename,job,comm from emp where comm is null;

ENAME      JOB             COMM

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

SMITH      CLERK

JONES      MANAGER

BLAKE      MANAGER

CLARK      MANAGER

SCOTT      ANALYST

KING       PRESIDENT

ADAMS      CLERK

JAMES      CLERK

FORD       ANALYST

MILLER     CLERK

10 rows selected.

14)

SQL> select ename,job,comm from emp where comm is not null

  2  ;

ENAME      JOB             COMM

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

ALLEN      SALESMAN         300

WARD       SALESMAN         500

MARTIN     SALESMAN        1400

TURNER     SALESMAN           0

15)

SQL> select ename,deptno from emp where deptno in(10,20);

ENAME          DEPTNO

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

SMITH              20

JONES              20

CLARK              10

SCOTT              20

KING               10

ADAMS              20

FORD               20

MILLER             10

8 rows selected.

16)

SQL> select ename,hiredate from emp where hiredate>'01-jan-81' and deptno=10;

ENAME      HIREDATE

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

CLARK      09-JUN-81

KING       17-NOV-81

MILLER     23-JAN-82

17)

SQL> select ename,deptno from emp where deptno !=10;

ENAME          DEPTNO

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

SMITH              20

ALLEN              30

WARD               30

JONES              20

MARTIN             30

BLAKE              30

SCOTT              20

TURNER             30

ADAMS              20

JAMES              30

FORD               20

11 rows selected.

Expriment no 4

1)

SQL> select count(ename) from emp;

COUNT(ENAME)

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

          14

2)

SQL>  select count(job) from emp where job='CLERK' and hiredate>'13-jan-81';

COUNT(JOB)

----------

         3

3)

SQL> select sum(sal)*12 as yearly_comp from emp where job='SALESMAN';

YEARLY_COMP

-----------

      67200

5)

SQL> select avg(sal) from emp;

  AVG(SAL)

----------

2073.21429

6)

SQL> select avg(sal)*12 as avg_yr_comp from emp where job like 'SALESMAN';

AVG_YR_COMP

-----------

      16800

7)

SQL> select min(sal) from emp;

  MIN(SAL)

----------

       800

 8)

SQL> select ename from emp order by ename;

ENAME

----------

ADAMS

ALLEN

BLAKE

CLARK

FORD

JAMES

JONES

KING

MARTIN

MILLER

SCOTT

ENAME

----------

SMITH

TURNER

WARD

14 rows selected.

9)

SQL> select max(sal) from emp;

  MAX(SAL)

----------

      5000

Experiment No 6

1)

SQL>  create table clientmast(client_no varchar(4) primary key check(client_no like 'c%')

  2  ,name varchar(4) not null,address1 varchar(8),address2 varchar(8),city varchar(8)

  3  ,state varchar(8),pin_code number,remark varchar(5),bal_due number(7,2)

  4  ,check(name=upper(name)));

Table created.

SQL> describe clientmast;

 Name                                      Null?    Type

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

 CLIENT_NO                                 NOT NULL VARCHAR2(4)

 NAME                                      NOT NULL VARCHAR2(4)

 ADDRESS1                                           VARCHAR2(8)

 ADDRESS2                                           VARCHAR2(8)

 CITY                                               VARCHAR2(8)

 STATE                                              VARCHAR2(8)

 PIN_CODE                                           NUMBER

 REMARK                                             VARCHAR2(5)

 BAL_DUE                                            NUMBER(7,2)

4)

SQL> insert into clientmast values('c01','ABC1','nagpada1','byculla1','mumbai','maharas',01,'good1',

1000);

1 row created.

 1* insert into clientmast values('c02','ABC2','nagpada2','byculla2','mumbai','maharas',02,'good2',

5)

SQL> SELECT * FROM CLIENTMAST;

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

c01  ABC1 nagpada1 byculla1 mumbai   maharas           1 good1       1000

c02  ABC2 nagpada2 byculla2 mumbai   maharas           2 good2       2000

c03  ABC3 nagpada3 byculla3 mumbai   maharas           3 good3       3000

c04  ABC4 nagpada4 byculla4 mumbai   maharas           4 good4       4000

c05  ABC5 nagpada5 byculla5 mumbai   maharas           5 good5       5000

6)

SQL> ALTER TABLE CLIENTMAST ADD(AGE NUMBER(2,2));

Table altered.

7)

SQL>  UPDATE CLIENTMAST SET AGE=35;

5 rows updated.

SQL>  UPDATE CLIENTMAST SET AGE=65 WHERE NAME='ABC5';

1 row updated.

SQL> SELECT * FROM CLIENTMAST;

 

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

       AGE

----------

c01  ABC1 nagpada1 byculla1 mumbai   maharas           1 good1       1000

        35

c02  ABC2 nagpada2 byculla2 mumbai   maharas           2 good2       2000

        35

c03  ABC3 nagpada3 byculla3 mumbai   maharas           3 good3       3000

        35

CLIE NAME ADDRESS1 ADDRESS2 CITY     STATE      PIN_CODE REMAR    BAL_DUE

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

       AGE

----------

c04  ABC4 nagpada4 byculla4 mumbai   maharas           4 good4       4000

        35

c05  ABC5 nagpada5 byculla5 mumbai   maharas           5 good5       5000

        65

SQL> DELETE FROM CLIENTMAST WHERE AGE>60;

1 row deleted.