Oracle Sql - Questions and Answers 6

 


Oracle Sql - Questions and Answers


                      Back                                                                                                      Next          


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.