EXERCISE TOPIC▼
SQL EXERCISES▼
SQL EXERCISES▼
- Simple Queries (4)
- Setting criteria using WHERE (5)
- Calculations (7)
- Calculations using dates (4)
- Basic joins (8)
- More exotic joins (2)
- Aggregation and grouping (8)
- Views (5)
- Subqueries (5)
- Stored procedures (5)
- Variables (8)
- Parameters and return values (11)
- Testing conditions (1)
- Looping (3)
- Scalar functions (6)
- Transactions (5)
- Creating tables (5)
- Temporary tables and table variables (9)
- Table-valued functions (6)
- Derived tables and CTEs (13)
- Dynamic SQL (4)
- Pivots (2)
- Triggers (2)
- Archived (70)
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 |
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:
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
AS
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.