Variables and SET statements in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SQL variable assignment?

Consider the following SQL code snippet:

DECLARE @count INT;
SET @count = (SELECT COUNT(*) FROM Employees WHERE Department = 'Sales');
SELECT @count AS SalesCount;

Assuming the Employees table has 5 employees in the Sales department, what will be the output of the SELECT statement?

SQL

DECLARE @count INT;
SET @count = (SELECT COUNT(*) FROM Employees WHERE Department = 'Sales');
SELECT @count AS SalesCount;

A Runtime error: variable not declared

B

SalesCount
0

C Syntax error due to missing semicolon

D

SalesCount
5

💡 Hint

The SET statement assigns the count of employees in Sales to the variable @count.

The variable @count is assigned the number of employees in the Sales department using a subquery. Since there are 5 such employees, the SELECT returns 5.

📝 Syntax

intermediate

Which option correctly declares and sets a variable in SQL?

You want to declare an integer variable named @total and set it to 100. Which of the following options is syntactically correct in standard SQL Server syntax?

A DECLARE @total INT = 100;

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

C SET @total INT = 100;

D DECLARE INT @total; SET @total := 100;

💡 Hint

In SQL Server, DECLARE and SET are separate statements.

Option B correctly declares the variable first, then sets its value. Option B is invalid in older SQL Server versions. Option B uses SET incorrectly. Option B uses MySQL syntax which is invalid in SQL Server.

❓ optimization

advanced

Optimizing variable assignment with multiple SET statements

You have the following SQL code:

DECLARE @a INT;
DECLARE @b INT;
SET @a = (SELECT COUNT(*) FROM Orders WHERE Status = 'Pending');
SET @b = (SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped');

Which option optimizes the variable assignments to reduce the number of queries?

SQL

DECLARE @a INT;
DECLARE @b INT;
SET @a = (SELECT COUNT(*) FROM Orders WHERE Status = 'Pending');
SET @b = (SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped');

A

DECLARE @a INT, @b INT;
SELECT @a = SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END), @b = SUM(CASE WHEN Status = 'Shipped' THEN 1 ELSE 0 END) FROM Orders;

B

DECLARE @a INT, @b INT;
SET @a = (SELECT COUNT(*) FROM Orders WHERE Status IN ('Pending', 'Shipped'));
SET @b = @a;

C

DECLARE @a INT, @b INT;
SELECT @a = COUNT(*), @b = COUNT(*) FROM Orders WHERE Status = 'Pending' OR Status = 'Shipped';

D

DECLARE @a INT, @b INT;
SET @a = (SELECT COUNT(*) FROM Orders WHERE Status = 'Pending');
SET @b = (SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped');

💡 Hint

Try to use a single query to assign both variables.

Option A uses a single SELECT with conditional aggregation to assign both variables in one query, reducing database calls and improving performance.

🔧 Debug

advanced

Identify the error in this variable assignment

Examine the following SQL code:

DECLARE @name VARCHAR(50);
SET @name = SELECT FirstName FROM Employees WHERE EmployeeID = 10;

What error will this code produce?

SQL

DECLARE @name VARCHAR(50);
SET @name = SELECT FirstName FROM Employees WHERE EmployeeID = 10;

A Syntax error near 'SELECT'

B No error, variable assigned correctly

C Runtime error: variable not declared

D Type mismatch error

💡 Hint

Check the syntax of the SET statement when assigning from a SELECT.

The SET statement requires parentheses around the SELECT query or the SELECT must be used alone to assign variables. Missing parentheses cause a syntax error.

🧠 Conceptual

expert

Understanding variable scope and lifetime in SQL scripts

In a SQL Server script, you declare a variable inside a BEGIN...END block as:

BEGIN
  DECLARE @temp INT = 5;
END
SELECT @temp;

What will happen when the last SELECT statement runs?

SQL

BEGIN
  DECLARE @temp INT = 5;
END
SELECT @temp;

A Output: 5

B Syntax error due to variable declaration inside BEGIN...END

C Runtime error: Must declare the scalar variable "@temp".

D Output: NULL

💡 Hint

Consider the scope of variables declared inside BEGIN...END blocks.

Variables declared inside a BEGIN...END block are scoped to that block and do not exist outside it. The SELECT outside the block cannot access @temp, causing a runtime error.

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