SQL | Subqueries exercise | Selecting data from a sub 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.

Software ==> SQL  (198 exercises)
Version ==> Any version of SQL Server
Topic ==> Subqueries  (5 exercises)
Level ==> Average difficulty
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.

Write a SELECT statement to return events from the 3 continents with the fewest events. To do this first write a SELECT query which returns all the continents and events.

SUB QUERY SELECT

Unsorted results returns 459 events.

 

Now underneath write another SELECT statemnt which lists  events for the 3 continents with the lowest COUNT of events. Put the COUNT in the ORDER BY clause, not the SELECT.

Lowest count of events

Not as many penguin parties as you'd expect.

 

Finally use the second SELECT as a filter in the first SELECT's WHERE clause. To do this use ContinentName IN (Sub Query).

Sub Query Continent

Only 8 events in these 3 continents - maybe stick to Ibiza and the Islands!

 

Optionally save this as Quiet places to visit.sql, and close it down.

This page has 3 threads Add post
FelixP   Reply to FelixPReply
21 Mar 21 at 11:24

MYSQL solution:


select sub1.ContName as Continent, eventName as Event from tblEvent e
    join tblCountry ctr on ctr.CountryID = e.CountryID
    join (
        select cont1.ContinentID, cont1.ContinentName as ContName, count(e1.EventID) as eventCount 
        from tblEvent e1
            join tblCountry ctr1 on ctr1.CountryID = e1.CountryID
            join tblContinent cont1 on cont1.ContinentID = ctr1.ContinentID
            group by cont1.ContinentID, cont1.ContinentName
            order by eventCount 
            limit 3) as sub1
        on sub1.ContinentID = ctr.ContinentID

NikD   Reply to NikDReply
16 Apr 20 at 15:17

select  T1.EventName,T3.ContinentName
from tblEvent T1
full join tblCountry T2 on  T2.CountryID=T1.CountryID
full join tblContinent T3 on T3.ContinentID = T2.ContinentID
where T3.ContinentName IN
    ( 
        select top 3 T3.ContinentName
        from tblEvent T1
        full join tblCountry T2 on  T2.CountryID=T1.CountryID
        full join tblContinent T3 on T3.ContinentID = T2.ContinentID
        group by T3.ContinentName order by count(t1.EventID) asc
    );

dannyoo7   Reply to dannyoo7Reply
12 Jun 19 at 08:51

i cannot seem to figure out the answer  of the second one this is my code so far :

select top 3 con.ContinentName, eve.EventName
from tblCountry as cou
inner join tblContinent con on con.ContinentID=cou.ContinentID
inner join tblEvent as eve on eve.CountryID=cou.CountryID

select top 3 cou.CountryName
from tblCountry as cou
inner join tblEvent as eve on eve.CountryID=cou.CountryID
group by cou.CountryName
order by count(eve.eventid) desc

ghostlife   Reply to ghostlifeReply
06 Aug 19 at 09:53

use WorldEvents
go


SELECT ContinentName, EventName 
FROM                tblEvent   AS a 
INNER JOIN            tblCountry AS b
ON                    a.CountryID = b.CountryID
INNER JOIN            tblContinent AS c
ON                    b.ContinentID = c.ContinentID
WHERE    ContinentName IN     (
                                            SELECT TOP 3    ContinentName--, COUNT(EventName) as [Event Count]
        
                                            FROM                tblEvent   AS a 
                                            INNER JOIN            tblCountry AS b
                                            ON                    a.CountryID = b.CountryID
                                            INNER JOIN            tblContinent AS c
                                            ON                    b.ContinentID = c.ContinentID
                                            Group By ContinentName
                                            ORDER BY COUNT(EventName)
                             )

Add a new post