Consider the following table structures related to a university for the below…
2020
Consider the following table structures related to a university for the below question
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 𝐼:
SELECT SUM (SALARY) MAX(SALARY) MIN(SALARY),
AVG (SALARY) FROM EMPLOYEE, DEPARTMENT
WHERE DEPTNO=DID
AND DNAME=’ENGLISH’
STATEMENT 𝐼𝐼:
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 𝐼 and Statement 𝐼𝐼 are true
- B.
Both Statement 𝐼 and Statement 𝐼𝐼 are false
- C.
Statement 𝐼 is correct but Statement 𝐼𝐼 is false
- D.
Statement 𝐼 is incorrect but Statement 𝐼𝐼 is true
Attempted by 342 students.
Show answer & explanation
Correct answer: C
Answer summary: The first statement is correct and the second statement is false.
Why the first statement is correct:
Correct form of the query: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DEPTNO = DID AND DNAME = 'ENGLISH';
Reason: The query joins EMPLOYEE to DEPARTMENT using DEPTNO = DID and then filters the department name to 'ENGLISH'. Aggregation functions over the filtered rows yield the sum, maximum, minimum and average salaries. No GROUP BY is required because the aggregates are over the whole filtered set.
Why the second statement is false:
As written, the query contains a trailing comma before FROM: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY), FROM ... — this is a syntax error.
Even if the trailing comma were removed, the query omits the join condition between EMPLOYEE and DEPARTMENT (DEPTNO = DID). Without that join condition the two tables form a Cartesian product, which multiplies rows and produces incorrect aggregate values for salaries.
Correct alternatives:
Use a proper join between the tables (same as the first statement): SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DEPTNO = DID AND DNAME = 'ENGLISH';
Or use a subquery on DEPARTMENT to avoid joining explicitly: SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY) FROM EMPLOYEE WHERE DEPTNO = (SELECT DID FROM DEPARTMENT WHERE DNAME = 'ENGLISH');
Conclusion: The first query (properly punctuated and with the DEPTNO = DID join) is correct. The second query as given is false due to syntax and/or missing join condition.
A video solution is available for this question — log in and enroll to watch it.