English
Menu

Database Basics

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?

  1. A1 row
  2. B3 rows
  3. C4 rows
Show answer and explanation

Answer: B. 3 rows

GROUP BY store gathers the rows by store into four groups with totals A = 500 + 700 = 1200, B = 300 + 400 = 700, C = 1200 and D = 200 + 900 = 1100. HAVING SUM(amount) >= 1000 keeps only groups whose total is 1000 or more, so the result has three rows: A, C and D. One row is the result of selecting by the amount of each individual row before grouping, as with WHERE amount >= 1000 (only C). Four rows is the number of groups without HAVING.

  • A. This is the result of selecting individual rows with an amount of 1000 or more (filtering with WHERE).
  • B. Correct. A (1200), C (1200) and D (1100) have totals of 1000 or more, giving three rows.
  • C. This is the number of groups before HAVING filters them. B, with a total of 700, is removed.
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. 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?
  2. 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?
  3. 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?
  4. 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?
  5. A customer table (cust_id, name) has four rows: (C1, Aoki), (C2, Inoue), (C3, Ueda), (C4, Eto). An orders table (order_id, cust_id, amount) has five rows: (O1, C1, 3000), (O2, C2, 8000), (O3, C1, 6000), (O4, C3, 2000), (O5, C2, 9000). How many rows does SELECT name FROM customer WHERE cust_id IN (SELECT cust_id FROM orders WHERE amount > 5000) return?