English
Menu

Database Basics

From an employee table (emp_no, dept, salary), you want the departments whose average salary is 300 or more, together with that average. Which SQL statement is correct?

  1. ASELECT dept, AVG(salary) FROM employee GROUP BY dept HAVING AVG(salary) >= 300
  2. BSELECT dept, AVG(salary) FROM employee WHERE AVG(salary) >= 300 GROUP BY dept
  3. CSELECT dept, AVG(salary) FROM employee WHERE salary >= 300 GROUP BY dept
Show answer and explanation

Answer: A. SELECT dept, AVG(salary) FROM employee GROUP BY dept HAVING AVG(salary) >= 300

Think of SQL processing in this order: WHERE selects rows from the table in FROM, GROUP BY gathers them into groups, and HAVING selects groups. WHERE is a condition on individual rows, evaluated before grouping, so aggregate functions such as AVG cannot be written in it. A condition on each group's aggregate goes in HAVING, so HAVING AVG(salary) >= 300 is the correct form. With WHERE salary >= 300, only employees earning 300 or more are selected before averaging, which means something different, the average over just those employees; a department whose overall average is below 300 still appears in the result as long as at least one of its employees earns 300 or more.

  • A. Correct. A condition on each group's aggregate is written in the HAVING clause.
  • B. Aggregate functions cannot be written in a WHERE clause, so this statement causes an error.
  • C. This averages only employees earning 300 or more, so departments whose overall average is below 300 also appear.
Practice this topic Back to topic

Related calculators

SQL PracticeRun SQL in your browser and check the results, with 10 practice exercises that grade your answers

Books on Database Basics Ad

As an Amazon Associate I earn from qualifying purchases.

More Database Basics questions

  1. A sales table (store, amount) has seven rows: (A, 500), (A, 700), (B, 300), (B, 400), (C, 1200), (D, 200), (D, 900). How many rows does SELECT store, SUM(amount) FROM sales GROUP BY store HAVING SUM(amount) >= 1000 return?
  2. An employee table (name, dept, salary) has eight rows, with salaries by department as follows: Sales 320, 280, 350; Dev 400, 310; Admin 260, 300; Planning 330. How many rows does SELECT dept, COUNT(*) FROM employee WHERE salary >= 300 GROUP BY dept HAVING COUNT(*) >= 2 return?
  3. An employee table (emp_no, name, dept_id) has four rows whose dept_id values are, in order, 10, 20, 10 and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT employee.name, department.dept_name FROM employee INNER JOIN department ON employee.dept_id = department.dept_id return?
  4. An employee table (emp_no, name, dept_id) has five rows whose dept_id values are, in order, 10, 20, 10, NULL and NULL. A department table (dept_id, dept_name) has three rows with dept_id 10, 20 and 30. How many rows does SELECT department.dept_name, employee.name FROM department LEFT OUTER JOIN employee ON department.dept_id = employee.dept_id return?
  5. Column x of table R has four rows with values 1, 1, 2, 3, and column x of table S has four rows with values 1, 1, 2, 4. How many rows does SELECT * FROM R INNER JOIN S ON R.x = S.x return?