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

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

 

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:

• A. Both Statement $I$ and Statement $II$ are true
• B. Both Statement $I$ and Statement $II$ are false
• C. Statement $I$ is correct but Statement $II$ is false
• D. Statement $I$ is incorrect but Statement $II$ is true

Answer 1

There must be matching between two tables (join condition) to link uniquely and then  fetch records .

1. SELECT SUM (SALARY) MAX(SALARY) MIN(SALARY),
2. AVG (SALARY) FROM EMPLOYEE, DEPARTMENT
3. WHERE DEPTNO=DID AND DNAME=’ENGLISH’                                                                                           // without DEPTNO=DID condition as in statement II there will be cartesian product of two tables and  many redundant rows with condition  DNAME=’ENGLISH’ will appear yielding incorrect results

 

option C )Statement I is correct but Statement II is false is right ans