SQL · Question 9 · Medium
Using a table Employee(id, name, dept, salary), which of the following queries is INVALID in standard SQL?
- SELECT dept FROM Employee WHERE AVG(salary) > 40000 GROUP BY dept
- SELECT dept FROM Employee GROUP BY dept HAVING AVG(salary) > 40000
- SELECT dept, MAX(salary) FROM Employee GROUP BY dept
- SELECT COUNT(*) FROM Employee WHERE salary > 40000
Answer and explanation
Answer: A. SELECT dept FROM Employee WHERE AVG(salary) > 40000 GROUP BY dept
Explanation: WHERE is evaluated row by row before grouping, so aggregate functions are not allowed in it. The condition on AVG(salary) has to go in HAVING.
Previous: Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT… · Next: Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
Practise sql
Try a fresh set of sql questions with an explanation after every answer, free and without signing up: SQL practice. Or sit a full placement mock test.
More sql questions
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table Employee: id | name | dept | salary 1 | Asha | HR | 30000 2 | Ravi | IT…
- Table A has one column x with rows: 1, 1, 2, NULL Table B has one column x…
- Table A has one column x with rows: 1, 1, 2, NULL Table B has one column x…
- Which keyword is written after SELECT to remove duplicate rows from a query's…
More technical & programming practice
Operating Systems · Computer Networks · Software Engineering · Cloud & DevOps · GenAI Basics · Pseudo-code · C Programming · Java
LogicWiz Exams home · All practice topics · Placement mock tests · Daily 10 challenge