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);
Given below are two statements to find the sum of salaries of all employees of the English department as well as the maximum, minimum and average salary in English department.
Statement I:
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DEPTNO=DID
AND DNAME='ENGLISH';
Statement II:
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='ENGLISH';
In the light of the above statements, 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 |
Given below are two statements to find the sum of salaries of all employees of the English department as well as the maximum, minimum and average salary in English department.
Statement I:
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DEPTNO=DID
AND DNAME='ENGLISH';
Statement II:
SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='ENGLISH';
In the light of the above statements, choose the correct answer from the options given below:
Solution
We need to find salary details of employees who belong to the English department.For this, EMPLOYEE table and DEPARTMENT table must be joined.
In Statement I:
DEPTNO=DID
This condition joins EMPLOYEE table with DEPARTMENT table.
Also,
DNAME='ENGLISH'
filters only English department employees.
So, Statement I is correct.
In Statement II:
Only condition is:
DNAME='ENGLISH'
There is no join condition between EMPLOYEE and DEPARTMENT.
So, it will not correctly connect employees with their department.
It may produce incorrect result due to Cartesian product.
Therefore, Statement II is false.
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.
