WHERE filters
BBC Country Profiles
Some examples of simple WHERE
statements:
The population of 'France'.
Strings should be in 'single quotes';
SELECT population FROM bbc
WHERE name = 'France'
SELECT population FROM bbc
WHERE name = 'France'
The names and population densities for the very large countries. We can use mathematical and string expressions as well as field names and constants.
SELECT name, population/area FROM bbc
WHERE area > 5000000
SELECT name, population/area FROM bbc
WHERE area > 5000000
Where to find some very small, very rich countries. We use AND to ensure that two or more conditions hold true.
SELECT name , region
FROM bbc
WHERE area < 2000
AND gdp > 5000000000
SELECT name , region
FROM bbc
WHERE area < 2000
AND gdp > 5000000000
Which of Ceylon
, Iran
, Persia
and Sri Lanka
is the name of a country?
The word IN allows us to check if an item is in a list.
SELECT name FROM bbc
WHERE name IN ('Sri Lanka', 'Ceylon',
'Persia', 'Iran')
SELECT name FROM bbc
WHERE name IN ('Sri Lanka', 'Ceylon',
'Persia', 'Iran')
What are the countries beginning with D?
The word LIKE
permits pattern matching - % is the wildcard.
SELECT name FROM bbc
WHERE name LIKE 'D%'
SELECT name FROM bbc
WHERE name LIKE 'D%'
Which countries are not too small and not too big?
BETWEEN
allows range checking - note that it is inclusive.
SELECT name, area FROM bbc
WHERE area BETWEEN 207600 AND 244820
SELECT name, area FROM bbc
WHERE area BETWEEN 207600 AND 244820
SELECT attribute-list FROM table-name WHERE condition
-
attribute-list
-
- This is usually a comma separated list of attributes (field names)
- Expressions involving these attributes may be used. The
normal mathematical operators
+, -, *, /
may be used on numeric values. String values may be concatenated using||
- To select all attributes use *
- The attributes in this case are:
name
,region
,area
,population
andgdp
-
table-name
- In these examples the table is always
bbc
.
- In these examples the table is always
-
condition
-
- This is a boolean expression which each row must satisfy.
- Operators which may be used include
AND
,OR
,NOT
, >, >=, =, <, <= - The
LIKE
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 thatMS Access SQL
uses?
and*
instead of_
and%
. - The
IN
operator allows an item to be tested against a list of values. - There is a
BETWEEN
operator for checking ranges.