Create a new version of paste:

Leaked AUSTRALIA BASE Included CIA AGENT NGUYEN RICARD !
Cybernatic Spy By : Anon1337
---------------------
PremanKeyboard      |
---------------------
-- 
-- The SQL for the CIA World Fact Book Database
-- Database Design by Richard Nguyen, Spring 2006
--
-- Only data for a small number of has been entered
--

DROP table CIA_Language  CASCADE;
DROP table CIA_Religion  CASCADE;
DROP table CIA_Ethnic    CASCADE;
DROP table CIA_Neighbour CASCADE;
DROP table CountryOcean  CASCADE;
DROP table CIA_Country   CASCADE;
DROP table CIA_Ocean     CASCADE;


-- 
-- Table creation
--


Create table CIA_Country
(
  Country       TEXT,             -- Name of the Country
  Location      TEXT,             -- Geographic location
  Land          DOUBLE PRECISION, -- Land area, in square kilometers
  HighestName   TEXT,             -- Name of the highest point. null if no name.
  Highest       INTEGER,          -- Highest point in metres
  LowestName    TEXT,             -- Name of the lowest point. null if no name.
  Lowest        INTEGER,          -- Lowest point in metres. <0 below sea level
  Arable        DOUBLE PRECISION, -- Arable land, as a percentage of land area
  Population    NUMERIC,          -- The total population of the nation
  SexRatio      DOUBLE PRECISION, -- Ratio of males to females
  Age14         DOUBLE PRECISION, -- Percent of population 14 years or younger
  Age65         DOUBLE PRECISION, -- Percent of population 65 years or older
  PopGrowth     DOUBLE PRECISION, -- Population growth rate, as percentage
  Migration     DOUBLE PRECISION, -- Migration rate, per thousand people
  InfantMort    DOUBLE PRECISION, -- Infant mortality, per thousand live births
  LifeExpectM   DOUBLE PRECISION, -- Male life expectancy at birth, in years
  LifeExpectF   DOUBLE PRECISION, -- Female life expectancy at birth, in years
  HIV           DOUBLE PRECISION, -- Adult prevalence rate, as a percentage
  Literacy      DOUBLE PRECISION, -- As a percentage of total population
  Government    TEXT,             -- Type of government 
  Capital       TEXT,             -- Capital city; legislative if multiple
  Independence  INTEGER,          -- Year gained independence; negative if BC
  Chief         TEXT,             -- Chief of State
  GDP           DOUBLE PRECISION, -- (purchasing power parity) in $USbillons
  Growth        DOUBLE PRECISION, -- GDP . real growth rate, as a percentage
  OilProd       DOUBLE PRECISION, -- bbl/day
  OilConsum     DOUBLE PRECISION, -- bbl/day
  OilExport     DOUBLE PRECISION, -- bbl/day
  CurrAccount   DOUBLE PRECISION, -- Current account balance; $USbillions/year
  Mobiles       DOUBLE PRECISION, -- Number of mobile phones, in millions
  Internet      NUMERIC,          -- The number of Internet users
  Paved         INTEGER,          -- Amount of paved road, in kilometers
  Unpaved       INTEGER,          -- Amount of unpaved road, in kilometers
  Military      DOUBLE PRECISION, -- Military expenditure in billions of dollars

  CONSTRAINT CIA_CountryPK PRIMARY KEY (Country),

  CONSTRAINT CIA_Country_Land CHECK (Land > 0),

  CONSTRAINT CIA_Country_Height CHECK (Highest > Lowest),

  CONSTRAINT CIA_Country_Arable CHECK ((Arable >= 0) AND (Arable <= 100)),

  CONSTRAINT CIA_Country_Population CHECK (Population > 0),

  CONSTRAINT CIA_Country_Age14 CHECK ((Age14 >= 0) AND (Age14 <= 100)),

  CONSTRAINT CIA_Country_Age65 CHECK ((Age65 >= 0) AND (Age65 <= 100)),

  CONSTRAINT CIA_Country_PopGrowth CHECK ((PopGrowth >= -100) AND
                                          (PopGrowth <=  100)),

  CONSTRAINT CIA_Country_Migration CHECK ((Migration >= -1000) AND
                                          (Migration <= 1000)),

  CONSTRAINT CIA_Country_InfantMort CHECK ((InfantMort >= 0) AND
                                           (InfantMort <= 1000)),

  CONSTRAINT CIA_Country_LifeExpectM CHECK ((LifeExpectM > 0) AND
                                            (LifeExpectM < 100)),

  CONSTRAINT CIA_Country_LifeExpectF CHECK ((LifeExpectF > 0) AND
                                            (LifeExpectF < 100)),

  CONSTRAINT CIA_Country_HIV CHECK ((HIV >= 0) AND (HIV <= 100)),

  CONSTRAINT CIA_Country_Literacy CHECK ((Literacy >= 0) AND
                                         (Literacy <= 100)),

  CONSTRAINT CIA_Country_Independence CHECK (Independence < 2007),

  CONSTRAINT CIA_Country_OilProd CHECK (OilProd >= 0),

  CONSTRAINT CIA_Country_OilConsum CHECK (OilConsum >= 0),

  CONSTRAINT CIA_Country_OilExport CHECK (OilExport >= 0),

  CONSTRAINT CIA_Country_Mobiles CHECK (Mobiles >= 0),

  CONSTRAINT CIA_Country_Internet CHECK (Internet >= 0),

  CONSTRAINT CIA_Country_Paved CHECK (Paved >= 0),

  CONSTRAINT CIA_Country_Unpaved CHECK (Unpaved >= 0),

  CONSTRAINT CIA_Country_Military CHECK (Military >= 0)
);


Create table CIA_Ocean
(
  Ocean           TEXT,           -- Name of the ocean
  Area            NUMERIC,        -- in millions of square kilometers
  DeepestName     TEXT,           -- Name of the deepest point. null if no name.
  Deepest         INTEGER,        -- Deepest point in metres

  CONSTRAINT CIA_OceanPK PRIMARY KEY (Ocean)
);


Create table CountryOcean
(
  Country         TEXT,           -- Name of the country
  Ocean           TEXT,           -- Name of the ocean

  CONSTRAINT CountryOceanPK PRIMARY KEY (Country,Ocean),

  CONSTRAINT CountryOceanFK_Invalid_Country FOREIGN KEY (Country)
                                            REFERENCES CIA_Country
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CountryOceanFK_Invalid_Ocean FOREIGN KEY (Ocean)
                                          REFERENCES CIA_Ocean
  ON DELETE CASCADE
  ON UPDATE CASCADE
);


Create table CIA_Neighbour
(
  CountryA        TEXT,              -- Name of one neighbour country
  CountryB        TEXT,              -- Name of the other neighbour country
  Length          DOUBLE PRECISION,  -- Length of the border between neighbours

  CONSTRAINT CIA_NeighbourPK PRIMARY KEY (CountryA,CountryB),

  CONSTRAINT CIA_NeighbourFK_Invalid_CountryA FOREIGN KEY (CountryA)
                                              REFERENCES CIA_Country (Country)
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CIA_NeighbourFK_Invalid_CountryB FOREIGN KEY (CountryB)
                                              REFERENCES CIA_Country (Country)
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CIA_Neighbour_Same_Country CHECK (CountryA <> CountryB),

  CONSTRAINT CIA_Neighbour_Alphabetical CHECK (CountryA < CountryB),

  CONSTRAINT CIA_Neighbour_Length CHECK (Length > 0)
);


Create table CIA_Language
(
  Country             TEXT,
  Language             TEXT, 
  Percentage       DOUBLE PRECISION,

  CONSTRAINT CIA_LanguagePK PRIMARY KEY (Country,Language),

  CONSTRAINT CIA_LanguageFK FOREIGN KEY (Country) REFERENCES CIA_Country
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CIA_Language_Percentage CHECK 
         (((Percentage > 0) AND (Percentage <= 100))  OR  (Percentage IS NULL))
);


Create table CIA_Religion
(
  Country             TEXT,
  Religion             TEXT,
  Percentage       DOUBLE PRECISION,

  CONSTRAINT CIA_ReligionPK PRIMARY KEY (Country,Religion),

  CONSTRAINT CIA_ReligionFK FOREIGN KEY (Country) REFERENCES CIA_Country
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CIA_Religion_Percentage CHECK 
         (((Percentage > 0) AND (Percentage <= 100))  OR  (Percentage IS NULL))
);


Create table CIA_Ethnic
(
  Country             TEXT,
  Ethnic             TEXT,
  Percentage       DOUBLE PRECISION,

  CONSTRAINT CIA_EthnicPK PRIMARY KEY (Country,Ethnic),

  CONSTRAINT CIA_EthnicFK FOREIGN KEY (Country) REFERENCES CIA_Country
  ON DELETE CASCADE
  ON UPDATE CASCADE,

  CONSTRAINT CIA_Ethnic_Percentage CHECK
         (((Percentage > 0) AND (Percentage <= 100))  OR  (Percentage IS NULL))
);


-- 
-- CIA_Country insertion
--


INSERT INTO CIA_Country VALUES ('Bolivia','Central South America',1084390,'Nevado Sajama',6542,'Rio Paraguay',90,2.78,8989046,0.98,35,4.6,1.45,-1.22,51.77,63.21,68.61,0.1,87.2,'Republic','La Paz',1825,'Morales',25.95,4,42000,48000,null,462,0.18,350000,4314,56448,0.13);

INSERT INTO CIA_Country VALUES ('India','Southern Asia',2973190,'Kanchenjunga',8598,'Indian Ocean',0,48.83,1095351995,1.06,30.8,4.9,1.38,-0.07,54.63,63.9,65.57,0.9,59.5,'Federal Republic','New Delhi',1947,'Kalam',3611,7.6,785000,2320000,350000,-12.95,69.19,50600000,2411001,1440439,19.04);

INSERT INTO CIA_Country VALUES ('Palau','Oceania',458,'Mount Ngerchelchuus',242,'Pacific Ocean',0,8.7,20579,1.13,26.3,4.6,1.31,1.85,14.46,67.26,73.77,null,92,'Constitutional','Koror',1994,'Remengesau',0.174,1,null,null,null,null,0.001,null,36,25,null);

INSERT INTO CIA_Country VALUES ('Vatican City','Southern Europe',0.44,'null',75,null,19,0,932,null,null,null,0.01,null,null,null,null,null,100,'Ecclesiastical','Vatican City',1929,'Benedict',null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Argentina',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Brazil',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Chile',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Paraguay',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Peru',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Bangladesh',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Bhutan',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Burma',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('China',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Nepal',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Pakistan',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);

INSERT INTO CIA_Country VALUES ('Italy',null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null);


-- 
-- CIA_Ocean insertion
--


INSERT INTO CIA_Ocean VALUES 
(
  'Pacific',            --      Ocean
  179.7,                --      Area in millions of square kilometers
  'Mariana Trench',     --      DeepestName
  10911                 --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Atlantic',           --      Ocean
  106.4,                --      Area in millions of square kilometers
  'Puerto Rico Trench', --      DeepestName
  8605                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Indian',             --      Ocean
  73.556,               --      Area in millions of square kilometers
  'Java Trench',        --      DeepestName 
  7450                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Southern',           --      Ocean
  20.327,               --      Area in millions of square kilometers
  'South Sandwich Trench', --   DeepestName
  7235                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Arctic',             --      Ocean
  14.056,               --      Area in millions of square kilometers
  'Eurasian Basin',     --      DeepestName
  5450                  --      Deepest point in metres
);


-- 
-- CountryOcean insertion
--


INSERT INTO CountryOcean VALUES ('India','Indian');
INSERT INTO CountryOcean VALUES ('Palau','Pacific');


-- 
-- CIA_Neighbour insertion
--


INSERT INTO CIA_Neighbour VALUES ('Argentina','Bolivia',832);
INSERT INTO CIA_Neighbour VALUES ('Bolivia','Brazil',3400);
INSERT INTO CIA_Neighbour VALUES ('Bolivia','Chile',861);
INSERT INTO CIA_Neighbour VALUES ('Bolivia','Paraguay',750);
INSERT INTO CIA_Neighbour VALUES ('Bolivia','Peru',900);
INSERT INTO CIA_Neighbour VALUES ('Bangladesh','India',4053);
INSERT INTO CIA_Neighbour VALUES ('Bhutan','India',605);
INSERT INTO CIA_Neighbour VALUES ('Burma','India',1463);
INSERT INTO CIA_Neighbour VALUES ('China','India',3380);
INSERT INTO CIA_Neighbour VALUES ('India','Nepal',1690);
INSERT INTO CIA_Neighbour VALUES ('India','Pakistan',2912);
INSERT INTO CIA_Neighbour VALUES ('Italy','Vatican City',3.2);


-- 
-- CIA_Language insertion
--


INSERT INTO CIA_Language VALUES ('Bolivia','Spanish',null);
INSERT INTO CIA_Language VALUES ('Bolivia','Quechua',null);
INSERT INTO CIA_Language VALUES ('Bolivia','Aymara',null);
INSERT INTO CIA_Language VALUES ('India','Indo-Aryan',74);
INSERT INTO CIA_Language VALUES ('India','Dravidian',24);
INSERT INTO CIA_Language VALUES ('India','other',2);
INSERT INTO CIA_Language VALUES ('Palau','Palauan',64.7);
INSERT INTO CIA_Language VALUES ('Palau','Filipino',13.5);
INSERT INTO CIA_Language VALUES ('Palau','English',9.4);
INSERT INTO CIA_Language VALUES ('Palau','Chinese',5.7);
INSERT INTO CIA_Language VALUES ('Palau','Carolinian',1.5);
INSERT INTO CIA_Language VALUES ('Palau','Japanese',1.5);
INSERT INTO CIA_Language VALUES ('Palau','other',2.3);
INSERT INTO CIA_Language VALUES ('Vatican City','Italian',null);
INSERT INTO CIA_Language VALUES ('Vatican City','Latin',null);
INSERT INTO CIA_Language VALUES ('Vatican City','French',null);


-- 
-- CIA_Religion insertion
--


INSERT INTO CIA_Religion VALUES ('Bolivia','Roman Catholic',95);
INSERT INTO CIA_Religion VALUES ('Bolivia','Protestant',5);
INSERT INTO CIA_Religion VALUES ('India','Hindu',80.5);
INSERT INTO CIA_Religion VALUES ('India','Muslim',13.4);
INSERT INTO CIA_Religion VALUES ('India','Christian',2.3);
INSERT INTO CIA_Religion VALUES ('India','Sikh',1.9);
INSERT INTO CIA_Religion VALUES ('India','other',1.8);
INSERT INTO CIA_Religion VALUES ('India','unspecified',0.1);
INSERT INTO CIA_Religion VALUES ('Palau','Roman Catholic',41.6);
INSERT INTO CIA_Religion VALUES ('Palau','Protestant',23.3);
INSERT INTO CIA_Religion VALUES ('Palau','Modekngei',8.8);
INSERT INTO CIA_Religion VALUES ('Palau','Seventh-Day Adventist',5.3);
INSERT INTO CIA_Religion VALUES ('Palau','Jehovah"s Witness',0.9);
INSERT INTO CIA_Religion VALUES ('Palau','Latter-Day Saints',0.6);
INSERT INTO CIA_Religion VALUES ('Palau','other',3.1);
INSERT INTO CIA_Religion VALUES ('Palau','unspecified',16.4);
INSERT INTO CIA_Religion VALUES ('Vatican City','Roman Catholic',100);


-- 
-- CIA_Ethnic insertion
--


INSERT INTO CIA_Ethnic VALUES ('Bolivia','Quecha',30);
INSERT INTO CIA_Ethnic VALUES ('Bolivia','mestizo',30);
INSERT INTO CIA_Ethnic VALUES ('Bolivia','Aymara',25);
INSERT INTO CIA_Ethnic VALUES ('Bolivia','white',15);
INSERT INTO CIA_Ethnic VALUES ('India','Indo-Aryan',72);
INSERT INTO CIA_Ethnic VALUES ('India','Dravidian',25);
INSERT INTO CIA_Ethnic VALUES ('India','other',3);
INSERT INTO CIA_Ethnic VALUES ('Palau','Palauan',69.9);
INSERT INTO CIA_Ethnic VALUES ('Palau','Filipino',15.3);
INSERT INTO CIA_Ethnic VALUES ('Palau','Chinese',4.9);
INSERT INTO CIA_Ethnic VALUES ('Palau','other Asian',2.4);
INSERT INTO CIA_Ethnic VALUES ('Palau','white',1.9);
INSERT INTO CIA_Ethnic VALUES ('Palau','Carolinian',1.4);
INSERT INTO CIA_Ethnic VALUES ('Palau','other',1.1);
INSERT INTO CIA_Ethnic VALUES ('Vatican City','Italian',null);
INSERT INTO CIA_Ethnic VALUES ('Vatican City','Swiss',null);


INSERT INTO CIA_Ocean VALUES 
(
  'Baltic Sea',         --      Ocean
  0.377000,             --      Area in millions of square kilometers
  null,                 --      DeepestName
  459                   --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Aral Sea',           --      Ocean
  0.1716,               --      Area in millions of square kilometers
  null,                 --      DeepestName
  null                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Caspian Sea',        --      Ocean
  0.371,                --      Area in millions of square kilometers
  null,                 --      DeepestName
  1025                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Dead Sea',           --      Ocean
  0.00081,              --      Area in millions of square kilometers
  null,                 --      DeepestName
  330                   --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Mediterranean Sea',  --      Ocean
  2.5,                  --      Area in millions of square kilometers
  'Calypso Deep',       --      DeepestName
  5267                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Black Sea',          --      Ocean
  0.4364,               --      Area in millions of square kilometers
  null,                 --      DeepestName
  2200                  --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Red Sea',            --      Ocean
  0.174,                --      Area in millions of square kilometers
  null,                 --      DeepestName
  500                   --      Deepest point in metres
);

INSERT INTO CIA_Ocean VALUES 
(
  'Persian Gulf',       --      Ocean
  0.233,                --      Area in millions of square kilometers
  null,                 --      DeepestName
  90                    --      Deepest point in metres
);