Oracle SQL Quick Start

------- Data Definition Language -------

-- Drop the COUNTRY table if it already exists.
DROP TABLE country;

-- Create the COUNTRY table.
CREATE TABLE country (
  ctry_id         NUMBER,
  ctry_name       VARCHAR2(50),
  ctry_area       NUMBER,
  ctry_pop        NUMBER,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)
);

-- Describe the table.
DESC country;

------- Data Manipulation Language -------

-- Populate the COUNTRY table.
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(14, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(48, 'Ghana', 92098, 23837000, null, 'Cedi');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(53, 'Australia', 2966200, 21884000, '4-sep-09', 'Australian Dollar');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(73, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(122, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(123, 'New Zealand', 104454, 4320300, '4-sep-09', 'New Zealand Dollar');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(147, 'Gambia', 4361, 1705000, null, 'Dalasi');
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(149, 'Gabon', 103347, 1475000, null, 'CFA franc');
COMMIT;

------- SELECT -------

-- Get all columns for all rows.
-- Details for all the countries.
SELECT *
FROM country;

-- Projection. Select 2 columns.
-- The name and size of all counties.
SELECT ctry_name, ctry_area
FROM country;

-- Selection. Return one row only.
-- All details for the country with an id of 53.
SELECT *
FROM country
WHERE ctry_id = 53;

-- Multiple conditions using AND.
-- Details for large countries which use the Euro.
SELECT ctry_name, ctry_area, ctry_pop, currency
FROM country
WHERE ctry_area >= 100000
  AND currency = 'Euro';

-- Using parentheses to change operator precedence.
-- The same as above but for large countries or countries
-- with large populations which use the Euro.
SELECT ctry_name, ctry_area, ctry_pop, currency
FROM country
WHERE (ctry_area >= 100000
  OR ctry_pop >= 10000000)
  AND currency = 'Euro';

-- Pattern matching with LIKE.
-- Currencies which contain the word 'Dollar'.
SELECT currency
FROM country
WHERE currency LIKE '%Dollar%';

-- Pattern matching with REGEXP_LIKE.
-- Currencies which contain the word 'Dollar' or 'dollar'.
SELECT currency
FROM country
WHERE REGEXP_LIKE (currency, '.*[Dd]ollar.*');

-- Range condition with BETWEEN.
-- Countries with a population between half a million and ten million (inclusive).
SELECT ctry_name, ctry_pop
FROM country
WHERE ctry_pop BETWEEN 500000 AND 10000000;

-- Range condition with BETWEEN.
-- Countries where the population was updated between 20th Nov 2008 and 1st Feb 2009
SELECT ctry_name, ctry_pop, pop_upd_on
FROM country
WHERE pop_upd_on BETWEEN '30-nov-08' AND '1-feb-09';

-- Membership condition using IN.
-- The area for Ghana, Australia and Greece.
SELECT ctry_name, ctry_area
FROM country
WHERE ctry_name IN ('Ghana', 'Australia', 'Greece');

-- Negated membership condition with NOT.
-- The area for countries other than Ghana, Australia and Greece.
SELECT ctry_name, ctry_area
FROM country
WHERE ctry_name NOT IN ('Ghana', 'Australia', 'Greece');

-- Multi-value membership condition.
-- Countries with a currency and a population updated date
-- which match values in a list.
SELECT ctry_name, pop_upd_on, currency
FROM country
WHERE (currency, pop_upd_on) IN (
        ('Euro', '30-NOV-08'),
        ('Lari', '01-JAN-09') );

-- Null comparison.
-- Countries with no population updated date.
SELECT ctry_id, pop_upd_on
FROM country
WHERE pop_upd_on IS NULL;

-- Non-Null comparison.
-- Countries with a population updated date.
SELECT ctry_id, pop_upd_on
FROM country
WHERE pop_upd_on IS NOT NULL;

-- Ordering the result set with ORDER BY.
-- Sizes of countries ordered by largest first.
SELECT ctry_name, ctry_area
FROM country
ORDER BY ctry_area DESC;

-- Ordering the result set with ORDER BY.
-- Countries ordered by descending name within ascending currency.
SELECT currency, ctry_name
FROM country
ORDER BY currency, ctry_name DESC;

-- Oracle scalar functions.
-- Various details for countries which sound like grease, jorjia and ganah.
SELECT
  ctry_name || ' has an area of ' || ctry_area || ' sq miles' AS "Country",
  to_char(ctry_pop, '999,999,999') AS "Population",
  nvl(to_char(pop_upd_on, 'ddspth Monthyyyy'), 'Unknown') AS "Updated On",
  trunc(ctry_pop / ctry_area, 2) AS "Density"
FROM country
WHERE soundex(ctry_name) IN (
        soundex('grease'),
        soundex('jorjia'),
        soundex('ganah'));


------- Data Manipulation Language -------

-- Insert a row.
-- Insert a country row for 'pain'.
INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(28, 'pain', 195364, 950, '1-jul-2009', 'Euro');

-- Commit the transaction.
COMMIT;

-- Update a row.
-- Update the country name and population for Spain.
UPDATE country
SET ctry_name='Spain', ctry_pop=46661950
WHERE ctry_id = 28;

COMMIT;

-- Update several rows.
-- Increase the population by 3% for Spain, Germany and Greece.
UPDATE country
SET ctry_pop = trunc(ctry_pop * 1.03),
    pop_upd_on = '1-jun-09'
WHERE ctry_id IN (14, 28, 73);

COMMIT;

-- Delete several rows.
-- Remove any small countries.
DELETE FROM country
WHERE ctry_area < 100000;

COMMIT;

-- Trash the data.
-- Delete all the countries.
DELETE FROM country;

-- Rollback the transaction.
ROLLBACK;


------- Data Definition Language -------

-- Rename a table.
ALTER TABLE country RENAME TO ctry;

-- Remove a column.
ALTER TABLE ctry DROP COLUMN currency;

-- Add a column.
ALTER TABLE ctry ADD gdp NUMBER;

-- Rename a table.
ALTER TABLE ctry RENAME TO country;

-- Remove the table.
DROP TABLE country;