-- Exercise 2
SELECT * FROM PERSONS;

-- Exercise 3
SELECT * FROM COUNTRIES;

-- Exercise 4
SELECT * FROM ERRORS;
-- This query generates a syntax error in the exercise context.

-- Exercise 5
SELECT * FROM ARMIES;

-- Exercise 6
SELECT * FROM SYSCOLUMNS;

-- Exercise 7
SELECT *
FROM how-would-we-know-what-you-picked;

-- Exercise 1
SELECT JOB
FROM JOBS;

-- Exercise 2
SELECT NAME, BDATE
FROM PERSONS;

-- Exercise 3
SELECT PERCENT, RELIGION, COUNTRY
FROM RELIGIONS;

-- Exercise 4, first form
SELECT COUNTRY, POP, AREA, GNP, LANGUAGE, LITERACY, COUNTRY
FROM COUNTRIES;

-- Exercise 4, with asterisk
SELECT *, COUNTRY
FROM COUNTRIES;

SELECT *
FROM COUNTRIES;

SELECT *
FROM COUNTRIES
WHERE AREA < 30;

SELECT NAME, COUNTRY
FROM PERSONS;

SELECT NAME, COUNTRY
FROM PERSONS
WHERE COUNTRY = 'CANADA';

SELECT NAME, BDATE
FROM PERSONS
WHERE BDATE > '1964-01-01';

SELECT BDATE
FROM PERSONS
WHERE NAME = 'O''TOOLE';

SELECT *
FROM PERSONS
WHERE COUNTRY = 'IRELAND';

SELECT *
FROM PERSONS
WHERE COUNTRY = 'IRELAND'
ORDER BY NAME;

SELECT *
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN';

SELECT *
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN'
ORDER BY GNP DESC;

SELECT COUNTRY, JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
ORDER BY JOB, NAME;

SELECT * FROM ARMIES ORDER BY BUDGET DESC;
SELECT * FROM ARMIES ORDER BY BUDGET;

SELECT JOB FROM PERSONS;
SELECT DISTINCT JOB FROM PERSONS;

SELECT LANGUAGE
FROM COUNTRIES
WHERE LITERACY < 30
ORDER BY LANGUAGE;

SELECT DISTINCT LANGUAGE
FROM COUNTRIES
WHERE LITERACY < 30
ORDER BY LANGUAGE;

SELECT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
ORDER BY COUNTRY;

SELECT DISTINCT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
ORDER BY COUNTRY;

SELECT DISTINCT RELIGION
FROM RELIGIONS
WHERE PERCENT < 5
ORDER BY RELIGION;

SELECT COUNTRY
FROM COUNTRIES
WHERE COUNTRY LIKE '%GUINEA%';

SELECT *
FROM PERSONS
WHERE NAME LIKE '_Z%';

SELECT *
FROM PERSONS
WHERE BDATE LIKE '%07-15';

SELECT *
FROM PERSONS
WHERE NAME LIKE '%''%';

SELECT DISTINCT RELIGION
FROM RELIGIONS
WHERE RELIGION LIKE '%ORTHODOX%'
ORDER BY RELIGION;

SELECT *
FROM PERSONS
WHERE JOB = 'S'
  AND COUNTRY = 'GERMANY';

SELECT *
FROM COUNTRIES
WHERE GNP < 3000
  AND LITERACY < 40
ORDER BY GNP;

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY >= 55
  AND LITERACY <= 60;

SELECT *
FROM PERSONS
WHERE COUNTRY = 'ENGLAND'
  AND BDATE LIKE '15%'
ORDER BY NAME;

SELECT *
FROM ARMIES
WHERE TROOPS > 100000
  AND TANKS < 1000
  AND SHIPS < 1000
  AND PLANES < 1000;

SELECT *
FROM COUNTRIES
WHERE AREA BETWEEN 5 AND 75;

SELECT *
FROM COUNTRIES
WHERE POP BETWEEN 100000 AND 200000;

SELECT *
FROM ARMIES
WHERE PLANES BETWEEN 301 AND 399;

SELECT *
FROM ARMIES
WHERE TROOPS >= 300000
  AND BUDGET BETWEEN 10000 AND 100000;

SELECT *
FROM ARMIES
WHERE COUNTRY = 'ISRAEL'
   OR COUNTRY = 'IRAQ';

SELECT *
FROM PERSONS
WHERE BDATE = '1835-10-19'
   OR BDATE = '1917-02-06';

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'POE'
   OR NAME = 'HUGO'
   OR NAME = 'DAHL';

SELECT *
FROM PERSONS
WHERE BDATE LIKE '12%'
   OR BDATE LIKE '14%';

SELECT *
FROM PERSONS
WHERE NAME IN ('EINSTEIN', 'GALILEI', 'NEWTON');

SELECT *
FROM COUNTRIES
WHERE LITERACY IN (20, 40, 60);

SELECT NAME, COUNTRY
FROM PERSONS
WHERE JOB = 'S'
  AND COUNTRY IN ('GERMANY', 'AUSTRIA', 'ITALY');

SELECT *
FROM PERSONS
WHERE JOB IN ('W', 'E', 'B')
  AND COUNTRY IN ('GERMANY', 'AUSTRIA', 'ITALY');

SELECT *
FROM COUNTRIES
WHERE GNP IS NULL;

SELECT *
FROM PERSONS
WHERE JOB = 'E'
  AND GENDER IS NULL;

SELECT *
FROM PERSONS
WHERE COUNTRY = 'ISRAEL'
  AND BDATE IS NULL;

SELECT * FROM COUNTRIES WHERE AREA < 10 AND GNP > 250;
SELECT * FROM COUNTRIES WHERE AREA < 10 AND GNP <= 250;
SELECT * FROM COUNTRIES WHERE AREA < 10;

SELECT *
FROM PERSONS
WHERE COUNTRY = 'GERMANY'
  AND (JOB = 'B' OR JOB = 'T')
ORDER BY NAME;

SELECT *
FROM PERSONS
WHERE COUNTRY = 'GERMANY'
  AND NOT (JOB = 'B' OR JOB = 'T')
ORDER BY NAME;

SELECT *
FROM ARMIES
WHERE NOT COUNTRY IN ('USA', 'RUSSIA')
  AND BUDGET > 30000;

SELECT POP * .2
FROM COUNTRIES
WHERE COUNTRY = 'CANADA';

SELECT BUDGET * 1000000 / TROOPS
FROM ARMIES
WHERE COUNTRY = 'USA';

SELECT BUDGET * 1000000 / TROOPS
FROM ARMIES
WHERE COUNTRY = 'CHINA';

SELECT TANKS + SHIPS + PLANES
FROM ARMIES
WHERE COUNTRY = 'USA';

SELECT BUDGET * 1000000 * .45 / 125
FROM ARMIES
WHERE COUNTRY = 'USA';

SELECT COUNTRY, POP, AREA, POP / AREA
FROM COUNTRIES
WHERE POP / AREA < 7
ORDER BY POP / AREA DESC;

SELECT COUNTRY, POP, LITERACY, LITERACY / 100 * POP
FROM COUNTRIES
WHERE LITERACY / 100 * POP > 100000000;

SELECT COUNTRY,
       BUDGET * 1000000 * .3 / 650 / TROOPS
FROM ARMIES
WHERE BUDGET * 1000000 * .3 / 650 / TROOPS > 10
ORDER BY BUDGET * 1000000 * .3 / 650 / TROOPS DESC;

SELECT COUNTRY, POP, LITERACY,
       LITERACY / 100 * POP AS READERS
FROM COUNTRIES
WHERE READERS > 100000000;

SELECT COUNTRY, POP, GNP,
       GNP * 1000000 / POP AS GPP
FROM COUNTRIES
WHERE GPP > 20000
ORDER BY GPP DESC;

SELECT COUNTRY,
       TROOPS * .20 * 20000 AS FISSION_FUND
FROM ARMIES
WHERE FISSION_FUND >= 2000000000
ORDER BY FISSION_FUND DESC;

SELECT SUM(TROOPS)
FROM ARMIES;

SELECT MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
WHERE LANGUAGE = 'FRENCH';

SELECT COUNT(COUNTRY), COUNT(DISTINCT LANGUAGE)
FROM COUNTRIES;

SELECT LANGUAGE, SUM(POP)
FROM COUNTRIES
WHERE LANGUAGE IN ('HEBREW', 'SPANISH', 'ENGLISH', 'FRENCH')
GROUP BY LANGUAGE;

SELECT LANGUAGE, MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
WHERE LANGUAGE IN ('HEBREW', 'SPANISH', 'ENGLISH', 'FRENCH')
GROUP BY LANGUAGE;

SELECT COUNTRY, GENDER, COUNT(PERSON)
FROM PERSONS
WHERE COUNTRY IN ('CANADA', 'FRANCE')
GROUP BY COUNTRY, GENDER
ORDER BY COUNTRY, GENDER;

SELECT JOB, GENDER, COUNT(PERSON)
FROM PERSONS
WHERE NOT GENDER IS NULL
GROUP BY JOB, GENDER
ORDER BY JOB, GENDER;

SELECT COUNTRY, COUNT(PERSON)
FROM PERSONS
WHERE JOB = 'W'
GROUP BY COUNTRY
ORDER BY COUNT(PERSON);

SELECT LANGUAGE, SUM(POP)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING SUM(POP) < 1000000;

SELECT LANGUAGE, MIN(LITERACY), MAX(LITERACY), AVG(LITERACY)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING MIN(LITERACY) <> MAX(LITERACY);

SELECT LANGUAGE, SUM(AREA), SUM(POP)
FROM COUNTRIES
GROUP BY LANGUAGE
HAVING COUNT(*) > 1
ORDER BY SUM(AREA) DESC;