COUNT function behavior in SQL - Mini Project: Build & Apply

Understanding COUNT Function Behavior in SQL

📖 Scenario: You are managing a small library database. You want to learn how to count the number of books and authors using SQL queries.

🎯 Goal: Build SQL queries step-by-step to understand how the COUNT function works with different columns and conditions.

📋 What You'll Learn

Create a table called books with columns id, title, and author

Insert 5 specific rows into the books table

Write a query to count all rows in the books table

Write a query to count only non-null author values

Write a query to count distinct authors

💡 Why This Matters

🌍 Real World

Counting records is a common task in databases to get summaries like total items, valid entries, or unique values.

💼 Career

Understanding COUNT helps in writing reports, analytics queries, and data validation in many database-related jobs.

1

Create the books table and insert data

Write SQL statements to create a table called books with columns id (integer), title (text), and author (text). Then insert these exact rows: (1, 'The Hobbit', 'Tolkien'), (2, '1984', 'Orwell'), (3, 'The Silmarillion', 'Tolkien'), (4, 'Unknown Book', NULL), (5, 'Animal Farm', 'Orwell').

SQL

-- Create the books table and insert the 5 rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Count all rows in the books table

Write a SQL query that uses COUNT(*) to count all rows in the books table. Name the result column total_books.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'The Silmarillion', 'Tolkien'),
(4, 'Unknown Book', NULL),
(5, 'Animal Farm', 'Orwell');

-- Write a query to count all rows as total_books
-- Your code here

Hint

Use COUNT(*) to count every row regardless of NULLs.

3

Count only non-null authors

Write a SQL query that uses COUNT(author) to count only the rows where author is not NULL. Name the result column authors_count.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'The Silmarillion', 'Tolkien'),
(4, 'Unknown Book', NULL),
(5, 'Animal Farm', 'Orwell');

SELECT COUNT(*) AS total_books FROM books;

-- Write a query to count non-null authors as authors_count
-- Your code here

Hint

Use COUNT(column_name) to count only rows where that column is not NULL.

4

Count distinct authors

Write a SQL query that uses COUNT(DISTINCT author) to count the number of unique authors in the books table. Name the result column unique_authors.

SQL

CREATE TABLE books (id INTEGER, title TEXT, author TEXT);
INSERT INTO books (id, title, author) VALUES
(1, 'The Hobbit', 'Tolkien'),
(2, '1984', 'Orwell'),
(3, 'The Silmarillion', 'Tolkien'),
(4, 'Unknown Book', NULL),
(5, 'Animal Farm', 'Orwell');

SELECT COUNT(*) AS total_books FROM books;

SELECT COUNT(author) AS authors_count FROM books;

-- Write a query to count distinct authors as unique_authors
-- Your code here

Hint

Use COUNT(DISTINCT column_name) to count unique non-null values.

Practice

1. What does the SQL function COUNT(*) do when used in a query?

easy

A. Counts only rows with NULL values

B. Counts only rows where all columns are NOT NULL

C. Counts all rows in the table, including those with NULL values

D. Counts only distinct values in a column

Solution

1. Step 1: Understand COUNT(*) behavior

   The COUNT(*) function counts every row in the table regardless of NULL values in any column.

2. Step 2: Compare with other COUNT variants

   Unlike COUNT(column), which skips NULLs, COUNT(*) includes all rows.

3. Final Answer:

   Counts all rows in the table, including those with NULL values -> Option C

4. Quick Check:

   COUNT(*) counts all rows [OK]

Hint: COUNT(*) counts every row, NULL or not [OK]

Common Mistakes:

• Thinking COUNT(*) skips NULL rows
• Confusing COUNT(*) with COUNT(column)
• Assuming COUNT(*) counts distinct values

2. Which of the following is the correct syntax to count only non-NULL values in the column age from the table persons?

easy

A. SELECT COUNT(*) FROM persons;

B. SELECT COUNT(age) FROM persons;

C. SELECT COUNT(DISTINCT age) FROM persons;

D. SELECT COUNT(age IS NOT NULL) FROM persons;

Solution

1. Step 1: Identify how to count non-NULL values

   COUNT(column) counts only non-NULL values in that column.

2. Step 2: Check syntax correctness

   SELECT COUNT(age) FROM persons; correctly counts non-NULL age values.

3. Final Answer:

   SELECT COUNT(age) FROM persons; -> Option B

4. Quick Check:

   COUNT(column) counts non-NULL values [OK]

Hint: Use COUNT(column) to count non-NULL values [OK]

Common Mistakes:

• Using COUNT(*) to count non-NULL values
• Using WHERE clause unnecessarily
• Using COUNT with boolean expressions

3. Given the table employees with the column department containing values: ['HR', 'IT', NULL, 'IT', 'HR', 'Finance', NULL], what is the result of the query SELECT COUNT(DISTINCT department) FROM employees;?

medium

A. 3

B. 5

C. 7

D. 4

Solution

1. Step 1: Identify distinct non-NULL values in department

   The distinct non-NULL values are 'HR', 'IT', and 'Finance'. That's 3 unique values.

2. Step 2: Understand COUNT(DISTINCT) behavior

   COUNT(DISTINCT column) counts unique non-NULL values only, so NULLs are excluded.

3. Step 3: Count distinct values

   There are 3 distinct non-NULL values, so the count is 3.

4. Final Answer:

   3 -> Option A

5. Quick Check:

   COUNT(DISTINCT) excludes NULLs [OK]

Hint: COUNT(DISTINCT) counts unique non-NULL values only [OK]

Common Mistakes:

• Including NULL as a distinct value
• Counting total rows instead of distinct
• Confusing COUNT(*) with COUNT(DISTINCT)

4. Consider the query: SELECT COUNT(employee_id) FROM staff; but it returns 0 even though the table has rows. What is the most likely reason?

medium

A. The column employee_id contains only NULL values

B. The table staff is empty

C. COUNT(employee_id) counts all rows including NULLs

D. Syntax error in the query

Solution

1. Step 1: Understand COUNT(column) behavior

   COUNT(column) counts only non-NULL values in that column.

2. Step 2: Analyze why count is zero

   If all employee_id values are NULL, COUNT returns 0 even if rows exist.

3. Final Answer:

   The column employee_id contains only NULL values -> Option A

4. Quick Check:

   COUNT(column) excludes NULLs [OK]

Hint: COUNT(column) returns 0 if all values are NULL [OK]

Common Mistakes:

• Assuming COUNT(column) counts all rows
• Thinking table is empty without checking data
• Assuming syntax error without checking query

5. You want to find how many unique customers placed orders, but some orders have NULL customer IDs. Which query correctly counts unique customers excluding NULLs?

hard

A. SELECT COUNT(customer_id) FROM orders;

B. SELECT COUNT(*) FROM orders WHERE customer_id IS NOT NULL;

C. SELECT COUNT(DISTINCT *) FROM orders;

D. SELECT COUNT(DISTINCT customer_id) FROM orders;

Solution

1. Step 1: Understand requirement for unique customers excluding NULLs

   We need to count distinct customer IDs ignoring NULLs.

2. Step 2: Analyze options

   SELECT COUNT(DISTINCT customer_id) FROM orders; uses COUNT(DISTINCT customer_id), which counts unique non-NULL values correctly.

3. Step 3: Eliminate incorrect options

   SELECT COUNT(customer_id) FROM orders; counts all non-NULL customer IDs including duplicates. SELECT COUNT(*) FROM orders WHERE customer_id IS NOT NULL; counts rows with non-NULL customer IDs but not distinct. SELECT COUNT(DISTINCT *) FROM orders; is invalid syntax.

4. Final Answer:

   SELECT COUNT(DISTINCT customer_id) FROM orders; -> Option D

5. Quick Check:

   COUNT(DISTINCT column) counts unique non-NULL values [OK]

Hint: Use COUNT(DISTINCT column) for unique non-NULL counts [OK]

Common Mistakes:

• Using COUNT(column) to count unique values
• Using COUNT(*) with WHERE instead of DISTINCT
• Using invalid syntax like COUNT(DISTINCT *)