LOOP, WHILE, FOR iterations in PostgreSQL - Mini Project: Build & Apply

Using LOOP, WHILE, and FOR Iterations in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to practice using different types of loops in PostgreSQL to process book data step-by-step.

🎯 Goal: Create a PostgreSQL function that uses LOOP, WHILE, and FOR iterations to process book sales data and update a summary table.

📋 What You'll Learn

Create a table books with columns id, title, and sales.

Create a table sales_summary with columns total_books and total_sales.

Write a function process_sales() that uses a LOOP to iterate over book IDs.

Inside the function, use a WHILE loop to sum sales for books with sales less than a threshold.

Use a FOR loop to update the sales_summary table with total books and total sales.

💡 Why This Matters

🌍 Real World

Looping through data in database functions helps automate calculations and updates, like sales summaries in a bookstore.

💼 Career

Database developers and administrators often write stored procedures with loops to process data efficiently inside the database.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and sales (integer). Insert these exact rows: (1, 'Book A', 10), (2, 'Book B', 20), (3, 'Book C', 5).

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 rows.

2

Create the sales_summary table

Create a table called sales_summary with columns total_books (integer) and total_sales (integer). Insert one row with values 0 for both columns.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);
-- Create sales_summary table and insert initial row
-- Your code here

Need a hint?

Define the sales_summary table and insert a starting row with zeros.

3

Write the process_sales() function with a LOOP and WHILE

Create a function called process_sales() in PL/pgSQL. Inside it, declare an integer variable book_id starting at 1. Use a LOOP to iterate while book_id is less than or equal to 3. Inside the loop, use a WHILE loop to sum sales of books with sales less than 15 into a variable small_sales_total. Increment book_id by 1 each iteration. End the loop after processing all books.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);

CREATE TABLE sales_summary (total_books INTEGER, total_sales INTEGER);
INSERT INTO sales_summary (total_books, total_sales) VALUES (0, 0);

-- Create function process_sales with LOOP and WHILE
-- Your code here

Need a hint?

Use LOOP with EXIT WHEN to control the loop. Use WHILE inside to add sales less than 15.

4

Add a FOR loop to update sales_summary in the function

Inside the process_sales() function, after the LOOP, add a FOR loop that iterates over all rows in books. Use variables rec for each row. Inside the FOR loop, update sales_summary by setting total_books to the count of books and total_sales to the sum of all sales. End the function properly.

PostgreSQL

CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, sales INTEGER);
INSERT INTO books (id, title, sales) VALUES
  (1, 'Book A', 10),
  (2, 'Book B', 20),
  (3, 'Book C', 5);

CREATE TABLE sales_summary (total_books INTEGER, total_sales INTEGER);
INSERT INTO sales_summary (total_books, total_sales) VALUES (0, 0);

CREATE OR REPLACE FUNCTION process_sales() RETURNS VOID AS $$
DECLARE
  book_id INTEGER := 1;
  small_sales_total INTEGER := 0;
  current_sales INTEGER;
BEGIN
  LOOP
    EXIT WHEN book_id > 3;
    SELECT sales INTO current_sales FROM books WHERE id = book_id;
    WHILE current_sales < 15 LOOP
      small_sales_total := small_sales_total + current_sales;
      EXIT; -- Exit WHILE after adding once to avoid infinite loop
    END LOOP;
    book_id := book_id + 1;
  END LOOP;

  -- Add FOR loop to update sales_summary
  -- Your code here
END;
$$ LANGUAGE plpgsql;

Need a hint?

Use a FOR rec IN SELECT * FROM books LOOP to iterate all books. Update counters inside the loop. Then update the sales_summary table.