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.