Database-Management-System

Question 271

Comprehension:

Consider the following table structures related to a university for Q96 to Q100:-

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) REFERENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFERENCES 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 (DANAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID);

PROJECT WORK:

        EMPID VARCHAR (30) NOT NULL,

        PROJNO INT (5) NOT NULL,

        PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNO),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

Q98: 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”

A	(A) and (B) only
B	(A) only
C	(B) and (C) only
D	(C) only

Database-Management-SystemSQLUGC NET JRF November 2020 Paper-2

Question 271 Explanation:

(A): mysql> update employee set salary=salary*1.20
-> where deptno in(select did from department where dname=’science’);
Query OK, 3 rows affected
mysql> select * from employee;

(B): (The dname column is not there in employee table)
mysql> update employee set salary=salary*1.20
-> where dname=’science’;
ERROR : Unknown column ‘dname’ in ‘where clause’
(C): Alter command is not used for updating values.

Correct Answer: B

Question 271 Explanation:

(A): mysql> update employee set salary=salary*1.20
-> where deptno in(select did from department where dname=’science’);
Query OK, 3 rows affected
mysql> select * from employee;

(B): (The dname column is not there in employee table)
mysql> update employee set salary=salary*1.20
-> where dname=’science’;
ERROR : Unknown column ‘dname’ in ‘where clause’
(C): Alter command is not used for updating values.

Database-Management-System

Database-Management-System

Database-Management-System

Database-Management-System

Mandeep Singh