SQL | Stored procedures exercise | Create a stored procedure to list Matt Smith's Dr. Who episodes

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 ==> Stored procedures  (5 exercises)
Level ==> Relatively easy
Subject ==> SQL training
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.

Write a SELECT statement in SQL to list out all of the episodes which featured Matt Smith as the Doctor:

Matt Smith episode rows

The first few of the 44 episodes featuring Matt Smith.

Two separate sets of instructions for how to do this are shown below, showing the easy way and the "hard" way.  Use the approach you feel comfortable with!

The Easy Way

The acceptable Mickey Mouse way to create a query is to use the Query Designer.  First right-click in a new query and choose this option:

Design query menu option

Choose to design a query using the Management Studio editor.


Now add tables, aliases, sorting, filtering and joins to create this query:

Query to create

The final query.

When you select OK you'll see a SELECT statement, which you can now tidy up!

The "Hard" Way

The other way to create a SQL statement is by typing it.  Here is the basic syntax for a SELECT statement in SQL:

-- select rows from one or more tables


-- list of columns to include

Column1 AS [Alias1],


ColumnN AS [AliasN]



-- one or more tables joined together

Table1 AS t1

INNER JOIN Table2 AS t2 ON t1.ColumnA = t2.ColumnB


-- put here conditions which must be true


-- sort by ascending or descending order

ColumnX ASC,

ColumnY DESC

Whether you choose the hard or the easy way, you should be left with a SQL statement listing out the 44 Matt Smith episodes.

Creating a Stored Procedure

Add the following at the start of your SQL to store it as a stored procedure:

CREATE PROC spMattSmithEpisodes


Run your stored procedure to show that it lists out 44 films.  Now change your script so that it alters the stored procedure to list out only those episodes featuring Matt Smith made in 2012, using this syntax in the WHERE clause:

-- only include 2012 episodes

AND year(EpisodeDate) = 2012

Run your revised stored procedure to prove that it lists out 6 episodes:

The 2012 episodes

The 6 Matt Smith episodes made in 2012.

Optionally, save this SQL as Matt Smith episodes.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.
This page has 0 threads Add post