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 | More exotic joins exercise | Use outer joins to show companions who have no 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.
You can learn how to do this exercise if you attend the course listed below!
Software ==> | SQL (198 exercises) |
Version ==> | Any version of SQL Server |
Topic ==> | More exotic joins (2 exercises) |
Level ==> | Average difficulty |
Course ==> | Fast-track SQL |
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.
If you haven't already done so, run the script in the above folder to generate the Doctor Who database.
The database contains details of companions, and the episodes each appeared in:

Each companion can appear in one or more episodes.
Use this to list the names of the companions who haven't featured in any episodes. You should find there's one of them, but we won't spoil the surprise by saying who it is!
Create a query based on the companions table, with an outer join to the episode companion table.
Save this query as outre.sql (that's neither a misspelling nor a funny joke), and close it down.
I am not getting any results. Is this the right code?
use doctorwho
Select companionname
from tblCompanion as c
left outer join tblEpisodeCompanion as e
on c.CompanionId=e.EpisodeCompanionId
where e.EpisodeCompanionId =null
SELECT C.CompanionName,C.WhoPlayed,EC.*
FROM [dbo].[tblCompanion] C
LEFT JOIN [dbo].[tblEpisodeCompanion] EC ON C.CompanionId = EC.CompanionId
WHERE EC.EpisodeCompanionId IS NULL OR EC.CompanionId IS NULL
USE DoctorWho
SELECT
C.[CompanionName],
E.[Title]
FROM
[dbo].[tblCompanion] AS C
LEFT OUTER JOIN [dbo].[tblEpisodeCompanion] AS EC
ON C.CompanionId = EC.CompanionId
LEFT OUTER JOIN [dbo].[tblEpisode] AS E
ON EC.EpisodeId = E.EpisodeId
WHERE
E.Title IS NULL
I got Sarah Jane Smith
select c.CompanionName,c.WhoPlayed,E.Episodeid,E.EpisodeCompanionId
from tblCompanion as C
left join tblEpisodeCompanion as E
on c.CompanionId = E.CompanionId
where (E.EpisodeCompanionId is null or E.EpisodeId is null)
Result i got Sarah Jane Smith Elisabeth Sladen NULL NULL
Right join will do just fine
SELECT [CompanionName]
FROM [dbo].[tblEpisodeCompanion]
RIGHT JOIN [dbo].[tblCompanion]
ON tblEpisodeCompanion.CompanionId=tblCompanion.CompanionId
WHERE EpisodeCompanionId IS NULL
You must use "where e.CompanionId is null", this is the result that I got :
CompanionName CompanionId WhoPlayed
Sarah Jane Smith 8 Elisabeth Sladen