Variables and control flow in MySQL - Mini Project: Build & Apply

Using Variables and Control Flow in MySQL

📖 Scenario: You are managing a small bookstore database. You want to calculate discounts for customers based on the number of books they buy. You will use MySQL variables and control flow to determine the discount percentage.

🎯 Goal: Build a MySQL script that uses variables and IF control flow to assign a discount percentage based on the number of books purchased.

📋 What You'll Learn

Create a variable to store the number of books purchased.

Create a variable to store the discount percentage.

Use IF control flow to set the discount percentage based on the number of books.

Complete the script by selecting the discount percentage.

💡 Why This Matters

🌍 Real World

Bookstores and retail businesses often use variables and control flow in their databases to calculate discounts and promotions automatically.

💼 Career

Understanding variables and control flow in SQL is essential for database administrators and developers to implement business logic inside the database.

1

Set the number of books purchased

Create a MySQL variable called @books_purchased and set it to 7 to represent the number of books a customer bought.

MySQL

-- Your code here

Need a hint?

Use the SET statement to assign a value to a variable in MySQL.

2

Create a variable for discount percentage

Create a MySQL variable called @discount and initialize it to 0 to hold the discount percentage.

MySQL

SET @books_purchased = 7;
-- Your code here

Need a hint?

Initialize the discount variable to zero before applying any conditions.

3

Use IF control flow to assign discount

Use a MySQL IF statement to set @discount to 10 if @books_purchased is greater than or equal to 5, otherwise keep it 0.

MySQL

SET @books_purchased = 7;
SET @discount = 0;
-- Your code here

Need a hint?

Use IF ... THEN ... END IF; syntax in MySQL to apply conditional logic.

4

Select the discount percentage

Write a SELECT statement to display the value of the @discount variable.

MySQL

SET @books_purchased = 7;
SET @discount = 0;
IF @books_purchased >= 5 THEN
  SET @discount = 10;
END IF;
-- Your code here

Need a hint?

Use SELECT to show the value of a variable in MySQL.