Difference between pages "BBC QUIZ" and "Strings"

From SQLZOO
(Difference between pages)
Jump to: navigation, search
 
 
Line 1: Line 1:
BBC QUIZ
+
<table align='right' border='1'>
<div class='ref_section'>
+
<caption>Compatibility</caption>
<table class='db_ref'>
+
<tr><th colspan='3'>s1 || s2</th></tr>
<caption>bbc</caption>
+
<tr><td align='center'>'''Engine'''</td><td align='center'>'''OK'''</td><td align='center'>'''Alternative'''</td></tr>
<tr>
+
<tr><td align='left'>ingres</td><td>Yes</td><td></td></tr>
<th>name</th>
+
<tr><td align='left'>mysql</td><td>No</td><td>CONCAT(s1,s2)</td></tr>
<th>region</th>
+
<tr><td align='left'>oracle</td><td>Yes</td><td></td></tr>
<th>area</th>
+
<tr><td align='left'>postgres</td><td>Yes</td><td></td></tr>
<th>population</th>
+
<tr><td align='left'>sqlserver</td><td>No</td><td>s1 + s2</td></tr>
<th>gdp</th>
+
</tr>
+
<tr>
+
<td>Afghanistan</td>
+
<td>South Asia</td>
+
<td align='right'>652225</td>
+
<td align='right'>26000000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td>Albania</td>
+
<td>Europe</td>
+
<td align='right'>28728</td>
+
<td align='right'>3200000</td>
+
<td align='right'>6656000000</td>
+
</tr>
+
<tr>
+
<td>Algeria</td>
+
<td>Middle East</td>
+
<td align='right'>2400000</td>
+
<td align='right'>32900000</td>
+
<td align='right'>75012000000</td>
+
</tr>
+
<tr>
+
<td>Andorra</td>
+
<td>Europe</td>
+
<td align='right'>468</td>
+
<td align='right'>64000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td>Brazil</td>
+
<td>South America</td>
+
<td align='right'>8550000</td>
+
<td align='right'>182800000</td>
+
<td>564852000000</td>
+
</tr>
+
<tr>
+
<td>Colombia</td>
+
<td>South America</td>
+
<td align='right'>1140000</td>
+
<td align='right'>45600000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td>Nauru</td>
+
<td>Asia-Pacific</td>
+
<td align='right'>21</td>
+
<td align='right'>9900</td>
+
<td></td>
+
</tr>
+
</tr>
+
<tr>
+
<td>Uzbekistan</td>
+
<td>Central Asia</td>
+
<td align='right'>652225</td>
+
<td align='right'>26000000</td>
+
<td></td>
+
</tr>
+
<tr>
+
<td colspan='5'>...</td>
+
</tr>
+
 
</table>
 
</table>
</div>
 
  
<quiz shuffle=none display=simple>
+
<h1>|| (strings)</h1>
{Select the code which gives the name of countries beginning with C
+
<p>|| allows you to stick two or more strings together. </p>
|type="()"}
+
<p>This operation is concatenation.</p>
- SELECT name FROM bbc WHERE name BEGIN with C
+
<pre style='width:60ex'>
- SELECT name FROM bbc WHERE name LIKE '%C'
+
  s1 || s2   
- SELECT name FROM bbc WHERE name LIKE '%C%'
+
</pre>
- SELECT name FROM bbc WHERE name LIKE C
+
+ SELECT name FROM bbc WHERE name LIKE 'C%'
+
  
{Select the code which shows just the population of United Kingdom?
+
<div class='ht'>
|type="()"}
+
In this example you put the region and the name together for each country.
- SELECT name FROM bbc WHERE name Like 'United%'
+
<source lang='sql' class='def e-mysql'>
- SELECT name FROM bbc WHERE population = 'United Kingdom'
+
SELECT CONCAT(region,name)
- SELECT name, population FROM bbc WHERE name = 'United Kingdom'
+
  FROM bbc
+ SELECT population FROM bbc WHERE name = 'United Kingdom'
+
</source>
- SELECT population FROM bbc WHERE name = United Kingdom
+
<source lang='sql' class='def e-sqlserver'>
 
+
SELECT region + name
{Select the answer which shows the problem with this SQL code:SELECT name FROM "bbc" WHERE population > '2000000'
+
  FROM bbc
|type="()"}
+
</source>
- Apostrophes should be used around 'bbc' instead of speech marks
+
<source lang='sql' class='def'>
- There are apostrophes surrounding '2000000'
+
SELECT region || name
+ There are speech marks surrounding "bbc"
+
  FROM bbc
- There are speech marks surrounding "bbc" and there is apostrophes surrounding '2000000'
+
</source>
- The use of a WHERE function is incorrect
+
</div>
 
+
{Select the result that would be obtained from the following code: SELECT name, population / 10 FROM bbc WHERE population < 10000
+
<table style='float:left'><caption>Table-A</caption><tr><td>Andorra</td><td>6400</td></tr><tr><td>Nauru</td><td>990</td></tr></table>
+
<table style='float:left'><caption>Table-B</caption><tr><td>Andorra</td><td>64000</td></tr><tr><td>Nauru</td><td>9900</td></tr></table>
+
<table style='float:left'><caption>Table-C</caption><tr><td>Nauru</td><td>99</td></tr></table>
+
<table style='float:left'><caption>Table-D</caption><tr><td>Nauru</td><td>990</td></tr></table>
+
<table style='float:left'><caption>Table-E</caption><tr><td>Nauru</td><td>9900</td></tr></table>
+
|type="()"}
+
- Table-A
+
- Table-B
+
- Table-C
+
+ Table-D
+
- Table-E
+
 
+
{Select the code which would reveal the name and population of countries in Europe, North America and South America
+
|type="()"}
+
- SELECT name FROM bbc WHERE region IN ('Europe', 'North America', 'South America')
+
+ SELECT name, population FROM bbc WHERE region IN ('Europe', 'North America', 'South America')
+
- SELECT name, population FROM bbc WHERE region IN (Europe North America South America)
+
- SELECT name, population FROM bbc WHERE region IS ('Europe', 'North America', 'South America')
+
- SELECT population FROM bbc WHERE region IN ('Europe', 'North America', 'South America')
+
  
{Select the code which would give two rows
+
<p>See also</p>
|type="()"}
+
<ul>
- SELECT name FROM bbc WHERE name = 'United Kingdom'
+
  <li>[[SUBSTRING |SUBSTRING function]]</li>
- SELECT name FROM bbc WHERE name = 'United Kingdom' AND name = 'Algeria'
+
  <li>[[TRIM |TRIM function]]</li>
- SELECT name FROM bbc WHERE name EITHER ('United Kingdom', 'Algeria')
+
</ul>
+ SELECT name FROM bbc WHERE name IN ('United Kingdom', 'Algeria')
+
- SELECT name FROM WHERE name IS 'Scotland'
+
  
{Select the result that would be obtained from this code:
+
{{Languages}}
<source lang='SQL'>SELECT name FROM bbc WHERE region = 'South America' AND population > 40000000</source>
+
<table style='float:left'><caption>Table-A</caption><tr><td>Afghanistan</td></tr><tr><td>Brazil</td></tr><tr><td>Colombia</td></tr></table>
+
<table style='float:left'><caption>Table-B</caption><tr><td>Brazil</td></tr></table>
+
<table style='float:left'><caption>Table-C</caption><tr><td>Brazil</td></tr><tr><td>Colombia</td></tr></table>
+
<table style='float:left'><caption>Table-D</caption><tr><td>Brazil</td><td>South America</td></tr><tr><td>Colombia</td><td>South America</td></tr></table>
+
<table style='float:left'><caption>Table-E</caption><tr><td>Brazil</td><td>182800000</td></tr><tr><td>Colombia</td><td>45600000</td></tr></table>
+
|type="()"}
+
- Table-A
+
- Table-B
+
+ Table-C
+
- Table-D
+
- Table-E
+
</quiz>
+
[[Category:Quizzes]]
+

Revision as of 17:05, 21 October 2012

Compatibility
s1 || s2
EngineOKAlternative
ingresYes
mysqlNoCONCAT(s1,s2)
oracleYes
postgresYes
sqlserverNos1 + s2

|| (strings)

|| allows you to stick two or more strings together.

This operation is concatenation.

   s1 || s2    

In this example you put the region and the name together for each country.

SELECT CONCAT(region,name)
  FROM bbc
SELECT region + name
  FROM bbc
SELECT region || name
  FROM bbc

See also

Language: English  • Deutsch
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense