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

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 the 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

1. Both Statement I and Statement II are true
2. Both Statement I and Statement II are false
3. Statement I is correct but Statement II is false
4. Statement I is incorrect but Statement II is true

Answer 1

Correct Answer - Option 3 : Statement I is correct but Statement II is false

The correct answer is option 3.

Two table tables (join condition) must be matched so that records can be linked uniquely.

Query: 

SUM (SALARY). MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DEPTNO=DID AND DNAME='ENGLISH';

Without DEPTNO=DID condition as in statement II, there will be the cartesian product of two tables (EMPLOYEE, DEPARTMENT) and it will appear many duplicate rows with DNAME='ENGLISH'.

Hence Statement I is correct but Statement II is false.