Variables and SET statements in SQL - Mini Project: Build & Apply

Using Variables and SET Statements in SQL

📖 Scenario: You are managing a small store's sales data. You want to calculate the total sales for a specific product category and store this value in a variable for later use.

🎯 Goal: Create a SQL script that declares a variable, sets it to the total sales amount for the 'Electronics' category, and then updates the variable with a discount amount.

📋 What You'll Learn

Declare a variable named @totalSales of type DECIMAL(10,2).

Set @totalSales to the sum of sales amounts from the Sales table where the category is 'Electronics'.

Declare a variable named @discount of type DECIMAL(10,2) and set it to 100.00.

Update @totalSales by subtracting @discount using a SET statement.

💡 Why This Matters

🌍 Real World

Variables in SQL help store temporary values like totals or counters during data processing, making queries more flexible and readable.

💼 Career

Knowing how to use variables and SET statements is essential for writing dynamic SQL scripts, stored procedures, and performing calculations in database jobs.

1

Declare the @totalSales variable

Declare a variable called @totalSales with the data type DECIMAL(10,2).

SQL

-- Declare the variable @totalSales
-- Your code here

Need a hint?

Use the DECLARE statement to create a variable with the specified name and type.

2

Set @totalSales to sum of sales for 'Electronics'

Write a SET statement to assign @totalSales the sum of the Amount column from the Sales table where the Category is 'Electronics'.

SQL

DECLARE @totalSales DECIMAL(10,2);
-- Set @totalSales to total sales amount for 'Electronics'
-- Your code here

Need a hint?

Use a SELECT statement inside parentheses to calculate the sum and assign it with SET.

3

Declare and set the @discount variable

Declare a variable called @discount with the data type DECIMAL(10,2) and set it to 100.00 using a SET statement.

SQL

DECLARE @totalSales DECIMAL(10,2);
SET @totalSales = (SELECT SUM(Amount) FROM Sales WHERE Category = 'Electronics');
-- Declare @discount and set it to 100.00
-- Your code here

Need a hint?

Declare the variable first, then assign the value with SET.

4

Update @totalSales by subtracting @discount

Use a SET statement to update @totalSales by subtracting the value of @discount.

SQL

DECLARE @totalSales DECIMAL(10,2);
SET @totalSales = (SELECT SUM(Amount) FROM Sales WHERE Category = 'Electronics');
DECLARE @discount DECIMAL(10,2);
SET @discount = 100.00;
-- Subtract @discount from @totalSales
-- Your code here

Need a hint?

Use SET to assign the new value by subtracting @discount from @totalSales.