Array aggregation with ARRAY_AGG in PostgreSQL - Mini Project: Build & Apply

Array aggregation with ARRAY_AGG in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to collect all book titles written by each author into a list.

🎯 Goal: Build a SQL query that groups books by author and uses ARRAY_AGG to collect all book titles for each author into an array.

📋 What You'll Learn

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

Insert exactly these rows into books: (1, 'Alice Walker', 'The Color Purple'), (2, 'George Orwell', '1984'), (3, 'Alice Walker', 'Meridian'), (4, 'George Orwell', 'Animal Farm').

Write a SQL query that selects author and an array of their title values using ARRAY_AGG.

Group the results by author.

💡 Why This Matters

🌍 Real World

Collecting related items into arrays is useful for reports, summaries, and data analysis in many business applications.

💼 Career

Database developers and analysts often use ARRAY_AGG to aggregate data efficiently for dashboards and applications.

1

Create the books table

Write a SQL statement to create a table called books with columns id as integer, author as text, and title as text.

PostgreSQL

-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER, author TEXT, title TEXT);

2

Insert book records into books

Insert these exact rows into books: (1, 'Alice Walker', 'The Color Purple'), (2, 'George Orwell', '1984'), (3, 'Alice Walker', 'Meridian'), (4, 'George Orwell', 'Animal Farm'). Use four separate INSERT INTO books statements.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    author TEXT,
    title TEXT
);
-- Your code here

Need a hint?

Use four INSERT INTO books VALUES (...); statements with the exact values.

3

Write the query using ARRAY_AGG

Write a SQL SELECT statement that selects author and an array of their title values using ARRAY_AGG(title). Use GROUP BY author to group the results by author.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    author TEXT,
    title TEXT
);

INSERT INTO books VALUES (1, 'Alice Walker', 'The Color Purple');
INSERT INTO books VALUES (2, 'George Orwell', '1984');
INSERT INTO books VALUES (3, 'Alice Walker', 'Meridian');
INSERT INTO books VALUES (4, 'George Orwell', 'Animal Farm');

-- Your code here

Need a hint?

Use SELECT author, ARRAY_AGG(title) AS titles FROM books GROUP BY author;

4

Complete the query with ordering inside ARRAY_AGG

Modify the ARRAY_AGG(title) in your query to order the titles alphabetically by adding ORDER BY title inside the parentheses. The query should still select author and the ordered array of title values grouped by author.

PostgreSQL

CREATE TABLE books (
    id INTEGER,
    author TEXT,
    title TEXT
);

INSERT INTO books VALUES (1, 'Alice Walker', 'The Color Purple');
INSERT INTO books VALUES (2, 'George Orwell', '1984');
INSERT INTO books VALUES (3, 'Alice Walker', 'Meridian');
INSERT INTO books VALUES (4, 'George Orwell', 'Animal Farm');

SELECT author, ARRAY_AGG(title ORDER BY title) AS titles
FROM books
GROUP BY author;
-- Your code here

Need a hint?

Add ORDER BY title inside ARRAY_AGG() to sort the titles alphabetically.