String aggregation with STRING_AGG in PostgreSQL - Mini Project: Build & Apply

String aggregation with STRING_AGG

📖 Scenario: You are managing a small bookstore database. You want to list each author along with all the book titles they have written, combined into a single string for easy reading.

🎯 Goal: Build a query that uses STRING_AGG to combine book titles for each author into one string, separated by commas.

📋 What You'll Learn

Create a table called books with columns author (text) and title (text).

Insert the exact data rows provided.

Write a query that uses STRING_AGG to group book titles by author.

Order the aggregated titles alphabetically within the string.

💡 Why This Matters

🌍 Real World

Combining multiple related text entries into one string is common in reports, summaries, and user interfaces where concise display is needed.

💼 Career

Database developers and analysts often use STRING_AGG to prepare readable aggregated data for dashboards, reports, and data exports.

1

Create the books table and insert data

Create a table called books with columns author (text) and title (text). Then insert these exact rows: ('Alice Walker', 'The Color Purple'), ('Alice Walker', 'Meridian'), ('George Orwell', '1984'), ('George Orwell', 'Animal Farm'), ('J.K. Rowling', 'Harry Potter and the Sorcerer''s Stone').

PostgreSQL

-- Create the books table and insert data
-- Your code here

Need a hint?

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

2

Set up the query to select authors

Write a SELECT statement that selects the author column from the books table and prepares to group results by author.

PostgreSQL

CREATE TABLE books (
    author TEXT,
    title TEXT
);

INSERT INTO books (author, title) VALUES
('Alice Walker', 'The Color Purple'),
('Alice Walker', 'Meridian'),
('George Orwell', '1984'),
('George Orwell', 'Animal Farm'),
('J.K. Rowling', 'Harry Potter and the Sorcerer''s Stone');

-- Write SELECT author and GROUP BY author
-- Your code here

Need a hint?

Use GROUP BY author to prepare for aggregation.

3

Use STRING_AGG to combine book titles

Modify the SELECT statement to include STRING_AGG(title, ', ' ORDER BY title) as books_list to combine book titles for each author, ordered alphabetically.

PostgreSQL

CREATE TABLE books (
    author TEXT,
    title TEXT
);

INSERT INTO books (author, title) VALUES
('Alice Walker', 'The Color Purple'),
('Alice Walker', 'Meridian'),
('George Orwell', '1984'),
('George Orwell', 'Animal Farm'),
('J.K. Rowling', 'Harry Potter and the Sorcerer''s Stone');

-- Add STRING_AGG(title, ', ' ORDER BY title) AS books_list
-- Your code here

Need a hint?

Use STRING_AGG with ORDER BY title inside to sort titles alphabetically.

4

Complete the query with ordering by author

Add an ORDER BY author clause at the end of the query to list authors alphabetically.

PostgreSQL

CREATE TABLE books (
    author TEXT,
    title TEXT
);

INSERT INTO books (author, title) VALUES
('Alice Walker', 'The Color Purple'),
('Alice Walker', 'Meridian'),
('George Orwell', '1984'),
('George Orwell', 'Animal Farm'),
('J.K. Rowling', 'Harry Potter and the Sorcerer''s Stone');

SELECT author, STRING_AGG(title, ', ' ORDER BY title) AS books_list
FROM books
GROUP BY author
-- Add ORDER BY author here
-- Your code here

Need a hint?

Use ORDER BY author to sort the final results by author name.