This page has 0 threads | Add post

Exercises

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

Software ==>	SQL (129 exercises)
Topic ==>	Table-valued functions (6 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

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

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

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

Head office

London

Manchester