This page has 4 threads | Add post

Exercises

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

Software ==>	SQL (129 exercises)
Topic ==>	Subqueries (5 exercises)
Level ==>	Average difficulty
Subject ==>	SQL training

Using sub queries filter the select statement.

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.

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.

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.

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

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.

You can find other training resources for the subject of this exercise here:

This page has 4 threads

Add a new post

From:	YUVA
When:	31 Jul 23 at 18:48

Reply to YUVA

(1) ---------
Select
top 3
cn.Continentname,
e.eventname

from tblEvent e
inner join tblCountry c on e.CountryID=c.CountryID
inner join tblContinent cn on cn.ContinentID =c.ContinentID

(2) ------------
Select
top 3
cn.Continentname

from tblEvent e
inner join tblCountry c on e.CountryID=c.CountryID
inner join tblContinent cn on cn.ContinentID =c.ContinentID

Group by cn.ContinentName
order by count(*)
(3) ------------
Select
cn.Continentname,
e.eventname

from tblEvent e
inner join tblCountry c on e.CountryID=c.CountryID
inner join tblContinent cn on cn.ContinentID =c.ContinentID

where ContinentName in ('Antarctic','South America','Australasia')

From:	FelixP
When:	21 Mar 21 at 11:24

Reply to FelixP

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

From:	NikD
When:	16 Apr 20 at 15:17

Reply to NikD

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

From:	dannyoo7
When:	12 Jun 19 at 08:51

Reply to dannyoo7

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

From:	ghostlife
When:	06 Aug 19 at 09:53

Reply to ghostlife

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

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

EXERCISE TOPIC

SQL EXERCISES

SQL EXERCISES

Using sub queries filter the select statement.

Head office

London

Manchester