Question

Assignment 3 Note:

Tables referred in this assignment are same as that of Assignment 2.

1. For each table, append your student id along with the table name. For e.g. employee_student id (employee_T16363737)

2. Format for questions: a. Question: b. SQL statement solution c. Screenshot for correct input d. Screenshot for violation (if any)

Q1) Check the structure of tables.

Q2) Check the constraint name for applied constraints?

Q3) Drop the unique constraint on ENAME

Q4) Drop the Foreign Key constraint on DEPTNO

Q5) Add Foreign Key constraint on DEPTNO

Q6) Change Data type of ENAME. (If previously you have marked it as VARCHAR 2, then make it CHAR or vice versa)

Q7) Change width of DNAME ( For e.g. If you have defined it’s size has 20 then make it 30)

Q8) Add COMM column in EMP table

Q9) Drop CITY column from S table

Q10) Create duplicate copy of EMP table

Q11) Copy structure of DEPT table in another table with different column names

Q12) Change the name and job of the employee whose EMPNO =100

Q13) Delete the record of employee who belong to computer department

Q14) Drop DEPT Table

Q15) Drop duplicate table of EMP table

Answer 1

1)

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME='tableName';   //for one particular table

OR

SELECT TABLE_NAME FROM   INFORMATION_SCHEMA.TABLES // it will return all the tables in the database that we are querying.

2)

    SELECT CONSTRAINT_NAME, SEARCH_CONDITION AS CONSTRAINT_TYPE

    FROM USER_CONSTRAINTS

    WHERE TABLE_NAME='XYZ’;

3)

(ALTER TABLE table_name DROP CONSTRIANT constraint name;) ALTER TABLE EMP DROP CONSTRAINT ENAME;

4)

ALTER TABLE EMP

DROP CONSTRAINT FK_name;

5)

Suppose we need to create a foreign key constraint on “deptno” when EMP table is already created,        ALTER TABLE EMP

ADD FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO);

6)

Changing datatype of column ENAME varchar to char in table EMP

ALTER TABLE EMP

ALTER COLUMN ENAME CHAR(size);

7)

Changing size of column DNAME from 20 to 30 in table DEPT

ALTER TABLE DEPT

ALTER DNAME VARCHAR (30);

8)

Adding COMM column in table EMP

    ALTER TABLE EMP

    ADD (COMM);

9)

ALTER TABLE S

DROP COLUMN CITY;

10)

     CREATE TABLE copy_emp SELECT * FROM EMP;

11)
   SELECT * INTO copy_dept  FROM DEPT;  ( Its only create copy of dept table)

     INSERT INTO copy_dept(Dept_Name, Department) SELECT DName, Dpt FROM DEPT;

12)

    SELECT name, job FROM EMP WHERE EMPNO=100;

13)

   DELETE From EMP

    WHERE DEPARTMENT_NAME = 'COMPUTER';

14)

    DROP TABLE DEPT;

15)

   DROP TABLE copy_emp;