WHILE loops in procedures in SQL - Mini Project: Build & Apply

Using WHILE Loops in SQL Procedures

📖 Scenario: You are managing a small store database. You want to create a procedure that counts from 1 to 5 and stores each number in a table. This will help you understand how loops work inside SQL procedures.

🎯 Goal: Create a SQL procedure that uses a WHILE loop to insert numbers from 1 to 5 into a table called NumberList.

📋 What You'll Learn

Create a table called NumberList with one column num of type INTEGER.

Create a procedure called InsertNumbers.

Inside the procedure, declare a variable counter starting at 1.

Use a WHILE loop to insert numbers from 1 to 5 into NumberList.

Increment the counter by 1 each loop iteration.

End the procedure properly.

💡 Why This Matters

🌍 Real World

Loops in SQL procedures help automate repetitive tasks like inserting multiple rows or updating many records without writing repetitive code.

💼 Career

Understanding loops in SQL procedures is useful for database administrators and developers who need to write efficient and maintainable database scripts.

1

Create the NumberList table

Write a SQL statement to create a table called NumberList with one column named num of type INTEGER.

SQL

-- Your code here to create the NumberList table

Need a hint?

Use CREATE TABLE NumberList (num INTEGER); to create the table.

2

Start the InsertNumbers procedure and declare counter

Write the beginning of a SQL procedure named InsertNumbers. Inside it, declare an integer variable called counter and set it to 1.

SQL

CREATE TABLE NumberList (num INTEGER);
-- Your code here to start procedure and declare counter

Need a hint?

Use CREATE PROCEDURE InsertNumbers() and inside BEGIN ... END, declare counter with DECLARE counter INT DEFAULT 1;.

3

Add the WHILE loop to insert numbers

Inside the InsertNumbers procedure, write a WHILE loop that runs while counter <= 5. In each loop, insert counter into NumberList and then increase counter by 1.

SQL

CREATE PROCEDURE InsertNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;
    -- Your code here to add WHILE loop and insert numbers

Need a hint?

Use WHILE counter <= 5 DO ... END WHILE; with INSERT INTO NumberList (num) VALUES (counter); and SET counter = counter + 1; inside.

4

Complete the procedure with END

Finish the InsertNumbers procedure by adding the END keyword to close the procedure block.

SQL

CREATE PROCEDURE InsertNumbers()
BEGIN
    DECLARE counter INT DEFAULT 1;
    WHILE counter <= 5 DO
        INSERT INTO NumberList (num) VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
    -- Your code here to end the procedure

Need a hint?

Close the procedure with END;.