GISQ: Tutorial One: Notes 2






A Gentle
Introduction
to



SQL

Tutorial One
Notes 2
CIA World Factbook
Simple use of SELECT and WHERE
These statements take the form :
SELECT <attrib-list>
FROM <table-name>
WHERE <cond-list>


<attrib-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 cia.
<cond-list>

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.

Observe the tables which result from each of the following

The population of France.
SELECT population FROM cia WHERE name = 'France'

population
58109160


The names and population densities for the very large countries.
SELECT name, population/area FROM cia WHERE area > 5000000

namepopulation / area
Australia2
Brazil19
Canada3
China125
Russia9
United States28


Where to find some very small, very rich countries.
SELECT name || ' is in ' || region || '.' FROM cia
WHERE (area < 2000) AND
(gdp > 5000000000)

name || is in || region ||th>
Bahrain is in Middle East.
Hong Kong is in Southeast Asia.
Mali is in Africa.
Mauritius is in World.
Singapore is in Southeast Asia.


Which of these is the name of a country?
SELECT name FROM cia
WHERE name in ('Sri Lanka', 'Ceylon', 'Persia', 'Iran')

name
Sri Lanka
Iran

What are the countries beginning with D?
SELECT name FROM cia WHERE name LIKE 'D%'

name
Denmark
Djibouti
Dominica
Dominican Republic



A common mistake made with boolean expressions

Boolean expressions often read like "normal" English however the
syntax is not as rich. For example we might say name equals 'France'
or 'Germany' however the corresponding boolean expression is
name = 'France' or name='Germany'