English
Menu

Database Basics

The dept column of an employee table contains, from the top row down, Sales, Dev, Sales, Admin, Dev, Sales (six rows in total). How many rows does SELECT DISTINCT dept FROM employee return?

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

Answer: B. 3 rows

DISTINCT removes duplicate rows from the result, collapsing identical values into one row. There are three distinct dept values, Sales, Dev and Admin, so the result has three rows. Without DISTINCT, SELECT dept FROM employee returns all six rows, duplicates included. Two rows is the count of only the departments that appear two or more times (Sales and Dev), which corresponds to the result of a query such as SELECT dept FROM employee GROUP BY dept HAVING COUNT(*) >= 2.

  • A. This counts only Sales and Dev, which appear more than once. Admin, which appears once, also remains as a row.
  • B. Correct. Removing duplicates leaves three rows: Sales, Dev and Admin.
  • C. This is the number of rows without DISTINCT.
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. What is the result of executing DELETE FROM employee in SQL without a WHERE clause?
  2. 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?
  3. 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?
  4. 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?
  5. 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?