UGC NET CSE | October 2020 | Part 2 | Question: 100

Question

Consider the following table structures related to a university for the below question.

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 (DNAME),
FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID)

PROJECT WORK:

    EMPID VARCHAR (10) NOTNULL,
    PROJNO INT(5) NOT NULL,
    PROJECTLOC VARCHAR (30) NOT NULL,
PRIMARY KEY (EMPID, PROJNO),
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?

1. UPDATE EMPLOYEE
   SET SALARY = SALARY*1.20
   WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME = ‘SCIENCE’);
   

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

3. ALTER TABLE EMPLOYEE
   SET SALARY=SALARY*1.20 
   WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME = ‘SCEINCE’)
   

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

Answer 1

Proper syntax is

Update Tablename 

hence option B(update table tablename and C (Alter table which is used for changing table structure or constraints) are out .

since DNAME is not a field in employee table we have  to use nested query to match DID from other table

1. UPDATE EMPLOYEE
2. SET SALARY = SALARY*1.20
3. WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME = ‘SCIENCE’);

Ans is option option B) i.e statement A