- 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 | Parameters and return values 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.
You can learn how to do this exercise if you attend one of more of the courses listed below!
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:
The tables you'll need for this query.
This query should return 16 rows when you run it:
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.