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

Need a 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

Need a 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

Need a 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

Need a hint?

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