OVERVIEW OF SQL

Please create the scott schema from the following GITHUB repository

//github.com/oracle/dotnet-db-samples/blob/master/schemas/scott.sql

EXECUTING SELECT STATEMENT

SQL> SELECT * FROM DEPT;

DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

HANDLING NULL VALUES

SQL> SELECT EMPNO,ENAME,NVL(MGR,0) FROM EMP;

EMPNO ENAME NVL(MGR,0)
———- ———- ———-
7369 SMITH 7902
7499 ALLEN 7698
4567 WARD 1234
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING 0
7844 TURNER 7698
7876 ADAMS 7788

THE DISTINCT KEYWORD

SQL> SELECT DISTINCT MGR FROM EMP;

MGR
———-
1234
7566
7698
7782
7788
7839
7902
8 rows selected.

CHANGING OUTPUT HEADINGS WITH ALIASES

SQL> SELECT EMPNO EMPLOYEEID,ENAME,NVL(MGR,0) AS MGR FROM EMP;

EMPLOYEEID ENAME MGR
———- ———- ———-
7369 SMITH 7902
7499 ALLEN 7698
4567 WARD 1234
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7788 SCOTT 7566
7839 KING 0
7844 TURNER 7698
7876 ADAMS 7788

7900 JAMES 7698
7902 FORD 7566
7934 MILLER 7782

PUTTING COLUMN TOGETHER WITH CONCATENATION

SQL>SELECT ENAME||’,WHO IS THE’||CONCAT(JOB,’FOR THE COMPANY’) “NAME AND ROLE”
FROM EMP

NAME AND ROLE
—————————————
SMITH,WHO IS THECLERKFOR THE COMPANY
ALLEN,WHO IS THESALESMANFOR THE COMPANY
WARD,WHO IS THESALESMANFOR THE COMPANY
JONES,WHO IS THEMANAGERFOR THE COMPANY
MARTIN,WHO IS THESALESMANFOR THE COMPAN
BLAKE,WHO IS THEMANAGERFOR THE COMPANY
CLARK,WHO IS THEMANAGERFOR THE COMPANY
SCOTT,WHO IS THEANALYSTFOR THE COMPANY
KING,WHO IS THEPRESIDENTFOR THE COMPANY
TURNER,WHO IS THESALESMANFOR THE COMPAN
ADAMS,WHO IS THECLERKFOR THE COMPANY

NAME AND ROLE
—————————————
JAMES,WHO IS THECLERKFOR THE COMPANY
FORD,WHO IS THEANALYSTFOR THE COMPANY
MILLER,WHO IS THECLERKFOR THE COMPANY

OTHER SQL*PLUS COMMAND TO KNOW

SQL> desc emp;

Name Null? Type
—————————————– ——– —————————-

EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL>list
SQL>DEL 3 (DELETE THE LINE NUMBER 3 FROM BUFFER)
SQL>CLEAR BUFFER;
SQL>RUN SELECT * FROM EMP;
SQL>SAVE DEB.SQL
SQL>SPO ON
SQL>SELECT * FROM EMP;
SQL>SPO OFF
SQL>ED ON.LST
SQL>C/SALARY/SAL;( CHANGING MISTAKEN WORD)
SQL>@EMP;( EXECUTING SQL)
SQL>STA EMP;( EXECUTING SQL)

LIMITING ,STORING AND MANIPULATING RETURN DATA

SORTING RETURN DATA WITH THE ORDER BY CLAUSE

SQL>SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY ENAME ASC;     (DEFAULT IS ASC)

EMPNO ENAME SAL
———- ———- ———-
7876 ADAMS 1100
7499 ALLEN 1600
7698 BLAKE 2850
7782 CLARK 2450
7902 FORD 3000
7900 JAMES 950
7566 JONES 2975
7839 KING 5000
7654 MARTIN 1250
7934 MILLER 1300
7788 SCOTT 3000

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800
7844 TURNER 1500
4567 WARD

14 ROWS SELECTED.

SQL> SELECT ENAME FROM EMP ORDER BY ENAME DESC;

ENAME
———-
WARD
TURNER
SMITH
SCOTT
MILLER
MARTIN
KING
JONES
JAMES
FORD
CLARK

ENAME
———-
BLAKE
ALLEN
ADAMS

14 ROWS SELECTED.

SQL> SELECT ENAME,JOB FROM EMP ORDER BY 2 DESC;(2 DENOTES THE SECOND COLUMN)

ENAME JOB
———- ———
ALLEN SALESMAN
WARD SALESMAN
TURNER SALESMAN
MARTIN SALESMAN
KING PRESIDENT
BLAKE MANAGER
JONES MANAGER
CLARK MANAGER
MILLER CLERK
SMITH CLERK
ADAMS CLERK

ENAME JOB
———- ———
JAMES CLERK
FORD ANALYST
SCOTT ANALYST

14 rows selected.

USE OF WHERE CLAUSE TO LIMIT DATA

SQL> SELECT EMPNO AS EID FROM EMP WHERE SAL IN(800,3000);

EID
———
7369
7788
7902

SQL> SELECT ENAME FROM EMP WHERE SAL BETWEEN 1500 AND 3000;

ENAME
———-
ALLEN
JONES
BLAKE
CLARK
SCOTT
TURNER
FORD

SQL> SELECT * FROM EMP WHERE JOB LIKE ‘_LE%’

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL>SELECT ENAME,COMM FROM EMP ORDER BY COMM DESC NULLS LAST;

ENAME COMM
———- ———-
MARTIN 1400
WARD 500
ALLEN 300
TURNER 0
SCOTT
KING
ADAMS
JAMES
FORD
MILLER
BLAKE

ENAME COMM
———- ———-
JONES
SMITH
CLARK

USING SINGLE ROW FUNCTIONS

STRING FUNCTION

SQL>SELECT CHR(65) FROM DUAL;

CHR(
—-
A

SQL>SELECT CONCAT(‘DEBASIS’,’MAITY’) FROM DUAL;

CONCAT(‘DEBA
————
DEBASISMAITY

SQL> SELECT INITCAP(‘deb’) FROM DUAL;

INI
—
Deb

SQL> SELECT LENGTH(‘DEBASIS MAITY’) FROM DUAL;

LENGTH(‘DEBASISMAITY’)
———————-
13

SQL> SELECT LOWER(‘DEB’),UPPER(‘deb’) FROM DUAL;

LOW UPP
— —
deb DEB

SQL> SELECT LPAD(ENAME,10,’-‘),RPAD(ENAME,10,’.’) FROM EMP WHERE ROWNUM<5;

LPAD(ENAME RPAD(ENAME
———- ———-
—–SMITH SMITH…..
—–ALLEN ALLEN…..
——WARD WARD……
—–JONES JONES…..

SQL> SELECT LTRIM(‘DEBASIS’,’DEB’) FROM DUAL;

LTRI
—-
ASIS

SQL> SELECT REPLACE(‘DEBASIS’,’DEB’,’RUBU’) FROM DUAL;

REPLACE(
——–
RUBUASIS

SQL> SELECT SUBSTR(‘DEBASIS’,1,3) FROM DUAL;

SUB
—
DEB

SQL> SELECT SUBSTR(‘DEBASIS’,-3,2) FROM DUAL;

SU
—
SI

The Oracle TRANSLATE() function returns a string with all occurrences of each character in a string replaced by its corresponding character in another string.

The TRANSLATE() function allows you to make several single-character, one-to-one translations or substitutions in one operation.

SQL> SELECT
TRANSLATE( ‘[127.8, 75.6]’, ‘[,]’, ‘( )’ ) Point,
TRANSLATE( ‘(127.8 75.6)’, ‘( )’, ‘[,]’ ) Coordinates
FROM
dual;

POINT COORDINATES
——————————————————————————————————————–
(127.8 75.6) [127.8,75.6]

SQL> select * from emp where soundex(job)=soundex(‘cleark’);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10

NOTE:TRIM=LTRIM(RTRIM(COLUMN NAME))

SQL>SELECT trim(‘S’ from ‘DEBASIS’ ) FROM DUAL;

DEBASI

NUMBER FUNCTION

SQL> SELECT ABS(-23),ABS(23) FROM DUAL;

ABS(-23) ABS(23)
——— ———
23 23
SQL> SELECT CEIL(-23.45) FROM DUAL;

CEIL(-23.45)
————
-23

SQL> SELECT FLOOR(-23.45),FLOOR(23.56),FLOOR(90.98),FLOOR(23.6) FROM DUAL;

FLOOR(-23.45) FLOOR(23.56) FLOOR(90.98) FLOOR(23.6)
————- ———— ———— ———–
-24 23 90 23

SQL> SELECT MOD(12,5) FROM DUAL;(12%5=2)

MOD(12,5)
———
2
SQL> SELECT POWER(5,2) FROM DUAL;

POWER(5,2)
———-
25
SQL> SELECT SQRT(25) FROM DUAL;

SQRT(25)
———
5
SQL> SELECT TRUNC(23.345,1) FROM DUAL;

TRUNC(23.345,1)
—————
23.3

DATE FUNCTION

SQL> SELECT SYSDATE,ADD_MONTHS(SYSDATE,2) FROM DUAL;

SYSDATE ADD_MONTH
——— ———
17-OCT-03 17-DEC-03

SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;

LAST_DAY(
———
31-OCT-03

SQL> SELECT MONTHS_BETWEEN(’17-OCT-05′,’31-OCT-2005′) FROM DUAL;

MONTHS_BETWEEN(’17-OCT-05′,’31-OCT-2005′)
—————————————–
-.4516129

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,’MONDAY’) FROM DUAL;

SYSDATE NEXT_DAY(
——— ———
17-OCT-03 20-OCT-03

SQL>SELECT TO_CHAR(SYSDATE,’DD-MONTH-YEAR:HH-MI’) FROM DUAL

TO_CHAR(SYSDATE,’DD-MONTH-YEAR:HH-MI’)
————————————————————-
17-OCTOBER -TWO THOUSAND THREE:10-50

SQL> select sysdate,round(sysdate) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 18-JAN-09

SQL> select sysdate,trunc(sysdate) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 17-JAN-09

SQL> select sysdate,round(sysdate,’MONTH’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 01-FEB-09

SQL> select sysdate,trunc(sysdate,’MONTH’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,round(sysdate,’YEAR’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,trunc(sysdate,’YEAR’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 01-JAN-09

SQL> select sysdate,round(sysdate,’DAY’) from dual;

SYSDATE ROUND(SYS
——— ———
17-JAN-09 18-JAN-09

SQL> select sysdate,trunc(sysdate,’DAY’) from dual;

SYSDATE TRUNC(SYS
——— ———
17-JAN-09 11-JAN-09

OTHER FUNCTIONS

SQL> SELECT DECODE(JOB,’CLERK’,’OFFICER’,JOB) FROM EMP WHERE ROWNUM<5;

DECODE(JO
———
OFFICER
SALESMAN
SALESMAN
MANAGER

SQL> SELECT USER FROM DUAL;

USER
——————————
SCOTT

SQL> SELECT USERENV(‘ISDBA’) FROM DUAL;

USEREN
——
FALSE

SQL> SELECT USERENV(‘INSTANCE’) FROM DUAL;

USERENV(‘INSTANCE’)
——————-
1
SQL>SHOW USER
SQL>PASSWORD USERNAME;

SQL> SELECT SAL,SAL + NVL(COMM,0) TOTAL_COMP FROM EMP;

SAL TOTAL_COMP
———- ———-
800 800
1600 1900
1250 1750
2975 2975
1250 2650
2850 2850
2450 2450
3000 3000
5000 5000
1500 1500
1100 1100

950 950
3000 3000
1300 1300

SQL> SELECT SIGN(-20),SIGN(20),SIGN(0) FROM DUAL;

SIGN(-20) SIGN(20) SIGN(0)
———- ———- ———-
-1 1 0

SQL> SELECT DECODE(SIGN(SAL-3000),-1,’LOWER PAID’,1,’HIGHER PAID’,’SAME’),SAL FROM EMP;

DECODE(SIGN(SAL-3000),-1,’LOWERP SAL
——————————– ———-
LOWER PAID 800
LOWER PAID 1600
LOWER PAID 1250
LOWER PAID 2975
LOWER PAID 1250
LOWER PAID 2850
LOWER PAID 2450
SAME 3000
HIGHER PAID 5000
LOWER PAID 1500
LOWER PAID 1100

LOWER PAID 950
SAME 3000
LOWER PAID 1300

14 rows selected.

SQL> select case
when sal>1000 and sal<2000 then
‘A’
when sal>2000 and sal<5000 then
‘B’
else
‘C’
end as “EXMP”,sal from emp order by exmp ;

A 1500
A 1250
A 1300
A 1100
A 1250
A 1600
B 3000
B 2850
B 2975
B 2450
B 3000

C 950
C 800
C 5000

The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1

SQL> select nullif(‘DEBASIS’,’DEBASIS’) from dual;

NULLIF(
——-

SQL> select nullif(‘DEBASIS’,’DEBASISM’) from dual;

NULLIF(
——-
DEBASIS

SQL> select to_char(1234,’$0,000.00MI’) from dual;

TO_CHAR(12
———-
$1,234.00

The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

SQL> select comm,sal from emp;

COMM SAL
———- ———-
800
300 1600
500 1250
2975
1400 1250
2850
2450
3000
5000
0 1500
1100

950
3000
1300

SQL> select greatest(1,2,3) from dual;

GREATEST(1,2,3)
—————
3

SQL> select least(1,2,3) from dual;

LEAST(1,2,3)
————
1

GROUP BY FUNCTION

SQL> SELECT COUNT(*) FROM EMP GROUP BY JOB;

COUNT(*)
———
2
4
3
1
4

SQL> SELECT SUM(SAL),JOB,MIN(SAL),MAX(SAL) FROM EMP GROUP BY JOB;

SUM(SAL) JOB MIN(SAL) MAX(SAL)
——— ——— ——— ———
6000 ANALYST 3000 3000
4150 CLERK 800 1300
8275 MANAGER 2450 2975
5000 PRESIDENT 5000 5000
5600 SALESMAN 1250 1600

SQL>SELECT SUM(SAL),JOB,MIN(SAL),MAX(SAL) FROM EMP GROUP BY JOB HAVING SUM(SAL)>5000

SUM(SAL) JOB MIN(SAL) MAX(SAL)
——— ——— ——— ———
6000 ANALYST 3000 3000
8275 MANAGER 2450 2975
5600 SALESMAN 1250 1600

SQL> SELECT DECODE(SUM(SAL),6000,’DEB’,SUM(SAL)) FROM EMP GROUP BY JOB

DECODE(SUM(SAL),6000,’DEB’,SUM(SAL))
—————————————-
DEB
4150
8275
5000
5600

JOINING

SQL> select empno,ename,emp.deptno,dname from emp,dept where emp.deptno=dept.deptno and emp.deptno=20;

EMPNO ENAME DEPTNO DNAME
——— ———- ——— ————–
7369 SMITH 20 RESEARCH
7566 JONES 20 RESEARCH
7788 SCOTT 20 RESEARCH
7876 ADAMS 20 RESEARCH
7902 FORD 20 RESEARCH

sql>select * from scott.emp natural join scott.dept where deptno>20;

sql>select * from scott.emp join scott.dept using(deptno);

SQL> SELECT EMPNO,ENAME,D.DEPTNO,DNAME FROM EMP,DEPT D WHERE EMP.DEPTNO=D.DEPTNO (+) AND ROWNUM<3;

EMPNO ENAME DEPTNO DNAME
——— ———- ——— ————–
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING

SQL> SELECT * FROM EMP
UNION
SELECT * FROM EMP_BAK;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
——— ———- ——— ——— ——— ——— ——— ———
123 DEBASIS
3434 DEBRAJ
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

SQL>SELECT * FROM EMP
INTERSECT
SELECT * FROM EMP_BAK;

SUB QUERIES

SINGLE ROW SUBQUERIES

SQL> select ename,deptno,sal from emp where deptno=(select deptno from dept where loc=’NEW YORK’);

ENAME DEPTNO SAL
———- ———- ———-
CLARK 10 2450
KING 10 5000
MILLER 10 1300

SQL> select ename,job,sal from emp where deptno in (select deptno from dept where dname in(‘ACCOUNTI
NG’,’SALES’));

ENAME JOB SAL
———- ——— ———-
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
MILLER CLERK 1300

9 rows selected.

SQL> select e.ename,e.job,e.sal from emp e where exists(select d.deptno from dept d where d.loc=’NEW
YORK’ and d.deptno=e.deptno);

ENAME JOB SAL
———- ——— ———-
CLARK MANAGER 2450
KING PRESIDENT 5000
MILLER CLERK 1300

SQL> select empno,ename,sal from emp where sal=(select min(sal) from emp);

EMPNO ENAME SAL
———- ———- ———-
7369 SMITH 800

MULTI-ROWS SUBQUERIES

SQL> select ename,job,sal from emp where deptno in (select deptno from dept where dname in (‘ACCOUNT
ING’,’SALES’));

ENAME JOB SAL
———- ——— ———-
ALLEN SALESMAN 1600
WARD SALESMAN 1250
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
KING PRESIDENT 5000
TURNER SALESMAN 1500
JAMES CLERK 950
MILLER CLERK 1300

9 rows selected.

HAVING CLAUSE AND SUBQURIES

SQL> select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>(select sal from emp wh
ere ename=’MARTIN’);

DEPTNO JOB AVG(SAL)
———- ——— ———-
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 MANAGER 2975
30 MANAGER 2850
30 SALESMAN 1400

7 rows selected.

MULTIPLE COLUMN SUBQUERIES

SQL> select deptno,ename,job,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

DEPTNO ENAME JOB SAL
———- ———- ——— ———-
10 KING PRESIDENT 5000
20 SCOTT ANALYST 3000
20 FORD ANALYST 3000
30 BLAKE MANAGER 2850

INLINE VIEWS IN SUBQUERIES

SQL> select e.ename,subq.loc from emp e,(select deptno,loc from dept where loc in(‘NEW YORK’,’DALLAS
‘)) subq where e.deptno=subq.deptno;

ENAME LOC
———- ————-
SMITH DALLAS
JONES DALLAS
CLARK NEW YORK
SCOTT DALLAS
KING NEW YORK
ADAMS DALLAS
FORD DALLAS
MILLER NEW YORK

8 rows selected.

TOP N-QUERIES

SQL> select ename,job,sal,rownum from (select ename,job,sal from emp order by sal) where rownum<=3;

ENAME JOB SAL ROWNUM
———- ——— ———- ———-
SMITH CLERK 800 1
JAMES CLERK 950 2
ADAMS CLERK 1100 3

SELECTING A PARTICULAR ROW WITH ROWNUM

SQL> (select empno,rownum from emp where rownum<=4) minus(select empno,rownum from emp where rownum<=1);

EMPNO ROWNUM
———- ———-
7499 2
7521 3
7566 4

ENTERING VALUES

SQL> select ename,job from emp where empno=&empno;
Enter value for empno: 111

old 1: select ename,job from emp where empno=&empno
new 1: select ename,job from emp where empno=111

no rows selected

FORMATING AS REPORT

SQL> SET HEADSEP !
SQL> TTITLE “EMPLOYEE LIST”
SQL> BTITLE “MADE BY DEBASIS”
SQL> SET PAGESIZE 50
SQL> SET LINESIZE 100
SQL> SET FEEDBACK OFF
SQL> SET UNDERLINE *
SQL> BREAK ON JOB
SQL> COL SAL FORMAT 9,999
SQL> COL ENAME FORMAT A8
SQL> SELECT * FROM EMP ORDER BY JOB ASC;

Thu Apr 22 page 1
EMPLOYEE LIST

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
********** ******** ********* ********** ********* ****** ********** **********
7788 SCOTT ANALYST 7566 19-APR-87 3,000 20
7902 FORD 7566 03-DEC-81 3,000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS 7788 23-MAY-87 1,100 20
7934 MILLER 7782 23-JAN-82 1,300 10
7900 JAMES 7698 03-DEC-81 950 30
7566 JONES MANAGER 7839 02-APR-81 2,975 20
7782 CLARK 7839 09-JUN-81 2,450 10
7698 BLAKE 7839 01-MAY-81 2,850 30
7839 KING PRESIDENT 17-NOV-81 5,000 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1,600 300 30
7654 MARTIN 7698 28-SEP-81 1,250 1400 30
7844 TURNER 7698 08-SEP-81 1,500 0 30
7521 WARD 7698 22-FEB-81 1,250 500 30
1111 DEBASIS

MADE BY DEBASIS
SAVING CUSTOMIZATIONS

SQL> STORE SET DEB.OUT
Created file DEB.OUT
SQL> GET DEB.OUT

CREATING ORACLE DATABASE OBJECTS

CREATING TEMPORARY TABLES

SQL>CREATE GLOBAL TEMPORARY TABLE TEMP_EMP (EMPNO NUMBER,ENAME VARCHAR2(10))

CREATING ONE TABLE WITH DATA FROM ANOTHER

SQL> create table emp_copy(empno,sal) as select empno,sal from emp;

ADDING AND MODIFYING AND DROPPING COLUMNS

SQL> alter table employee add (hire_date date);
SQL> alter table products modify(lastname varchar2(30));
SQL> alter table employee set unused column salary;
SQL> alter table employee drop unused columns;
SQL> alter table employee drop column salary;

DROPPING,RENAMING AND TRUNCATING TABLES

SQL> drop table emp;
SQL>drop table emp cascade constraints;
SQL> truncate table emp;

RENAMING TABLE

SQL> rename emp to emp_bak;
SQL>alter table emp rename to emp_bak;

COMMENTING OBJECTS

SQL> comment on table emp is
‘This is a table containing employees’;

CREATING TABLE

SQL>CREATE TABLE EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

SQL>CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2) CHECK (SAL <=10000),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
CONSTRAINT PK_EMP
PRIMARY KEY ( EMPNO )
USING INDEX PCTFREE 10
STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )
TABLESPACE SYSTEM)
TABLESPACE SYSTEM PCTUSED 40 PCTFREE 10
STORAGE(INITIAL 12288 NEXT 12288 PCTINCREASE 50 )
PARALLEL (DEGREE 1 INSTANCES 1) NOCACHE;

SQL>CREATE TABLE EMP (
ENAME VARCHAR2(90);
EMPNO NUMBER(20);
CONSTRAINT PK_NAMES PRIMARY KEY (ENAME,EMPNO));

ADDING INTEGRITY CONSTRAINTS TO EXISTING TABLES

SQL> alter table emp add constraints pk_emp_01 primary key(empno);
SQL> alter table emp add constraints fk_emp_01 foreign key (deptno) references dept(deptno);
SQL> alter table emp add constraints uk_emp_o1 unique(empno);
SQL> alter table emp modify (ename not null);

DISABLING CONSTRAINTS

SQL> alter table emp disable primary key ;
SQL> alter table emp disable constraint pk_emp_01;
SQL> alter table emp disable primary key cascade;

ENABLING A DISABLED CONSTRAINTS

SQL> alter table department enable primary key;
SQL> alter table emp enable uk_emp_01;

WHEN EXISTING DATA IN A COLUMN VIOLATES A DISABLED CONSTRAINT

SQL> @@$ORACLE_HOME/rdbms/admin/utlexcpt;

Let us assume that there is a table where primary key constraint is enabled and we have inserted two rows with different values.

SQL>create table example_1(col1 number);
SQL>insert into example_1 values(10);
SQL> insert into example_1 values(1);
SQL>alter table example_1 add(constraint pk_01 primary key(col1));

Now we have disabled the constraint

SQL>alter table example_1 disable constraint pk_01;
SQL>insert into example_1 values(1);

Now the rows that causes unique constraint violation will be inserted into exception table

SQL> alter table exp_1 enable constraint pk_01 exceptions into exceptions;
SQL>select e.row_id,a.col1 from exceptions e,example_1 a where e.row_id=a.rowid;

USING DEFFERED CONSTRAINT

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE dept (
deptno NUMBER(2) NOT NULL
, dname CHAR(14)
, loc CHAR(13)
, CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

INSERT INTO dept VALUES (10,’FINANCE’,’PITTSBURGH’);
INSERT INTO dept VALUES (20,’SALES’,’NEW YORK’);
INSERT INTO dept VALUES (30,’OPERATIONS’,’BOSTON’);

COMMIT;

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno)
);

INSERT INTO emp VALUES (1001, ‘JEFF’, ‘PRESIDENT’, 10);
INSERT INTO emp VALUES (1002, ‘MELODY’, ‘MANAGER’, 30);
INSERT INTO emp VALUES (1003, ‘MARK’, ‘MANAGER’, 10);
INSERT INTO emp VALUES (1004, ‘MARTIN’, ‘MANAGER’, 20);

COMMIT;

REM — ——————————-
REM — Try to delete from parent table
REM — ——————————-

DELETE FROM dept WHERE deptno = 10;

ERROR at line 1:
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found

REM — ——————————————————–
REM — Set this transaction to defer all deferrable constraints
REM — ——————————————————–

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM dept WHERE deptno = 10;

1 row deleted.

COMMIT;

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated – child record found

REMOVING CONSTRAINTS

SQL> alter table employee drop unique (govt_id);
SQL> alter table employee drop primary key cascade;
SQL> alter table employee drop constraint ck_emp_01;

MANIPULATING ORACLE DATA

ADDNING NEW ROWS TO TABLE

SQL>INSERT INTO EMPLOYEE(EMPID,LASTNAME,FIRSTNAME,SALARY,DEPT,HIRE_DATE) VALUES (‘39334’,’SMITH’,’GINA’,75000,NULL,’15-MAR-97’);

(YOU HAVE TO DECLARE THE COLUMN NAME EXPLICITLY OTHERWISE YOU HAVE TO PROVIDE ALL THE VALUES)

SQL>INSERT INTO EMPLOYEE(‘0223’,’WALIA’,’RAJENDRA’,60000,’01-JAN-2006’,NULL);
(YOU HAVE TO INSERT NULL VALUE IF ANY COLUMN IS NOT REQUIRED DATA)

INSERTING FROM OTHER TABLE

SQL>INSER INTO EMP (SELECT * FROM EMP_BAK);

SPECIFYING EXPLICIT DEFAULT VALUES

SQL>INSERT INTO SAMPLE (COL1) VALUES (1);(DEFAULT VALUE WILL BE INSERTED AUTOMATICALLY);

SQL>INSERT INTO SAMPLE (COL1,COL2) VALUES (2,DEFAULT);

UPDATING AND DELETING

SQL>UPDATE EMP SET FIRST_NAME=’DEB’,LAST_NAME=’MAITY’ WHERE EMPID=1;

SQL>UPDATE EMP SET LAST_NAME=(SELECT ENAME FROM EMPLOYEE WHERE EMPID=2) WHERE EMPID=1;

SQL>DELETE FROM EMP(ALL DATA WILL BE DELETED!!!)

SQL>DELETE FROM EMP WHERE ROWNUM<2;

THE IMPORTANCE OF TRANSACTION CONTROL

SQL>commit;

SQL>ROLLBACK;

SAVEPOINT

SQL> UPDATE EMP SET SAL=5000 WHERE ENAME=’FORD’;

1 row updated.

SQL> SAVEPOINT DEB;

Savepoint created.

SQL> UPDATE EMP SET SAL=6000 WHERE ENAME=’FORD’;

1 row updated.

SQL> ROLLBACK TO SAVEPOINT DEB;

Rollback complete.

SQL> COMMIT;

Commit complete.

SQL> SELECT SAL FROM EMP WHERE ENAME=’FORD’;

SAL
———-
5000

CREATING UNDERLYING TABLE DATA THROUGH SIMPLE VIEWS

SQL>CREATE VIEW EMP_VIEW AS (SELECT * FROM EMP WHERE JOB=’CLERK’);

SQL>create view my_view as (select * from emp) order by empno;

SQL>create view my_view as (select distinct(job) as my_jobs from emp);

SQL>CREATE OR REPLACE VIEW EMP_VIEW AS ( SELECT EMPNO,ENAME,DECODE(ENAME,USER,SAL,’KING’,SAL,0) AS SAL,DECODE(ENAME,USER,COMM,’KING’,SAL,0) AS COMM,DEPTNO FROM EMP;

TO RESTRICT VIEW FROM MODIFICATION

SQL>create or replace view emp_view as (select empno,ename,job,deptno from emp where deptno=10) with check option constraint emp_view_constraint;

SQL>select constraint_name,constraint_type from user_constraints;

SQL>create or replace view emp_view as (select * from emp) with read only;

COMPLEX VIEW

SQL>create or replace view emp_dept_view as (select empno,ename,job,loc from emp e,dept d where e.deptno=d.deptno);

SQL>select column_name,updatable from user_updatable_columns where table_name=’EMP_DEPT_VIEW’;

MODIFYING AND REMOVING VIEWS

SQL>alter view emp_dept_view compile;

SQL>select object_name,status from user_objects where object_name=’PROFITS_VIEW’;

SQL>drop view profits_view;

SEQUENCES

SQL>create sequence count_20 start with 20 increment by 1 maxvalue 20 minvalue 0 cycle order cache 2;

SQL>select count_20.nextval from dual;

SQL>selct count_20.currval from dual;

SQL>Insert into expense(exense_no,empid) values(count_20.nextval,2345);

SQL>update product set product_num=count_20.currval where serial_num=123;

Modifying Sequences

SQL>alter sequence count_20 increment by –4;
SQL>Alter sequence count_20 nocycle;

INDEXES

SQL>create unique index emp_empno_01 on emp(deptno);
SQL>create index emp_sal_01 on (sal);
SQL>create unique index employee_ename_index_01 on emp(empno,ename);
SQL>create index emp_ename_reverse_indx on emp(ename) reverse;
SQL>create bitmap index emp_deptno_indx_01 on emp(deptno);
SQL>create index ixd_emp_01 on emp(sal*1.08);
SQL>alter session set query_rewritable_enabled=true;
SQL>drop index employee_list_indx;

View
1.user_indexes;
2.user_ind_columns

SYNONYMS

SQL>create synonym emp for scott.emp;
SQL>drop synonym all_my_emps;
SQL>drop public synonym emp;

DELETING DUPLICATE ROW

delete from dup_data where rowid not in (select min(rowid) from dup_data group by col1,col2);

USE OF BIND VARIABLE

conn / as sysdba

GRANT select ON gv_$statname TO uwclass;

conn uwclass/uwclass

CREATE TABLE run_stats (
runid VARCHAR2(15),
name VARCHAR2(80),
value INT);

CREATE OR REPLACE VIEW stats AS
SELECT ‘STAT…’ || a.name NAME, b.value
FROM gv$statname a, gv$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT ‘LATCH.’ || NAME, gets
FROM gv$latch;

CREATE TABLE t (
x INT);

set timing on

ALTER SYSTEM FLUSH SHARED_POOL;

INSERT INTO run_stats
SELECT ‘before’, stats.*
FROM stats;

— not using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 5000
LOOP
EXECUTE IMMEDIATE ‘SELECT ‘ || i || ‘ INTO :b1 FROM dual’
INTO x;
END LOOP;
END;
/

INSERT INTO run_stats
SELECT ‘after 1’, stats.*
FROM stats;

— using bind variables
DECLARE
x NUMBER(10);
BEGIN
FOR i IN 1 .. 5000
LOOP
EXECUTE IMMEDIATE ‘SELECT :b1 FROM dual’
INTO x
USING i;
END LOOP;
END;
/

INSERT INTO run_stats
SELECT ‘after 2’, stats.*
FROM stats;

col name format a35

SELECT a.name, b.value-a.value RUN1,
c.value-b.value RUN2,
((c.value-b.value)-(b.value-a.value)) DIFF
FROM run_stats a, run_stats b, run_stats c
WHERE a.name = b.name
AND b.name = c.name
AND a.runid = ‘before’
AND b.runid = ‘after 1’
AND c.runid = ‘after 2’
AND (c.value-a.value) > 0
AND (c.value-b.value) <> (b.value-a.value)
ORDER BY ABS((c.value-b.value)-(b.value-a.value));

SELECT sql_fulltext
FROM gv$sql s, gv$sqltext_with_newlines n
WHERE s.hash_value = n.hash_value
AND n.sql_text LIKE ‘%dual%’
ORDER BY last_active_time;

HANDLING LARGE OBJECTS

1.Create table as following structure

CREATE TABLE BOOK_SAMPLES
(
BOOK_SAMPLE_ID NUMBER(10),
ISBN CHAR(10 BYTE),
DESCRIPTION CLOB,
BOOK_COVER BLOB,
CHAPTER BFILE
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
LOB (BOOK_COVER) STORE AS
( TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
LOB (DESCRIPTION) STORE AS
( TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 0
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
MONITORING;

ALTER TABLE BOOK_SAMPLES ADD (
PRIMARY KEY
(BOOK_SAMPLE_ID)
USING INDEX
TABLESPACE USERS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
));

2.Create external LOB storage for storing BFILE

create directory book_sample_loc as ‘c:/oracle’

3.Inserting data into table

INSERT INTO DEB.BOOK_SAMPLES (
BOOK_SAMPLE_ID, ISBN, DESCRIPTION,
BOOK_COVER, CHAPTER)
VALUES (1 ,’07123455′ ,’This is test book’,empty_blob(),bfilename(‘book_sample_loc’,’test.jpeg’));