Oracle Sql - Questions and Answers 7

 


Oracle Sql - Questions and Answers


           Back                                                                                                                     Next                       

96. Create Emp2 table again from Emp table without copying data. 

CREATE TABLE EMP2 AS (SELECT * FROM EMP WHERE 1=2);

Table created.



97. Drop Emp2 table. 

DROP EMP2;

Table dropped.



98. Flashback the first Emp2 table that contained data and check your table. 
  
        First, By the below command get the " RECYCLEBIN NAME "  of the table you want to flashback by using the "DROP TIME" column.
SHOW RECYCLEBIN;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DEPT1            BIN$72FJF9brQAi7cy9vu3H3pw==$0 TABLE        2020-10-03:20:37:51
DEPT1            BIN$IpuVwxOSS8iom+ig9Mvxow==$0 TABLE        2020-10-03:19:34:47
EMP1             BIN$1WwwLfEnTBqi8Fcw2ns0LQ==$0 TABLE        2020-10-03:20:37:42
EMP1             BIN$fNdjTdMvSoihYNQmQCcyNA==$0 TABLE        2020-10-03:19:54:13
EMP11            BIN$LKkDVHhwQdKV1W4o9inOEQ==$0 TABLE        2020-10-03:19:55:00
EMP2             BIN$aKivbMHMSMmHzmC5reMqqA==$0 TABLE        2020-10-03:20:45:57
EMP2             BIN$YC2mwd+JT1q1/DdClqduGw==$0 TABLE        2020-10-03:20:44:21
LODEGE_DETAILS   BIN$vaSIg5VIQaelxJIYLPyWyg==$0 TABLE        2020-09-17:22:44:13

        Now Flashback the table using RECYCLEBIN NAME using the below command.
        And check if the tables are back.
FLASHBACK TABLE "BIN$YC2mwd+JT1q1/DdClqduGw==$0" TO BEFORE DROP;

Flashback complete.

SELECT * FROM EMP2;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7839 KING             5500
      7698 BLAKE            3000
      7782 CLARK            2450
      7566 JONES            2975
      7788 SCOTT            3000
      7902 FORD             3000
      7369 SMITH             800
      7499 ALLEN            1900
      7521 WARD             1250
      7654 MARTIN           1250
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7934 MILLER           1300

14 rows selected.



99. Flashback the recently dropped table by renaming to EMP2_1. 

 FLASHBACK TABLE "BIN$aKivbMHMSMmHzmC5reMqqA==$0" TO BEFORE DROP RENAME TO EMP2_1;

Flashback complete.



100. Check your tables. 

SELECT * FROM TAB WHERE TNAME LIKE 'EMP2%';

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP2                           TABLE
EMP2_1                         TABLE



101. Create EmpTest from Emp table by copying structure and data. 

CREATE TABLE EMPTEST AS (SELECT * FROM EMP);

Table created.



102. Add a new row into the EmpTest table for Empno, Ename, Sal columns. In Ename should have the Current user. 
 
INSERT INTO EMPTEST (EMPNO, ENAME, SAL) VALUES(9988, 'UDAY', 6700);

1 row created.



103. Update EmpTest by increasing the Salary of TURNER by 15%. Confirm your changes. 
 
UPDATE EMPTEST SET SAL = SAL + (SAL * 15/100) WHERE ENAME='TURNER';

1 row updated.

COMMIT;

Commit complete.



104. Update the salary of Smith with salary of Scott using the EmpTest table. 
 
 UPDATE EMPTEST SET SAL=(SELECT SAL FROM EMP WHERE ENAME='SCOTT') WHERE ENAME='SMITH';

1 row updated.



105. Increase all the employees salary by 10% in EmpTest table who are working in New York. 
 
UPDATE EMPTEST SET SAL=SAL+(SAL * 10/100) WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOC='NEW YORK');

3 rows updated.



106. Delete all the Comm data from EmpTest Table. 

 ALTER TABLE EMPTEST DROP COLUMN COMM;

Table altered.



107. Delete all the employees from EmpTest table who are working in SALES dept. 
 
DELETE FROM EMPTEST WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');

6 rows deleted.



108. Delete all the employees who are working with that employee, except that employee. ( Prompt for the ENAME ). 
 
 DELETE FROM EMPTEST WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME = '&EMPLOYEENAME');
Enter value for employeename: CLARK
old   1: DELETE FROM EMPTEST WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME = '&EMPLOYEENAME')
new   1: DELETE FROM EMPTEST WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME = 'CLARK')

3 rows deleted.



109. Create Emp2 from Emp by only copying Empno, Ename, Sal without copying data. 
 
CREATE TABLE EMP2 AS (SELECT EMPNO,ENAME,SAL FROM EMP WHERE 1=2);

Table created.



110. Create Emp3 from Emp by only copying Empno, Job without copying data. 
 
 CREATE TABLE EMP3 AS (SELECT EMPNO,JOB FROM EMP WHERE 1=2);

Table created.



111. Using multitable insert Emp data into Emp2 and Emp3 tables. 

INSERT ALL INTO EMP2 (EMPNO, ENAME, SAL) VALUES (EMPNO,ENAME,SAL)
 INTO EMP3 (EMPNO,JOB) VALUES (EMPNO,JOB)
 SELECT EMPNO,ENAME,JOB,SAL FROM EMP

28 rows created.



112. Truncate Emp2 Table and insert following two rows as follows :
        7788, SMITH, 4500 ;    7654, JACK, 3500;

TRUNCATE TABLE EMP2;

Table truncated.

 INSERT INTO EMP2 VALUES(7788, 'SMITH', 4500);

1 row created.

 INSERT INTO EMP2 VALUES(7654, 'JACK', 3500);

1 row created.



113. Commit the data. 
 
COMMIT;

Commit complete.



114. Using Merge statement insert and update Emp2 using Emp. 
 
MERGE INTO EMP2 A
    USING (SELECT EMPNO,ENAME,SAL FROM EMP)B
    ON (A.EMPNO = B.EMPNO)
    WHEN MATCHED THEN
    UPDATE SET A.ENAME=B.ENAME, A.SAL = B.SAL
    WHEN NOT MATCHED THEN
    INSERT (A.EMPNO, A.ENAME, A.SAL ) VALUES (B.EMPNO, B.ENAME, B.SAL)



115. Verify your changes. 
 
SELECT * FROM EMP2;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7654 MARTIN           1250
      7369 SMITH             800
      7499 ALLEN            1900
      7521 WARD             1250
      7566 JONES            2975
      7698 BLAKE            3000
      7782 CLARK            2450
      7839 KING             5500
      7844 TURNER           1500
      7876 ADAMS            1100
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 rows selected.



116. Rollback the Data. 

 ROLLBACK;

Rollback complete.
 


117. Using Merge Statements update Emp2 table for only Empno=7788 and Insert only those employees whose salary is more than 3000. 
 
 MERGE INTO EMP2 A
 USING (SELECT EMPNO,ENAME,SAL FROM EMP) B
 ON (A.EMPNO=B.EMPNO)
 WHEN MATCHED THEN
 UPDATE SET A.ENAME=B.ENAME, A.SAL=B.SAL WHERE A.EMPNO=7788
 WHEN NOT MATCHED THEN
 INSERT (A.EMPNO, A.ENAME, A.SAL ) VALUES (B.EMPNO, B.ENAME, B.SAL) WHERE B.SAL>3000;

2 rows merged.



118. Verify your changes. 

SELECT * FROM EMP2;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7654 JACK             3500
      7839 KING             5500



119. Create a User Eswar. 

 CREATE USER ESWAR IDENTIFIED BY eswar;

User created.



120. Grant all permission on EMP table from SCOTT to Eswar user. 

GRANT ALL ON EMP TO ESWAR;

Grant succeeded.