Variables and SET statements in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a variable named @count as an integer.

SQL

DECLARE [1] INT;

Drag options to blanks, or click blank then click option'

Acount

B$count

C#count

D@count

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting the '@' symbol before the variable name.

Using invalid characters like '#' or '$' in variable names.

In SQL, variables are declared with an '@' prefix. So '@count' is the correct variable name.

2fill in blank

medium

Complete the code to set the value 10 to the variable @count.

SQL

SET [1] = 10;

Drag options to blanks, or click blank then click option'

Acount

B#count

C@count

D$count

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the '@' symbol when setting the variable.

Using the variable name without declaration.

To assign a value to a variable in SQL, use SET with the variable name including '@'.

3fill in blank

hard

Fix the error in the code to correctly set the variable @total to the sum of 5 and 7.

SQL

SET [1] = 5 + 7;

Drag options to blanks, or click blank then click option'

Atotal

B@total

C#total

D$total

Attempts:

3 left

💡 Hint

Common Mistakes

Using variable names without '@' in SET statements.

Using invalid variable prefixes like '#' or '$'.

Variables in SQL must be referenced with '@' when assigning values.

4fill in blank

hard

Fill both blanks to declare a variable @name as VARCHAR(50) and set it to 'Alice'.

SQL

DECLARE [1] [2];
SET @name = 'Alice';

Drag options to blanks, or click blank then click option'

A@name

BVARCHAR(50)

CINT

DTEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INT or TEXT instead of VARCHAR(50) for string variables.

Forgetting '@' in variable declaration.

Declare the variable with '@name' and specify its type as VARCHAR(50) for text up to 50 characters.

5fill in blank

hard

Fill all three blanks to declare @score as INT, set it to 100, and then increase it by 20.

SQL

DECLARE [1] [2];
SET [3] = 100;
SET @score = @score + 20;

Drag options to blanks, or click blank then click option'

A@score

BINT

DVARCHAR(10)

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR type for numeric variables.

Forgetting '@' in variable names during SET.

Declare @score as INT, then set @score to 100, and finally add 20 to it.

Practice

1. What is the purpose of using variables in SQL with DECLARE and SET statements?

easy

A. To store temporary values that can be used later in the query

B. To permanently save data in the database

C. To create new tables automatically

D. To delete rows from a table

Solution

1. Step 1: Understand variable usage in SQL

   Variables declared with DECLARE hold temporary values during query execution.

2. Step 2: Identify the purpose of SET

   SET assigns values to these variables for use later in the query.

3. Final Answer:

   To store temporary values that can be used later in the query -> Option A

4. Quick Check:

   Variables = Temporary storage [OK]

Hint: Variables hold temporary data during query execution [OK]

Common Mistakes:

• Thinking variables save data permanently
• Confusing variables with table creation
• Assuming variables delete data

2. Which of the following is the correct syntax to declare an integer variable named @count and set its value to 10 in SQL?

easy

A. DECLARE @count INT; SET @count = 10;

B. SET @count INT; DECLARE @count = 10;

C. DECLARE @count = 10 INT;

D. SET @count = 10 INT;

Solution

1. Step 1: Review correct variable declaration syntax

   In SQL, DECLARE @variable_name TYPE; declares a variable.

2. Step 2: Assign value using SET

   SET @variable_name = value; assigns the value.

3. Final Answer:

   DECLARE @count INT; SET @count = 10; -> Option A

4. Quick Check:

   DECLARE then SET = correct syntax [OK]

Hint: Declare first, then set value with SET [OK]

Common Mistakes:

• Using SET before DECLARE
• Mixing declaration and assignment in wrong order
• Incorrect placement of data type

3. Consider the following SQL code:

DECLARE @x INT;
SET @x = 5;
SET @x = @x + 3;
SELECT @x;

What will be the output of the SELECT statement?

medium

A. 5

B. 8

C. 3

D. NULL

Solution

1. Step 1: Initialize variable @x

   @x is declared as integer and set to 5.

2. Step 2: Update @x by adding 3

   @x = 5 + 3 = 8.

3. Final Answer:

   8 -> Option B

4. Quick Check:

   5 + 3 = 8 [OK]

Hint: Add values stepwise to variable [OK]

Common Mistakes:

• Forgetting to update variable value
• Assuming variable resets after SET
• Confusing initial and updated values

4. Identify the error in the following SQL code:

DECLARE @total INT
SET total = 100;
SELECT @total;

medium

A. Missing semicolon after DECLARE statement

B. SELECT statement syntax is incorrect

C. Variable name missing '@' in SET statement

D. Variable @total not declared

Solution

1. Step 1: Check variable declaration

   @total is declared correctly with DECLARE @total INT.

2. Step 2: Check SET statement

   SET statement uses total without '@', which is incorrect; it should be @total.

3. Final Answer:

   Variable name missing '@' in SET statement -> Option C

4. Quick Check:

   Variable names need '@' prefix [OK]

Hint: Always use '@' before variable names [OK]

Common Mistakes:

• Omitting '@' in variable usage
• Forgetting semicolons (usually optional)
• Misreading SELECT syntax

5. You want to calculate the total price for 3 items each costing 15, 20, and 25 using variables. Which SQL code correctly declares variables, sets their values, and calculates the total?

hard

A. DECLARE @item1 INT = 15, @item2 INT = 20, @item3 INT = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

B. DECLARE @total INT; SET total = 15 + 20 + 25; SELECT @total;

C. SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

D. DECLARE @item1 INT; DECLARE @item2 INT; DECLARE @item3 INT; DECLARE @total INT; SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total;

Solution

1. Step 1: Declare all variables before use

   Variables @item1, @item2, @item3, and @total must be declared first.

2. Step 2: Assign values and calculate total

   Assign each item price with SET, then sum them into @total.

3. Step 3: Select the total value

   Use SELECT to output the total price.

4. Final Answer:

   DECLARE @item1 INT; DECLARE @item2 INT; DECLARE @item3 INT; DECLARE @total INT; SET @item1 = 15; SET @item2 = 20; SET @item3 = 25; SET @total = @item1 + @item2 + @item3; SELECT @total; -> Option D

5. Quick Check:

   Declare then set all variables before calculation [OK]

Hint: Declare all variables first, then assign and calculate [OK]

Common Mistakes:

• Assigning values without declaring variables
• Trying to declare and assign in one line incorrectly
• Missing variable declarations