ORACLE DATABASE

WIP.................



Yet to update:Database Architecture, Memory structure,Background process
DDL,DML,DCL command, SQL Basic commands,



Basic sql queries:-

1)To check the first date, last date,first year date,last year date  

syn:- select trunc (sysdate, 'month') "first day of current month" from dual;
select trunc (last_day (sysdate)) "last day of current month" from dual;
select trunc (sysdate, 'year') "year first day" from dual;
select add_months (trunc (sysdate, 'year'), 12) -1 "year last day" from dual;

o/p

SQL>  select trunc (sysdate, 'month') "first day of current month" from dual;
first day
---------
01-JUL-16

SQL> select trunc (last_day (sysdate)) "last day of current month" from dual;

last day

---------

31-JUL-16

SQL> 

SQL> select trunc (sysdate, 'year') "year first day" from dual;

year firs

---------

01-JAN-16

SQL

SQL>  select add_months (trunc (sysdate, 'year'), 12) -1 "year last day" from dual;

year last

---------

31-DEC-16

SQL>

You can use the below link for any ref" http://viralpatel.net/blogs/useful-oracle-queries/"


2) Create   a table and  do insert  , update delete.

syn:

  create table  student (id int, name char, age int );
  insert into student(id,name,age) values('121','r','20');
  delete  from student where name='r';

  o/p

SQL> create table  student (id int, name char, age int );

Table created.

SQL> 

SQL> desc student;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               CHAR(1)
 AGE                                                NUMBER(38)

SQL>  insert into student(id,name,age) values('121','r','20');

1 row created.

SQL> desc student;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 NAME                                               CHAR(1)
 AGE                                                NUMBER(38)


SQL> select * from student;

        ID N        AGE
---------- - ----------
       121 r         20
       123 e         23

SQL> delete  from student where name='r';

1 row deleted.

SQL> select * from student;

        ID N        AGE
---------- - ----------
       123 e         23

123 e         23


SQL> delete  from student where
1 row deleted.

SQL> select * from student;

        ID N        AGE
---------- - ----------
       123 e         23



SQL> select * from student;

        ID N        AGE
---------- - ----------
       121 r         20
       123 e         23

SQL> delete  from student where name='r';

1 row deleted.

SQL> select * from student;

        ID N        AGE
---------- - ----------
       123 e         23

SQL> delete  from student where name='r';

1 row deleted.

SQL> select * from student;

        ID N        AGE
---------- - ----------

       123 e         23

SQL> select * from student;

        ID N        AGE
---------- - ----------
       121 r         20
       123 e         23

SQL> delete  from student where name='r';

1 row deleted.

SQL> select * from student;

        ID N        AGE
---------- - ----------
       123 e         23


SQL>

SQL>  select add_months (trunc (sysdate, 'year'), 12) -1 "year last day" from dual;

year last
---------
31