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 and gdp
• table-name
  • In these examples the table is always bbc.
• 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 that MS 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.