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

Using DISTINCT to Find Unique Values in MySQL

📖 Scenario: You work at a small bookstore that keeps track of all book sales in a database. Sometimes, you want to find out which unique book titles have been sold, without counting duplicates.

🎯 Goal: Build a simple MySQL query that uses DISTINCT to list unique book titles sold from the sales table.

📋 What You'll Learn

Create a table called sales with columns id (integer), title (text), and price (decimal).

Insert the exact sales data with duplicate book titles as specified.

Write a query using DISTINCT to select unique book titles from the sales table.

Complete the final query to return only the unique titles.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use DISTINCT to find unique items sold, unique customers, or unique product categories from their sales data.

💼 Career

Knowing how to use DISTINCT is essential for data analysts and database administrators to clean data and generate accurate reports.

1

Create the sales table and insert data

Create a table called sales with columns id as INT, title as VARCHAR(100), and price as DECIMAL(5,2). Then insert these rows exactly: (1, 'The Great Gatsby', 10.99), (2, '1984', 8.99), (3, 'The Great Gatsby', 10.99), (4, 'To Kill a Mockingbird', 7.99), (5, '1984', 8.99).

MySQL

-- Create the sales table and insert the data below
-- Your code here

Hint

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

2

Set up the SELECT statement to get book titles

Write a SELECT statement that selects the title column from the sales table. Assign this query to a variable called query.

MySQL

CREATE TABLE sales (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO sales (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'The Great Gatsby', 10.99),
(4, 'To Kill a Mockingbird', 7.99),
(5, '1984', 8.99);

-- Write a SELECT statement to get the title column
SET @query = 'SELECT title FROM sales';

Hint

Use SET @query = 'SELECT title FROM sales' to store the query as a string.

3

Modify the SELECT statement to use DISTINCT

Change the @query variable to select only unique title values using DISTINCT from the sales table.

MySQL

CREATE TABLE sales (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO sales (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'The Great Gatsby', 10.99),
(4, 'To Kill a Mockingbird', 7.99),
(5, '1984', 8.99);

-- Modify the query to select distinct titles
SET @query = 'SELECT DISTINCT title FROM sales';

Hint

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

4

Complete the query to return unique book titles

Finalize the query by writing a SELECT statement that returns unique title values from the sales table using DISTINCT. This is the final query you will run.

MySQL

CREATE TABLE sales (
  id INT,
  title VARCHAR(100),
  price DECIMAL(5,2)
);

INSERT INTO sales (id, title, price) VALUES
(1, 'The Great Gatsby', 10.99),
(2, '1984', 8.99),
(3, 'The Great Gatsby', 10.99),
(4, 'To Kill a Mockingbird', 7.99),
(5, '1984', 8.99);

-- Write the final SELECT query to get unique titles
SELECT DISTINCT title FROM sales;

Hint

Use SELECT DISTINCT title FROM sales; to get the unique book titles.

Practice

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

easy

A. It sorts the results in ascending order.

B. It updates values in a column.

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

D. It returns only unique values by removing duplicates.

Solution

1. Step 1: Understand the purpose of DISTINCT

   The DISTINCT keyword is used to filter out duplicate rows in the result set.

2. Step 2: Compare with other options

   Sorting is done by ORDER BY, counting by COUNT(), and updating by UPDATE statement, so these are incorrect.

3. Final Answer:

   It returns only unique values by removing duplicates. -> Option D

4. Quick Check:

   DISTINCT = unique values [OK]

Hint: DISTINCT removes duplicates, giving unique results only [OK]

Common Mistakes:

• Confusing DISTINCT with ORDER BY
• Thinking DISTINCT counts rows
• Using DISTINCT to update 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 city UNIQUE FROM customers;

C. SELECT UNIQUE city FROM customers;

D. SELECT city FROM customers DISTINCT;

Solution

1. Step 1: Recall correct syntax for unique selection

   MySQL uses DISTINCT keyword immediately after SELECT to get unique values.

2. Step 2: Check each option

   SELECT DISTINCT city FROM customers; uses correct syntax: SELECT DISTINCT city FROM customers; others are invalid syntax.

3. Final Answer:

   SELECT DISTINCT city FROM customers; -> Option A

4. Quick Check:

   Syntax for unique values = SELECT DISTINCT [OK]

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

Common Mistakes:

• Using UNIQUE instead of DISTINCT in SELECT
• Placing DISTINCT after FROM clause
• Misplacing keywords causing syntax errors

3. Given the table orders with a column product_id containing values: [1, 2, 2, 3, 1, 4], what will the query SELECT DISTINCT product_id FROM orders; return?

medium

A. [1, 2, 3, 4]

B. [1, 2, 2, 3, 1, 4]

C. [2, 3, 4]

D. [1, 3, 4]

Solution

1. Step 1: Identify duplicates in product_id

   The values are 1, 2, 2, 3, 1, 4. Duplicates are 1 and 2 repeated.

2. Step 2: Apply DISTINCT to remove duplicates

   Removing duplicates leaves unique values: 1, 2, 3, 4.

3. Final Answer:

   [1, 2, 3, 4] -> Option A

4. Quick Check:

   DISTINCT removes duplicates = unique list [OK]

Hint: DISTINCT returns only one of each repeated value [OK]

Common Mistakes:

• Expecting all original values including duplicates
• Confusing DISTINCT with sorting
• Ignoring repeated values in output

4. What is wrong with this query if you want unique customer names from the customers table?
SELECT DISTINCT name, age FROM customers;
Assuming some customers share the same name but have different ages.

medium

A. It will cause a syntax error.

B. It will return unique names only, ignoring age.

C. It will return unique combinations of name and age, not just names.

D. It will return duplicate names.

Solution

1. Step 1: Understand DISTINCT with multiple columns

   DISTINCT applies to the entire row of selected columns, so it returns unique pairs of name and age.

2. Step 2: Analyze the effect on names

   If customers share the same name but different ages, each pair is unique, so duplicates in names alone are not removed.

3. Final Answer:

   It will return unique combinations of name and age, not just names. -> Option C

4. Quick Check:

   DISTINCT on multiple columns = unique rows [OK]

Hint: DISTINCT on multiple columns checks all columns together [OK]

Common Mistakes:

• Thinking DISTINCT applies to each column separately
• Expecting only unique names when selecting multiple columns
• Assuming syntax error for multiple columns with DISTINCT

5. You have a table sales with columns region and salesperson. Some salespersons work in multiple regions. Which query will list all unique salespersons regardless of region?

hard

A. SELECT DISTINCT region, salesperson FROM sales;

B. SELECT DISTINCT salesperson FROM sales;

C. SELECT salesperson FROM sales GROUP BY region;

D. SELECT salesperson FROM sales WHERE DISTINCT region;

Solution

1. Step 1: Identify the goal

   We want unique salespersons only, ignoring regions.

2. Step 2: Analyze each query

   SELECT DISTINCT region, salesperson FROM sales; returns unique pairs of region and salesperson, so duplicates by salesperson remain if regions differ. SELECT DISTINCT salesperson FROM sales; returns unique salespersons only. SELECT salesperson FROM sales GROUP BY region; groups by region, not salesperson, so duplicates remain. SELECT salesperson FROM sales WHERE DISTINCT region; is invalid syntax.

3. Final Answer:

   SELECT DISTINCT salesperson FROM sales; -> Option B

4. Quick Check:

   Distinct on salesperson column only = unique salespersons [OK]

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

Common Mistakes:

• Selecting multiple columns with DISTINCT when only one is needed
• Using GROUP BY on wrong column
• Writing invalid WHERE DISTINCT syntax