Question

Comprehension

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

NAME	VARCHAR (30)	NOT NULL,
EID	VARCHAR (10)	NOT NULL,
DEPTNO	INT (5)	NOT NULL,
HODEID	VARCHAR (10),
SALARY	INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

DID	INT (5)	NOT NULL,
DNAME	VARCHAR (30)	NOT NULL,
HODID	VARCHAR (10)	NOT NULL,
HODNAME	VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

EMPID	VARCHAR (10)	NOT NULL,
PROJNO	INT (5)	NOT NULL,
PROJECTLOC	VARCHAR (30)	NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

In reference to the above given table structures, which of the following query/queries will drop the ‘SALARY’ column from 'EMPLOYEE' table ?

(A) ALTER TABLE EMPLOYEE DROP SALARY CASCADE;

(B) ALTER TABLE EMPLOYEE DROP SALARY RESTRICT;

(C) ALTER EMPLOYEE DROP SALARY:

Choose the correct answer from the options given below:

1. (A) and (B) only
2. (A) and (C) only
3. (B) and (C) only
4. (A) only

Answer 1

Correct Answer - Option 1 : (A) and (B) only

The correct answer is option 1.

 Alter table Syntax:

ALTER TABLE table-Name    
DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]

•  The keyword COLUMN, CASCADE, RESTRICT are optional. The default is CASCADE. If you use the RESTRICT option, the column drop will be rejected if it would invalidate a dependent schema object. If CASCADE is defined, the column drop should also remove any other schema objects that have become invalid.
• Since the salary field is not present in any other table, CASCADE | RESTRICT has no impact.

• (C) ALTER EMPLOYEE DROP SALARY is incorrect syntax.

∴ Hence the correct answer is  (A) and (B) only.