PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Constraints

Update And Delete:

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                                                                            
 
ENAME             SAL                                                            
---------- ----------                                                            
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                                                            
 
ENAME             SAL                                                            
---------- ----------                                                            
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                                           
 
ENAME             SAL     DEPTNO JOB                                             
---------- ---------- ---------- ---------                                       
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  

Go Back