UpdateDelete

SQL> UPDATE Emp
2 SET
3 Comm = NULL
4 WHERE Job = 'CLERK';

4 rows updated.

SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3* Sal = NULL
4 /

14 rows updated.

SQL> SELECT Ename, Sal FROM Emp;

ENAME SAL
---------- ----------
KING
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
SCOTT
ADAMS
MILLER

14 rows selected.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT Ename, Sal FROM Emp;

ENAME SAL
---------- ----------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250
ALLEN 1600
TURNER 1500
JAMES 950
WARD 1250
FORD 3000
SMITH 800
SCOTT 3000
ADAMS 1100
MILLER 1300

14 rows selected.

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Ename = 'ALLEN';

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
ALLEN SALESMAN 1600 30

SQL> UPDATE Emp
2 SET
3 Job = 'MANAGER',
4 Sal = 2500,
5 Deptno = 10
6 WHERE Ename = 'ALLEN';

1 row updated.

SQL> SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Ename = 'ALLEN';

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
ALLEN MANAGER 2500 10

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Ename = 'BLAKE';

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
BLAKE MANAGER 2850 30

SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5 WHERE Ename = 'BLAKE';

1 row updated.

SQL> SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Ename = 'BLAKE';

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
BLAKE ANALYST 3850 30

SQL> ED
Wrote file afiedt.buf

1 SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3* WHERE Ename = 'BLAKE'
SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = 1000 + Sal
5* WHERE Ename = 'BLAKE'
SQL> /

1 row updated.

SQL> SELECT Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Ename = 'BLAKE';

ENAME JOB SAL DEPTNO
---------- --------- ---------- ----------
BLAKE ANALYST 4850 30

SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = 1000 + (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'BLAKE'
8 )
9* WHERE Ename = 'BLAKE'
SQL> /

1 row updated.

SQL> ED

Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'BLAKE'
8 ) + 1000
9* WHERE Ename = 'BLAKE'
SQL> /
) + 1000
*
ERROR at line 8:
ORA-00933: SQL command not properly ended


SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = (
5 (
6 SELECT Sal
7 FROM Emp
8 WHERE Ename = 'BLAKE') + 1000
9 )
10* WHERE Ename = 'BLAKE'
SQL> /
WHERE Ename = 'BLAKE') + 1000
*
ERROR at line 8:
ORA-00907: missing right parenthesis

SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = Sal + 1000
5* WHERE Ename = 'BLAKE'
SQL> cl scr

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> SELECT Ename, Job, Sal
2 FROM Emp;

ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
JAMES CLERK 950
WARD SALESMAN 1250
FORD ANALYST 3000
SMITH CLERK 800

ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
ADAMS CLERK 1100
MILLER CLERK 1300

14 rows selected.

SQL> UPDATE Emp
2 SET
3 Job = 'ANALYST',
4 Sal = 1000 + (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'JONES'
8 )
9 WHERE Ename = 'BLAKE';

1 row updated.

SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET
3 Job = (
4 SELECT Job
5 FROM Emp
6 WHERE Ename = 'SMITH'
7 ),
8 Sal = 1000 + (
9 SELECT Sal
10 FROM Emp
11 WHERE Ename = 'JONES'
12 )
13* WHERE Ename = 'BLAKE'
SQL> /

1 row updated.

SQL> cl scr

SQL> ROLLBACK;


Rollback complete.

SQL> UPDATE Emp
2 SET Sal = Sal * 1.10
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE Loc = 'CHICAGO');

6 rows updated.

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> SELECT Empno, Ename, Deptno, Job
2 FROM Emp
3 WHERE Empno = 7788;

EMPNO ENAME DEPTNO JOB
---------- ---------- ---------- ---------
7788 SCOTT 20 ANALYST

SQL> UPDATE Emp
2 SET
3 Deptno = (SELECT Deptno
4 FROM Emp
5 WHERE Empno = 7788)
6 WHERE
7 Job = (SELECT Job
8 FROM Emp
9 WHERE Empno = 7788);

2 rows updated.

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> UPDATE Emp
2 SET
3 (Job, Deptno) = (SELECT Job, Deptno
4 FROM Emp
5 WHERE Empno = 7499)
6 WHERE Empno = 7698;

1 row updated.

SQL> ED
Wrote file afiedt.buf

1 UPDATE Emp
2 SET

3 Job = (SELECT Job
4 FROM Emp
5 WHERE Empno = 7499),
6 Deptno = (SELECT Deptno
7 FROM Emp
8 WHERE Empno = 7499)
9* WHERE Empno = 7698
SQL> /

1 row updated.

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> UPDATE Emp E1
2 SET Deptno = (SELECT Deptno
3 FROM Dept
4 WHERE Loc = 'DALLAS'),
5 (Sal, Comm) = (SELECT 1.1 * AVG (Sal),
6 1.5 * AVG ( Comm )
7 FROM Emp E2
8 WHERE E1.Deptno = E2.Deptno)
9 WHERE Deptno IN (SELECT Deptno
10 FROM Dept
11 WHERE Loc = 'NEW YORK' OR
12 Loc = 'BOSTON');

3 rows updated.

SQL> SPOOL OFF
SQL> cl scr

SQL> SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 20;

ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
FORD 3000 20
SMITH 800 20
SCOTT 3000 20
ADAMS 1100 20

SQL> UPDATE Emp
2 SET Sal = Sal + 1000
3 WHERE Deptno = 20;

5 rows updated.

SQL> SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 20;

ENAME SAL DEPTNO
---------- ---------- ----------
JONES 3975 20
FORD 4000 20
SMITH 1800 20
SCOTT 4000 20
ADAMS 2100 20

SQL> SELECT Deptno, SUM(Sal)
2 FROM Emp
3 WHERE Deptno = 20
4 GROUP BY Deptno;

DEPTNO SUM(SAL)
---------- ----------
20 15875

SQL> ROLLBACK;

Rollback complete.

SQL> VARIABLE Dept20SalSum NUMBER
SQL> UPDATE Emp
2 SET Sal = Sal + 1000
3 WHERE Deptno = 20
4 RETURNING SUM(Sal) INTO :Dept20SalSum;

5 rows updated.

SQL> PRINT Dept20SalSum;

DEPT20SALSUM
------------
15875

SQL> cl scr

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 20;

ENAME SAL DEPTNO
---------- ---------- ----------
JONES 2975 20
FORD 3000 20
SMITH 800 20
SCOTT 3000 20
ADAMS 1100 20

SQL> cl scr

SQL> CREATE TABLE MyBonus
2 (

3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );
CREATE TABLE MyBonus
*
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> DROP PRCEDURE MyBonus;
DROP PRCEDURE MyBonus
*
ERROR at line 1:
ORA-00950: invalid DROP option


SQL> DROP PROCEDURE MyBonus;

Procedure dropped.

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> cl scr

SQL> CREATE TABLE MyBonus
2 (
3 Empno NUMBER,
4 Bonus NUMBER DEFAULT 100
5 );

Table created.

SQL> SELECT * FROM MyBonus;

no rows selected

SQL> INSERT INTO MyBonus(Empno)
2 (SELECT E.Empno
3 FROM Emp E
4 WHERE Job = 'SALESMAN');

4 rows created.

SQL> SELECT * FROM MyBonus;

EMPNO BONUS
---------- ----------
7654 100
7499 100
7844 100
7521 100

SQL> SELECT Empno, Sal, Deptno
2 FROM Emp
3 WHERE Deptno = 30;


EMPNO SAL DEPTNO
---------- ---------- ----------
7698 2850 30
7654 1250 30
7499 1600 30
7844 1500 30
7900 950 30
7521 1250 30

6 rows selected.

SQL> MERGE INTO MyBonus B
2 USING (SELECT Empno, Sal, Deptno
3 FROM Emp
4 WHERE Deptno = 30) S
5 ON (B.Empno = S.Empno)
6 WHEN MATCHED THEN
7 UPDATE
8 SET B.Bonus = B.Bonus + S.Sal * 0.1
9 DELETE
10 WHERE (S.Sal > 4000)
11 WHEN NOT MATCHED THEN
12 INSERT(B.Empno, B.Bonus)
13 VALUES(S.Empno, S.Sal * 0.1)
14 WHERE(S.Sal <= 4000)
15 /

6 rows merged.

SQL> SELECT * FROM MyBonus;

EMPNO BONUS
---------- ----------
7654 225
7499 260
7844 250
7521 225
7698 285
7900 95

6 rows selected.

SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1234, 'SAMPLE01', 30, 3750);

1 row created.

SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1235, 'SAMPLE02', 30, 4050);

1 row created.

SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1236, 'SAMPLE03', 30, 3550);

1 row created.


SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1237, 'SAMPLE04', 30, 4250);

1 row created.

SQL> UPDATE Emp
2 SET
3 Sal = Sal + 2000
4 WHERE Empno = 7698;

1 row updated.

SQL> UPDATE Emp
2 SET
3 Sal = Sal + 2700
4 WHERE Empno = 7499;

1 row updated.

SQL> MERGE INTO MyBonus B
2 USING (SELECT Empno, Sal, Deptno
3 FROM Emp
4 WHERE Deptno = 30) S
5 ON (B.Empno = S.Empno)
6 WHEN MATCHED THEN
7 UPDATE
8 SET B.Bonus = B.Bonus + S.Sal * 0.1
9 DELETE
10 WHERE (S.Sal > 4000)
11 WHEN NOT MATCHED THEN
12 INSERT(B.Empno, B.Bonus)
13 VALUES(S.Empno, S.Sal * 0.1)
14 WHERE(S.Sal <= 4000)
15 /

8 rows merged.

SQL> SELECT * FROM MyBonus;

EMPNO BONUS
---------- ----------
7654 350
7844 400
7521 350
7900 190
1236 355
1234 375

6 rows selected.

SQL> cl scr

SQL> DELETE FROM Emp;

18 rows deleted.

SQL> SELECT * FROM Emp;

no rows selected

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM Emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10

7698 BLAKE MANAGER 7839 01-MAY-81 2850
30

7782 CLARK MANAGER 7839 09-JUN-81 2450
10


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

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30

7900 JAMES CLERK 7698 03-DEC-81 950
30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000

20

7369 SMITH CLERK 7902 17-DEC-80 800
20

7788 SCOTT ANALYST 7566 09-DEC-82 3000
20


EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7876 ADAMS CLERK 7788 12-JAN-83 1100
20

7934 MILLER CLERK 7782 23-JAN-82 1300
10

14 rows selected.

SQL> cl scr

SQL> DELETE Emp;

14 rows deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> DELETE FROM Emp
2 WHERE Deptno = 30;

6 rows deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> DELETE FROM Emp
2 WHERE Deptno = (
3 SELECT Deptno
4 FROM Dept
5 WHERE Dname = 'SALES'
6 );

6 rows deleted.

SQL> DELETE FROM Emp
2 WHERE Deptno = 10;
DELETE FROM Emp
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.EMP_SELF_KEY) violated - child record
found



SQL> SPOOL OFF

SQL> cl scr

SQL> CREATE TABLE ExamTimeTable
2 (
3 ExamName VARCHAR2(30),
4 ExamTime VARCHAR2(12),
5 CONSTRAINT ExamNamePK PRIMARY KEY(ExamName)
6 );

Table created.

SQL> SELECT * FROM ExamTimeTable;

no rows selected

SQL> INSERT INTO ExamTimeTable
2 VALUES ('PHYSICAL SCIENCES' , '9:00 AM');

1 row created.

SQL> MERGE INTO ExamTimeTable E1
2 USING ExamTimeTable E2
3 ON
4 (E2.ExamName = E1.ExamName AND
5 E1.ExamName = 'PHYSICAL SCIENCES')
6 WHEN MATCHED THEN
7 UPDATE
8 SET E1.ExamTime = '10:30 AM'
9 WHEN NOT MATCHED THEN
10 INSERT(E1.ExamName, E1.ExamTime)
11 VALUES('PHYSICAL SCIENCES' , '10:30 AM')
12 /

1 row merged.

SQL> SELECT * FROM ExamTimeTable;

EXAMNAME EXAMTIME
------------------------------ ------------
PHYSICAL SCIENCES 10:30 AM

SQL> MERGE INTO ExamTimeTable E1
2 USING ExamTimeTable E2
3 ON
4 (E2.ExamName = E1.ExamName AND
5 E1.ExamName = 'CHEMICAL SCIENCES')
6 WHEN MATCHED THEN
7 UPDATE SET E1.ExamTime = '12:30 PM'
8 WHEN NOT MATCHED THEN
9 INSERT(E1.ExamName, E1.ExamTime )
10 VALUES('CHEMICAL SCIENCES' , '12:30 PM');

1 row merged.


SQL> SELECT * FROM ExamTimeTable;

EXAMNAME EXAMTIME
------------------------------ ------------
PHYSICAL SCIENCES 10:30 AM
CHEMICAL SCIENCES 12:30 PM

SQL> SPOOL OFF
SQL> cl scr

SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp;

ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
KING 5000 10 PRESIDENT
BLAKE 2850 30 MANAGER
CLARK 2450 10 MANAGER
JONES 2975 20 MANAGER
MARTIN 1250 30 SALESMAN
ALLEN 1600 30 SALESMAN
TURNER 1500 30 SALESMAN
JAMES 950 30 CLERK
WARD 1250 30 SALESMAN
FORD 3000 20 ANALYST
SMITH 800 20 CLERK
SCOTT 3000 20 ANALYST
ADAMS 1100 20 CLERK
MILLER 1300 10 CLERK

14 rows selected.

SQL> SELECT * FROM Dept;

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

SQL> INSERT INTO Dept
2 VALUES(50, 'SHIPPING', 'CHENNAI');

1 row created.

SQL> INSERT INTO Dept
2 VALUES(60, 'CARGO', 'MUMBAI');

1 row created.

SQL> SELECT * FROM Dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI
60 CARGO MUMBAI

6 rows selected.

SQL> DELETE FROM Dept
2 WHERE Deptno = 60;

1 row deleted.

SQL> DELETE FROM Emp
2 WHERE Ename = 'SMITH';

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> cl scr

SQL> SELECT * FROM Dept;

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

SQL> COLUMN Empno FORMAT 9999
SQL> COLUMN MGR FORMAT 9999
SQL> COLUMN Deptno FORMAT 99
SQL> SELECT * FROm Emp;

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ---------- ---------- ------

7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> cl scr

SQL> SHOW USER
USER is "SCOTT"
SQL> GRANT SELECT, INSERT, UPDATE, DELETE
2 ON Dept TO SampTR;

Grant succeeded.

SQL> SELECT * FROM Dept;

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

SQL> INSERT INTO Dept
2 VALUES(50, 'SHIPPING', 'CHENNAI');

1 row created.

SQL> SELECT * FROM Dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI

SQL> /

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SHIPPING CHENNAI

SQL> SELECT * FROM Dept;

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

50 SHIPPING CHENNAI

SQL> INSERT INTO Dept
2 VALUES(60, 'CARGO', 'MUMBAI');
INSERT INTO Dept
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEPT_PRIMARY_KEY) violated


SQL> SELECT * FROM Dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 CARGO MUMBAI
50 SHIPPING CHENNAI

6 rows selected.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM Dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 CARGO MUMBAI

SQL> cl scr

SQL> COMMIT;

Commit complete.

SQL> cl scr

SQL> SELECT * FROm Dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 CARGO MUMBAI
50 SHIPPING CHENNAI

6 rows selected.


SQL> SAVEPOINT DeptDel01;

Savepoint created.

SQL> DELETE FROM Dept
2 WHERE Deptno = 60;

1 row deleted.

SQL> SAVEPOINT DeptDel02;

Savepoint created.

SQL> DELETE FROM Dept
2 WHERE Deptno = 50;

1 row deleted.

SQL> SAVEPOINT EmpIns01;

Savepoint created.

SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1234, 'SAMPLE01', 30, 2000);

1 row created.

SQL> SAVEPOINT EmpDel02;

Savepoint created.

SQL> DELETE FROM Emp
2 WHERE Ename = 'SMITH';

1 row deleted.

SQL> ROLLBACK TO SAVEPOINT EmpDel02;

Rollback complete.

SQL> ROLLBACK TO SAVEPOINT DeptDel02;

Rollback complete.

SQL> cl scr

SQL> CREATE TABLE SampleALT
2 (
3 SampID NUMBER(2)
4 CONSTRAINT SampleALT-SampID-PK PRIMARY KEY
5
SQL> ED
Wrote file afiedt.buf

1 CREATE TABLE SampleALT
2 (

3 SampID NUMBER(2)
4* CONSTRAINT SampleALT_SampID_PK PRIMARY KEY
SQL> ED
Wrote file afiedt.buf

1 CREATE TABLE SampleALT
2 (
3 SampID NUMBER(2)
4 CONSTRAINT SampleALT_SampID_PK PRIMARY KEY
5* )
SQL> /

Table created.

SQL> ALTER TABLE SampleALT
2 ADD
3 (
4 SampName VARCHAR2(10)
5 CONSTRAINT SampleALT_SampName_NN NOT NULL,
6 SampDate DATE
7 );

Table altered.

SQL> cl scr

SQL> DROP TABLE SampleALT;

Table dropped.

SQL> SELECT * FROm TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP_SUM TABLE
MYSUBTOT TABLE
MYBONUS TABLE
MYMASTERDF TABLE
DEPT10 TABLE
INSERTDEPT VIEW
EDEPT30 VIEW
EXAMTIMETABLE TABLE
BIN$VxL2g13AQcO02YkDCYGTyw==$0 TABLE
SAMPF TABLE
DEPT TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE
BONUS TABLE
SALGRADE TABLE
DUMMY TABLE
CUSTOMER TABLE
ORD TABLE
ITEM TABLE
PRODUCT TABLE
PRICE TABLE

SALES VIEW

21 rows selected.

SQL> PURGE RECYCLEBIN;

Recyclebin purged.

SQL> SELECT * FROm TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP_SUM TABLE
MYSUBTOT TABLE
MYBONUS TABLE
MYMASTERDF TABLE
DEPT10 TABLE
INSERTDEPT VIEW
EDEPT30 VIEW
EXAMTIMETABLE TABLE
SAMPF TABLE
DEPT TABLE
EMP TABLE

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
SALGRADE TABLE
DUMMY TABLE
CUSTOMER TABLE
ORD TABLE
ITEM TABLE
PRODUCT TABLE
PRICE TABLE
SALES VIEW

20 rows selected.

SQL> cl scr

SQL> SELECT * FROM Emp;

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- --------- ---------- ---------- ------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> DELETE FROM Emp;

14 rows deleted.

SQL> SELECT * FROM Emp;

no rows selected

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM Emp;

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

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ---------- ---------- ------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

SQL> TRUNCATE TABLE Emp;

Table truncated.

SQL> SELECT * FROM Emp;

no rows selected

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM Emp;

no rows selected

SQL> cl scr


SQL> cl scr

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 NOT NULL NUMBER(2)

SQL> COMMENT ON TABLE Emp
2 IS 'This Table Stores Employees Information';

Comment created.

SQL> COMMNET ON COLUMN Emp.Ename
SP2-0734: unknown command beginning "COMMNET ON..." - rest of line ignored.
SQL> COMMENT ON COLUMN Emp.Ename
2 IS 'This Column Stores The Information for Employee Names';

Comment created.

SQL> COMMENT ON COLUMN Emp.Ename
2 IS '';

Comment created.

SQL> SPOOL OFF

SQL> cl scr

Advance Table

SQL> CREATE TABLE SampDept

2 AS

3 SELECT * FROM Dept

4 /

Table created.

SQL> DESC Dept

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> DESC SampDept

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept;

DEPTNO DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept1(

2 DeptID,

3 DeptName,

4 Place

5 )

6 AS

7 SELECT * FROM Dept

8 /

Table created.

SQL> DESC SampDept1

Name Null? Type

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

DEPTID NOT NULL NUMBER(2)

DEPTNAME VARCHAR2(14)

PLACE VARCHAR2(13)

SQL> SELECT * FROM SampDept1;

DEPTID DEPTNAME PLACE

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> CREATE TABLE SampDept2

2 AS

3 SELECT Deptno, Dname

4 FROM Dept

5 /

Table created.

SQL> DESC SampDept2

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

SQL> CREATE TABLE SampDept3

2 AS

3 SELECT * FROM Dept

4 WHERE 1 = 2

5 /

Table created.

SQL> DESC SampDept3

Name Null? Type

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

DEPTNO NOT NULL NUMBER(2)

DNAME VARCHAR2(14)

LOC VARCHAR2(13)

SQL> SELECT * FROM SampDept3;

no rows selected

SQL> CREATE TABLE SampleTablePart

2 (

3 SampID NUMBER(4)

4 CONSTRAINT SampIDPK PRIMARY KEY,

5 SampName VARCHAR2(20)

6 CONSTRAINT SampNameNN NOT NULL

7 CONSTRAINT SampNameCHK

8 CHECK(SampName = UPPER(SampName)),

9 SampDate DATE DEFAULT SYSDATE

10 CONSTRAINT SampdateNN NOT NULL,

11 SampDesc VARCHAR2(4000)

12 DEFAULT 'NOT PROVIDED'

13 )

14 PARTITION BY RANGE(SampID)

15 (

16 PARTITION SampIDPart1

17 VALUES LESS THAN (500),

18 PARTITION SampIDPart2

19 VALUES LESS THAN (1000),

20 PARTITION SampIDPart3

21 VALUES LESS THAN (2000),

22 PARTITION SampIDPart4

23 VALUES LESS THAN (3000),

24 PARTITION SampIDPart5

25 VALUES LESS THAN (4000),

26 PARTITION SampIDPart6

27 VALUES LESS THAN (MAXVALUE)

28 );

Table created.

SQL> cl scr

CREATE TABLE EmpTableHash

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10, 20, 30, 40, 50, 60, 70, 80, 90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpTableHash(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY HASH(DeptNO)

PARTITIONS 9

CREATE TABLE EmpSampleList1

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK1 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN1 NOT NULL

CONSTRAINT ENameCHK1

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN1 NOT NULL

CONSTRAINT JobCHKUpper1

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList1 CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange1

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList1

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf1

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN1 NOT NULL,

Sal NUMBER(8,2)

DEFAULT 10000

CONSTRAINT SalNN1 NOT NULL

CONSTRAINT SalChkRange1

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7,2)

DEFAULT NULL

CONSTRAINT CommNN1 NOT NULL

CONSTRAINT CommChkRange1

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH1

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK1

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPresident

VALUES('PRESIDENT'),

PARTITION JAnalyst

VALUES('ANALYST'),

PARTITION JManager

VALUES('MANAGER'),

PARTITION JSalesman

VALUES('SALESMAN'),

PARTITION JClerk

VALUES('CLERK')

);

CREATE TABLE EmpSampleList

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN NOT NULL

CONSTRAINT ENameCHK

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN NOT NULL

CONSTRAINT JobCHKUpper

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList CHECK

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2)

CONSTRAINT DeptnoCHKRange

CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf

REFERENCES EmpSampleList(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN NOT NULL

CONSTRAINT SalChkRange

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN NOT NULL

CONSTRAINT CommChkRange

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKHASH

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK

CHECK(Sal + Comm <= 175000)

)

PARTITION BY LIST(Job)

(

PARTITION JPartList1

VALUES(

'PRESIDENT',

'ANALYST'

),

PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN',

'CLERK'

)

);

CREATE TABLE EmpSampleSubPart

(

EmpNo NUMBER(6)

CONSTRAINT EmpNOPK2 PRIMARY KEY,

EName VARCHAR2(20)

DEFAULT 'NOT PROVIDED'

CONSTRAINT ENameNN2 NOT NULL

CONSTRAINT ENameCHK2

CHECK(EName = UPPER(EName)),

Job VARCHAR2(30)

DEFAULT 'NOT MENTIONED'

CONSTRAINT SampdateNN2 NOT NULL

CONSTRAINT JobCHKUpper2

CHECK(Job = UPPER(Job))

CONSTRAINT JobCHKList2

CONSTRAINT JobCHKList CHECK(Job IN('PRESIDENT','ANALYST','MANAGER','SALESMAN','CLERK')),

DeptNo NUMBER(2) CONSTRAINT

DeptnoCHKRange2 CHECK(DeptNo BETWEEN 10 AND 99)

CONSTRAINT DeptnoCHKList2

CHECK(DeptNo IN(10,20,30,40,50,60,70,80,90)),

MGRID NUMBER(6)

CONSTRAINT MGRIDFKSelf2

REFERENCES EmpSampleSubPart(EmpNo)

ON DELETE SET NULL,

HireDate DATE

DEFAULT SYSDATE

CONSTRAINT HireDateNN2 NOT NULL,

Sal NUMBER(8, 2)

DEFAULT 10000

CONSTRAINT SalNN2 NOT NULL

CONSTRAINT SalChkRange2

CHECK(Sal BETWEEN 10000 AND 150000),

Comm NUMBER(7, 2)

DEFAULT NULL

CONSTRAINT CommNN2 NOT NULL

CONSTRAINT CommChkRange2

CHECK(Comm BETWEEN 0 AND 25000),

CONSTRAINT DeptNOFKSubPart2

FOREIGN KEY(DeptNo)

REFERENCES Dept(Deptno)

ON DELETE CASCADE,

CONSTRAINT TotalSalaryCHK2

CHECK(Sal + Comm <= 175000)

)

PARTITION BY RANGE(Ename)

SUBPARTITION BY HASH(Job)

SUBPARTITIONS 5

(

PARTITION NameP1

VALUES LESS THAN('G'),

PARTITION NAMEP2

VALUES LESS THAN('N'),

PARTITION NAMEP3

VALUES LESS THAN('T'),

PARTITION NAMEP4

VALUES LESS THAN(MAXVALUE)

)

/

ALTER TABLE SampleTablePart

SPLIT PARTITION SampIDPart6

AT(5000)

INTO(

PARTITION SampIDPart6,

PARTITION SampIDPart7

)

/

ALTER TABLE EmpSampleList

SPLIT PARTITION JPartList2

VALUES(

'MANAGER',

'SALESMAN'

)

INTO(

PARTITION MANAGER,

PARTITION SALESMAN

)

/

ALTER TABLE EmpSampleList

MERGE

PARTITIONS

MANAGER,

SALESMAN

INTO

PARTITION

JPARTLIST2

/

ALTER TABLE SampleTablePart

MERGE

PARTITIONS

SampIDPart6,

SampIDPart7

INTO

PARTITION

SampIDPart7

/