EXERCISE TOPIC▼
- Access exercises (91)
- C# exercises (79)
- Excel exercises (278)
- Power Apps exercises (13)
- Power Automate exercises (18)
- Power BI exercises (139)
- Python exercises (28)
- Report Builder exercises (141)
- SQL exercises (198)
- SSAS exercises (51)
- SSIS exercises (46)
- SSRS exercises (99)
- VBA exercises (85)
- Visual Basic exercises (46)
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 | Derived tables and CTEs exercise | Solve a complex question using CTEs and/or subqueries
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 ==> | Derived tables and CTEs (13 exercises) |
Level ==> | Harder than average |
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 run the script shown above to generate the Doctor Who training database.
Create a query which lists the David Tennant episodes for which none of the enemies appear in any non-David Tennant episodes:

The first few of the 31 David Tennant episodes which don't share any enemies with any other non-Tennant episodes.
There are probably hundreds of ways to do this in SQL, and the answer given doesn't claim to use a particularly efficient one.
Optionally, save this query as Tennant only, then close it down.
Spoiler alert :)
********************************
my solution:
create function [dbo].[getAuthorId](@authorName varchar(max))
returns varchar(max)
as
begin
return
(select DoctorId
from tblDoctor
where lower(DoctorName) like '%' + @authorName)
end
go
create function [dbo].[getEpisodesByAuthor](@authorName varchar(max))
returns table
as
return select e.EpisodeId, e.Title, e.DoctorId, ee.EnemyId, ee.EpisodeEnemyId
from tblEpisode as e
left join tblEpisodeEnemy as ee
on e.EpisodeId = ee.EpisodeId
where DoctorId = [dbo].getAuthorId(@authorName)
go
create function [dbo].[getEpisodesNotByAuthor](@authorName varchar(max))
returns table
as
return select e.EpisodeId, e.Title, e.DoctorId, ee.EnemyId, ee.EpisodeEnemyId
from tblEpisode as e
join tblEpisodeEnemy as ee
on e.EpisodeId = ee.EpisodeId
where DoctorId <> [dbo].getAuthorId(@authorName)
go
create function [dbo].[getIdsOfEpisodeWithoutExclusiveEnemies](@authorName varchar(max))
returns table
as
return
select distinct EpisodeId
from tblEpisodeEnemy
where EnemyId in (
select distinct EnemyId
from [dbo].[getEpisodesNotByAuthor](@authorName))
go
declare @authorName varchar(max) = 'Tennant'
select distinct EpisodeId, Title, DoctorId --, EnemyId, EpisodeEnemyId
from [dbo].[getEpisodesByAuthor](@authorName)
where
EpisodeId not in (
select EpisodeId
from [dbo].getIdsOfEpisodeWithoutExclusiveEnemies(@authorName))
order by Title
ps - this was the trickiest one for me :)