<!doctypehtml><html class=client-nojs dir=ltr lang=en><meta charset=utf-8><title>
   SELECT within SELECT Tutorial - SQLZOO
  </title><script>document.documentElement.className="client-js";RLCONF={"wgBreakFrames":!1,"wgSeparatorTransformTable":["",""],"wgDigitTransformTable":["",""],"wgDefaultDateFormat":"dmy","wgMonthNames":["","January","February","March","April","May","June","July","August","September","October","November","December"],"wgRequestId":"211132cd21b8a4680cb45746","wgCSPNonce":!1,"wgCanonicalNamespace":"","wgCanonicalSpecialPageName":!1,"wgNamespaceNumber":0,"wgPageName":"SELECT_within_SELECT_Tutorial","wgTitle":"SELECT within SELECT Tutorial","wgCurRevisionId":39744,"wgRevisionId":39744,"wgArticleId":21,"wgIsArticle":!0,"wgIsRedirect":!1,"wgAction":"view","wgUserName":null,"wgUserGroups":["*"],"wgCategories":["Pages using deprecated source tags","Languages pages"],"wgPageContentLanguage":"en","wgPageContentModel":"wikitext","wgRelevantPageName":"SELECT_within_SELECT_Tutorial","wgRelevantArticleId":21,"wgIsProbablyEditable":!1,"wgRelevantPageIsProbablyEditable":!1,"wgRestrictionEdit":[],
"wgRestrictionMove":[]};RLSTATE={"site.styles":"ready","noscript":"ready","user.styles":"ready","user":"ready","user.options":"loading","ext.pygments":"ready","ext.embedVideo.styles":"ready","skins.vector.styles.legacy":"ready"};RLPAGEMODULES=["ext.embedVideo","site","mediawiki.page.ready","mediawiki.toc","skins.vector.legacy.js"];</script><script>(RLQ=window.RLQ||[]).push(function(){mw.loader.implement("user.options@1hzgi",function($,jQuery,require,module){/*@nomin*/mw.user.tokens.set({"patrolToken":"+\\","watchToken":"+\\","csrfToken":"+\\"});
});});</script><link href=/w/load.php?lang=en&modules=ext.embedVideo.styles%7Cext.pygments%7Cskins.vector.styles.legacy&only=styles&skin=vector rel=stylesheet><script async src=/w/load.php?lang=en&modules=startup&only=scripts&raw=1&skin=vector></script><meta name=ResourceLoaderDynamicStyles><link href=/w/load.php?lang=en&modules=site.styles&only=styles&skin=vector rel=stylesheet><meta content="MediaWiki 1.36.1"name=generator><link rel="shortcut icon"href=/favicon.ico><link title="SQLZOO (en)"href=/w/opensearch_desc.php rel=search type=application/opensearchdescription+xml><link href=sqlzoo.net/w/api.php?action=rsd rel=EditURI type=application/rsd+xml><link title="SQLZOO Atom feed"href=/w/index.php?title=Special:RecentChanges&feed=atom rel=alternate type=application/atom+xml><meta content=width=device-width,initial-scale=1.0 name=viewport><script>(function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
(i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
})(window,document,'script','https://www.google-analytics.com/analytics.js','ga');
ga('create', 'UA-33860668-1', 'auto');
ga('send', 'pageview');</script><style rel=stylesheet>.ans{display:none;}</style><body class="mediawiki ltr sitedir-ltr mw-hide-empty-elt ns-0 ns-subject page-SELECT_within_SELECT_Tutorial rootpage-SELECT_within_SELECT_Tutorial skin-vector action-view skin-vector-legacy"><ins class=CANBMDDisplayAD data-bmd-ad-unit=59890220191226T184303083D28836C7F11440BBA04E9E522FE3AAB5 style=display:block> </ins><script src=//secureaddisplay.com/au/bmd/></script><div class=noprint id=mw-page-base></div><div class=noprint id=mw-head-base></div><div class=mw-body id=content role=main><a id=top> </a><div class=mw-body-content id=siteNotice></div><div class="mw-indicators mw-body-content"></div><h1 class=firstHeading id=firstHeading>SELECT within SELECT Tutorial</h1><div class=mw-body-content id=bodyContent><div class=noprint id=siteSub>From SQLZOO</div><div id=contentSub></div><div id=contentSub2></div><div id=jump-to-nav></div><a class=mw-jump-link href=#mw-head> Jump to navigation </a><a class=mw-jump-link href=#searchInput> Jump to search </a><div class=mw-content-ltr dir=ltr id=mw-content-text lang=en><div class=mw-parser-output><table style="border:1px solid #AAA; border-collapse:collapse; clear:both; font-size:85%; margin: 0.5em 0"class=nmbox><tbody><tr style=background:#EEF3E2><th style="white-space:nowrap; padding:4px 1em; border-right:1px solid #aaa"class=mbox-image><a title="Special:MyLanguage/Project:Language policy"href=/wiki/Special:MyLanguage/Project:Language_policy> Language: </a> <span style=display:none> <a title="SQLZOO:Language policy"href=/wiki/SQLZOO:Language_policy> Project:Language policy </a> </span><td class=mbox-text style=background:#F6F9ED><b> <a class="mw-selflink selflink"> English </a> </b>  • <bdi lang=ja> <a title="SELECT within SELECT Tutorial/ja"href=/wiki/SELECT_within_SELECT_Tutorial/ja> 日本語 </a> </bdi> <span class=autonym> </span> <span class=autonym> </span>  • <bdi lang=zh> <a title="SELECT within SELECT Tutorial/zh"href=/wiki/SELECT_within_SELECT_Tutorial/zh> 中文 </a> </bdi></table><p>This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.<div class=ref_section><table class=db_ref><tbody><tr><th>name<th>continent<th>area<th>population<th>gdp<tr><td>Afghanistan<td>Asia<td class=r>652230<td class=r>25500100<td class=r>20343000000<tr><td>Albania<td>Europe<td class=r>28748<td class=r>2831741<td class=r>12960000000<tr><td>Algeria<td>Africa<td class=r>2381741<td class=r>37100000<td class=r>188681000000<tr><td>Andorra<td>Europe<td class=r>468<td class=r>78115<td class=r>3712000000<tr><td>Angola<td>Africa<td class=r>1246700<td class=r>20609294<td class=r>100990000000<tr><td colspan=5>...</table></div><p><a title="Using nested SELECT"href=/wiki/Using_nested_SELECT> Using nested SELECT </a><div class=progress_panel><div><div class=summary>Summary</div><div class=progressbarbg><div class=progressbar></div></div></div></div><div aria-labelledby=mw-toc-heading class=toc id=toc role=navigation><input class=toctogglecheckbox id=toctogglecheckbox role=button style=display:none type=checkbox><div class=toctitle dir=ltr lang=en><h2 id=mw-toc-heading>Contents</h2><span class=toctogglespan> <label class=toctogglelabel for=toctogglecheckbox></label> </span></div><ul><li class="toclevel-1 tocsection-1"><a href=#Bigger_than_Russia> <span class=tocnumber> 1 </span> <span class=toctext> Bigger than Russia </span> </a><li class="toclevel-1 tocsection-2"><a href=#Richer_than_UK> <span class=tocnumber> 2 </span> <span class=toctext> Richer than UK </span> </a><li class="toclevel-1 tocsection-3"><a href=#Neighbours_of_Argentina_and_Australia> <span class=tocnumber> 3 </span> <span class=toctext> Neighbours of Argentina and Australia </span> </a><li class="toclevel-1 tocsection-4"><a href=#Between_Canada_and_Poland> <span class=tocnumber> 4 </span> <span class=toctext> Between Canada and Poland </span> </a><li class="toclevel-1 tocsection-5"><a href=#Percentages_of_Germany> <span class=tocnumber> 5 </span> <span class=toctext> Percentages of Germany </span> </a><li class="toclevel-1 tocsection-6"><a href=#Bigger_than_every_country_in_Europe> <span class=tocnumber> 6 </span> <span class=toctext> Bigger than every country in Europe </span> </a><li class="toclevel-1 tocsection-7"><a href=#Largest_in_each_continent> <span class=tocnumber> 7 </span> <span class=toctext> Largest in each continent </span> </a><li class="toclevel-1 tocsection-8"><a href=#First_country_of_each_continent_.28alphabetically.29> <span class=tocnumber> 8 </span> <span class=toctext> First country of each continent (alphabetically) </span> </a><li class="toclevel-1 tocsection-9"><a href=#Difficult_Questions_That_Utilize_Techniques_Not_Covered_In_Prior_Sections> <span class=tocnumber> 9 </span> <span class=toctext> Difficult Questions That Utilize Techniques Not Covered In Prior Sections </span> </a></ul></div><h2><span class=mw-headline id=Bigger_than_Russia> Bigger than Russia </span></h2><div class=qu><p class=imper>List each country <b> name </b> where the <b> population </b> is larger than that of 'Russia'.<pre>world(name, continent, area, population, gdp)</pre><p><br><div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span>
  <span class=k>WHERE</span> <span class=n>population</span> <span class=o>></span>
     <span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span>
      <span class=k>WHERE</span> <span class=n>name</span><span class=o>=</span><span class=s1>'Romania'</span><span class=p>)</span>
</pre></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span>
  <span class=k>WHERE</span> <span class=n>population</span> <span class=o>></span>
     <span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span>
      <span class=k>WHERE</span> <span class=n>name</span><span class=o>=</span><span class=s1>'Russia'</span><span class=p>)</span>
</pre></div></div><div class="thumb embedvideo autoResize"style="width: 648px;"><div class="embedvideo autoResize"><div style="width: 640px;"class=embedvideowrap><iframe title="Play video"allowfullscreen frameborder=0 height=360 src=//www.youtube.com/embed/UPFwlsJFcsw? width=640></iframe></div></div></div><h2><span class=mw-headline id=Richer_than_UK> Richer than UK </span></h2><div class=qu><p class=imper>Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.<div title="Per Capita GDP"class=hint>The per capita GDP is the gdp/population</div><div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span>
  <span class=k>WHERE</span> <span class=n>continent</span><span class=o>=</span><span class=s1>'Europe'</span> <span class=k>AND</span> <span class=n>gdp</span><span class=o>/</span><span class=n>population</span> <span class=o>></span>
     <span class=p>(</span><span class=k>SELECT</span> <span class=n>gdp</span><span class=o>/</span><span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span>
      <span class=k>WHERE</span> <span class=n>name</span><span class=o>=</span><span class=s1>'United Kingdom'</span><span class=p>)</span>
</pre></div></div><div class="thumb embedvideo autoResize"style="width: 648px;"><div class="embedvideo autoResize"><div style="width: 640px;"class=embedvideowrap><iframe title="Play video"allowfullscreen frameborder=0 height=360 src=//www.youtube.com/embed/RELAqb4DlvY? width=640></iframe></div></div></div><h2><span class=mw-headline id=Neighbours_of_Argentina_and_Australia> Neighbours of Argentina and Australia </span></h2><div class=qu><p><span class="param respectorder"> </span><p class=imper>List the <b> name </b> and <b> continent </b> of countries in the continents containing either <b> Argentina </b> or <b> Australia </b> . Order by name of the country.<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span><span class=p>,</span><span class=n>continent</span>
<span class=k>FROM</span> <span class=n>world</span>
<span class=k>WHERE</span> <span class=n>continent</span> <span class=k>IN</span> <span class=p>(</span>
  <span class=k>SELECT</span> <span class=n>continent</span>
  <span class=k>FROM</span> <span class=n>world</span>
  <span class=k>WHERE</span> <span class=n>name</span> <span class=k>IN</span> <span class=p>(</span><span class=s1>'Australia'</span><span class=p>,</span><span class=s1>'Argentina'</span><span class=p>))</span>
<span class=k>ORDER</span> <span class=k>BY</span> <span class=n>name</span>
</pre></div></div><h2><span class=mw-headline id=Between_Canada_and_Poland> Between Canada and Poland </span></h2><div class=qu><p class=imper>Which country has a population that is more than Canada but less than Poland? Show the name and the population.<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span><span class=p>,</span><span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span>
<span class=k>WHERE</span> <span class=n>population</span> <span class=k>BETWEEN</span>
<span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span><span class=o>+</span><span class=mi>1</span> <span class=k>FROM</span> <span class=n>world</span> <span class=k>WHERE</span> <span class=n>name</span><span class=o>=</span><span class=s1>'Canada'</span><span class=p>)</span>
<span class=k>AND</span>
<span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span><span class=o>-</span><span class=mi>1</span> <span class=k>FROM</span> <span class=n>world</span> <span class=k>WHERE</span> <span class=n>name</span><span class=o>=</span><span class=s1>'Poland'</span><span class=p>)</span>
</pre></div></div><h2><span class=mw-headline id=Percentages_of_Germany> Percentages of Germany </span></h2><div class=qu><p>Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.<p class=imper>Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.<p>The format should be <i> Name, Percentage </i> for example:<table class=sqlmine><tbody><tr><th>name<th>percentage<tr><td>Albania<td>3%<tr><td>Andorra<td>0%<tr><td>Austria<td>11%<tr><td>...<td>...</table><div title="Decimal places"class=hint>You can use the function <a href=/wiki/ROUND title=ROUND> ROUND </a> to remove the decimal places.</div><div title="Percent symbol %"class=hint>You can use the function <a href=/wiki/CONCAT title=CONCAT> CONCAT </a> to add the percentage symbol.</div><div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span><span class=p>,</span> <span class=n>CONCAT</span><span class=p>(</span><span class=k>CAST</span><span class=p>(</span><span class=n>ROUND</span><span class=p>(</span><span class=mi>100</span><span class=o>*</span><span class=n>population</span><span class=o>/</span><span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span> <span class=k>WHERE</span> <span class=n>name</span> <span class=o>=</span> <span class=s1>'Germany'</span><span class=p>),</span><span class=mi>0</span><span class=p>)</span> <span class=k>as</span> <span class=nb>int</span><span class=p>),</span> <span class=s1>'%'</span><span class=p>)</span>
<span class=k>FROM</span> <span class=n>world</span>
<span class=k>WHERE</span> <span class=n>continent</span> <span class=o>=</span> <span class=s1>'Europe'</span>
</pre></div></div><div class="thumb embedvideo autoResize"style="width: 648px;"><div class="embedvideo autoResize"><div style="width: 640px;"class=embedvideowrap><iframe title="Play video"allowfullscreen frameborder=0 height=360 src=//www.youtube.com/embed/lCbLvHUNBG4? width=640></iframe></div></div></div><p><br> <a title="SUM and COUNT"href=/wiki/SUM_and_COUNT> To get a well rounded view of the important features of SQL you should move on to the next tutorial concerning aggregates. </a><p>To gain an absurdly detailed view of one insignificant feature of the language, read on.<p>We can use the word <code>
        ALL
       </code> to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:<div class="mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span>
  <span class=k>FROM</span> <span class=n>world</span>
 <span class=k>WHERE</span> <span class=n>population</span> <span class=o>>=</span> <span class=k>ALL</span><span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span>
                           <span class=k>FROM</span> <span class=n>world</span>
                          <span class=k>WHERE</span> <span class=n>population</span><span class=o>></span><span class=mi>0</span><span class=p>)</span>
</pre></div><p>You need the condition <b> population>0 </b> in the sub-query as some countries have <b> null </b> for population.<h2><span class=mw-headline id=Bigger_than_every_country_in_Europe> Bigger than every country in Europe </span></h2><div class=qu><p class=imper>Which countries have a GDP greater than every country in Europe? [Give the <b> name </b> only.] (Some countries may have NULL gdp values)<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span> 
  <span class=k>WHERE</span> <span class=n>gdp</span> <span class=o>></span> <span class=k>ALL</span>
   <span class=p>(</span><span class=k>SELECT</span> <span class=n>gdp</span> <span class=k>FROM</span> <span class=n>world</span>
    <span class=k>WHERE</span> <span class=n>continent</span> <span class=o>=</span> <span class=s1>'Europe'</span> 
      <span class=k>AND</span> <span class=n>gdp</span> <span class=k>IS</span> <span class=k>NOT</span> <span class=k>NULL</span><span class=p>)</span>
</pre></div></div><p>We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.<h2><span class=mw-headline id=Largest_in_each_continent> Largest in each continent </span></h2><div class=qu><p class=imper>Find the largest country (by area) in each continent, show the <b> continent </b> , the <b> name </b> and the <b> area </b> :<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>continent</span><span class=p>,</span> <span class=n>name</span><span class=p>,</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>x</span>
  <span class=k>WHERE</span> <span class=n>population</span> <span class=o>>=</span> <span class=k>ALL</span>
    <span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>y</span>
        <span class=k>WHERE</span> <span class=n>y</span><span class=p>.</span><span class=n>continent</span><span class=o>=</span><span class=n>x</span><span class=p>.</span><span class=n>continent</span>
          <span class=k>AND</span> <span class=n>population</span><span class=o>></span><span class=mi>0</span><span class=p>)</span>
</pre></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>continent</span><span class=p>,</span> <span class=n>name</span><span class=p>,</span> <span class=n>area</span> 
 <span class=k>FROM</span> <span class=n>world</span> <span class=n>x</span>
  <span class=k>WHERE</span> <span class=n>area</span> <span class=o>>=</span> <span class=k>ALL</span>
    <span class=p>(</span><span class=k>SELECT</span> <span class=n>area</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>y</span>
        <span class=k>WHERE</span> <span class=n>y</span><span class=p>.</span><span class=n>continent</span><span class=o>=</span><span class=n>x</span><span class=p>.</span><span class=n>continent</span>
         <span class=k>and</span> <span class=n>area</span> <span class=o>></span> <span class=mi>0</span> <span class=p>)</span>
</pre></div><p>The above example is known as a <b> correlated </b> or <b> synchronized </b> sub-query.<div title="Using correlated subqueries"class=hint><p>A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.<p>One way to interpret the line in the <b> WHERE </b> clause that references the two table is <i> “… where the correlated values are the same” </i> .<p>In the example provided, you would say <i> “select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same” </i> .</div></div><h2><span id=First_country_of_each_continent_(alphabetically)> </span> <span class=mw-headline id=First_country_of_each_continent_.28alphabetically.29> First country of each continent (alphabetically) </span></h2><div class=qu><p class=imper>List each continent and the name of the country that comes first alphabetically.<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>continent</span><span class=p>,</span><span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>x</span>
  <span class=k>WHERE</span> <span class=n>x</span><span class=p>.</span><span class=n>name</span> <span class=o><=</span> <span class=k>ALL</span> <span class=p>(</span>
    <span class=k>SELECT</span> <span class=n>name</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>y</span>
     <span class=k>WHERE</span> <span class=n>x</span><span class=p>.</span><span class=n>continent</span><span class=o>=</span><span class=n>y</span><span class=p>.</span><span class=n>continent</span><span class=p>)</span>
</pre></div></div><h2><span class=mw-headline id=Difficult_Questions_That_Utilize_Techniques_Not_Covered_In_Prior_Sections> Difficult Questions That Utilize Techniques Not Covered In Prior Sections </span></h2><div class=qu><p class=imper>Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show <b> name </b> , <b> continent </b> and <b> population </b> .<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span><span class=p>,</span><span class=n>continent</span><span class=p>,</span><span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>x</span>
  <span class=k>WHERE</span> <span class=mi>25000000</span> <span class=o>>=</span> <span class=k>ALL</span> <span class=p>(</span>
    <span class=k>SELECT</span> <span class=n>population</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>y</span>
     <span class=k>WHERE</span> <span class=n>x</span><span class=p>.</span><span class=n>continent</span><span class=o>=</span><span class=n>y</span><span class=p>.</span><span class=n>continent</span>
       <span class=k>AND</span> <span class=n>y</span><span class=p>.</span><span class=n>population</span><span class=o>></span><span class=mi>0</span><span class=p>)</span>
</pre></div></div><div class=qu><p class=imper>Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.<div class="def mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr></div><div class="ans mw-highlight mw-highlight-lang-sql mw-content-ltr"dir=ltr><pre><span></span><span class=k>SELECT</span> <span class=n>name</span><span class=p>,</span> <span class=n>continent</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>x</span> <span class=k>WHERE</span>
 <span class=n>population</span> <span class=o>></span> <span class=k>ALL</span>
 <span class=p>(</span><span class=k>SELECT</span> <span class=n>population</span><span class=o>*</span><span class=mi>3</span> <span class=k>FROM</span> <span class=n>world</span> <span class=n>y</span>
 <span class=k>WHERE</span> <span class=n>y</span><span class=p>.</span><span class=n>continent</span> <span class=o>=</span> <span class=n>x</span><span class=p>.</span><span class=n>continent</span>
 <span class=k>AND</span> <span class=n>y</span><span class=p>.</span><span class=n>name</span> <span class=o>!=</span> <span class=n>x</span><span class=p>.</span><span class=n>name</span><span class=p>)</span>
</pre></div></div><div><p class=mw-empty-elt><div class=quizlink><a title="Nested SELECT Quiz"href=/wiki/Nested_SELECT_Quiz> Nested SELECT Quiz </a></div><p class=mw-empty-elt></div></div><div id=zc579913c-59f3-4c56-8660-72e3f9356e4f style=display:none></div><script>!function(a,n,e,t,r){tagsync=e;var c=window[a];if(tagsync){var d=document.createElement("script");d.src="//3933.tm.zedo.com/v1/ecec6ff9-67bc-44c0-bd5f-e114adb816e7/atm.js",d.async=!0;var i=document.getElementById(n);if(null==i||"undefined"==i)return;i.parentNode.appendChild(d,i),d.onload=d.onreadystatechange=function(){var a=new zTagManager(n);a.initTagManager(n,c,this.aync,t,r)}}else document.write("<script src='//3933.tm.zedo.com/v1/ecec6ff9-67bc-44c0-bd5f-e114adb816e7/tm.js?data="+a+"'><"+"/script>")}("datalayer","zc579913c-59f3-4c56-8660-72e3f9356e4f",true, 1 , 1);</script><div class=printfooter>Retrieved from " <a dir=ltr href=http:///sqlzoo.net/w/index.php?title=SELECT_within_SELECT_Tutorial&oldid=39744> http:///sqlzoo.net/w/index.php?title=SELECT_within_SELECT_Tutorial&oldid=39744 </a> "</div></div><div class=catlinks data-mw=interface id=catlinks><div class=mw-normal-catlinks id=mw-normal-catlinks><a href=/wiki/Special:Categories title=Special:Categories> Categories </a> : <ul><li><a title="Category:Pages using deprecated source tags (page does not exist)"class=new href=/w/index.php?title=Category:Pages_using_deprecated_source_tags&action=edit&redlink=1> Pages using deprecated source tags </a><li><a title="Category:Languages pages (page does not exist)"class=new href=/w/index.php?title=Category:Languages_pages&action=edit&redlink=1> Languages pages </a></ul></div></div></div></div><div id=mw-navigation><h2>Navigation menu</h2><div id=mw-head><nav class="mw-portlet mw-portlet-personal vector-menu"aria-labelledby=p-personal-label id=p-personal role=navigation><h3 class=vector-menu-heading id=p-personal-label><span> Personal tools </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li id=pt-createaccount><a title="You are encouraged to create an account and log in; however, it is not mandatory"href=/w/index.php?title=Special:CreateAccount&returnto=SELECT+within+SELECT+Tutorial> Create account </a><li id=pt-login><a title="You are encouraged to log in; however, it is not mandatory [o]"accesskey=o href=/w/index.php?title=Special:UserLogin&returnto=SELECT+within+SELECT+Tutorial> Log in </a></ul></div></nav><div id=left-navigation><nav class="mw-portlet mw-portlet-namespaces vector-menu vector-menu-tabs"aria-labelledby=p-namespaces-label id=p-namespaces role=navigation><h3 class=vector-menu-heading id=p-namespaces-label><span> Namespaces </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li class=selected id=ca-nstab-main><a title="View the content page [c]"accesskey=c href=/wiki/SELECT_within_SELECT_Tutorial> Page </a><li id=ca-talk><a title="Discussion about the content page [t]"accesskey=t href=/wiki/Talk:SELECT_within_SELECT_Tutorial rel=discussion> Discussion </a></ul></div></nav><nav class="mw-portlet mw-portlet-variants emptyPortlet vector-menu vector-menu-dropdown"aria-labelledby=p-variants-label id=p-variants role=navigation><input aria-labelledby=p-variants-label class=vector-menu-checkbox type=checkbox><h3 class=vector-menu-heading id=p-variants-label><span> Variants </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list></ul></div></nav></div><div id=right-navigation><nav class="mw-portlet mw-portlet-views vector-menu vector-menu-tabs"aria-labelledby=p-views-label id=p-views role=navigation><h3 class=vector-menu-heading id=p-views-label><span> Views </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li class=selected id=ca-view><a href=/wiki/SELECT_within_SELECT_Tutorial> Read </a><li id=ca-viewsource><a title="This page is protected.
You can view its source [e]"accesskey=e href=/w/index.php?title=SELECT_within_SELECT_Tutorial&action=edit> View source </a><li id=ca-history><a title="Past revisions of this page [h]"accesskey=h href=/w/index.php?title=SELECT_within_SELECT_Tutorial&action=history> View history </a></ul></div></nav><nav class="mw-portlet mw-portlet-cactions emptyPortlet vector-menu vector-menu-dropdown"aria-labelledby=p-cactions-label id=p-cactions role=navigation><input aria-labelledby=p-cactions-label class=vector-menu-checkbox type=checkbox><h3 class=vector-menu-heading id=p-cactions-label><span> More </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list></ul></div></nav><div id=p-search role=search><h3><label for=searchInput>Search</label></h3><form action=/w/index.php id=searchform><div data-search-loc=header-navigation id=simpleSearch><input placeholder="Search SQLZOO"title="Search SQLZOO [f]"accesskey=f autocapitalize=sentences id=searchInput name=search type=search><input name=title type=hidden value=Special:Search><input class="searchButton mw-fallbackSearchButton"title="Search the pages for this text"id=mw-searchButton name=fulltext type=submit value=Search><input title="Go to a page with this exact name if it exists"class=searchButton id=searchButton name=go type=submit value=Go></div></form></div></div></div><div id=mw-panel><div id=p-logo role=banner><a title="Visit the main page"class=mw-wiki-logo href=/wiki/SQL_Tutorial> </a></div><nav class="mw-portlet mw-portlet- vector-menu vector-menu-portal portal"aria-labelledby=p--label id=p- role=navigation><h3 class=vector-menu-heading id=p--label><span> </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li id=n-SELECT-basics><a href=/wiki/SELECT_basics> SELECT basics </a><li id=n-quiz><a href=/wiki/SELECT_Quiz> quiz </a><li id=n-SELECT-from-world><a href=/wiki/SELECT_from_WORLD_Tutorial> SELECT from world </a><li id=n-quiz><a href=/wiki/BBC_QUIZ> quiz </a><li id=n-SELECT-from-nobel><a href=/wiki/SELECT_from_Nobel_Tutorial> SELECT from nobel </a><li id=n-quiz><a href=/wiki/Nobel_Quiz> quiz </a><li id=n-SELECT-in-SELECT><a href=/wiki/SELECT_within_SELECT_Tutorial> SELECT in SELECT </a><li id=n-quiz><a href=/wiki/Nested_SELECT_Quiz> quiz </a><li id=n-SUM-and-COUNT><a href=/wiki/SUM_and_COUNT> SUM and COUNT </a><li id=n-quiz><a href=/wiki/SUM_and_COUNT_Quiz> quiz </a><li id=n-JOIN><a href=/wiki/The_JOIN_operation> JOIN </a><li id=n-quiz><a href=/wiki/JOIN_Quiz> quiz </a><li id=n-More-JOIN><a href=/wiki/More_JOIN_operations> More JOIN </a><li id=n-quiz><a href=/wiki/JOIN_Quiz_2> quiz </a><li id=n-Using-NULL><a href=/wiki/Using_Null> Using NULL </a><li id=n-quiz><a href=/wiki/Using_Null_Quiz> quiz </a><li id=n-Self-JOIN><a href=/wiki/Self_join> Self JOIN </a><li id=n-quiz><a href=/wiki/Self_join_Quiz> quiz </a></ul></div></nav><nav class="mw-portlet mw-portlet-Reference vector-menu vector-menu-portal portal"aria-labelledby=p-Reference-label id=p-Reference role=navigation><h3 class=vector-menu-heading id=p-Reference-label><span> Reference </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li id=n-SELECT><a href=/wiki/SELECT> SELECT </a><li id=n-Functions><a href=/wiki/FUNCTIONS> Functions </a><li id=n-SELECT-..-WHERE><a href=/wiki/SELECT_.._WHERE> SELECT .. WHERE </a><li id=n-SELECT-..-GROUP-BY><a href=/wiki/SELECT_.._GROUP_BY> SELECT .. GROUP BY </a><li id=n-SELECT-..-JOIN><a href=/wiki/SELECT_.._JOIN> SELECT .. JOIN </a><li id=n-SELECT-..-SELECT><a href=/wiki/SELECT_.._SELECT> SELECT .. SELECT </a><li id=n-INSERT-..-VALUES><a href=/wiki/INSERT_.._VALUES> INSERT .. VALUES </a><li id=n-INSERT-..-SELECT><a href=/wiki/INSERT_.._SELECT> INSERT .. SELECT </a><li id=n-UPDATE><a href=/wiki/UPDATE> UPDATE </a><li id=n-DELETE><a href=/wiki/DELETE> DELETE </a><li id=n-CREATE-TABLE><a href=/wiki/CREATE_TABLE> CREATE TABLE </a><li id=n-CREATE-VIEW><a href=/wiki/CREATE_VIEW> CREATE VIEW </a><li id=n-CREATE-INDEX><a href=/wiki/CREATE_INDEX> CREATE INDEX </a><li id=n-DROP><a href=/wiki/DROP> DROP </a><li id=n-ALTER><a href=/wiki/ALTER> ALTER </a><li id=n-UNION><a href=/wiki/UNION> UNION </a><li id=n-LEFT-JOIN><a href=/wiki/LEFT_JOIN> LEFT JOIN </a><li id=n-NULL><a href=/wiki/NULL> NULL </a></ul></div></nav><nav class="mw-portlet mw-portlet-tb vector-menu vector-menu-portal portal"aria-labelledby=p-tb-label id=p-tb role=navigation><h3 class=vector-menu-heading id=p-tb-label><span> Tools </span></h3><div class=vector-menu-content><ul class=vector-menu-content-list><li id=t-whatlinkshere><a title="A list of all wiki pages that link here [j]"accesskey=j href=/wiki/Special:WhatLinksHere/SELECT_within_SELECT_Tutorial> What links here </a><li id=t-recentchangeslinked><a title="Recent changes in pages linked from this page [k]"accesskey=k href=/wiki/Special:RecentChangesLinked/SELECT_within_SELECT_Tutorial rel=nofollow> Related changes </a><li id=t-specialpages><a title="A list of all special pages [q]"accesskey=q href=/wiki/Special:SpecialPages> Special pages </a><li id=t-print><a title="Printable version of this page [p]"accesskey=p href=javascript:print(); rel=alternate> Printable version </a><li id=t-permalink><a title="Permanent link to this revision of the page"href=/w/index.php?title=SELECT_within_SELECT_Tutorial&oldid=39744> Permanent link </a><li id=t-info><a title="More information about this page"href=/w/index.php?title=SELECT_within_SELECT_Tutorial&action=info> Page information </a></ul></div></nav></div></div><footer class=mw-footer id=footer role=contentinfo><ul id=footer-info><li id=footer-info-lastmod>This page was last edited on 25 March 2020, at 11:47.</ul><ul id=footer-places><li id=footer-places-privacy><a title="SQLZOO:Privacy policy"href=/wiki/SQLZOO:Privacy_policy> Privacy policy </a><li id=footer-places-about><a href=/wiki/SQLZOO:About title=SQLZOO:About> About SQLZOO </a><li id=footer-places-disclaimer><a title="SQLZOO:General disclaimer"href=/wiki/SQLZOO:General_disclaimer> Disclaimers </a></ul><ul class=noprint id=footer-icons><li id=footer-poweredbyico><a href=https://www.mediawiki.org/> <img alt="Powered by MediaWiki"srcset="/w/resources/assets/poweredby_mediawiki_132x47.png 1.5x, /w/resources/assets/poweredby_mediawiki_176x62.png 2x"height=31 loading=lazy src=/w/resources/assets/poweredby_mediawiki_88x31.png width=88> </a></ul></footer><script>(RLQ=window.RLQ||[]).push(function(){mw.config.set({"wgPageParseReport":{"limitreport":{"cputime":"0.297","walltime":"6.236","ppvisitednodes":{"value":2260,"limit":1000000},"postexpandincludesize":{"value":7688,"limit":2097152},"templateargumentsize":{"value":14439,"limit":2097152},"expansiondepth":{"value":18,"limit":40},"expensivefunctioncount":{"value":77,"limit":100},"unstrip-depth":{"value":0,"limit":20},"unstrip-size":{"value":14321,"limit":5000000},"timingprofile":["100.00%  137.478      1 -total","100.00%  137.478      1 Template:Languages"," 74.17%  101.970      1 Template:Nmbox"," 61.83%   85.003    149 Template:Languages/Lang"," 15.29%   21.026      1 Template:Ll","  9.20%   12.648      1 Template:Languages/Title","  8.03%   11.036      1 Template:LangSwitch","  6.10%    8.389      1 Template:Uselang","  4.44%    6.102      2 Template:Translatable","  3.99%    5.490      2 Template:Pagelang"]},"cachereport":{"timestamp":"20211031205531","ttl":86400,"transientcontent":false}}});mw.config.set({"wgBackendResponseTime":6415});});</script>