English
Menu

Database Basics

The score column of an exam table contains four values: 80, NULL, 70, 90 (NULL means the person did not take the exam). What is the result of SELECT AVG(score) FROM exam?

  1. A60
  2. B80
  3. CNULL
Show answer and explanation

Answer: B. 80

SQL aggregate functions (SUM, AVG, MAX, MIN, and COUNT with a column specified) ignore NULL. AVG(score) is the average of the three non-NULL values: (80 + 70 + 90) ÷ 3 = 240 ÷ 3 = 80. Treating NULL as 0 and dividing by 4 would give 240 ÷ 4 = 60, but AVG does not calculate that way. To average with absentees counted as 0 points, replace NULL with 0 before aggregating, as in AVG(COALESCE(score, 0)). If all values are NULL, the result of AVG is NULL.

  • A. This treats NULL as 0 and divides by 4. AVG ignores NULL.
  • B. Correct. It is the average of the three non-NULL values: 240 ÷ 3 = 80.
  • C. A single NULL makes the result NULL for operators such as +, but aggregate functions ignore NULL.
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. 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?
  2. 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?
  3. 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?
  4. 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?
  5. 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?