GISQ: Tutorial Two






A Gentle
Introduction
to



SQL

Tutorial Two

Fantasy World Factbook

This tutorial explains how to create tables and change values in SQL.
Because we will be changing data each student must create their
own table. Depending on the environment you may need addition
permissions from your DBA to create or change tables. It may well
be that you cannot change the CIA database on your system.

Instructions

Use copy and paste to enter the following SQL commands. Hopefully
the syntax is self-explanatory. Note that we are dropping GDP
and region.
CREATE TABLE fantasy
( name char(50),
area decimal(10),
population decimal(11),
primary key (name)
);

If you want to delete the table use the phrase
DROP TABLE fantasy
 
You can add individual items into your table using the INSERT
command.
INSERT INTO fantasy VALUES ('Neverland', NULL, 0);
INSERT INTO fantasy VALUES ('Atlantis' , 100, 100);

Notice that the values NULL are different to 0. What do you suppose
the average values are for area and population? Check it out.

Importing data from cia.
First delete the existing records:
DELETE FROM fantasy;
Then copy the data using a SELECT phrase in place of the values.
INSERT INTO fantasy (name, area, population)
SELECT name, area, population FROM cia;


Let's go to work

Putting the world to rights:
The following problems are complicated - it is easy to make a mistake - you should issue COMMIT before starting each question - after each question check if the total area and population are correct - if not you can ROLLBACK and try again, if they are OK you can COMMIT.
China takes over Hong Kong - the population and area of
of Hong Kong must be added to that of China. The record
for Hong Kong must be deleted. You should not have to
do any arithmetic yourself.
While you're at it you can give Gibraltar to the Spanish and
the Falklands to Argentina.

Independence for Scotland! Create a new country and give it
a population of 5 million and an area of 78000 to be taken
from the United Kingdom.


None of the above operations involved creating or destroying people
or real estate. The total area and population of the world should
be unchanged. The values for the original cia file can be referenced.
It may well yield:

SUM OF areaSUM OF population
1302288645729533973



Create a view based on the data in CIA. The view should be called REGIONS and have fields: name, area,
population and count. There should be one entry per region, the values of area and populations should be totals, the count field should give the number of countries in the region.

Very difficult question Table splitting: We might save disk space and data entry effort by numbering the regions. The numbers assigned to each region would be arbirary - so long as they are unique. The first task is to create a table listing each region and it's number. The following code will create such a table. CREATE TABLE region (id INTEGER, name CHAR(60));
INSERT INTO region
SELECT COUNT(a.name), b.name FROM regions a, regions b
WHERE a.name