SELECT .. SELECT - Revision history

JesseTG: Added more `tags`


2012-12-29T04:44:37Z
Added more <code> tags

				
				
				
				
			
			← Older revision
			Revision as of 04:44, 29 December 2012
			
Line 4:
Line 4:
  ==Subquery with FROM==   ==Subquery with FROM==
  <div class='qu'>   <div class='qu'>
− You may use a SELECT statement in the FROM line. + You may use a <code>SELECT</code> statement in the <code>FROM</code> line.
    
− In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>. + In this case the derived table <code>X</code> has columns <code>name</code> and <code>gdp_per_capita</code>.
− The calculated values in the inner SELECT can be used in the outer SELECT. + The calculated values in the inner <code>SELECT</code> can be used in the outer <code>SELECT</code>.
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
  SELECT name, ROUND(gdp_per_capita)   SELECT name, ROUND(gdp_per_capita)
Line 17:
Line 17:
  </source>   </source>
  Notice that   Notice that
− *the inner table is given an alias X + *the inner table is given an alias <code>X</code>
  *the first column in the inner query keeps its name   *the first column in the inner query keeps its name
  *the second column in the inner query has an alias   *the second column in the inner query has an alias
Line 25:
Line 25:
  <div class='qu'>   <div class='qu'>
  <p class='imper'>Find the countries in the same region as Bhutan</p>   <p class='imper'>Find the countries in the same region as Bhutan</p>
− You may use a SELECT statement in the WHERE line - this returns a list of regions. + You may use a <code>SELECT</code> statement in the <code>WHERE</code> line - this returns a list of regions.
    
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
Line 53:
Line 53:
  <div>See also:</div>   <div>See also:</div>
  <ul>   <ul>
−    <li>[http://sqlzoo.net/w/index.php/SELECT_within_SELECT_Tutorial SELECT in SELECT Tutorial]</li> +    <li>[[SELECT within SELECT Tutorial]]</li>
     <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>      <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>
  </ul>   </ul>



Andr3w at 00:33, 9 August 2012
2012-08-09T00:33:10Z


				
				
				
				
			
			← Older revision
			Revision as of 00:33, 9 August 2012
			
Line 37:
Line 37:
    
  ==Correlated Subquery==   ==Correlated Subquery==
− <div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery". + <div class='qu'>
  + <p>If a value from the outer query appears in the inner query this is "correlated subquery".</p>
  <p class='imper'>Show the countries where the population is greater than 5 times the average for its region</p>   <p class='imper'>Show the countries where the population is greater than 5 times the average for its region</p>
    



Andr3w at 00:25, 9 August 2012
2012-08-09T00:25:18Z


				
				
				
				
			
			← Older revision
			Revision as of 00:25, 9 August 2012
			
Line 1:
Line 1:
− <h3>Subquery Table, Derived Tables, Nested Queries</h3> + __NOTOC__ 
  + ==Subquery Table, Derived Tables, Nested Queries==
  <p>You can use the results from one query in another query</p>   <p>You can use the results from one query in another query</p>
  ==Subquery with FROM==   ==Subquery with FROM==



Andr3w at 00:22, 9 August 2012
2012-08-09T00:22:54Z


				
				
				
				
			
			← Older revision
			Revision as of 00:22, 9 August 2012
			
Line 37:
Line 37:
  ==Correlated Subquery==   ==Correlated Subquery==
  <div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery".   <div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery".
− <p class='imper'>Show the countries where the population is greater than average for its region</p> + <p class='imper'>Show the countries where the population is greater than 5 times the average for its region</p>
    
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
  SELECT name   SELECT name
     FROM bbc b1      FROM bbc b1
−   WHERE population> +   WHERE population>  
−    (SELECT AVG(population) FROM bbc +    5*(SELECT AVG(population) FROM bbc
−     WHERE region=b1.region) +      WHERE region=b1.region)
  </source>   </source>
  </div>   </div>



Andr3w at 00:20, 9 August 2012
2012-08-09T00:20:47Z


				
				
				
				
			
			← Older revision
			Revision as of 00:20, 9 August 2012
			
Line 1:
Line 1:
  <h3>Subquery Table, Derived Tables, Nested Queries</h3>   <h3>Subquery Table, Derived Tables, Nested Queries</h3>
  <p>You can use the results from one query in another query</p>   <p>You can use the results from one query in another query</p>
  + ==Subquery with FROM==
  <div class='qu'>   <div class='qu'>
− You may use a SELECT statement in the FROM line SELECT f FROM (SELECT ). + You may use a SELECT statement in the FROM line.
  +  
  In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>.   In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>.
  The calculated values in the inner SELECT can be used in the outer SELECT.   The calculated values in the inner SELECT can be used in the outer SELECT.
Line 19:
Line 21:
  </div>   </div>
    
  + ==Subquery with IN==
  <div class='qu'>   <div class='qu'>
  <p class='imper'>Find the countries in the same region as Bhutan</p>   <p class='imper'>Find the countries in the same region as Bhutan</p>
Line 31:
Line 34:
  </source>   </source>
  </div>   </div>
  + 
  + ==Correlated Subquery==
  + <div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery".
  + <p class='imper'>Show the countries where the population is greater than average for its region</p>
  + 
  + <source lang='sql' class='def'>
  + SELECT name
  +   FROM bbc b1
  +  WHERE population>
  +   (SELECT AVG(population) FROM bbc
  +     WHERE region=b1.region)
  + </source>
  + </div>
  + 
    
  <div>See also:</div>   <div>See also:</div>



Andr3w at 00:05, 9 August 2012
2012-08-09T00:05:10Z


				
				
				
				
			
			← Older revision
			Revision as of 00:05, 9 August 2012
			
Line 20:
Line 20:
    
  <div class='qu'>   <div class='qu'>
− <p class='imper'>Find the countries in the same region as Butan</p> + <p class='imper'>Find the countries in the same region as Bhutan</p>
  You may use a SELECT statement in the WHERE line - this returns a list of regions.   You may use a SELECT statement in the WHERE line - this returns a list of regions.
    
Line 28:
Line 28:
    WHERE region IN     WHERE region IN
     (SELECT region FROM bbc      (SELECT region FROM bbc
−      WHERE name='Butan') +      WHERE name='Bhutan')
  </source>   </source>
  </div>   </div>



Andr3w at 00:03, 9 August 2012
2012-08-09T00:03:09Z


				
				
				
				
			
			← Older revision
			Revision as of 00:03, 9 August 2012
			
Line 11:
Line 11:
             gdp/population AS gdp_per_capita              gdp/population AS gdp_per_capita
        FROM bbc) X         FROM bbc) X
−   WHERE region='North America' +   WHERE gdp_per_capita>20000
  </source>   </source>
  Notice that   Notice that
Line 18:
Line 18:
  *the second column in the inner query has an alias   *the second column in the inner query has an alias
  </div>   </div>
  + 
  + <div class='qu'>
  + <p class='imper'>Find the countries in the same region as Butan</p>
  + You may use a SELECT statement in the WHERE line - this returns a list of regions.
  + 
  + <source lang='sql' class='def'>
  + SELECT name
  +   FROM bbc
  +  WHERE region IN
  +   (SELECT region FROM bbc
  +     WHERE name='Butan')
  + </source>
  + </div>
  + 
  <div>See also:</div>   <div>See also:</div>
  <ul>   <ul>



Andr3w at 23:57, 8 August 2012
2012-08-08T23:57:29Z


				
				
				
				
			
			← Older revision
			Revision as of 23:57, 8 August 2012
			
Line 1:
Line 1:
−  <h3>The Derived Table (SELECT FROM SELECT)</h3> + <h3>Subquery Table, Derived Tables, Nested Queries</h3>
  <p>You can use the results from one query in another query</p>   <p>You can use the results from one query in another query</p>
  <div class='qu'>   <div class='qu'>
− You may use a SELECT statement in the FROM line. + You may use a SELECT statement in the FROM line SELECT f FROM (SELECT ).
  In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>.   In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>.
  The calculated values in the inner SELECT can be used in the outer SELECT.   The calculated values in the inner SELECT can be used in the outer SELECT.
Line 13:
Line 13:
    WHERE region='North America'     WHERE region='North America'
  </source>   </source>
  + Notice that
  + *the inner table is given an alias X
  + *the first column in the inner query keeps its name
  + *the second column in the inner query has an alias
  </div>   </div>
−  
− <h2>What about ties?</h2>  
− <p>If one region has two countries with the same, highest population then the query will  
− produce the right answers but there will be duplication- both countries will be shown.  
− </p>  
  <div>See also:</div>   <div>See also:</div>
  <ul>   <ul>



Andr3w at 23:52, 8 August 2012
2012-08-08T23:52:19Z


				
				
				
				
			
			← Older revision
			Revision as of 23:52, 8 August 2012
			
Line 3:
Line 3:
  <div class='qu'>   <div class='qu'>
  You may use a SELECT statement in the FROM line.   You may use a SELECT statement in the FROM line.
− In this case the derived table X has columns <code>name</code>, + In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>.
− <code>region</code> and <code>gdp_per_capita</code> + The calculated values in the inner SELECT can be used in the outer SELECT.
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
  SELECT name, ROUND(gdp_per_capita)   SELECT name, ROUND(gdp_per_capita)
     FROM      FROM
−    (SELECT name,region, +    (SELECT name,
             gdp/population AS gdp_per_capita              gdp/population AS gdp_per_capita
        FROM bbc) X         FROM bbc) X



Andr3w at 23:39, 8 August 2012
2012-08-08T23:39:07Z


				
				
				
				
			
			← Older revision
			Revision as of 23:39, 8 August 2012
			
Line 3:
Line 3:
  <div class='qu'>   <div class='qu'>
  You may use a SELECT statement in the FROM line.   You may use a SELECT statement in the FROM line.
− In this example the table is <code>beast</code> and the columns are <code>id</code> and <code>legs</code>. + In this case the derived table X has columns <code>name</code>,
  + <code>region</code> and <code>gdp_per_capita</code>
  <source lang='sql' class='def'>   <source lang='sql' class='def'>
− SELECT name, gdp_pre_capita + SELECT name, ROUND(gdp_per_capita)
     FROM      FROM
−    (SELECT name, gdp/population AS gdp_per_capita +    (SELECT name,region,
  +           gdp/population AS gdp_per_capita
        FROM bbc) X         FROM bbc) X
  +  WHERE region='North America'
  </source>   </source>
  </div>   </div>

SELECT .. SELECT - Revision history

JesseTG: Added more tags

Andr3w at 00:33, 9 August 2012

Andr3w at 00:25, 9 August 2012

Andr3w at 00:22, 9 August 2012

Andr3w at 00:20, 9 August 2012

Andr3w at 00:05, 9 August 2012

Andr3w at 00:03, 9 August 2012

Andr3w at 23:57, 8 August 2012

Andr3w at 23:52, 8 August 2012

Andr3w at 23:39, 8 August 2012

JesseTG: Added more `tags`