Oracle Sql - Questions and Answers
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.
Now Flashback the table using RECYCLEBIN NAME using the below command.
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.