Quick Start contains a summary of all the SQL from this section.
Cut and paste this script into a command line client such as SQL Developer or TOAD
to experiment with the SQL syntax.
-- Drop the tables if they already exist
DROP TABLE visit;
DROP TABLE head_of_state;
DROP TABLE city;
DROP TABLE country;
DROP TABLE continent;
-- Create and populate the CONTINENT table
CREATE TABLE continent (
cont_id NUMBER,
cont_name VARCHAR2(20)
);
INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Africa');
INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Asia');
INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North America');
INSERT INTO continent (cont_id, cont_name) VALUES (5, 'South America');
INSERT INTO continent (cont_id, cont_name) VALUES (6, 'Oceania');
INSERT INTO continent (cont_id, cont_name) VALUES (7, 'Antarctica');
COMMIT;
-- Create and populate the COUNTRY table
CREATE TABLE country (
ctry_id NUMBER,
cont_id NUMBER,
ctry_name VARCHAR2(50),
ctry_area NUMBER,
ctry_pop NUMBER,
pop_upd_on DATE,
currency VARCHAR2(50)
);
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(48, 1, 'Ghana', 92098, 23837000, null, 'Cedi');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(53, 6, 'Australia', 2966200, 21884000, '4-sep-09', 'Australian Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(122, 3, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(123, 6, 'New Zealand', 104454, 4320300, '4-sep-09', 'New Zealand Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(147, 1, 'Gambia', 4361, 1705000, null, 'Dalasi');
INSERT INTO country (ctry_id, cont_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
VALUES(149, 1, 'Gabon', 103347, 1475000, null, 'CFA franc');
COMMIT;
-- Create and populate the CITY table
CREATE TABLE city (
city_id NUMBER,
ctry_id NUMBER,
city_name VARCHAR2(50),
city_area NUMBER,
city_pop NUMBER
);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (1, 53, 'Sydney', 4689, 4399722);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (2, 53, 'Melbourne', 3400, 3892419);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (3, 53, 'Brisbane', 2279, 1945639);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (4, 53, 'Perth', 2079, 1602559);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (5, 53, 'Adelaide', 705, 1172105);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (6, 123, 'Auckland', 419, 1313200);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (7, 123, 'Christchurch', 550, 368900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (8, 123, 'Wellington', 171, 381900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (9, 123, 'Hamilton', 98, 138500);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (10, 123, 'Tauranga', 64, 110500);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (11, 73, 'Athens', 15, 745514);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (12, 73, 'Thessaloniki', 7 , 763468);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (13, 73, 'Piraeus', 4, 175697);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (14, 73, 'Patras', 48, 171616);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (15, 73, 'Heraklion', 42, 137711);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (16, 14, 'Berlin', 344, 3396300);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (17, 14, 'Hamburg', 291, 1731200);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (18, 14, 'Munich', 119, 1241100);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (19, 14, 'Cologne', 156, 969500);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (20, 14, 'Frankfurt', 95, 646000);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (21, 122, 'Tbilisi', 280, 1382900);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (22, 122, 'Kutaisi', 27, 267300);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (23, 122, 'Batumi', null, 144600);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (24, 122, 'Rustavi', 23, 121786);
INSERT INTO city (city_id, ctry_id, city_name, city_area, city_pop)
VALUES (25, 122, 'Zugdidi', null, 104900);
COMMIT;
-- Create and populate the HEAD_OF_STATE table
CREATE TABLE head_of_state (
hos_id NUMBER,
ctry_id NUMBER,
hos_name VARCHAR2(50)
);
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (1, 122, 'President Mikheil Saakashvili');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (2, 14, 'President Horst Köhler');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (3, 73, 'President Karolos Papoulias');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (4, 48, 'President John Atta Mills');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (5, 147, 'President Yahya Jammeh');
INSERT INTO head_of_state (hos_id, ctry_id, hos_name)
VALUES (6, 149, 'President-elect Ali Bongo Ondimba');
COMMIT;
-- Create and populate the VISIT table
CREATE TABLE visit (
ctry_id NUMBER,
hos_id NUMBER
);
INSERT INTO visit (ctry_id, hos_id) VALUES (14, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (48, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (149, 3);
INSERT INTO visit (ctry_id, hos_id) VALUES (73, 2);
INSERT INTO visit (ctry_id, hos_id) VALUES (73, 1);
COMMIT;
------- Inner Join -------
-- Implicit inner join returning a single row.
-- The continent that Greece is in.
SELECT cont_name
FROM country
JOIN continent USING (cont_id)
WHERE ctry_name = 'Greece';
-- Implicit inner join returning multiple rows.
-- Countries in Europe.
SELECT ctry_name
FROM country
JOIN continent USING (cont_id)
WHERE cont_name = 'Europe';
-- Multiple inner joins.
-- Cities in Oceania.
SELECT city_name, ctry_name
FROM continent
JOIN country USING (cont_id)
JOIN city USING (ctry_id)
WHERE cont_name = 'Oceania'
ORDER BY city_name;
-- Explicit inner join.
-- Continents and their countries.
SELECT cont_name, ctry_name
FROM continent
INNER JOIN country USING (cont_id);
-- Join using ON.
-- Cities which are larger than countries.
SELECT city_name, city_area, ctry_name, ctry_area
FROM country
JOIN city ON city_area > ctry_area;
-- Multiple join conditions using ON.
-- Cities with a population greater than 10% of the entire population.
SELECT city_name, city_pop, ctry_name, ctry_pop
FROM country
JOIN city ON city_pop > ctry_pop * 0.1
AND country.ctry_id = city.ctry_id;
-- Complex join.
-- Cities in countries that do not use the Euro as their currency,
-- that have a population greater than one percent of the country total
-- and have less than or the same number of letters in the name
-- as the continent they are in.
SELECT city_name, ctry_name, cont_name
FROM continent
JOIN country ctry USING (cont_id)
JOIN city ON city_pop > ctry_pop * .01
AND ctry.ctry_id = city.ctry_id
AND length(city_name) <= length(cont_name)
WHERE currency != 'Euro';
------- Outer Join -------
-- Left outer join.
-- All continents and their countries.
SELECT cont_name, ctry_name
FROM continent
LEFT OUTER JOIN country USING (cont_id);
-- Multiple left outer joins.
-- All countries and their heads of state.
SELECT ctry_name, hos_name
FROM country
LEFT OUTER JOIN visit USING (ctry_id)
LEFT OUTER JOIN head_of_state USING (hos_id);
-- Multiple right outer joins.
-- All heads of state and their countries.
SELECT ctry_name, hos_name
FROM country
RIGHT OUTER JOIN visit USING (ctry_id)
RIGHT OUTER JOIN head_of_state USING (hos_id);
-- Full outer join
-- All countries and all heads of state.
SELECT ctry_name, hos_name
FROM country
FULL OUTER JOIN visit USING (ctry_id)
FULL OUTER JOIN head_of_state USING (hos_id);
-- Left outer join with extra selection criteria.
-- Countries and their heads of state excluding President Köhler
SELECT ctry_name, hos_name
FROM country
LEFT OUTER JOIN visit USING (ctry_id)
LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler';
-- Left outer join with extra selection criteria.
-- All countries and their heads of state excluding President Köhler.
SELECT ctry_name, hos_name
FROM country
LEFT OUTER JOIN visit USING (ctry_id)
LEFT OUTER JOIN head_of_state USING (hos_id)
WHERE hos_name != 'President Horst Köhler'
OR hos_name IS NULL;
------- Other Join Types -------
-- Self join.
-- Countries larger than or equal to the size of New Zealand.
SELECT ctry.ctry_name, ctry.ctry_area, nz.ctry_area AS nz_area
FROM country nz
JOIN country ctry ON ctry.ctry_area >= nz.ctry_area
AND ctry.ctry_id != nz.ctry_id
WHERE nz.ctry_name = 'New Zealand';
-- Cross join.
-- All heads of state and the continents they may potentially visit.
SELECT hos_name, cont_name, ' ' AS "VISITING (PLEASE TICK)"
FROM continent
CROSS JOIN head_of_state
ORDER BY hos_name, cont_name;
-- Theta-stye join.
-- Countries and their continents.
SELECT ctry_name, cont_name
FROM continent cont, country ctry
WHERE cont.cont_id = ctry.cont_id;
-- Theta-style join.
-- Cities larger than 10,000 sq miles in Oceania.
SELECT ctry_name, city_name
FROM continent cont, country ctry, city
WHERE cont.cont_id = ctry.cont_id
AND ctry.ctry_id = city.ctry_id
AND ctry_area > 10000
AND cont_name = 'Oceania';
-- Same query as above using ANSI join syntax.
-- Cities larger than 10,000 sq miles in Oceania.
SELECT ctry_name, city_name
FROM continent
JOIN country USING (cont_id)
JOIN city USING (ctry_id)
WHERE ctry_area > 10000
AND cont_name = 'Oceania';
-- Natural join.
-- City areas in Greece.
SELECT city_name, city_area
FROM country
NATURAL JOIN city
WHERE ctry_name='Greece';
|
