Exercise: Create a procedure to show episodes for any given enemy

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.

The answer to the exercise will be included and explained if you attend the Wise Owl course listed below!

Category ==> SQL  (115 exercises)
Topic ==> Parameters and return values  (8 exercises)
Level ==> Relatively easy
Course ==> Advanced SQL
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

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.

Using the following tables, create a query to show all of the episodes which feature enemies containing the letters Dalek

3 tables joined together

The tables you'll need for this query.

This query should return 16 rows when you run it:

The rows returned

The first few of the 16 episodes featuring Daleks, sorted by series and episode number.

Now change this query into a stored procedure called spEnemyEpisodes, which lists out all of the episodes featuring a given enemy.  Here are some results you should get when you try running this stored procedure with different parameters:

Procedure Number of rows returned
spEnemyEpisodes 'ood' 4 (the episodes featuring the Ood)
spEnemyEpisodes 'auton' 1 (episode featuring the Auton)
spEnemyEpisodes 'silence' 5 (episodes featuring the Silence)

Optionally, save this as Oodve thought it.sql, then close it down, reflecting on what a terrible pun this is.

This page has 0 threads Add post