Using nested SELECT - Revision history

Andr3w: /* Using binary operators over a set */

2012-12-08T22:19:14Z

‎Using binary operators over a set

Andr3w: /* Multiple results from the subquery */

2012-12-08T22:18:40Z

‎Multiple results from the subquery

Andr3w at 22:18, 8 December 2012

2012-12-08T22:18:20Z

Andr3w: /* Using a subquery on the SELECT line */

2012-12-08T22:17:42Z

‎Using a subquery on the SELECT line

Andr3w: /* Using a subquery on the SELECT line */

2012-12-08T22:06:17Z

‎Using a subquery on the SELECT line

Andr3w: /* Multiple results from the subquery */

2012-12-08T22:05:23Z

‎Multiple results from the subquery

Andr3w at 22:02, 8 December 2012

2012-12-08T22:02:43Z

Andr3w: /* Using a query on the SELECT line */

2012-08-22T19:30:29Z

‎Using a query on the SELECT line

Andr3w at 19:30, 22 August 2012

2012-08-22T19:30:05Z

Andr3w: /* Using binary operators over a set */

2012-08-22T19:29:34Z

‎Using binary operators over a set

@@ Line 69: / Line 69: @@
 </div>
-==Using binary operators over a set==
+==Operators over a set==
 These operators are ''binary'' - they normally take two parameters:
   =     equals

@@ Line 33: / Line 33: @@
 </p>
-==Multiple results from the subquery==
+==Multiple Results==
 The subquery may return more than one result - if this happens the query above will fail as you are testing one value against more than one value.

@@ Line 25: / Line 25: @@
 </div>
-<h2>Notes</h2>
+==Alias==
 <p>Some versions of SQL insist that you give the subquery an <i>alias</i>. Simply put <code>AS somename</code> after the closing bracket:
 <pre>

@@ Line 56: / Line 56: @@
 </div>
-==Using a subquery on the SELECT line==
+==Subquery on the SELECT line==
 If you are certain that only one value will be returned you can use that query on the SELECT line.
 <div class='qu'>

@@ Line 61: / Line 61: @@
 <p class='imper'>Show the population of China as a multiple of the population of the United Kingdom</p>
 <source lang='sql' class='def'>
-SELECT population/(SELECT population FROM world
+SELECT
-                    WHERE name='United Kingdom')
+ population/(SELECT population FROM world
    FROM world
 WHERE name = 'China'

@@ Line 41: / Line 41: @@
 will return two values ('North America' and 'South America'). You should use:
   SELECT name, continent FROM world
-  WHERE continent IN (SELECT continent FROM world WHERE name='Brazil'
+  WHERE continent IN
-                                            OR name='Mexico')
+  (SELECT continent FROM world WHERE name='Brazil'
+                                  OR name='Mexico')
 <div class='qu'>
@@ Line 49: / Line 49: @@
 <source lang='sql' class='def'>
 SELECT name, continent FROM world
-WHERE continent IN (SELECT continent
+WHERE continent IN
-                   FROM world WHERE name='Brazil'
+  (SELECT continent
-                               OR name ='Mexico')
+     FROM world WHERE name='Brazil'
 </source>
 </div>

@@ Line 4: / Line 4: @@
      <p>The result of a SELECT statement may be used as a value in
      another statement. For example the statement
-     <b>SELECT region FROM bbc WHERE name = 'Brazil'</b>
+     <b>SELECT continent FROM world WHERE name = 'Brazil'</b>
       evaluates to <code>'South America'</code> so we can use this value to
-     obtain a list of all countries in the same region as
+     obtain a list of all countries in the same continent as
      'Brazil'</p>
 <div class='qu'>
-List each country in the same region as 'Brazil'.
+List each country in the same continent as 'Brazil'.
 <source lang='sql' class='def'>
-SELECT name FROM bbc WHERE region =
+SELECT name FROM world WHERE continent =
-(SELECT region
+(SELECT continent
-FROM bbc WHERE name = 'Brazil')
+FROM world WHERE name = 'Brazil')
 </source>
 <source lang='sql' class='ans'>
 SELECT name
-FROM bbc WHERE region =
+FROM world WHERE continent =
-   (SELECT region FROM bbc WHERE name = 'Brazil')
+   (SELECT continent FROM world WHERE name = 'Brazil')
 </source>
 </div>
@@ Line 28: / Line 28: @@
 <p>Some versions of SQL insist that you give the subquery an <i>alias</i>. Simply put <code>AS somename</code> after the closing bracket:
 <pre>
-SELECT name FROM bbc WHERE region =
+SELECT name FROM world WHERE continent =
-   (SELECT region FROM bbc WHERE name='Brazil') AS brazil_region
+   (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent
 </pre>
 </p>
@@ Line 38: / Line 38: @@
 It is safer to use IN to cope with this possibility.
-The phrase <code>(SELECT region FROM bbc WHERE name = 'Brazil' OR name='Mexico')</code>
+The phrase <code>(SELECT continent FROM world WHERE name = 'Brazil' OR name='Mexico')</code>
 will return two values ('North America' and 'South America'). You should use:
-  SELECT name, region FROM bbc
+  SELECT name, continent FROM world
-  WHERE region IN (SELECT region FROM bbc WHERE name='Brazil'
+  WHERE continent IN (SELECT continent FROM world WHERE name='Brazil'
                                              OR name='Mexico')
 <div class='qu'>
-List each country and its region in the same region as 'Brazil' or 'Mexico'.
+List each country and its continent in the same continent as 'Brazil' or 'Mexico'.
 <source lang='sql' class='def'>
-SELECT name, region FROM bbc
+SELECT name, continent FROM world
-WHERE region IN (SELECT region
+WHERE continent IN (SELECT continent
-                    FROM bbc WHERE name='Brazil'
+                    FROM world WHERE name='Brazil'
                                 OR name ='Mexico')
 </source>
@@ Line 60: / Line 60: @@
 <p class='imper'>Show the population of China as a multiple of the population of the United Kingdom</p>
 <source lang='sql' class='def'>
-SELECT population/(SELECT population FROM bbc
+SELECT population/(SELECT population FROM world
                      WHERE name='United Kingdom')
-   FROM bbc
+   FROM world
 WHERE name = 'China'
 </source>
@@ Line 79: / Line 79: @@
 Note that we mean greater than every single country in Europe; not the combined population of Europe.
 <source lang='sql' class='def'>
-SELECT name FROM bbc
+SELECT name FROM world
   WHERE population > ALL
-       (SELECT population FROM bbc
+       (SELECT population FROM world
-         WHERE region='Europe')
+         WHERE continent='Europe')
 </source>
 </div>
 Return to the [[SELECT within SELECT Tutorial]]

@@ Line 1: / Line 1: @@
-Return to the tutorial [[SELECT within SELECT Tutorial]]
+Return to the [[SELECT within SELECT Tutorial]]
 ==Using SELECT in SELECT==
 <p>See SELECT FROM SELECT for how to use a [http://sqlzoo.net/w/index.php/SELECT_.._SELECT derived table].</p>

← Older revision		Revision as of 19:29, 22 August 2012
Line 86:		Line 86:
	</div>		</div>

−	Return to the ~~tutorial~~ [[SELECT within SELECT Tutorial]]	+	Return to the [[SELECT within SELECT Tutorial]]