Why the following statement is erroneous?
SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;
a)
Dept_id should not be used in group by clause
b)
Group by clause is not valid in this query
c)
Avg(salary) should not be selected
d)
None
Correct answer is option 'A'. Can you explain this answer?

Question

Why the following statement is erroneous?

SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;

a)
Dept_id should not be used in group by clause
b)
Group by clause is not valid in this query
c)
Avg(salary) should not be selected
d)
None

Correct answer is option 'A'. Can you explain this answer?

Answer

Erroneous Statement:
SELECT dept_name, ID, avg (salary)
FROM instructor
GROUP BY dept_name;

Explanation:
The erroneous statement is option 'A', which states that the 'dept_id should not be used in the group by clause'.

Reasoning:
1. GROUP BY Clause:
The GROUP BY clause is used in SQL queries to group rows based on one or more columns. It is typically used in conjunction with aggregate functions such as AVG, SUM, COUNT, etc. When the GROUP BY clause is used, the result set is divided into groups based on the specified column(s) and the aggregate function is applied to each group.

2. Columns in SELECT Statement:
In the SELECT statement, we specify the columns that we want to include in the result set. These columns can be either individual columns or aggregate functions applied to specific columns.

3. Error Analysis:
Let's analyze each part of the erroneous statement to understand why it is incorrect.

- SELECT dept_name, ID, avg (salary):
The SELECT clause specifies the columns to be included in the result set. In this case, it includes 'dept_name', 'ID', and 'avg(salary)'. The usage of the 'avg(salary)' column is correct as it calculates the average salary for each group.

- FROM instructor:
The FROM clause specifies the table from which the data is to be retrieved. In this case, it is the 'instructor' table.

- GROUP BY dept_name:
The GROUP BY clause is used to group the rows based on the specified column(s). In this case, it groups the rows by 'dept_name'. This is a valid usage as we want to calculate the average salary for each department.

- Analysis:
The erroneous statement is incorrect because it states that the 'dept_id' should not be used in the GROUP BY clause. However, the 'dept_id' column is not even present in the statement. It is not being used in the SELECT clause or the GROUP BY clause. Therefore, option 'A' is incorrect.

Conclusion:
In conclusion, the correct answer is option 'A' because it incorrectly states that the 'dept_id' should not be used in the GROUP BY clause, even though it is not present in the statement. The other options are incorrect because they do not accurately identify the error in the statement.

Answer

Any property that does not occur in the group by clause must only appear in an aggregate function if it also appears in the select clause; otherwise, the query is considered incorrect.

Similar Computer Science Engineering (CSE) Doubts

Which of the following statements is false?

Consider the case: f(n) = O(g(n)). Then, following two statements are claimed to be inferred from the above case.Statement I:2f(n)= O(2g(n))Statement II:2g(n)= O(2f(n))

Top Courses for Computer Science Engineering (CSE)View all

GATE Computer Science Engineering(CSE) 2025 Mock Test Series

Question Bank for GATE Computer Science Engineering

General Aptitude for GATE

Computer Networks

Programming and Data Structures

Top Courses for Computer Science Engineering (CSE)

Top Courses for Computer Science Engineering (CSE)

Suggested Free Tests

Related Content

Mock Test Series

Subject-wise Courses

Subject-wise Courses

Other Engg Branches

Company