English
Menu

Database Basics

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?

  1. A2 rows
  2. B3 rows
  3. C4 rows
Show answer and explanation

Answer: A. 2 rows

First, WHERE keeps only rows with a salary of 300 or more. Sales keeps two rows (320 and 350), Dev two (400 and 310), Admin one (300) and Planning one (330); Sales' 280 and Admin's 260 are removed. Next, GROUP BY gathers the rows by department into four groups with counts Sales 2, Dev 2, Admin 1 and Planning 1. Finally, HAVING COUNT(*) >= 2 keeps only groups with two or more rows, so the result is two rows: Sales and Dev. Counting without WHERE gives Sales 3, Dev 2, Admin 2 and Planning 1, and three rows would satisfy HAVING. The order in which WHERE works before grouping and HAVING works after it determines the result.

  • A. Correct. Sales and Dev each still have two or more rows after WHERE filters, giving two rows.
  • B. This counts before WHERE removes salaries below 300, so Admin wrongly remains.
  • C. This is the number of groups before HAVING filters them.
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 (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?
  2. 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?
  3. 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?
  4. 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?
  5. From a customer table (cust_id, name) and an orders table (order_id, cust_id, amount), you want the names of customers who have never placed an order. Which SQL statement is correct?