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),

Refer table, structures given above, University decided to give all employees in the ‘SCIENCE’ department a 20% rise in salary. Which of the following query/queries will compute the above results?

(A)

UPDATE EMPLOYEE

SET SALARY = SALARY*1.20

WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');

(B)

UPDATE TABLE EMPLOYEE

SET SALARY = SALARY*1.20

WHERE DNAME='SCIENCE’;

(C)

ALTER TABLE EMPLOYEE

SET SALARY=SALARY*1.20

WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’)

Choose the correct answer from the options given below:

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

Answer 1

Correct Answer - Option 2 : (A) only

The correct answer is option 1.

Syntax:  

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Option A:

UPDATE EMPLOYEE

SET SALARY = SALARY*1.20

WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');

Option B:

UPDATE TABLE EMPLOYEE SET SALARY = SALARY*1.20 WHERE DNAME='SCIENCE’;

Here, DNAME is not an attribute of an employee table it's an incorrect query.

Option C:

ALTER TABLE EMPLOYEE

SET SALARY=SALARY*1.20

WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’)

Here, the ALTER TABLE statement is used to add, delete, or modify columns in an existing table and used to add and drop various constraints on an existing table. Alter table is mainly used for changing table structure or constraints. Here DNAME is not an attribute of an employee table it's an incorrect query.

∴ Hence the correct answer is (A).