This page has 1 thread | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SQL (129 exercises)
Topic ==>	Variables (8 exercises)
Level ==>	Relatively easy
Subject ==>	SQL training

Use a variable holding a row id to get at the details for a row

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.

Before you can do this exercise, you'll need to download and unzip this file.

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.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 1 thread

Add a new post

From:	niflheimis
When:	17 Jun 22 at 11:23

Reply to niflheimis

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'

From:	Andy B
When:	17 Jun 22 at 17:11

Reply to Andy B

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.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

EXERCISE TOPIC

SQL EXERCISES

SQL EXERCISES

Use a variable holding a row id to get at the details for a row

Head office

London

Manchester