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
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
No comments:
Post a Comment