562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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 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 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
Whether you choose the hard or the easy way, you should be left with a SQL statement listing out the 44 Matt Smith episodes.
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.
25 Aytoun Street