This page has 1 thread | Add post

Exercises

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

Software ==>	SQL (202 exercises)
Topic ==>	Derived tables and CTEs (19 exercises)
Level ==>	Harder than average
Subject ==>	SQL training

Use common table expressions, subqueries or any other technique to solve a complex query

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

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 1 thread

Add a new post

From:	svjmandryk
When:	01 Aug 20 at 17:04

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 :)

From:	Marcus
When:	02 Aug 23 at 20:44

with DoctorsAndEnemies
as
(
   select
       d1.DoctorName,
       e1.Title,
       ee1.EnemyId
   from
       tblDoctor d1
       join tblEpisode e1 on d1.DoctorId = e1.DoctorId
       left join tblEpisodeEnemy ee1 on e1.EpisodeId = ee1.EpisodeId
)

select
   tennant.Title
from
   DoctorsAndEnemies tennant
   left join DoctorsAndEnemies others on others.EnemyId = tennant.EnemyId
       and others.DoctorName != 'David Tennant'
where
   tennant.DoctorName = 'David Tennant'
group by
   tennant.Title
having
   max(case when tennant.EnemyId = others.Enemyid then 1 else 0 end) = 0
order by tennant.Title

I'm not the gratest fan of CTEs ... but sometimes they become handy.

P.S. Don't use UDF (Inline as well) functions too often... they're not well optimised and usually slow queries a lot (even with the UDF inlining introduced with SQL Server 2019).

From:	Andy B
When:	03 Aug 23 at 11:25

I use CTEs for virtually everything in SQL - I probably shouldn't, but I'm a big fan!

From:	Andy B
When:	03 Aug 23 at 14:23

I agree with all your points, but ... speed isn't usually an issue for our internal systems, but ease of development is. So in this case I'd go for anything which saves the human time! But your points are well made.

From:	Marcus
When:	03 Aug 23 at 13:29

I was using CTEs a lot during my Oracle times. Using temporary tables was a chore and CTEs had one very neat hint – MATERIALIZED – it allowed to compute CTE only once and store it in RAM for a time of an execution… But that’s Oracle…

In SQL Server:

1) There’s no option for materialized CTE – it is first computed when select statement demands it, flushed and if it’s called once again – recomputed (very rarely lasts).

2) It’s not so memory based as people think – if CTE is large enough, it’ll be spooled into tempDB, the very same way as intermediate “tables” are.

3) Overall – it’s syntax sugar for derived tables – it reduces redundancy in terms of writing a code (don’t need to write your subquery twice) – increases code readability. But doesn’t have any positive impact in terms of performance (could even worsen it)

Now I’m using them only in such scenarios:

1) Recursion – hierarchical structures, need for generation some sequence of data (e.g. dates, numbers)

2) Deduplication (delete duplicates directly from cte)

3) Basic low cost queries

What’s better? Temporary tables are:

1) They behave the very same way as normal table pages – not so easily flushed, but if needed to – stored on the disk.

2) Since SQL Server 2019 “memory only” temp tables are promoted. If your temp table is short lived, there is a strong chance that it would be never written on the disk

3) You’re guaranteed that they’re computed only once. Even if stored on disk – the reuse is simple as data stream from the I/O without costly joins or other computations

4) They’re permanent in terms of session – it’s very debug friendly. If something wrong you can peek what it is by selecting it.

5) On the larger sets – they can be indexed as any normal table.

Add a new post

EXERCISE TOPIC

SQL EXERCISES

SQL EXERCISES

Use common table expressions, subqueries or any other technique to solve a complex query

Head office

London

Manchester