Database-Management-System

Question 279

Comprehension:

Consider the following table structures related to a university for Q96 to Q100:-

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

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID);

PROJECT WORK:

        EMPID VARCHAR (30) NOT NULL,

        PROJNO INT (5) NOT NULL,

        PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNO),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

Q96: 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

Database-Management-SystemSQLUGC NET JRF November 2020 Paper-2

Question 279 Explanation:

Output for statement – I:
mysql> select sum(salary),max(salary),min(salary),avg(salary)
-> from employee,department
-> where deptno=did and dname=’english’;

Output for statement-II:
mysql> select sum(salary),max(salary),min(salary),avg(salary)
-> from employee,department
-> where dname=’english’;

Correct Answer: C

Question 279 Explanation:

Output for statement – I:
mysql> select sum(salary),max(salary),min(salary),avg(salary)
-> from employee,department
-> where deptno=did and dname=’english’;

Output for statement-II:
mysql> select sum(salary),max(salary),min(salary),avg(salary)
-> from employee,department
-> where dname=’english’;

Database-Management-System

Database-Management-System

Database-Management-System

Database-Management-System

Mandeep Singh