SELECT with PostgreSQL-specific features - Mini Project: Build & Apply

Using PostgreSQL-Specific SELECT Features

📖 Scenario: You are managing a small bookstore database. You want to practice using PostgreSQL-specific features in SELECT queries to get useful information about books and authors.

🎯 Goal: Build a series of PostgreSQL SELECT queries that use PostgreSQL-specific features like RETURNING, FILTER, and DISTINCT ON to retrieve and manipulate bookstore data.

📋 What You'll Learn

Create a table called books with columns id, title, author, price, and published_year.

Insert sample data into the books table.

Write a SELECT query using DISTINCT ON to get the first book by each author ordered by published year.

Write a SELECT query using FILTER to count books published after 2010.

Write an INSERT statement using RETURNING to add a new book and get its details.

💡 Why This Matters

🌍 Real World

Bookstores and many other businesses use PostgreSQL to store and query their data efficiently. Using PostgreSQL-specific features helps write simpler and more powerful queries.

💼 Career

Knowing PostgreSQL-specific SELECT features is valuable for database developers and analysts working with PostgreSQL databases to optimize queries and handle complex data retrieval.

1

Create the books table and insert sample data

Create a table called books with columns id as serial primary key, title as text, author as text, price as numeric, and published_year as integer. Then insert these exact rows into books: (1, 'The Alchemist', 'Paulo Coelho', 10.99, 1988), (2, 'Brida', 'Paulo Coelho', 12.50, 1990), (3, '1984', 'George Orwell', 15.00, 1949), (4, 'Animal Farm', 'George Orwell', 9.99, 1945), (5, 'Clean Code', 'Robert C. Martin', 33.99, 2008).

PostgreSQL

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

Need a hint?

Use CREATE TABLE with the specified columns and types. Use INSERT INTO books with the exact rows.

2

Set up a variable for the year threshold

Create a variable called year_threshold and set it to 2010. This will be used to filter books published after this year.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    published_year INTEGER
);

INSERT INTO books (id, title, author, price, published_year) VALUES
(1, 'The Alchemist', 'Paulo Coelho', 10.99, 1988),
(2, 'Brida', 'Paulo Coelho', 12.50, 1990),
(3, '1984', 'George Orwell', 15.00, 1949),
(4, 'Animal Farm', 'George Orwell', 9.99, 1945),
(5, 'Clean Code', 'Robert C. Martin', 33.99, 2008);

-- Create a variable year_threshold and set it to 2010
-- Your code here

Need a hint?

Use the PostgreSQL psql command \set to create a variable.

3

Select the first book by each author using DISTINCT ON

Write a SELECT query that uses DISTINCT ON (author) to get the first book by each author ordered by published_year ascending. Select author, title, and published_year.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    published_year INTEGER
);

INSERT INTO books (id, title, author, price, published_year) VALUES
(1, 'The Alchemist', 'Paulo Coelho', 10.99, 1988),
(2, 'Brida', 'Paulo Coelho', 12.50, 1990),
(3, '1984', 'George Orwell', 15.00, 1949),
(4, 'Animal Farm', 'George Orwell', 9.99, 1945),
(5, 'Clean Code', 'Robert C. Martin', 33.99, 2008);

\set year_threshold 2010

-- Write a SELECT query using DISTINCT ON (author) to get the first book by each author ordered by published_year
-- Your code here

Need a hint?

Use DISTINCT ON (author) and order by author and published_year ascending.

4

Count books published after 2010 using FILTER and insert a new book with RETURNING

Write a SELECT query that counts how many books have published_year greater than the variable :year_threshold using COUNT(*) FILTER (WHERE published_year > :year_threshold). Then write an INSERT statement to add a new book with title 'The Pragmatic Programmer', author 'Andrew Hunt', price 42.00, and published_year 1999, and use RETURNING * to get the inserted row details.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT,
    price NUMERIC,
    published_year INTEGER
);

INSERT INTO books (id, title, author, price, published_year) VALUES
(1, 'The Alchemist', 'Paulo Coelho', 10.99, 1988),
(2, 'Brida', 'Paulo Coelho', 12.50, 1990),
(3, '1984', 'George Orwell', 15.00, 1949),
(4, 'Animal Farm', 'George Orwell', 9.99, 1945),
(5, 'Clean Code', 'Robert C. Martin', 33.99, 2008);

\set year_threshold 2010

SELECT DISTINCT ON (author) author, title, published_year
FROM books
ORDER BY author, published_year ASC;

-- Write a SELECT query using COUNT(*) FILTER (WHERE published_year > :year_threshold)
-- Then write an INSERT statement to add the book 'The Pragmatic Programmer' by 'Andrew Hunt' priced 42.00 published in 1999
-- Use RETURNING * to get the inserted row
-- Your code here

Need a hint?

Use COUNT(*) FILTER (WHERE ...) to count conditionally. Use INSERT INTO ... RETURNING * to insert and get the new row.