Oracle SQL -Structure Query Language

Oracle SQL- Structure Query Language

 INDEX

1) RDBMS  FEATURES                                                     

2) DATA TYPES                                                               

3) SQL STATEMENTS                                                      

             

4) JOINS                                                                         

5) SUB QUERIES                                                             

6) CONSTRAINTS                                                            

7) VIEWS                                                                         

8) SYNONYMS       

9) CLUSTERS                                                                   

********************************************************************

RDBMS:-

Features:-

Dr. E.F  Codd  proposed the relational model for data base systems.

1) Data is represnted in the form of table(rows/columns).

2) Intersection of row and column gives single value.

3) No data redundancy.

4) Relations are maintained logically.

5) No physical link.

6) Data supports unlimited size.

7) Supports to store any type of data(numbers, char’s, images, sounds).

8) Supports the codd’s rule .

9) Data will be stored centrally at one place and shared by multiple users at once.

10) Provides high security.

11) Supports intergrity constraints.

12) Supports null values.

Oracle data types:-

1) Simple data types.

2) Composite data types.

1) Simple data types:-

 

1)Number (p ,s):-

Ex:-  empno  NUMBER(4), sal NUMBER(7,2).

2) Character data type :- Fixed length character data type. Fixed size is always 10 characters.

Ex:- ename  CHAR(20),course CHAR(15).

3) Varchar2 data type :- Varying length char data type.

Ex:- name VARCHAR2(20).

4) Date:- Used to hold date and time, by default time is set to null.

Ex:-date_of_birth DATE.

5) Long :-Supports to hold characters or numeric values.max length is 2 GB.

6) Raw:- Used to store images or binary data.

7) Long Raw:-Max limit for image is  2 GB.

  We have different LOB’s (large objects). They are:-

1) Clob ( character lob):- Holds character only.

2) Nclob (native language support lob):-Supports to store code in other languages like  german, French etc.

3) Blob (binary lob):-Used to store images.

4) Bfile (binary file):-Used to hold operating system files.

SQL Statements:-

1) Data Definition Language.

2) Data Manipulation Language.

3) Data Retrieving Language.

4) Transactional Control Language.

5) Data Control Language.

 1)Data Definition Language:-  Used to define data base objects.

                                           1)Create

                                            2)Alter

                                            3)drop

                                            4)rename

                                            5)truncate

1)Create:-Used to create any data base objects.

syntax:- CREATE TABLE  tablename

                            (<column1> < DATATYPE >,

                            <column2> < DATATYPE >,

                            <column3> <DATATYPE>,etc);

  

ex:-  CREATE TABLE  emp  ( empno       NUMBER(4),

                                     ename        VARCHAR2 (20),

                                     job            VARCHAR2(20),

                                     mgr           NUMBER(4),

                                     hiredate      DATE,

                                     sal            NUMBER(7,2),

                                     comm         NUMBER(7,2),

                                     deptno        NUMBER(4));

We can create table by using a sub query syntax.

Syntax:-  CREATE TABLE  table name <column>,<column>,<column>,<column>)

         As sub query.

Ex:- CREATE TABLE  raj

       AS SELECT  empno,ename,sal FROM emp;

2) Alter:- Used to modify the structure of existing table. In Alter we have three options. they are:-

                   

           1)add

          2)modify

           3)drop

1) Add:- Used to add columns for a table.

Syntax:- ALTER TABLE table name

              ADD (<column1>< datatype>,<column2><datatype>,

                     <column3> <datatype>,etc);

Ex:-  ALTER TABLE  emp

           ADD (phone     NUMBER(10),

                    address  VARCHAR2(40));

2)Modify :-

                     Used to modify data type of a column .we can increase the size  of a column data type if it contains data or doesn’t contain data in a table.

  We cannot decrease the size if it contains data in a table .

Syntax:- ALTER TABLE  table name 

                              MODIFY (<column1> <DATATYPE>,

                                           <column2> <DATATYPE>,

                                          <column3> <DATATYPE>,etc);

Ex:-  ALTER  TABLE  emp

      MODIFY (empno NUMBER(8),ename VARCHAR2(30));

3)Drop:- We can drop a column in a table or we can drop table also.

Syntax:- ALTER TABLE table name 

                          DROP COLUMN  (<column>);

Ex:-  ALTER TABLE emp

                      DROP COLUMN  empno;

Syntax:- ALTER TABLE table name

                         DROP (<column1> ,<column2>,<column3>,etc);

Ex:-  ALTER TABLE emp DROP(phone,address);

We can use set unused option with out directly droping the columns. later we can drop the unused columns.

 Syntax:- ALTER TABLE  <table name> SET UNUSED <columns>

                                        Or

            ALTER TABLE table name SET UNUSED COLUMN  column

ex:-  ALTER TABLE  emp

                   SET UNUSED COLUMN empno;

                

 Then we can drop unused columns .

  

 Syntax:-alter table table name drop unused columns.

Ex:- ALTER TABLE emp

                    DROP UNUSED columns;

Syntax:- DROP TABLE <table name>.

Ex:-  DROP TABLE emp;

4) Rename:- Rename old name to new name.

 ex:-  Rename emp TO employees.

5) Truncate:- It will delete all the rows from the table where we cannot rollback.

 

syntax:- TRUNCATE TABLE < table name>

ex:- TRUNCATE TABLE emp; 

Using data dictionary we can find out what are the table we have created .

    SELECT  table_name

       FROM user_tables;

   SELECT  DISTINCT  object_type

        FROM user_objects;

Use describe command to find the structure of table what we have created.

Syntax:-Desc table name;

DESC emp;

DESC dept;

2) Data Manipulation Language:- Used to insert or delete or update  records.

                             1)insert.

                             2)update.

                             3)delete.

                             4)merge.

1)Insert :- Inserts new record into the table.

Syntax:-INSERT  INTO TABLE              (<column>,<column>,<column>,<column>) VALUES

               (<value>,<value>,<value>,<value>);

Ex:- INSERT  INTO  emp (empno, ename, sal) VALUES(1,’siva’,2000);

Ex:- INSERT INTO  emp

        VALUES (1,’siva’,’manager’,555,’12-jan-89’,2000,300,10);

2) Update:-  Use to update rows in a table.

Syntax:- UPDATE TABLE  

                                    SET  column= value,

                                            column= value

                 WHERE  condition;

Ex:- 1) UPDATE  emp

                            SET sal =5000

                            WHERE empno=7900;

   2) UPDATE emp

               SET             sal=5000,

                              Comm=500

              WHERE empno=7900;

3)Delete:- Used to delete records.

 Syntax:- DELETE FROM  table

                       WHERE condition.

Example 1:-  DELET FROM emp

                        WHERE  deptno=10;

Example 2:   DELETE FROM  emp;

4) Merge:- U  can conditionally insert or update rows in a table.

Syntax:- MERGE INTO TABLE AS  table _alias  USING

            (table/view/sub query) as alias  on

            join condition

          WHEN matched THEN

            UPDATE SET

              col1=col_val1,

               col2=col_val2

          WHEN  no matched THEN

           INSERT  (column_list ) VALUES  (column_values);

Ex:- MERGE INTO  employees  AS  Emps  USING

           Emp E  ON  (emps.empno=E.empno)

WHEN matched  THEN

  UPDATE SET  

          Emps.ename=E.ename,

          Emps.job       =E.job,

          Emps.mgr      =E.mgr,

          Emps.sal        =E.sal,

          Emps.hiredate=E.hiredate,

          Emps.comm=E.comm,

          Emps.deptno=E.deptno

WHEN  not matched THEN

     INSERT VALUES (E.empno,E.ename, E.job,E.mgr,E.hiredate,E.sal,E.comm,E.deptno);

3)Data Retrieving Language:- It is used to query the data from data base objects for read only purpose.

Syntax :-SELECT *|{[DISTINCT] column|expression [alias],….} FROM table;

1)    SELECT * FROM table;          

           it will displays all the columns available in that table.

   Ex:-  SELECT * FROM emp;   

      

1)    SELECT  column FROM  table;

           it displays specific columns which are mentioned in that table. 

  Ex:- SELECT  empno,ename,sal FROM  emp;   

3) SELECT DISTINCT  column FROM  table;

Ex:- 1) SELECT DISTINCT job FROM  emp;

        2) SELECT DISTINCT job,deptno  FROM  emp;

4) we can use expressions in select statement like +,*,-,/ .

Ex:- 1)SELECT  empno,ename,sal,sal+500 FROM  emp;

     2)SELECT  empno,ename,sal,12*sal+500  FROM  emp;

Rules of Precedence :-

1. Arithmetic operators
2. Concatenation  operator
3. Comparison conditions
4. IS [NOT] NULL, LIKE, [NOT] IN 
5. [NOT]BETWEEN  
6. NOT logical condition
7. AND logical condition
8. OR logical condition 

Ex:-SELECT  empno,ename,sal,12*(sal+500 )  

       FROM emp;

5) Alias:- It is used to rename column headings.

 Ex:- 1)SELECT  empno eno, deptno dno FROM  emp;

2) SELECT empno eno,ename  “name”, sal*12 “annual salary ”

   FROM emp;

6)Concatenation Operator:- It is used to join  columns or character strings.

Ex:- 1) SELECT   ename||job  AS “employee”

          FROM  emp;

We can use literals in between them .

2)SELECT  ename||’is a’||job  AS “employee” FROM  emp;

Where clause:-

Syntax :-select *|{[distinct] column|expression [alias],….}

                            FROM                table

                            WHERE condition(s);

You can restrict the rows returned from the query by using where clause.

Ex:-1) SELECT  empno,ename,sal FROM  emp WHERE deptno=10;

2)    SELECT  empno,ename,sal

     FROM emp

        WHERE ename=’KING’;

we can use following comparison operators in where clause.

1)   =      

2)    >          

3)   <           

4)  >=       

5)  <=  

6)  <>

Ex:- 1) SELECT  empno,ename,sal

           FROM  emp

           WHERE sal<=2000;

2)      SELECT  empno,ename,sal

       FROM  emp

       WHERE sal>=2000;

Other Comparison Conditions:-

1) between  …. And …..       

2)  in       

3)  like       

4)  is null

Ex:-

1)    SELECT  empno,ename,sal

     FROM  emp

     WHERE sal

     BETWEEN   2000 AND 5000;

1)    SELECT  empno,ename,sal,deptno

     FROM  emp

    WHERE  deptno IN  (10,20);

LIKE :- used to perform wild card searches of valid search string values. search conditions contain either literal characters or numbers.

     % (Percentage) denotes zero or many characters.

     _ (Underscore) denotes one character.

Ex:-

1)  SELECT  empno,ename,sal,hiredate

    FROM emp

    WHERE  hiredate LIKE ‘%81’;

2)    SELECT empno,ename,job,sal

     FROM  emp

    WHERE  ename

    LIKE ‘_A%’;

Logical Conditions:-

 1) AND                 2) OR                 3) NOT

Ex:-

1)    SELECT  empno,ename,sal,job

     FROM  emp

    WHERE  sal<=2000

        AND job LIKE ‘%CLE%’;

2)    SELECT  empno,ename,sal,job

    FROM  emp

    WHERE sal<=2000

    OR  job LIKE  ‘%MAN%’;