DISTINCT for unique values in SQL - Mini Project: Build & Apply

Using DISTINCT to Find Unique Values in SQL

📖 Scenario: You work at a bookstore that keeps track of all book sales in a database table called sales. Each sale record includes the book_title and the customer_name. Sometimes customers buy the same book multiple times.Your manager wants to know the list of unique book titles sold so far, without duplicates.

🎯 Goal: Build an SQL query that uses DISTINCT to find all unique book titles from the sales table.

📋 What You'll Learn

Create a table called sales with columns book_title and customer_name.

Insert the exact sales data provided.

Write a query that selects unique book titles using DISTINCT.

Order the results alphabetically by book_title.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use DISTINCT to find unique items, customers, or transactions in their databases.

💼 Career

Knowing how to use DISTINCT helps database analysts and developers write queries that avoid duplicate data and produce clean reports.

1

Create the sales table and insert data

Create a table called sales with columns book_title and customer_name, both as text. Then insert these exact rows into sales: ('The Hobbit', 'Alice'), ('1984', 'Bob'), ('The Hobbit', 'Charlie'), ('Dune', 'Alice'), ('1984', 'David').

SQL

-- Create the sales table and insert the given rows
-- Your code here

Hint

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

2

Set up the SELECT statement

Write a SELECT statement to get the book_title column from the sales table. Do not add DISTINCT yet.

SQL

CREATE TABLE sales (
    book_title TEXT,
    customer_name TEXT
);

INSERT INTO sales (book_title, customer_name) VALUES
('The Hobbit', 'Alice'),
('1984', 'Bob'),
('The Hobbit', 'Charlie'),
('Dune', 'Alice'),
('1984', 'David');

-- Write a SELECT statement to get book_title from sales
SELECT book_title FROM sales;

Hint

Use SELECT book_title FROM sales to get all book titles.

3

Add DISTINCT to get unique book titles

Modify the SELECT statement to use DISTINCT so it returns only unique book_title values from the sales table.

SQL

CREATE TABLE sales (
    book_title TEXT,
    customer_name TEXT
);

INSERT INTO sales (book_title, customer_name) VALUES
('The Hobbit', 'Alice'),
('1984', 'Bob'),
('The Hobbit', 'Charlie'),
('Dune', 'Alice'),
('1984', 'David');

-- Add DISTINCT to select unique book titles
SELECT DISTINCT book_title FROM sales;

Hint

Add the keyword DISTINCT right after SELECT to get unique values.

4

Order the unique book titles alphabetically

Add an ORDER BY clause to the query to sort the unique book_title values alphabetically in ascending order.

SQL

CREATE TABLE sales (
    book_title TEXT,
    customer_name TEXT
);

INSERT INTO sales (book_title, customer_name) VALUES
('The Hobbit', 'Alice'),
('1984', 'Bob'),
('The Hobbit', 'Charlie'),
('Dune', 'Alice'),
('1984', 'David');

-- Add ORDER BY to sort book titles alphabetically
SELECT DISTINCT book_title FROM sales ORDER BY book_title ASC;

Hint

Use ORDER BY book_title ASC to sort the results alphabetically.

Practice

1. What does the SQL keyword DISTINCT do in a query?

easy

A. It sorts the results in ascending order.

B. It returns only unique rows by removing duplicates.

C. It counts the number of rows in a table.

D. It updates duplicate rows with new values.

Solution

1. Step 1: Understand the purpose of DISTINCT

   The DISTINCT keyword is used to filter out duplicate rows in the result set, so only unique rows remain.

2. Step 2: Compare with other options

   Sorting is done by ORDER BY, counting by COUNT(), and updating by UPDATE statement, none of which are related to DISTINCT.

3. Final Answer:

   It returns only unique rows by removing duplicates. -> Option B

4. Quick Check:

   DISTINCT = unique rows [OK]

Hint: DISTINCT removes duplicates, showing only unique rows [OK]

Common Mistakes:

• Confusing DISTINCT with ORDER BY
• Thinking DISTINCT counts rows
• Assuming DISTINCT updates data

2. Which of the following is the correct syntax to select unique city names from a table called Customers?

easy

A. SELECT DISTINCT city FROM Customers;

B. SELECT UNIQUE city FROM Customers;

C. SELECT city UNIQUE FROM Customers;

D. SELECT city FROM Customers DISTINCT;

Solution

1. Step 1: Recall correct DISTINCT syntax

   The correct syntax to get unique values is SELECT DISTINCT column_name FROM table_name.

2. Step 2: Check each option

   SELECT DISTINCT city FROM Customers; matches the correct syntax. The other options use incorrect keyword placement or invalid keywords.

3. Final Answer:

   SELECT DISTINCT city FROM Customers; -> Option A

4. Quick Check:

   Correct syntax uses DISTINCT after SELECT [OK]

Hint: Use SELECT DISTINCT column FROM table for unique values [OK]

Common Mistakes:

• Using UNIQUE instead of DISTINCT
• Placing DISTINCT after FROM
• Writing keywords in wrong order

3. Given the table Orders with a column Product containing values: ['Pen', 'Pen', 'Book', 'Notebook', 'Book'], what will the query SELECT DISTINCT Product FROM Orders; return?

medium

A. Pen, Book, Notebook

B. Pen, Book

C. Pen, Pen, Book, Notebook, Book

D. Notebook

Solution

1. Step 1: Identify duplicates in Product column

   The values are Pen (twice), Book (twice), and Notebook (once).

2. Step 2: Apply DISTINCT to remove duplicates

   DISTINCT returns each unique value once: Pen, Book, Notebook.

3. Final Answer:

   Pen, Book, Notebook -> Option A

4. Quick Check:

   DISTINCT removes duplicates, leaving unique values [OK]

Hint: DISTINCT returns each unique value once [OK]

Common Mistakes:

• Listing duplicates as separate rows
• Missing some unique values
• Confusing DISTINCT with COUNT

4. What is wrong with this query to get unique customer names from Customers table?
SELECT DISTINCT name, age FROM Customers;
Assuming you want unique names only.

medium

A. The query needs GROUP BY instead of DISTINCT.

B. DISTINCT cannot be used with multiple columns.

C. DISTINCT should be replaced with UNIQUE.

D. DISTINCT applies to all selected columns, so duplicates in name may remain if age differs.

Solution

1. Step 1: Understand how DISTINCT works with multiple columns

   DISTINCT treats the combination of all selected columns as one unit, so rows are unique only if all columns differ.

2. Step 2: Analyze the effect on name uniqueness

   If the same name appears with different ages, both rows appear because the combination is unique, not just the name.

3. Final Answer:

   DISTINCT applies to all selected columns, so duplicates in name may remain if age differs. -> Option D

4. Quick Check:

   DISTINCT on multiple columns = unique row combos [OK]

Hint: DISTINCT on multiple columns checks combined uniqueness [OK]

Common Mistakes:

• Thinking DISTINCT applies to each column separately
• Using UNIQUE keyword instead of DISTINCT
• Confusing DISTINCT with GROUP BY

5. You have a table Sales with columns Region and SalesPerson. You want to find all unique salespersons regardless of region. Which query gives the correct result?

hard

A. SELECT SalesPerson FROM Sales GROUP BY Region;

B. SELECT DISTINCT Region, SalesPerson FROM Sales;

C. SELECT DISTINCT SalesPerson FROM Sales;

D. SELECT UNIQUE SalesPerson FROM Sales;

Solution

1. Step 1: Identify the goal

   You want unique salespersons regardless of region, so only SalesPerson column matters.

2. Step 2: Choose query that returns unique SalesPerson values

   SELECT DISTINCT SalesPerson FROM Sales returns unique salespersons. SELECT DISTINCT Region, SalesPerson FROM Sales; returns unique pairs of Region and SalesPerson, which may repeat salespersons. SELECT SalesPerson FROM Sales GROUP BY Region; groups by Region, not SalesPerson, so it won't give unique salespersons. SELECT UNIQUE SalesPerson FROM Sales; uses UNIQUE which is invalid syntax.

3. Final Answer:

   SELECT DISTINCT SalesPerson FROM Sales; -> Option C

4. Quick Check:

   Use DISTINCT on the column you want unique values for [OK]

Hint: DISTINCT only on needed column for unique values [OK]

Common Mistakes:

• Selecting multiple columns with DISTINCT causing duplicates
• Using GROUP BY on wrong column
• Using UNIQUE keyword instead of DISTINCT