USEFUL SQL QUERIES AND INFORMATIONS

SQL DATA TYPES:

`````````````````````````

1     1  Number ->Size 38 digit
2      Character ->size upto 2000 bytes
3      Varchar2 -> 4000 bytes
4      Date ->9 bytes
5      Long data type ->2 GB
6      Raw ->store binary data
7      Long Raw ->2 GB
8      Lob ->Blob(binary lob),clob(character lob)

SSQL OPERATORS:

````````````````````````````

1)      Operator -> +,-,*,/   

Example ->select sal+100 from emp;

2)      Comparison operator  ->  =,>,<=,>=,||,(<>,!=    <-not equal to )

Example ->select * from emp where sal > 3000;

3)      Logical operator -> and, or, not

Example -> select * from emp where ename=’smith’ and sal=800;

4)      Comparison operator ->between and, not between ,in , not in, like ,in null

Select * from emp where sal between 2000 and 3000 ;

Select * from emp where deptno in (10,20);

Select ename from emp where sal>=1000 and job like ‘%m’;

DATA DEFINITION LANGUAGE (DDL):

``````````````````````````````````````````````````````````

Create:

SQL> create table bank(accno number(3),branch varchar(20),cname varchar(20));

Table created.

SQL> desc bank;

 Name                                                  Null?    Type

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

 ACCNO                                                          NUMBER(3)

 BRANCH                                                        VARCHAR2(20)

 CNAME                                                          VARCHAR2(20)

Alter:

SQL> desc bank;

 Name                                                  Null?    Type

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

 ACCNO                                                          NUMBER(3)

 BRANCH                                                        VARCHAR2(20)

 CNAME                                                          VARCHAR2(20)

SQL> alter table bank add(balance number(4));

Table altered.

SQL> desc bank;

 Name                                                  Null?    Type

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

 ACCNO                                                             NUMBER(3)

 BRANCH                                                           VARCHAR2(20)

 CNAME                                                             VARCHAR2(20)

 BALANCE                                                         NUMBER(4)

Truncate:

SQL> truncate table bank;

Table truncated.

Delete:

SQL> drop table bank;

Table dropped.

DATA  MANIPULATION  LANGUAGE (DML):

``````````````````````````````````````````````````````````````````

Insert:

SQL> insert into bank (accno, cname, branch) values (2444,'dhana','salem');

1 row created.

SQL> insert into bank values(444,'hari','chennai');

1 row created.

SQL> insert into bank values(&accno,'&cname','&branch');

Enter value for accno: 100

Enter value for cname: arun

Enter value for branch: erode

old    1: insert into bank values(&accno,'&cname','&branch');

new  1: insert into bank values(100,'arun','erode')

select:

SQL> select*from bank;

 ACCNO        CNAME                 BRANCH

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

  2444              dhana                     salem

  444                hari                        Chennai

  100                arun                        erode

SQL>select  cname,branch where accno=444;

ACCNO        CNAME                 BRANCH

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

    444                hari                        Chennai

SQL>select branch where accno=100 and branch=Chennai;

ACCNO        CNAME                 BRANCH

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

100                 arun                        erode

Update:

SQL>update bank set accno=244 where accno=2444;

1 row updated;

SQL>select*from bank;

 ACCNO        CNAME                 BRANCH

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

  244               dhana                       salem

  444                hari                         Chennai

  100                arun                        erode

Delete:

SQL>delete  from bank where cname=’dhana’;

SQL>select*from bank;

 ACCNO        CNAME                 BRANCH

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

  444                hari                         Chennai

  100                arun                        erode