GISQ: Tutorial One: Notes 4






A Gentle
Introduction
to



SQL

Tutorial One
Notes 4

CIA World Factbook
Other features of SELECT:
SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY
The functions SUM, COUNT, MAX and AVG may be applied to a numeric
attribute resulting in a single row being returned by the query. These
functions are even more useful when used in conjunction with the GROUP
BY clause.

By default the result of a SELECT may contain duplicate rows.
We can remove these duplicates using the DISTINCT key word.
ORDER BY permits us to see the result of a SELECT in any
particular order.

The total population and GDP in millions of Europe.

SELECT SUM(population)/1000000, SUM(gdp)/1000000 FROM cia WHERE region = 'Europe'


SUM OF population / 1000000SUM OF gdp / 1000000
393.7088236584875


How many different regions are there?

SELECT COUNT(DISTINCT region) FROM cia


COUNT OF region
15


Show the large countries by order of population.

SELECT name, population FROM cia WHERE population > 100000000
ORDER BY population DESC


namepopulation
China1203097268
India936545814
United States263814032
Indonesia203583886
Brazil160737489
Russia149909089
Pakistan131541920
Bangladesh128094948
Japan125506492
Nigeria101232251