This page has 0 threads | Add post

Exercises

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

Software ==>	SQL (203 exercises)
Topic ==>	Stored procedures (8 exercises)
Level ==>	Relatively easy
Subject ==>	SQL training

Create a stored procedure to list Dr Who episodes featuring Matt Smith

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.

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

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:

Choose to design a query using the Management Studio editor.

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

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

SELECT

-- list of columns to include

Column1 AS [Alias1],

...

ColumnN AS [AliasN]

FROM

-- one or more tables joined together

Table1 AS t1

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

WHERE

-- put here conditions which must be true

ORDER BY

-- 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 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.

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

This page has 0 threads

Add a new post

EXERCISE TOPIC

SQL EXERCISES

SQL EXERCISES

Create a stored procedure to list Dr Who episodes featuring Matt Smith

The Easy Way

The "Hard" Way

Creating a Stored Procedure

Head office

London

Manchester