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 | Aggregation and grouping exercise | Show which doctor/author combos are the most popular
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!
| Software ==> | SQL (198 exercises) |
| Version ==> | Any version of SQL Server |
| Topic ==> | Aggregation and grouping (8 exercises) |
| Level ==> | Average difficulty |
| Courses ==> | Introduction to SQL / 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 tables you'll need for this exercise are as follows:
You'll need to link each episode to its author and its doctor.
Write a query to list out for each author and doctor the number of episodes made, but restrict your output to show only the author/doctor combinations for which more than 5 episodes have been written. Here's what you should see:
Here we've sorted the rows so the most common combination appears first.
You'll need to use the HAVING clause. Remember that sweaty feet will give horrible odours!
Save this as Favourite combos, then close it down.
SELECT
au.AuthorName Writer, --1st Table
Doc.DoctorName Doctor, --2nd Table
COUNT(Ep.EpisodeId) 'Num Of Episodes' --3rd Table
FROM tblAuthor AS Au
INNER JOIN tblEpisode AS Ep
ON Au.AuthorId = Ep.AuthorId
INNER JOIN tblDoctor Doc
ON Doc.DoctorId = Ep.DoctorId
GROUP BY Au.AuthorName, Doc.DoctorName
HAVING COUNT(Ep.EpisodeId) > 5
ORDER BY [Num Of Episodes] Desc
We don't normally post answers to exercises, as it removes the incentive for people to try them, but I've made an exception for this because the query is so well-written and presented!