PostgreSQL Exercises

PostgreSQL is a powerful, open-source relational database system that supports complex queries, data types, and performance optimization features. This article provides PostgreSQL practice exercises with solutions, allowing learners to explore how joins, aggregations, triggers, and foreign keys work in real scenarios.

These exercises cover every aspect of PostgreSQL, from basic SQL queries to advanced database management tasks like creating triggers and handling views. Let's jump into these exercises and master the art of database management using PostgreSQL.

50 PostgreSQL Exercise Questions

This collection of 50 PostgreSQL exercises offers a comprehensive set of questions designed to challenge and enhance our SQL skills. Covering a wide range of topics, from basic queries to advanced database management techniques, these exercises will help you gain practical experience in working with relational databases.

1. Authors Table

The Author's table contains essential information about authors, including their names, birth years, and countries. This table serves as a reference point for the Books table, linking each book to its respective author through the author_id foreign key.

Query:

CREATE TABLE Authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birth_year INT,
    country VARCHAR(100)
);

-- Insert values into the Authors table
INSERT INTO Authors (name, birth_year, country)
VALUES
('George Orwell', 1903, 'UK'),
('J.K. Rowling', 1965, 'UK'),
('Isaac Asimov', 1920, 'Russia'),
('Mark Twain', 1835, 'USA'),
('Harper Lee', 1926, 'USA');

Output

2. Books Table

The Books table holds data about various books, including titles, associated authors, categories, publication years, and the number of copies available. By referencing the author_id from the Authors table, this table establishes a connection that allows users to query book information alongside author details.

Query:

CREATE TABLE Books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    author_id INT REFERENCES Authors(author_id),
    category VARCHAR(50),
    published_year INT,
    copies_available INT
);

-- Insert values into the Books table
INSERT INTO Books (title, author_id, category, published_year, copies_available)
VALUES
('1984', 1, 'Dystopian', 1949, 5),
('Animal Farm', 1, 'Political Satire', 1945, 3),
('Harry Potter and the Philosopher''s Stone', 2, 'Fantasy', 1997, 7),
('Harry Potter and the Chamber of Secrets', 2, 'Fantasy', 1998, 6),
('Foundation', 3, 'Science Fiction', 1951, 4),
('The Adventures of Tom Sawyer', 4, 'Adventure', 1876, 8),
('To Kill a Mockingbird', 5, 'Fiction', 1960, 10);

Output

3. Members Table

The Members table tracks information about library members, including their names, unique email addresses, and membership dates. This structure is fundamental for managing user access to library resources. The member_id serves as a unique identifier for each member.

Query:

CREATE TABLE Members (
    member_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    membership_date DATE
);

-- Insert values into the Members table
INSERT INTO Members (name, email, membership_date)
VALUES
('Alice Johnson', 'alice.johnson@example.com', '2023-01-15'),
('Bob Smith', 'bob.smith@example.com', '2023-02-10'),
('Charlie Brown', 'charlie.brown@example.com', '2023-03-05'),
('Diana Prince', 'diana.prince@example.com', '2023-04-20'),
('Edward Stark', 'edward.stark@example.com', '2023-05-25');

Output

4. Borrowings Table

The Borrowings table records transactions where members borrow books. It includes details such as the book and member IDs, borrowing dates, and return dates. This table is used for tracking the borrowing activity within the library, allowing for queries that can analyze borrowing patterns, overdue items, and member engagement.

Query:

CREATE TABLE Borrowings (
    borrowing_id SERIAL PRIMARY KEY,
    book_id INT REFERENCES Books(book_id),
    member_id INT REFERENCES Members(member_id),
    borrowed_date DATE,
    return_date DATE
);

-- Insert values into the Borrowings table
INSERT INTO Borrowings (book_id, member_id, borrowed_date, return_date)
VALUES
(1, 1, '2023-07-10', '2023-07-20'),
(3, 2, '2023-06-15', '2023-06-25'),
(5, 3, '2023-08-05', NULL),
(7, 4, '2023-09-01', '2023-09-15'),
(2, 5, '2023-09-10', NULL);

Output

PostgreSQL Questions for Beginners

This section includes practical PostgreSQL queries for basic operations like SELECT, INSERT, UPDATE, and DELETE. The exercises use a schema with Authors, Books, Members, and Borrowings to show how to manage data. Practicing these queries will help us better understand PostgreSQL and improve our skills in real-world database.

Q1. Select all books from the database.

Query:

SELECT * FROM Books;

Output

Explanation:

This query retrieves all the rows and columns from the Books table. It displays details like book ID, title, author ID, category, year of publication, and available copies.

Q2. Find the title and category of all books published in 2020.

Query:

SELECT title, category FROM Books WHERE published_year = 2020;

Output

0 rows

Explanation:

No books were published in 2020 in the current dataset. The query returns no results, indicating that no books in the Books table match the condition of being published in 2020.

Q3. List all authors from the USA.

Query:

SELECT name FROM Authors WHERE country = 'USA';

Output

Explanation:

The query retrieves the names of all authors from the Authors table where the country is 'USA'. In this case, Mark Twain and Harper Lee are from the USA.

Q4. Insert a new book into the Books table.

Query:

INSERT INTO Books (book_id, title, author_id, category, published_year, copies_available)
VALUES (101, 'The Pragmatic Programmer', 1, 'Programming', 1999, 5);

Output

Explanation:

This query inserts a new book with the title "The Pragmatic Programmer" authored by author_id 1, published in 1999, and 5 copies available. The query doesn't return output but adds this record to the Books table.

Q5. Find all members who joined in the year 2023.

Query:

SELECT * FROM Members WHERE EXTRACT(YEAR FROM membership_date) = 2023;

Output

Explanation:

This query retrieves all members from the Members table who joined in the year 2023. It uses the EXTRACT function to filter rows based on the year of the membership_date

Q6. Update the number of copies available for a specific book.

Query:

UPDATE Books SET copies_available = 4 WHERE book_id = 101;

Output

Explanation:

This query updates the copies_available field for the book with book_id 101, setting the value to 4. No output is returned, but the value is modified in the database.

Q7. Delete a book from the Books table.

Query:

DELETE FROM Books WHERE book_id = 101;

Output

Explanation:

This query deletes the book with book_id 101 from the Books table. There’s no output as this is a delete operation, but the book is removed from the database.

Q8. Find all books in the ‘Fiction’ category.

Query:

SELECT * FROM Books WHERE category = 'Fiction';

Output

Explanation:

The query retrieves all books from the Books table that belong to the 'Fiction' category. In this case, "To Kill a Mockingbird" is returned as it falls under this category.

Q9. Display the name and email of all members.

SELECT name, email FROM Members;

Output

Explanation:

The query retrieves the name and email columns of all members from the Members table.

Q10. Count how many books are available in the 'History' category.

Query:

SELECT COUNT(*) FROM Books WHERE category = 'History';

Output

Explanation:

No books belong to the 'History' category in the current dataset. The query returns a count of 0, indicating that there are no books in this category.

Q11. Find the title of the book borrowed by the member with ID 3.

Query:

SELECT title FROM Books 
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id 
WHERE member_id = 3;

Output

Explanation:

The query returns the title of the book borrowed by the member with member_id 3, which is "Foundation". The INNER JOIN between Books and Borrowings tables is used to match the relevant book borrowed by this member.

Q12. Display the name and borrowed_date of all members who borrowed a book in January 2023.

Query:

SELECT name, borrowed_date FROM Members 
INNER JOIN Borrowings ON Members.member_id = Borrowings.member_id 
WHERE EXTRACT(MONTH FROM borrowed_date) = 1 AND EXTRACT(YEAR FROM borrowed_date) = 2023;

Output

0 rows

Explanation:

This query finds members who borrowed a book in January 2023. It uses EXTRACT to filter the month and year and returns (0 rows) which means that no members borrowed books in January 2023.

Q13. List all books authored by 'George Orwell'.

Query:

SELECT title FROM Books 
INNER JOIN Authors ON Books.author_id = Authors.author_id 
WHERE Authors.name = 'George Orwell';

Output

Explanation:

The query returns all books authored by George Orwell using an INNER JOIN between the Books and Authors tables. George Orwell authored "1984" and "Animal Farm".

Q14. Find all authors who were born before 1950.

Query:

SELECT name FROM Authors WHERE birth_year < 1950;

Output

Explanation:

The query returns the names of authors born before 1950. It lists George Orwell, Isaac Asimov, Mark Twain, and Harper Lee.

Q15. Insert a new author into the Authors table.

Query:

INSERT INTO Authors (author_id, name, birth_year, country)
VALUES (5, 'Isaac Asimov', 1920, 'Russia');

Output

ERROR:  duplicate key value violates unique constraint "authors_pkey"
DETAIL:  Key (author_id)=(5) already exists.

Explanation:

The error occurs because the author_id value 5 already exists in the Authors table. The primary key constraint ensures that each author_id must be unique, and the insertion is failing because an author with author_id = 5 already exists.

Q16. Display the total number of members.

Query:

SELECT COUNT(*) FROM Members;

Output

Explanation:

The query returns the total count of members in the Members table, which is 5 in this case.

Q17. Show all borrowings that have not been returned yet.

Query:

SELECT * FROM Borrowings WHERE return_date IS NULL;

Output

Explanation:

In this output, borrowing entries with return_date = NULL are displayed, meaning these are the books that are still borrowed and haven’t been returned yet.

Q18. List all unique categories of books in the library.

Query:

SELECT DISTINCT category FROM Books;

Output

Explanation:

The query would return 6 rows which contains different category of books without duplicates.

Q19. Find the number of books available for each category

Query:

SELECT category, COUNT(*) AS total_books 
FROM Books 
GROUP BY category;

Output

Explanation:

• The table groups books by category and counts the number of books in each one.
  
• There are two books in the Fiction category, one in the Science category, and two in the Programming category.

Q20. Display the name of the member who borrowed the book titled '1984'

Query:

SELECT Members.name 
FROM Members 
INNER JOIN Borrowings ON Members.member_id = Borrowings.member_id 
INNER JOIN Books ON Borrowings.book_id = Books.book_id 
WHERE Books.title = '1984';

Output

Explanation:

• The book titled "1984" has book_id = 1. According to the Borrowings table, the member with member_id = 2 borrowed this book. So Bob is the one who borrowed "1984".

PostgreSQL Questions for Intermediate

The section features 20 medium-level PostgreSQL questions designed to enhance your SQL skills through practical exercises. These questions cover various topics such as querying member borrowings, identifying the most popular books and authors, and managing book records. we will gain valuable experience in performing essential database operations and developing our understanding of PostgreSQL.

Q1. Find the total number of books borrowed by each member.

Query:

SELECT member_id, COUNT(book_id) AS total_borrowed 
FROM Borrowings 
GROUP BY member_id;

Output

Explanation:

The query is counting how many books each member has borrowed, grouped by their member_id. The COUNT(book_id) function is used to count the number of books borrowed by each member.

Q2. Display the title and author of the most borrowed book.

Query:

SELECT title, name FROM Books 
INNER JOIN Authors ON Books.author_id = Authors.author_id
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id 
GROUP BY title, name 
ORDER BY COUNT(Borrowings.book_id) DESC LIMIT 1;

Output

Explanation:

• The query used GROUP BY to group borrowings by the combination of book title and author name, then counted the number of times each book was borrowed.
  
• After sorting by the count of borrowings in descending order, "Animal Farm" came out as the top result.

Q3. Show the author who has the most books in the library.

Query:

SELECT name, COUNT(book_id) AS total_books 
FROM Authors 
INNER JOIN Books ON Authors.author_id = Books.author_id 
GROUP BY name 
ORDER BY total_books DESC LIMIT 1;

Output

Explanation:

• The query grouped the books by author (name) and counted the total number of books associated with each author.
  
• After sorting by the count (total_books) in descending order, George Orwell came out as the author with the most books (2 books).

Q4. Find all members who have borrowed more than 3 books.

Query:

SELECT member_id, COUNT(book_id) AS total_borrowed 
FROM Borrowings 
GROUP BY member_id 
HAVING COUNT(book_id) > 3;

Output

returns 0 rows

Explanation:

• No books have been borrowed by any members yet.
  
• All borrowings might have been deleted or are stored in a different table.
  
• There could be an issue with the database or the data in the Borrowings table, like missing rows or a data reset.

Q5. List all books that have been borrowed but not returned.

Query:

SELECT title FROM Books 
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id 
WHERE Borrowings.return_date IS NULL;

Output

Explanation:

This table shows the titles of the books that have been borrowed and not yet returned, as indicated by the return_date being NULL.

Q6. Find the average number of copies available per category.

Query:

SELECT category, AVG(copies_available) 
FROM Books 
GROUP BY category;

Output

Explanation:

This output shows the average number of copies available for books in each category in the Books table. The AVG(copies_available) function calculates the average of available copies, grouped by each book category.

Q7. Update the return date of a book borrowed by a member.

Query:

UPDATE Borrowings 
SET return_date = '2023-09-10' 
WHERE borrowing_id = 5;

Output

Explanation:

The query updates the return_date for the borrowing record with borrowing_id = 5 to '2023-09-10'. Initially, the return date was NULL, indicating the book hadn't been returned. After the update, it reflects the return on '2023-09-10'.

Q8. Find the titles of books written by authors born in the 20th century.

Query:

SELECT title FROM Books 
INNER JOIN Authors ON Books.author_id = Authors.author_id 
WHERE Authors.birth_year BETWEEN 1901 AND 2000;

Output

Explanation:

This query retrieves the titles of books written by authors born in the 20th century (between 1901 and 2000)

Q9. Display the total number of borrowings made in 2023.

Query:

SELECT COUNT(*) 
FROM Borrowings 
WHERE EXTRACT(YEAR FROM borrowed_date) = 2023;

Output

Explanation:

The query counts the total number of borrowings made in the year 2023 by extracting the year from the borrowed_date field.

Q10. Delete all books in the 'Science' category.

Query:

DELETE FROM Books 
WHERE category = 'Science';

Output

returns 0 rows

Explanation:

The query deletes all books from the Books table where the category is listed as 'Science'. After executing this query, all records in the Books table under the 'Science' category will be removed.

Q11. Display the top 5 most recently published books.

Query:

SELECT title, published_year 
FROM Books 
ORDER BY published_year DESC 
LIMIT 5;

Output

Explanation:

• Harry Potter and the Chamber of Secrets (1998) is the most recently published book in the list.
  
• The results are ordered from the most recent to the oldest published book.
  
• Only the top 5 results are displayed, as limited by the query.

Q12. List the names of members who have borrowed all available books.

Query:

SELECT name FROM Members 
WHERE member_id IN 
(SELECT member_id FROM Borrowings 
 GROUP BY member_id 
 HAVING COUNT(book_id) = (SELECT COUNT(*) FROM Books));

Output

0 rows

Explanation:

No members have borrowed all the available books in the library, which is why the result set is empty

Q13. Find the total number of unique books borrowed by members in 2023.

Query:

SELECT COUNT(DISTINCT book_id) 
FROM Borrowings 
WHERE EXTRACT(YEAR FROM borrowed_date) = 2023;

Output

Explanation:

The result of 5 indicates that five different books were borrowed at least once in 2023

Q14. Display the name and email of members who borrowed books more than twice in 2022.

Query:

SELECT name, email FROM Members 
WHERE member_id IN 
(SELECT member_id FROM Borrowings 
 WHERE EXTRACT(YEAR FROM borrowed_date) = 2022 
 GROUP BY member_id HAVING COUNT(borrowing_id) > 2);

Output

0 rows

Explanation:

This output indicates that there are no members who borrowed more than 2 books in the year 2022. Hence, the table returns zero rows.

Q15. Find all books that were borrowed but have not been returned for over 30 days.

Query:

SELECT title FROM Books 
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id 
WHERE return_date IS NULL 
AND (CURRENT_DATE - borrowed_date) > 30;

Output

Explanation:

This query retrieves the titles of books that have been borrowed but not returned (return_date IS NULL) and have been outstanding for more than 30 days (CURRENT_DATE - borrowed_date > 30). The result shows that both "Animal Farm" and "Foundation" meet these criteria.

Q16. Find the youngest author in the library.

Query:

SELECT name FROM Authors 
ORDER BY birth_year DESC 
LIMIT 1;

Output

Explanation:

This query selects the name of the youngest author in the library by ordering the authors by their birth year in descending order and limiting the result to the first entry. "J.K. Rowling" is returned as the youngest author based on the available data.

Q17. Find the title of books that have more than 10 copies available.

Query:

SELECT title FROM Books 
WHERE copies_available > 10;

Output

Explanation:

This query checks for books in the library that have more than 10 copies available. Since the result returns "(0 rows)," it indicates that there are currently no books in the database with more than 10 available copies.

Q18. Create a view that shows all books along with the author's name.

Query:

CREATE VIEW BookAuthors AS
SELECT Books.title, Authors.name AS author_name
FROM Books
INNER JOIN Authors ON Books.author_id = Authors.author_id;

Output

Explanation:

The command successfully creates a view named BookAuthors, which combines the titles of books with their corresponding authors' names from the Books and Authors tables

Q19. Find the top 3 categories with the most books.

Query:

SELECT category, COUNT(*) AS book_count 
FROM Books 
GROUP BY category 
ORDER BY book_count DESC 
LIMIT 3;

Output

Explanation:

This query retrieves the top three book categories with the highest number of books in the Books table. The output indicates that the "Fantasy" category has the most books (2), while both "Adventure" and "Political Satire" categories have only one book each. The results are sorted in descending order based on the count of books in each category.

Q20. Display the names of all members who borrowed books written by 'J.K. Rowling'.

Query:

SELECT Members.name FROM Members
INNER JOIN Borrowings ON Members.member_id = Borrowings.member_id
INNER JOIN Books ON Borrowings.book_id = Books.book_id
INNER JOIN Authors ON Books.author_id = Authors.author_id
WHERE Authors.name = 'J.K. Rowling';

Output

Explanation:

This query retrieves the names of members who have borrowed books written by the author "J.K. Rowling." The result shows that the member named "Bob Smith" has borrowed at least one book authored by J.K. Rowling, indicating his engagement with the author's works.

Advanced PostgreSQL Questions

This section presents 10 hard-level PostgreSQL questions aimed at challenging your SQL proficiency through complex queries. The questions cover advanced topics like tracking unique book borrowings, calculating average borrowing durations, and creating database triggers for automated updates.

Q1. Find the member who has borrowed the most unique books.

Query:

SELECT member_id, COUNT(DISTINCT book_id) AS unique_books 
FROM Borrowings 
GROUP BY member_id 
ORDER BY unique_books DESC 
LIMIT 1;

Output

Explanation:

This query retrieves the member ID and counts the number of unique books borrowed by each member. It groups the results by member ID and orders them in descending order based on the count of unique books.

Q2. Display the name of the member who borrowed a book for the longest time.

Query:

SELECT name, (return_date - borrowed_date) AS days_borrowed 
FROM Members
INNER JOIN Borrowings ON Members.member_id = Borrowings.member_id 
ORDER BY days_borrowed DESC 
LIMIT 1;

Output

Explanation:

This query retrieves the name of the member along with the number of days they have borrowed a book. It calculates the difference between the return date and the borrowed date to find out how long the book was borrowed.

Q3. Calculate the total number of copies of books borrowed per author.

Query:

SELECT Authors.name, SUM(Books.copies_available) AS total_copies 
FROM Authors
INNER JOIN Books ON Authors.author_id = Books.author_id
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id
GROUP BY Authors.name;

Output

Explanation:

This query retrieves the names of authors along with the total number of available copies of their books that have been borrowed. It joins the Authors, Books, and Borrowings tables to aggregate the data. The SUM(Books.copies_available) function calculates the total number of copies available for each author, grouped by the author's name.

Q4. Find the author with the fewest books in the library.

Query:

SELECT Authors.name, COUNT(Books.book_id) AS book_count 
FROM Authors 
INNER JOIN Books ON Authors.author_id = Books.author_id 
GROUP BY Authors.name 
ORDER BY book_count ASC 
LIMIT 1;

Output

Explanation:

The query retrieves the author with the fewest number of books in the library by joining the Authors and Books tables. It groups the results by the author's name and counts the number of books associated with each author. In this case, Isaac Asimov has the least number of books recorded in the library, with only 1 book.

Q5. Write a query to find the members who never borrowed a book.

Query:

SELECT name FROM Members 
WHERE member_id NOT IN (SELECT member_id FROM Borrowings);

Output

Explanation:

The output indicates that there are no members in the database who haven't borrowed any books, meaning all members have at least one borrowing record.

Q6. Display the average number of days books were borrowed before being returned in 2022.

Query:

SELECT AVG(return_date - borrowed_date) AS avg_days_borrowed FROM Borrowings 
WHERE EXTRACT(YEAR FROM borrowed_date) = 2022;

Output

Explanation:

This query calculates the average number of days books were borrowed before being returned for the year 2022. However, the output shows null, indicating that there are no records of borrowings for that year.

Q7. Find the total number of books borrowed per month in 2023.

Query:

SELECT EXTRACT(MONTH FROM borrowed_date) AS month, COUNT(book_id) AS books_borrowed 
FROM Borrowings WHERE EXTRACT(YEAR FROM borrowed_date) = 2023 
GROUP BY month ORDER BY month;

Output

Explanation:

This query counts the number of books borrowed each month in the year 2023

Q8. List all members who borrowed more than 2 books in January 2023 but haven't borrowed any since.

Query:

SELECT member_id, COUNT(book_id) AS total_borrowed 
FROM Borrowings 
WHERE EXTRACT(MONTH FROM borrowed_date) = 1 
  AND EXTRACT(YEAR FROM borrowed_date) = 2023
GROUP BY member_id 
HAVING COUNT(book_id) > 2 
  AND member_id NOT IN 
    (SELECT member_id 
     FROM Borrowings 
     WHERE EXTRACT(MONTH FROM borrowed_date) > 1 
       AND EXTRACT(YEAR FROM borrowed_date) = 2023);

Output

0 rows

Explanation:

The output will display the member_id of any member who borrowed more than 2 books in January 2023 and has not borrowed any books since then. If no such members exist, the query will return no rows.

Q9. Write a query to find the total number of authors who have at least one book borrowed in 2023.

Query:

SELECT COUNT(DISTINCT Authors.author_id) AS total_authors FROM Authors 
INNER JOIN Books ON Authors.author_id = Books.author_id 
INNER JOIN Borrowings ON Books.book_id = Borrowings.book_id 
WHERE EXTRACT(YEAR FROM borrowed_date) = 2023;

Output

Explanation:

This query counts the total number of unique authors who have at least one book borrowed in the year 2023. The output shows that 4 distinct authors had their books borrowed during this period, indicating a diverse selection of authors available to readers.

Q10 .Create a trigger that updates the copies_available field in the Books table whenever a book is borrowed or returned.

Query:

CREATE OR REPLACE FUNCTION update_copies_available() RETURNS TRIGGER AS $$
BEGIN
   IF (TG_OP = 'INSERT') THEN
      UPDATE Books SET copies_available = copies_available - 1 WHERE book_id = NEW.book_id;
   ELSIF (TG_OP = 'UPDATE') AND NEW.return_date IS NOT NULL THEN
      UPDATE Books SET copies_available = copies_available + 1 WHERE book_id = NEW.book_id;
   END IF;
   RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_copies_available_trigger
AFTER INSERT OR UPDATE ON Borrowings
FOR EACH ROW EXECUTE FUNCTION update_copies_available();

Output

CREATE FUNCTION
CREATE TRIGGER

Explanation:

The output indicates that both the function and trigger have been successfully created in the database. The function will adjust the copies_available in the Books table based on borrowing actions, while the trigger will automatically invoke this function after any insertion or update in the Borrowings table.

Conclusion

In this article, we have explained 50 PostgreSQL exercises across basic, medium, and advanced levels, designed to strengthen our PostgreSQL skills and database management abilities. From fundamental operations like SELECT and INSERT to more complex queries involving unique borrowings and automated triggers.

These exercises provide a comprehensive approach to mastering PostgreSQL. Engaging with these questions will enhance your proficiency and prepare you for real-world database challenges