EXERCISE TOPIC▼
- Access exercises (91)
- C# exercises (79)
- Excel exercises (278)
- Power Apps exercises (13)
- Power Automate exercises (18)
- Power BI exercises (139)
- Power Platform exercises (157)
- Python exercises (28)
- Report Builder exercises (141)
- SQL exercises (198)
- SSAS exercises (51)
- SSIS exercises (46)
- SSRS exercises (99)
- VBA exercises (85)
- Visual Basic exercises (46)
SQL EXERCISES▼
SQL EXERCISES▼
- Simple Queries (4)
- Setting criteria using WHERE (5)
- Calculations (7)
- Calculations using dates (4)
- Basic joins (8)
- More exotic joins (2)
- Aggregation and grouping (8)
- Views (5)
- Subqueries (5)
- Stored procedures (5)
- Variables (8)
- Parameters and return values (11)
- Testing conditions (1)
- Looping (3)
- Scalar functions (6)
- Transactions (5)
- Creating tables (5)
- Temporary tables and table variables (9)
- Table-valued functions (6)
- Derived tables and CTEs (13)
- Dynamic SQL (4)
- Pivots (2)
- Triggers (2)
- Archived (70)
SQL | Variables exercise | Use variables to show details for a given episode number
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
Software ==> | SQL (198 exercises) |
Version ==> | Any version of SQL Server |
Topic ==> | Variables (8 exercises) |
Level ==> | Relatively easy |
Subject ==> | SQL training |
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
First (if you haven't already done so) run the script shown above to generate the Doctor Who training database.
Start a query, creating a variable to hold the id number of the Doctor Who episode whose details you want to show:
USE DoctorWho
-- the episode number of interest
DECLARE @EpisodeId int = 54
The aim of this exercise is to get to the point where you can run this command:
-- list out the details for this episode
SELECT
@EpisodeName as Title,
@EpisodeId as 'Episode id',
@NumberCompanions as 'Number of companions',
@NumberEnemies as 'Number of enemies'
Here's what this should show for episode id 54, for example:

This episode featured 2 companions and 2 enemies.
First, create a variable called @EpisodeName and set this to hold the title of the episode with id number equal to the value of variable @EpisodeId. The syntax that you'll need is:
DECLARE @VariableName VariableType = (
-- some select statement which returns a single value
SELECT ...
)
Now create two variables and set their values as follows:
Variable name | Value |
---|---|
@NumberCompanions | The number of companions for this episode (ie the count of the number of rows in table tblEpisodeCompanion where the episode id equals the one contained in the variable @EpisodeId). |
@NumberEnemies | The number of enemies for this episode (this time counting the number of rows for this episode in table tblEpisodeEnemy). |
Complete and run your query to check that it gives this output if you change the episode id to 42:

This episode had 1 companion and 2 enemies.
Optionally, save your query as Counting by numbers, then close it down.
I have no experience with SQL (yet:)) and I wrote a slightly different code. I didn't want to write multiple selects, and wanted to write one select with inner join. The code works well, but I was wondering which is more practical in general? This one or the one in the answer?
DECLARE @EpisodeName varchar(100)
DECLARE @EpisodeId int = 42
DECLARE @NumberCompanions int
DECLARE @NumberEnemies int
SELECT
@EpisodeName = Title
,@NumberCompanions = COUNT(c.EpisodeId)
,@NumberEnemies = COUNT(EnemyID)
FROM
tblEpisode as e
INNER JOIN tblEpisodeCompanion as c
ON c.EpisodeId = e.EpisodeId
INNER JOIN tblEpisodeEnemy as n
ON e.EpisodeId = n.EpisodeId
WHERE
@EpisodeID = e.EpisodeId
GROUP BY
EpisodeNumber
,e.Title
,e.EpisodeID
SELECT
@EpisodeName AS Title
,@EpisodeID AS 'Episode id'
,@NumberCompanions AS 'Number of companions'
,@NumberEnemies AS 'Number of enemies'
I find the comment that you have no experience of SQL unconvincing!
It's often the case in programming that if you can break a complex instruction down into a series of simpler steps the resulting code is easier to debug, understand and maintain. You can be too clever! However, on this occasion I'd say your approach is probably the better one, although it's obviously not what the question asks you to do.