Oracle Sql - Questions and Answers
75. Create table dept1 as below with following columns : DEPTNO Integer PK,
Dname Varchar2(30) Not Null, Loc Varchar2(30) Not Null.
CREATE TABLE DEPT1 (DEPTNO NUMBER PRIMARY KEY, DNAME VARCHAR2(30) NOT NULL, LOC VARCHAR2(30) NOT NULL); Table created.
76. Create table emp1 with the following columns :
Empno Integer Pk,
Ename Varchar2(20) Not Null,
Sal Integer(10, 2) check(sal>5000),
mgr Integer FK(Empno),
Deptno Integer FK(Deptno).
CREATE TABLE EMP1 (EMPNO NUMBER PRIMARY KEY, ENAME VARCHAR2(20) CONSTRAINT CONS_NOT_NULL NOT NULL, SAL NUMBER(10,2) CHECK(SAL>5000), MGR NUMBER CONSTRAINT CONS_MGR REFERENCES EMP(EMPNO), DEPTNO NUMBER CONSTRAINT CONS_DEPTNO REFERENCES DEPT(DEPTNO)); Table created.
77. Create table Dept11, Emp11 from Dept1, Emp1.
CREATE TABLE DEPT11 AS (SELECT * FROM DEPT1); Table created. CREATE TABLE EMP11 AS (SELECT * FROM EMP1); Table created.
78. Add a new column Address-Varchar2(30) in the Emp1 table.
ALTER TABLE EMP1 ADD(ADDRESS VARCHAR2(30)); Table altered.
79. Rename Sal column in Emp1 table to SALARY.
ALTER TABLE EMP1 RENAME COLUMN SAL TO SALARY; Table altered.
80. Rename the FK constraint names and verify the names for the
Emp1.
ALTER TABLE EMP1 RENAME CONSTRAINT CONS_MGR TO CONSTRAINT_MGR; Table altered. ALTER TABLE EMP1 RENAME CONSTRAINT CONS_DEPTNO TO CONSTRAINT_DEPTNO; Table altered. SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMP1' AND CONSTRAINT_TYPE='R' ; CONSTRAINT_NAME ------------------------------ CONSTRAINT_MGR CONSTRAINT_DEPTNO
81. Modify Ename column by increasing the length of the column to
40.
ALTER TABLE EMP1 MODIFY(ENAME VARCHAR2(40)); Table altered.
82. Drop the Not Null constraint for Ename.
ALTER TABLE EMP1 DROP CONSTRAINT CONS_NOT_NULL; Table altered.
83. Create comment on Dept1 table as 'Depts of Eswar '.
COMMENT ON TABLE DEPT1 IS 'DEPTS OF ESWAR'; Comment created.
84. Create comment on Deptno of Dept1 as 'Deptno of Eswar '.
COMMENT ON COLUMN DEPT1.DEPTNO IS 'DEPTNO OF ESWAR'; Comment created.
85. Create a comment on Emp1 table as 'Employees of Eswar '.
COMMENT ON TABLE EMP1 IS 'EMPLOYEES OF ESWAR'; Comment created.
86. Create a comment on Empno of Emp1 table as 'Empno of Eswar
'.
COMMENT ON COLUMN EMP1.EMPNO IS 'EMPNO OF ESWAR'; Comment created.
87. Remove all the comment on the tables and columns.
COMMENT ON TABLE EMP1 IS ''; Comment created. COMMENT ON TABLE DEPT1 IS ''; Comment created. COMMENT ON COLUMN DEPT1.DEPTNO IS ''; Comment created. COMMENT ON COLUMN EMP1.EMPNO IS ''; Comment created.
88. Set Sal, Ename columns of Emp1 table aas unused.
ALTER TABLE EMP1 SET UNUSED (SALARY,ENAME); Table altered.
89. Drop the unused columns.
ALTER TABLE EMP1 DROP UNUSED COLUMNS; Table altered.
90. Drop Emp1, Dept1 Tables.
DROP TABLE EMP1; Table dropped. DROP TABLE DEPT1; Table dropped.
91. Create Table Emp1 from Emp table by copying data also. Check
your data.
CREATE TABLE EMP1 AS (SELECT * FROM EMP); Table created. SELECT * FROM EMP1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7839 KING PRESIDENT 17-NOV-81 5500 10 7698 BLAKE MANAGER 7839 01-MAY-81 3000 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7566 JONES MANAGER 7839 02-APR-81 2975 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1900 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected.
92. Rename Emp1 to EMP_TEST.
ALTER TABLE EMP1 RENAME TO EMP_TEST; Table altered.
93. Truncate EMP_TEST table and confirm your delete.
TRUNCATE TABLE EMP_TEST; Table truncated. SELECT * FROM EMP_TEST; no rows selected
94. Create Emp2 from Emp by only copying Empno, Ename, Sal columns
by copying Data.
CREATE TABLE EMP2 AS (SELECT EMPNO,ENAME,SAL FROM EMP); Table created.
95. Drop Emp2 Table.
DROP EMP2; Table dropped.