1. China takes over Hong Kong - the population and area of Hong Kong must be added to that of China. The record for Hong Kong must be deleted.

   UPDATE fantasy
   SET population=(SELECT SUM(population) FROM cia
                          WHERE name IN ('Hong Kong','China'))
   WHERE name='China';
   UPDATE fantasy
   SET area      =(SELECT SUM(area)       FROM cia
                          WHERE name IN ('Hong Kong','China'))
   WHERE name='China';
   DELETE FROM fantasy
   WHERE name='Hong Kong';

2. Give Gibraltar to the Spanish

   UPDATE fantasy
   SET population=(SELECT SUM(population) FROM cia
                          WHERE name IN ('Gibraltar', 'Spain'))
   WHERE name='Spain';
   UPDATE fantasy
   SET area      =(SELECT SUM(area)       FROM cia
                          WHERE name IN ('Gibraltar', 'Spain'))
   WHERE name='Spain';
   
   DELETE FROM fantasy
   WHERE name='Gibralta';

   Give the Falklands to Argentina

   UPDATE fantasy
   SET population=(SELECT SUM(population) FROM cia
                          WHERE name='Argentina' OR name LIKE 'Falkland%')
   WHERE name='Argentina';
   UPDATE fantasy
   SET area      =(SELECT SUM(area)       FROM cia
                          WHERE name='Argentina' OR name LIKE 'Falkland%')
   WHERE name='Argentina';
   
   DELETE FROM fantasy
   WHERE name LIKE 'Falkland%';

   In order to stream-line the decolonisation process we would like to parameterise this routine. ORACLE permits variables to be included in statements. Try

   UPDATE fantasy
   SET population=(SELECT SUM(population) FROM cia
                          WHERE name IN ('&big_fish', '&little_fish'))
   WHERE name = '&big_fish';
   UPDATE fantasy
   SET area      =(SELECT SUM(area)       FROM cia
                          WHERE name IN ('&big_fish', '&little_fish'))
   WHERE name = '&big_fish';
   
   DELETE FROM fantasy
   WHERE name = '&little_fish';

3. Independence for Scotland!

   UPDATE fantasy
   SET population=(SELECT population-5000000 FROM cia
                          WHERE name='United Kingdom'),
       area      =(SELECT area      -  78000 FROM cia
                          WHERE name='United Kingdom')
   WHERE name='United Kingdom';
   INSERT INTO fantasy (name, population, area)
     VALUES ('Scotland',5000000,78000);

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

CREATE VIEW regions AS
  SELECT region name, SUM(area) area, SUM(population) population,
         COUNT(name) countries FROM cia GROUP BY region;