PostgreSQL Exercises (original) (raw)

Last Updated : 23 Jul, 2025

**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 Exercises

**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 20**22.

**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 BEGINIF(TGOP=′INSERT′)THENUPDATEBooksSETcopiesavailable=copiesavailable−1WHEREbookid=NEW.bookid;ELSIF(TGOP=′UPDATE′)ANDNEW.returndateISNOTNULLTHENUPDATEBooksSETcopiesavailable=copiesavailable+1WHEREbookid=NEW.bookid;ENDIF;RETURNNEW;END;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;BEGINIF(TGO​P=′INSERT′)THENUPDATEBooksSETcopiesa​vailable=copiesa​vailable−1WHEREbooki​d=NEW.booki​d;ELSIF(TGO​P=′UPDATE′)ANDNEW.returnd​ateISNOTNULLTHENUPDATEBooksSETcopiesa​vailable=copiesa​vailable+1WHEREbooki​d=NEW.booki​d;ENDIF;RETURNNEW;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