English
Menu

Database Basics

The score column of a result table contains six values: 50, NULL, 70, NULL, 90, 30. Which shows, in order, the result of SELECT COUNT(*), COUNT(score), AVG(score) FROM result?

  1. A6, 6, 40
  2. B6, 4, 40
  3. C6, 4, 60
Show answer and explanation

Answer: C. 6, 4, 60

COUNT(*) counts rows regardless of whether they contain NULL, so it is 6. With a column specified, as in COUNT(score), only rows where that column is not NULL are counted, so it is 4. AVG(score) divides the sum of the four non-NULL values, 50 + 70 + 90 + 30 = 240, by the number of non-NULL values, 4, giving 240 ÷ 4 = 60. Note that this differs from dividing the sum by the total number of rows, SUM(score) / COUNT(*) = 240 ÷ 6 = 40. To treat NULL as 0 points, replace it with COALESCE(score, 0) before aggregating.

  • A. This is the result of treating NULL as 0. COUNT(score) does not count rows with NULL.
  • B. This confuses AVG with the sum divided by the total number of rows, 6.
  • C. Correct. There are 6 rows, 4 non-NULL values, and their average is 240 ÷ 4 = 60.
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. 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?
  2. 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?
  3. 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?
  4. 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?
  5. 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?