SQL


 sQL INTRODUCTION

In general if we want to store Employee Information,we store as shown below:  

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
100
PAVAN
CHAIRMAN

01-JAN-2005
30000
10000
10
101
GAYATRI
P.S
100
01-JAN-2005
10000
1000
10
102
BALAJI
MANAGER
100
01-JAN-2005
10000
1000
10
103
SMITH
MANAGER
100
01-JAN-2005
10000
1000
10

In Oracle We use Tables to store Data.
Table contains Columns and Rows.

OPENING SQL* PLUS IN ORACLE 8  :

ü  GO TO ALL PROGRAMS
ü  AND SELECT ORACLE FOR WINDOWS 95 AND
ü  CLICK SQL*PLUS.
ü  YOU GET DIALOG BOX.
ü  TYPE SCOTT IN USERNAME AND TIGER IN PASSWORD AND PRESS ENTER.
ü  YOU GET SQL PROMPT.

OPENING SQL* PLUS IN ORACLE 9 :

ü  GO TO ALL PROGRAMS
ü  AND SELECT ORACLE-ORAHOME90
ü  SELECT APPLICATION DEVELOPMENT IN THERE CLICK SQL PLUS
ü  AND TYPE SCOTT IN USER NAME TIGER IN PASSWORD BOX.

IN ORACLE 8 :USER CREATION:
           
ü  HERE IF YOU WANT TO CREATE YOUR OWN USER YOU GO TO ALL PROGRAMS,
ü  THERE PERSONAL ORACLE FOR WINDOWS 95
ü  AND ORACLE NAVIGATOR.
ü  CLICK THAT YOU WILL GET WINDOW
ü  THERE DOUBLE CLICK PERSONAL ORACLE 8
ü  AND DOUBLE CLICK ON LOCAL DATABASE.
ü  YOU SEE DROP DOWN LIST.
ü  THERE CLICK ON USER AND RIGHT CLICK ON USER AND NEW.
ü  YOU GET WINDOW TYPE USERNAME,PASSWORD(TWICE).
ü  YOUR USER CREATED.

 THEN CLICK SQL*PLUS AS SHOWN ABOVE AND TYPE YOUR USERNAME AND PASSWORD.



In the above table we take Empno, Ename, Job,MGR,HIREDATE,SAL,COMM, Deptno as columns in creating table in Oracle.

CREATION OF TABLE:

  SQL>CREATE TABLE EMPLOYEE_INFO
  2   (EMPNO             NUMBER(10),
  3   ENAME              VARCHAR2(10),
  4   JOB                   VARCHAR2(10),
  5   MGR                   NUMBER(10),
  6   HIREDATE         DATE,
  7   SAL                    NUMBER(10),
  8   COMM                NUMBER(10),
  9*  DEPTNO            NUMBER(10));

  Table created.

IN SQL*PLUS,WE USE CL SCR TO CLEAR SCREEN.

IN SQL*PLUS,WE TYPE ED AT SQL PROMPT TO GET EDITOR,WE EDIT SQL STATEMENTS THERE.

DESCRIBE COMMAND WILL GIVE US WITH WHAT COLUMNS WE CREATED TABLE AND THEIR DATATYPE.

SQL> DESC EMPLOYEE_INFO

 Name                            Null?    Type

 EMPNO                                   NUMBER(10)
 ENAME                                               VARCHAR2(10)
 JOB                                         VARCHAR2(10)
 MGR                                        NUMBER(10)
 HIREDATE                              DATE
 SAL                                         NUMBER(10)
 COMM                                                 NUMBER(10)
 DEPTNO                                 NUMBER(10)

INSERTING VALUES IN TO TABLE:

  SQL>  INSERT INTO EMPLOYEE_INFO
  2  VALUES
  3 (100,'PAVAN','CHAIRMAN',NULL,'01-JAN-2005',30000,10000,10);

  1 row created.

WHEN EVER WE WRITE VALUES IN INSERT WE HAVE TO GIVE VALUES FOR ALL COLUMNS.

  SQL>  INSERT INTO EMPLOYEE_INFO
  2  VALUES
  3 (101,'GAYATRI','P.S',100,'01-JAN-2005',10000,1000,10);

  1 row created.

 SQL>  INSERT INTO EMPLOYEE_INFO
  2  VALUES
  3 (102,'KUMARI','MANAGER',100,'01-JAN-2005',10000,1000,10);

 1 row created.

  SQL> INSERT INTO EMPLOYEE_INFO
  2  VALUES
  3 (103,'SMITH','MANAGER',100,'01-JAN-2005',10000,1000,10);

 1 row created.

IF I WANT TO INSERT INTO ONLY FEW COLUMNS THEN I WILL WRITE LIKE THIS:

  SQL> INSERT INTO EMPLOYEE_INFO
   (EMPNO,ENAME,JOB,DEPTNO)        
  2  VALUES
  3 (104,'SMITH','MANAGER',10);

1 row created.

SELECTING VALUES FROM A  TABLE:

WHENEVER WE SELECT FROM THE TABLE WE GET COMPLETE INFORMATION IN A TABLE.

SQL> SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMPLOYEE_INFO;

    EMPNO ENAME  JOB                       MGR    HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100             PAVAN      CHAIRMAN              01-JAN-05     30000     10000        10                  
      101             GAYATRI   P.S                 100   01-JAN-05     10000     1000          10                  
      102             KUMARI    MANAGER      100    01-JAN-05     10000     1000          10                  
      103             SMITH       MANAGER      100    01-JAN-05     10000     1000          10                  
4 rows selected.

HERE INSTEAD OF TYPING ALL THE COLUMN NAMES WE TYPE ‘*”.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB         MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100             PAVAN      CHAIRMAN              01-JAN-05     30000     10000        10                  
      101             GAYATRI   P.S              100      01-JAN-05     10000     1000          10                  
      102             KUMARI    MANAGER   100       01-JAN-05     10000     1000          10                  
      103             SMITH       MANAGER   100       01-JAN-05     10000     1000          10                  

4 rows selected.

SELECTING WITH WHERE CLAUSE:

WHEN EVER WE SELECT USING WHERE CLAUSE WE GET PARTICULAR INFORMATION DEPENDS ON THE COLUMN YOU SPECIFY IN WHERE CLAUSE.

SQL> SELECT * FROM EMPLOYEE_INFO
 
2  WHERE EMPNO=100;

    EMPNO ENAME      JOB              MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100 PAVAN      CHAIRMAN             01-JAN-05               30000    10000        10                  

1 row selected.

  SQL> SELECT * FROM EMPLOYEE_INFO
  2 WHERE ENAME='GAYATRI';

    EMPNO ENAME      JOB              MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      101 GAYATRI    P.S                  100   01-JAN-05     10000      1000        10                  

1 row selected.

UPDATE STATEMENT:

WHILE UPDATING A TABLE IF YOU DON’T GIVE WHERE CLAUSE WHOLE TABLE WILL BE UPDATED.


SQL> UPDATE EMPLOYEE_INFO
  2  SET SAL= SAL+1000;

4 rows updated.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME   JOB           MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100 PAVAN      CHAIRMAN           01-JAN-05     31000     10000       10                  
      101 GAYATRI   P.S              100   01-JAN-05     11000      1000        10                  
      102 KUMARI    MANAGER   100   01-JAN-05     11000      1000        10                   
      103 SMITH      MANAGER    100   01-JAN-05     11000     1000         10                  

4 rows selected.

UPDATE TABLE USING WHERE CLAUSE:

WHEN EVER WE GIVE WHERE CLAUSE IN UPDATION ONLY THAT COLUMN CORESSPONDING ROWS WILL BE UPDATED.

SQL>  UPDATE EMPLOYEE_INFO
  2   SET SAL= SAL+1000
  3  WHERE EMPNO=100;

1 row updated.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB                   MGR                 HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100 PAVAN      CHAIRMAN                                      01-JAN-05     32000     10000       10                  
      101 GAYATRI   P.S                                     100                   01-JAN-05     11000      1000        10                  
      102 KUMARI    MANAGER               100                   01-JAN-05     11000      1000        10                  
      103 SMITH       MANAGER               100                   01-JAN-05     11000      1000        10                  

4 rows selected.

SQL>  UPDATE EMPLOYEE_INFO
  2   SET SAL= SAL+1000
  3  WHERE ENAME='GAYATRI';

1 row updated.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB              MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
100 PAVAN      CHAIRMAN             01-JAN-05     32000     10000      10
101 GAYATRI   P.S               100    01-JAN-05     12000      1000        10
102 KUMARI    MANAGER    100    01-JAN-05     11000      1000        10
103 SMITH      MANAGER     100    01-JAN-05     11000      1000        10

4 rows selected.



DELETING DATA FROM A TABLE:

IF YOU WANT TO DELETE  INFO FROM A TABLE,HERE IF YOU WON’T SPECIFY THE WHERE CLAUSE WHOLE TABLE  INFO WILL BE DELETED.


SQL> DELETE FROM EMPLOYEE_INFO
  2  WHERE EMPNO=102;

1 row deleted.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB          MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100 PAVAN      CHAIRMAN             01-JAN-05     32000     10000       10                  
      101 GAYATRI    P.S              100    01-JAN-05     12000      1000        10                   
      103 SMITH      MANAGER     100    01-JAN-05     11000      1000        10                  

3 rows selected.

SQL> DELETE FROM EMPLOYEE_INFO
  2  WHERE ENAME='SMITH';

1 row deleted.

SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB          MGR HIREDATE        SAL      COMM    DEPTNO                  
--------- ---------- ---------- --------- --------- --------- --------- ---------                  
      100 PAVAN      CHAIRMAN             01-JAN-05     32000     10000       10                  
      101 GAYATRI    P.S              100    01-JAN-05     12000      1000        10                  

2 rows selected.

HERE IF YOU WANT TO DELETE ALL EMPLOYEES WE USE THIS STATEMENT.

IF YOU DON’T SPECIFY WHERE IN DELETE COMPLETE TABLE WILL BE DELETED.


SQL> DELETE FROM EMPLOYEE_INFO;

2 rows deleted.


CREATING TABLE WITH SELECT STATEMENT:

IF YOU WANT TO CREATE SAME TABLE AS EMPLOYEE_INFO WITH DATA, WE USE BELOW STATEMENT:

CREATE TABLE EMP

AS

SELECT * FROM EMPLOYEE_INFO;

HERE EMP TABLE HAS SAME COLUMNS AS THAT OF EMPLOYEE_INFO AND EMP HAS SAME DATA THAT OF EMPLOYEE_INFO.HERE BOTH ARE SAME TABLES.

IF YOU WANT TO CREATE  TABLE WITH SELETED COLUMNS THAT OF EMPLOYEE_INFO WITH DATA FOR THAT COLUMNS,WE USE BELOW STATEMENT:

CREATE TABLE EMP

AS

SELECT EMPNO,ENAME FROM EMPLOYEE_INFO;

HERE EMP TABLE CREATED WITH EMPNO,ENAME COLUMNS AND DATA FOR THAT COLUMNS FROM EMPLOYEE_INFO TABLE.

IF YOU WANT TO CREATE  TABLE WITH SELETED COLUMNS THAT OF EMPLOYEE_INFO WITH DATA FOR THAT COLUMNS WITH YOUR OWN NAME FOR COLUMNS,WE USE BELOW STATEMENT:

CREATE TABLE EMP

(EMPLOYEE_NO,EMPLOYEE_NAME)

AS

SELECT EMPNO,ENAME FROM EMPLOYEE_INFO;

HERE EMP TABLE CREATED WITH COLUMNS EMPNO,ENAME BUT THEIR HEADINGS WILL BE EMPLOYEE_NO,EMPLOYEE_NAME.


INSERTING VALUES WITH SELECT STATEMENT:

INSERT INTO EMP

SELECT * FROM EMPLOYEE_INFO;

HERE BOTH EMP,EMPLOYEE_INFO TABLES SHOULD HAVE SAME STRUCTURE.

MOST IMPORTANT TABLES IN ORACLE DATABASE:

IN USER SCOTT WE HAVE THREE IMPORTANT TABLES THOSE ARE

ü  EMP

ü  DEPT

ü  SALGRADE.

DELETE VS TRUNCATE:


SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB              MGR         HIREDATE        SAL      COMM    DEPTNO                
    100       PAVAN      CHAIRMAN                      01-JAN-05     32000     10000        10                     
    101      GAYATRI    P.S              100 01-JAN-05     12000      1000        10                  

SQL> DELETE FROM EMPLOYEE_INFO;

2 rows deleted.

SQL> SELECT * FROM EMPLOYEE_INFO;

no rows selected

SQL> ROLL BACK;

Rollback complete.



SQL> SELECT * FROM EMPLOYEE_INFO;

    EMPNO ENAME      JOB          MGR             HIREDATE        SAL      COMM    DEPTNO                  
      100     PAVAN      CHAIRMAN          01-JAN-05     32000     10000        10                         
      101     GAYATRI    P.S              100             01-JAN-05     12000      1000        10     

SQL> DELETE FROM EMPLOYEE_INFO;

 

2 rows deleted.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> ROLL BACK;

 

Rollback complete.

 

SQL> SELECT * FROM EMPLOYEE_INFO;

 

no rows selected

 

 

SQL> INSERT INTO EMPLOYEE_INFO

 

  2  VALUES

 

  3  (100,'PAVAN','CHAIRMAN',NULL,'01-JAN-05',32000,10000,10);

 

1 row created.

 

SQL> COMMIT;

 

Commit complete.

 

SQL> SELECT * FROM EMPLOYEE_INFO;

 

    EMPNO ENAME      JOB              MGR HIREDATE        SAL      COMM    DEPTNO                  

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

      100 PAVAN      CHAIRMAN             01-JAN-05     32000     10000        10                  

 

SQL> TRUNCATE TABLE EMPLOYEE_INFO;

 

Table truncated.

 

SQL> ROLL BACK;

 

Rollback complete.

 

SQL> SELECT * FROM EMPLOYEE_INFO;

 

no rows selected


IN TRUNCATE TABLE THE WHOLE INFORMATION WIIL BE DELETED PERMENENTLY. BUT IN CASE OF DELETE THE INFORMATION WILL BE GET BACK AFTER ROLL BACK. BUT AFTER DELETE IF YOU TYPE COMMIT. THEN INFORMATION WILL BE DELETED PERMENENTLY.

DELETE +COMMIT = TRUNCATE.

 


ORDER BY CLAUSE:

We can  retrive data either in ascending order or in descending order by using ORDER BY clause:


For retriving data in ascending order:


Select  ename , sal from emp ORDER BY sal  ASC ;


Select ename , sal  from emp   ORDER BY sal ; default is ascending order.

For retriving data in descending order:

Select ename,sal from emp ORDER BY sal DESC ;

 

We can retrive  one particular

 and specific type of data  by using

the key word ‘like’:



Select  enmae ,job,sal from emp where deptno LIKE 10;

Select ename,sal,deptno from  emp where job LIKE ‘ CLERK ‘ ;

We can retrive more
specific data by using
 LIKE  and symbols ‘ % ’ ,’ _ ’:

 “ %”  represents group of characters .

   “ _ “    represents single character .

TO GET EMPLOYEES WITH S LETTER IN THE FIRST OF THEIR NAME:

Select  * from emp where ename LIKE ‘ S% ’ ;

TO GET EMPLOYEES WITH S LETTER IN THE LAST OF THEIR NAME:

Select * from emp where job LIKE ‘ %S ’ ;
TO GET EMPLOYEES WITH S LETTER IN  THEIR NAME:


Select * from emp where ename  LIKE ‘ %S% ‘ ;

TO GET EMPLOYEES WITH FOUR LETTERS:


Select * from emp wHere ename LIKE  ‘____’ ;

TO GET EMPLOYEES WITH SECOND LETTER ‘A’:


SQL> SELECT * FROM EMP WHERE ENAME LIKE  '_A___';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                            
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                            
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                            

TO GET EMPLOYEES WITH THIRD LETTER ‘I’:


SQL> SELECT * FROM EMP WHERE ENAME LIKE  '__I__';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                            
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                            
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                            

TO GET EMPLOYEES WITH THIRD AND FOURTH LETTERS AS ‘NE’:


SQL> SELECT * FROM EMP WHERE ENAME LIKE  '__NE_';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                            
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                            
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20                                            

TO GET EMPLOYEES WITH HIREDATE IN THE YEAR ‘81’:


  1* SELECT * FROM EMP WHERE HIREDATE LIKE  '%81'
SQL> /

     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                                            
      7566 JONES      MANAGER        7839 02-APR-81       2975                        20                                            
      7654 MARTIN     SALESMAN      7698 28-SEP-81       1250       1400         30                                            
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                       30                                            
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                       10                                            
      7839 KING       PRESIDENT                  17-NOV-81       5000                      10                                            
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30                                            
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30                                            
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20                                            

10 rows selected.

TO GET EMPLOYEES WITH HIREDATE IN the month ‘feb’:

SELECT * FROM EMP WHERE HIREDATE LIKE  '%FEB%';


     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                                            

TO GET EMPLOYEES WITH HIREDATE ‘-‘ in their date:

  1* SELECT * FROM EMP WHERE HIREDATE LIKE '%-%'
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                            
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                            
      7369 SMITH      CLERK                7902 17-DEC-80        800                       20                                            
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30                                            
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30                                            
      7566 JONES      MANAGER         7839 02-APR-81       2975                       20                                            
      7654 MARTIN     SALESMAN       7698 28-SEP-81       1250       1400        30                                            
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                       30                                            
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                       10                                            
      7788 SCOTT      ANALYST           7566 19-APR-87       3000                      20                                            
      7839 KING          PRESIDENT               17-NOV-81       5000                      10                                             
      7844 TURNER    SALESMAN        7698 08-SEP-81       1500          0         30                                            
      7876 ADAMS      CLERK                7788 23-MAY-87       1100                    20                                             
      7900 JAMES       CLERK                7698 03-DEC-81        950                    30                                            
      7902 FORD        ANALYST            7566 03-DEC-81       3000                    20                                             
      7934 MILLER      CLERK                7782 23-JAN-82       1300                    10                                            

14 rows selected.


TO GET EMPLOYEES WITH  ‘ 8’ in last in their empno:

  1* SELECT * FROM EMP WHERE EMPNO LIKE  '%8'
SQL> /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                            
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                            
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30                                            
     7788 SCOTT      ANALYST           7566 19-APR-87       3000                    20                                            


DISTINCT:

We can  suppress  duplicate rows while selecting data by using the  key word DISTINCT:

select DISTINCT (job) from emp;

HERE WE GET JOBS WITHOUT DUPLICATES.

SQL> SELECT JOB FROM EMP;

JOB                                                                                                
---------                                                                                          
CLERK                                                                                               
SALESMAN                                                                                           
SALESMAN                                                                                           
MANAGER                                                                                            
SALESMAN                                                                                           
MANAGER                                                                                             
MANAGER                                                                                            
ANALYST                                                                                            
PRESIDENT                                                                                          
SALESMAN                                                                                           
CLERK                                                                                               
CLERK                                                                                              
ANALYST                                                                                            
CLERK                                                                                               

14 rows selected.

SQL> SELECT DISTINCT(JOB) FROM EMP;

JOB                                                                                                
---------                                                                                           
ANALYST                                                                                            
CLERK                                                                                               
MANAGER                                                                                            
PRESIDENT                                                                                          
SALESMAN