This page has 0 threads | Add post

Exercises

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

Software ==>	SQL (203 exercises)
Topic ==>	Table-valued functions (8 exercises)
Level ==>	Harder than average
Subject ==>	SQL training

Show episodes, listing companions or enemies according to parameter, using an MSTVF function

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.

You'll find this exercise much easier to do if you have access to the following scalar functions:

Function	Argument	Returns
fnCompanions	EpisodeId	A comma-delimited list of the companions in this episode
fnEnemies	EpisodeId	A comma-delimited list of the enemies in this episode

You may have already created these as part of a (much) earlier exercise on variables; otherwise, this should be your first task!

Write a function (the one in the answer is called fnSilly, for reasons which will become apparent) which takes two arguments:

Argument	Heading 2	Example
@CompanionName	Part or all of a companion name	Wilf
@EnemyName	Part or all of an enemy name	Ood

The easiest way to describe what your function should return is by example. If you run this SQL:

-- show episodes featuring either Wilfred Mott or The Ood

SELECT * FROM dbo.fnSilly('wilf','ood')

Then you should see this:

There are 2 episodes featuring Wilf, and 4 featuring the Ood (although one of the episodes is the same in each case).

The important thing to notice is that where an episode is selected by reason of its companion, the Appearing column shows a comma-delimited list of the companions appearing in the episode; where it is selected by reason of its enemy, the Appearing column shows a comma-delimited list of the enemies appearing in the episode.

Still bored? You could always work out how to combine the companions and enemies in the Appearing column for a duplicated episode, to avoid it appearing twice in the list (this isn't included in the answer!).

Optionally, save your query as Getting silly.sql, 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 0 threads

Add a new post