Aspire's Library
A Place for Latest Exam wise Questions, Videos, Previous Year Papers,
Study Stuff for MCA Examinations - NIMCET
Study Stuff for MCA Examinations - NIMCET
Previous Year Question (PYQs)
Consider the following table structures related to a university for the below questions.
EMPLOYEE
PRIMARY KEY (EID),
FOREIGN KEY (HODEID) REFERENCES EMPLOYEE (EID),
FOREIGN KEY (DEPTNO) REFERENCES DEPARTMENT (DID);
DEPARTMENT
PRIMARY KEY (DID),
UNIQUE (DNAME),
FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID);
PROJECT WORK
PRIMARY KEY (EMPID, PROJNO),
FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID);
Refer table structures given above. The university decided to give all employees in the 'SCIENCE' department a $20\%$ rise in salary. Which of the following query or queries will compute the above results?
(A)
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.20
WHERE DEPTNO 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:
| NAME | VARCHAR(30) | NOT NULL |
| EID | VARCHAR(10) | NOT NULL |
| DEPTNO | INT(5) | NOT NULL |
| HODEID | VARCHAR(10) | |
| SALARY | INT(10) |
| DID | INT(5) | NOT NULL |
| DNAME | VARCHAR(30) | NOT NULL |
| HODID | VARCHAR(10) | NOT NULL |
| HODNAME | VARCHAR(30) |
| EMPID | VARCHAR(10) | NOT NULL |
| PROJNO | INT(5) | NOT NULL |
| PROJECTLOC | VARCHAR(30) | NOT NULL |
Refer table structures given above. The university decided to give all employees in the 'SCIENCE' department a $20\%$ rise in salary. Which of the following query or queries will compute the above results?
(A)
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.20
WHERE DEPTNO 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:
Solution
We need to increase the salary of all employees who belong to the 'SCIENCE' department.The salary is stored in EMPLOYEE table.
The department name is stored in DEPARTMENT table.
So, first we need to find the DID of the 'SCIENCE' department.
This can be done by:
SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE'
Then update salary of employees whose DEPTNO matches that DID.
So, correct query is:
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.20
WHERE DEPTNO IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');
Therefore, query (A) is correct.
Query (B) is incorrect because UPDATE TABLE is not valid syntax.
Also, DNAME is not present in EMPLOYEE table.
Query (C) is incorrect because ALTER TABLE is used to change table structure, not to update data.
Also, it compares DEPTNO with DNAME, which is wrong.
Therefore, only query (A) is correct.
Online Test Series, Information About Examination,
Syllabus, Notification
and More.
View More
Online Test Series, Information About Examination,
Syllabus, Notification
and More.
View More
Ask Your Question or Put Your Review.
