DISTINCT Keyword

The SELECT DISTINCT statement is used to return only unique (different) values from one or more columns. It eliminates duplicate rows from the result set.

Database Preparation

Run this block to set up the Books table.

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INTEGER,
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 101, 'Fantasy', 1954),
       (4, 'Dune', 103, 'Sci-Fi', 1965);

SELECT * FROM Books;

1. DISTINCT on a Single Column

Query:

SELECT DISTINCT Genre
FROM Books;

2. Using COUNT with DISTINCT

Query:

SELECT COUNT(DISTINCT AuthorID) AS NumberOfAuthors
FROM Books;

3. DISTINCT on Multiple Columns

Query:

SELECT DISTINCT AuthorID, Genre
FROM Books;

Explanation: The Genre column contains 'Fantasy', 'Dystopian', 'Fantasy', and 'Sci-Fi'. The DISTINCT keyword will filter out the duplicate 'Fantasy' entry.

Result:

2. Using COUNT with DISTINCT

You can use DISTINCT inside an aggregate function like COUNT() to count only the unique occurrences of a value.

Operation: Count how many different authors are represented in the Books table.

Query:

SELECT COUNT(DISTINCT AuthorID) AS NumberOfAuthors
FROM Books;

Explanation:

• A regular COUNT(AuthorID) would return 4, as there are four rows.
• COUNT(DISTINCT AuthorID) first finds the unique AuthorID values (101, 102, 103) and then counts them.

Result:

3. DISTINCT on Multiple Columns

When DISTINCT is applied to more than one column, it returns only the unique combinations of values from those columns.

Operation: Get the unique combinations of AuthorID and Genre.

Query:

SELECT DISTINCT AuthorID, Genre
FROM Books;

Explanation: The query looks at each row's (AuthorID, Genre) pair and eliminates any duplicates.

• Row 1: (101, Fantasy) - Unique, included.
• Row 2: (102, Dystopian) - Unique, included.
• Row 3: (101, Fantasy) - This is a duplicate of the pair from Row 1, so it is excluded.
• Row 4: (103, Sci-Fi) - Unique, included.

Result:

Task: Retrieve the unique list of genres available in the Books table.

Your Query:

-- Write your query here

Expected Results: