SELECT within SELECT Tutorial - Revision history

Andr3w at 21:05, 30 April 2013

2013-04-30T21:05:10Z

Andr3w at 21:03, 30 April 2013

2013-04-30T21:03:28Z

Taichikitty: MAX on gdp is unnecessary when using ALL in this case. Both work, and MAX is cleaner, but this example is supposed to be all about the ALL.

2013-03-23T18:31:41Z

MAX on gdp is unnecessary when using ALL in this case. Both work, and MAX is cleaner, but this example is supposed to be all about the ALL.

Taichikitty: Changed question text ( removed a '0' ) quantity to match quantity in answer SQL. I am starting to suspect someone is subtly corrupting this page.

2013-03-19T23:05:58Z

Changed question text ( removed a '0' ) quantity to match quantity in answer SQL. I am starting to suspect someone is subtly corrupting this page.

Taichikitty: Question 6 asks for area, but the answer SQL was the same as the example, and gave country with greatest population. I tweaked the question text to ask for continent, name, and area. I changed the answer SQL to check for greatest area.

2013-03-19T17:12:56Z

Question 6 asks for area, but the answer SQL was the same as the example, and gave country with greatest population. I tweaked the question text to ask for continent, name, and area. I changed the answer SQL to check for greatest area.

Varnim at 17:33, 10 March 2013

2013-03-10T17:33:40Z

Andr3w at 21:50, 8 December 2012

2012-12-08T21:50:51Z

Andr3w at 21:50, 8 December 2012

2012-12-08T21:50:05Z

Andr3w at 21:43, 8 December 2012

2012-12-08T21:43:29Z

Andr3w at 21:38, 8 December 2012

2012-12-08T21:38:12Z

Show changes

@@ Line 138: / Line 138: @@
-The following questions are very difficult:
+==Difficult Questions==
-−
 <div class='qu'>
 <p class='imper'>Find each country that belongs to a continent where all populations are less than 25000000. Show '''name''', '''continent''' and '''population'''. </p>

@@ Line 119: / Line 119: @@
 <div class='qu'>
-<p class='imper'>Find the largest country in each continent, show the '''continent''', the '''name''' and the '''area''': </p>
+<p class='imper'>Find the largest country (by area) in each continent, show the '''continent''', the '''name''' and the '''area''': </p>
 <source lang='sql' class='def'>

@@ Line 110: / Line 110: @@
 SELECT name FROM world
    WHERE gdp > ALL
-    (SELECT MAX(gdp) FROM world
+    (SELECT gdp FROM world
      WHERE continent = 'Europe'
        AND gdp IS NOT NULL)

@@ Line 141: / Line 141: @@
 <div class='qu'>
-<p class='imper'>Find each country that belongs to a continent where all populations are less than 250000000. Show '''name''', '''continent''' and '''population'''. </p>
+<p class='imper'>Find each country that belongs to a continent where all populations are less than 25000000. Show '''name''', '''continent''' and '''population'''. </p>
 <source lang='sql' class='def'>

@@ Line 119: / Line 119: @@
 <div class='qu'>
-<p class='imper'>Find the largest country in each continent, show the '''continent''', the '''name''' and the '''population''': </p>
+<p class='imper'>Find the largest country in each continent, show the '''continent''', the '''name''' and the '''area''': </p>
 <source lang='sql' class='def'>
@@ Line 130: / Line 130: @@
 <source lang='sql' class='ans'>
-SELECT continent, name, population FROM world x
+SELECT continent, name, area FROM world x
-   WHERE population >= ALL
+   WHERE area >= ALL
-     (SELECT population FROM world y
+     (SELECT area FROM world y
-         WHERE y.continent=x.continent
+         WHERE y.continent=x.continent)
-          AND population>0)
 </source>
 </div>

@@ Line 80: / Line 80: @@
 <div class='qu'>
-<p class='imper'>Which country has a population that is more than Canada but less than Poland?</p>
+<p class='imper'>Which country has a population that is more than Canada but less than Poland? Show the name and the population.</p>
 <source lang='sql' class='def'></source>

@@ Line 47: / Line 47: @@
 </div>
 <div class='qu'>
-<p class='imper'>List the '''name''' and '''continent''' of countries in the continents containing 'India', 'Iran'.</p>
+<p class='imper'>List the '''name''' and '''continent''' of countries in the continents containing 'Belize', 'Belgium'.</p>
 <source lang='sql' class='def'>
@@ Line 54: / Line 54: @@
 <source lang='sql' class='ans'>
-SELECT name,continent FROM world
+SELECT name,continent
-  WHERE continent IN
+FROM world
-    (SELECT continent FROM world
+WHERE continent IN (
-     WHERE name IN ('India','Iran'))
+  SELECT continent
 </source>
 </div>