Connor: Created page with "

BBC Country Profiles

Some examples of simple WHERE statements:

The population of 'France'. '''Strings should be in 'single..."

2012-07-12T11:25:47Z

Created page with "<h3>BBC Country Profiles</h3> Some examples of simple <code>WHERE</code> statements: <div class='qu'> The population of <code>'France'.</code> '''Strings should be in 'single..."

New page

<h3>BBC Country Profiles</h3>
Some examples of simple <code>WHERE</code> statements:

<div class='qu'>
The population of <code>'France'.</code>
'''Strings should be in 'single quotes';'''
<source lang='sql' class='def'>
SELECT population FROM bbc
WHERE name = 'France'
</source>

<source lang='sql' class='ans'>
SELECT population FROM bbc
WHERE name = 'France'
</source>
</div>

<div class='qu'>
The names and population densities for the very large countries.
'''We can use mathematical and string expressions as well as field names and constants.'''
<source lang='sql' class='def'>
SELECT name, population/area FROM bbc
WHERE area > 5000000
</source>

<source lang='sql' class='ans'>
SELECT name, population/area FROM bbc
WHERE area > 5000000
</source>
</div>

<div class='qu'>
Where to find some very small, very rich countries.
'''We use AND to ensure that two or more conditions hold true.'''
<source lang='sql' class='def'>
SELECT name , region
FROM bbc
WHERE area < 2000
AND gdp > 5000000000
</source>

<source lang='sql' class='ans'>
SELECT name , region
FROM bbc
WHERE area < 2000
AND gdp > 5000000000
</source>
</div>

<div class='qu'>
Which of <code>Ceylon</code>, <code>Iran</code>, <code>Persia</code> and <code>Sri Lanka</code> is the name of a country?
'''The word IN allows us to check if an item is in a list.'''
<source lang='sql' class='def'>
SELECT name FROM bbc
WHERE name IN ('Sri Lanka', 'Ceylon',
'Persia', 'Iran')
</source>

<source lang='sql' class='ans'>
SELECT name FROM bbc
WHERE name IN ('Sri Lanka', 'Ceylon',
'Persia', 'Iran')
</source>
</div>

<div class='qu'>
What are the countries beginning with D?
'''The word <code>LIKE</code> permits pattern matching - % is the wildcard.'''
<source lang='sql' class='def'>
SELECT name FROM bbc
WHERE name LIKE 'D%'
</source>

<source lang='sql' class='ans'>
SELECT name FROM bbc
WHERE name LIKE 'D%'
</source>
</div>

<div class='qu'>
Which countries are not too small and not too big?
<code>BETWEEN</code> allows range checking - note that it is inclusive.
<source lang='sql' class='def'>
SELECT name, area FROM bbc
WHERE area BETWEEN 207600 AND 244820
</source>

<source lang='sql' class='ans'>
SELECT name, area FROM bbc
WHERE area BETWEEN 207600 AND 244820
</source>
</div>

<pre>SELECT attribute-list
FROM table-name
WHERE condition</pre>
<ul>
<li>
<code><i>attribute-list</i></code>
</li>

<li style="list-style: none">
<ul>
<li>This is usually a comma separated list of attributes
(field names)</li>

<li>Expressions involving these attributes may be used. The
normal mathematical operators <code>+, -, *, /</code> may be used on
numeric values. String values may be concatenated using
<code>||</code></li>

<li>To select all attributes use *</li>

<li>The attributes in this case are: <code>name</code>, <code>region</code>, <code>area</code>,
<code>population</code> and <code>gdp</code></li>
</ul>
</li>

<li>
<code><i>table-name</i></code>
<ul style="list-style-type: circle">
<li>In these examples the table is always <code>bbc</code>.</li>
</ul>
</li>

<li>
<code><i>condition</i></code>
</li>

<li style="list-style: none">
<ul>
<li>This is a boolean expression which each row must
satisfy.</li>

<li>Operators which may be used include <code>AND</code>,
<code>OR</code>, <code>NOT</code>, >,
>=, =, <, <=</li>

<li>The <code>LIKE</code> operator permits strings to be compared using
'wild cards'. The symbols _ and % are used to represent a
single character or a sequence of characters. Note that <code>MS
Access SQL</code> uses <code>?</code> and <code>*</code> instead of <code>_</code> and <code>%</code> .</li>

<li>The <code>IN</code> operator allows an item to be tested against a
list of values.</li>

<li>There is a <code>BETWEEN</code> operator for checking ranges.</li>
</ul>
</li>
</ul>

WHERE filters - Revision history

Connor: Created page with "

BBC Country Profiles