ORACLE
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.
STRUCTURED QUERY LANGUAGE
ORACLE
2
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.
STRUCTURED QUERY LANGUAGE
ORACLE
3
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
STRUCTURED QUERY LANGUAGE
ORACLE
4
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:
STRUCTURED QUERY LANGUAGE
ORACLE
5
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;
STRUCTURED QUERY LANGUAGE
ORACLE
6
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.
STRUCTURED QUERY LANGUAGE
ORACLE
7
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.
STRUCTURED QUERY LANGUAGE
ORACLE
8
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
STRUCTURED QUERY LANGUAGE
ORACLE
9
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.
STRUCTURED QUERY LANGUAGE
ORACLE
10
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:
STRUCTURED QUERY LANGUAGE
ORACLE
11
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
STRUCTURED QUERY LANGUAGE
ORACLE
12
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;
STRUCTURED QUERY LANGUAGE
ORACLE
13
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
STRUCTURED QUERY LANGUAGE