SELECT NAME, LANGUAGE
FROM PERSONS, COUNTRIES
WHERE PERSONS.COUNTRY = COUNTRIES.COUNTRY
  AND JOB = 'S'
  AND BDATE LIKE '19%';

SELECT COUNTRIES.COUNTRY, GNP, BUDGET
FROM COUNTRIES, ARMIES
WHERE COUNTRIES.COUNTRY = ARMIES.COUNTRY
  AND POP > 100000000;

SELECT COUNTRIES.COUNTRY, GNP, BUDGET,
       BUDGET / GNP * 100
FROM COUNTRIES, ARMIES
WHERE COUNTRIES.COUNTRY = ARMIES.COUNTRY
  AND POP > 100000000;

SELECT TROOPS * PERCENT / 100
FROM ARMIES, RELIGIONS
WHERE ARMIES.COUNTRY = RELIGIONS.COUNTRY
  AND ARMIES.COUNTRY = 'GERMANY'
  AND RELIGION = 'PROTESTANT';

SELECT C.COUNTRY, POP, TROOPS
FROM COUNTRIES AS C, ARMIES AS A
WHERE C.COUNTRY = A.COUNTRY
  AND POP > 100000000;

SELECT DISTINCT LANGUAGE
FROM COUNTRIES AS C, RELIGIONS AS R
WHERE C.COUNTRY = R.COUNTRY
  AND RELIGION = 'MUSLIM'
  AND PERCENT > 90;

SELECT NAME, TITLE, LANGUAGE
FROM PERSONS AS P, JOBS AS J, COUNTRIES AS C
WHERE P.JOB = J.JOB
  AND P.COUNTRY = C.COUNTRY
  AND BDATE < '1400-01-01';

SELECT COUNTRY
FROM RELIGIONS
WHERE RELIGION = 'PROTESTANT'
  AND PERCENT > 40
UNION
SELECT COUNTRY
FROM COUNTRIES
WHERE LANGUAGE = 'GERMAN';
-- 10 rows

SELECT COUNTRY
FROM PERSONS
WHERE JOB = 'S'
UNION
SELECT COUNTRY
FROM ARMIES
WHERE BUDGET > 10000
ORDER BY 1;

SELECT NAME
FROM PERSONS
WHERE NAME LIKE '_Z%'
UNION
SELECT COUNTRY
FROM COUNTRIES
WHERE COUNTRY LIKE '_Z%'
UNION
SELECT LANGUAGE
FROM COUNTRIES
WHERE LANGUAGE LIKE '_Z%';
-- 9 rows

SELECT MAX(POP)
FROM COUNTRIES;

SELECT *
FROM COUNTRIES
WHERE POP = (SELECT MAX(POP) FROM COUNTRIES);
-- China wins.

SELECT *
FROM ARMIES
WHERE BUDGET > (SELECT AVG(BUDGET) FROM ARMIES);

SELECT LANGUAGE, NAME, TITLE, BDATE
FROM PERSONS AS P, JOBS AS J, COUNTRIES AS C
WHERE P.JOB = J.JOB
  AND P.COUNTRY = C.COUNTRY
  AND P.JOB = (SELECT JOB
               FROM PERSONS
               WHERE NAME = 'LUTHER')
ORDER BY LANGUAGE, NAME;

SELECT COUNTRY
FROM COUNTRIES
WHERE LITERACY < 50;

SELECT *
FROM PERSONS
WHERE JOB = 'W'
  AND COUNTRY IN
      (SELECT COUNTRY
       FROM COUNTRIES
       WHERE LITERACY < 50);

SELECT *
FROM PERSONS
WHERE COUNTRY IN
      (SELECT COUNTRY
       FROM RELIGIONS
       WHERE RELIGION = 'CATHOLIC'
         AND PERCENT > 95);

SELECT *
FROM PERSONS
WHERE JOB = 'M'
  AND COUNTRY IN
      (SELECT COUNTRY
       FROM COUNTRIES
       WHERE LANGUAGE = 'ENGLISH')
  AND COUNTRY IN
      (SELECT COUNTRY
       FROM ARMIES
       WHERE TROOPS < (SELECT AVG(TROOPS) FROM ARMIES));

SELECT JOB, NAME
FROM PERSONS AS P
WHERE BDATE =
      (SELECT MAX(BDATE)
       FROM PERSONS
       WHERE JOB = P.JOB);

SELECT *
FROM PERSONS AS P
WHERE BDATE =
      (SELECT MAX(BDATE)
       FROM PERSONS
       WHERE GENDER = P.GENDER);

SELECT COUNTRY, RELIGION
FROM RELIGIONS AS R
WHERE PERCENT =
      (SELECT MAX(PERCENT)
       FROM RELIGIONS
       WHERE COUNTRY = R.COUNTRY)
  AND COUNTRY IN
      (SELECT COUNTRY
       FROM COUNTRIES
       WHERE LANGUAGE = 'GERMAN');

SELECT * FROM JOBS;

INSERT
INTO JOBS (JOB, TITLE)
VALUES ('A', 'Author');

SELECT *
FROM PERSONS
WHERE PERSON >= 500;

INSERT
INTO PERSONS (PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
VALUES (500, 'name', 'bdate', 'gen', 'country', 'job');

INSERT
INTO PERSONS (PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
VALUES (501, 'name', 'bdate', 'gen', 'country', 'job');

UPDATE PERSONS
SET JOB = 'A'
WHERE JOB = 'W';
SELECT * FROM PERSONS ORDER BY JOB;

UPDATE PERSONS
SET BDATE = 'bdate', JOB = 'E'
WHERE PERSON = 500;
SELECT * FROM PERSONS WHERE PERSON = 500;

UPDATE PERSONS
SET JOB = 'W'
WHERE JOB = 'A';
SELECT * FROM PERSONS ORDER BY JOB;

DELETE FROM PERSONS WHERE PERSON = 501;
DELETE FROM PERSONS WHERE PERSON = 500;
SELECT * FROM PERSONS WHERE PERSON >= 500;

SELECT COUNT(*)
FROM PERSONS
WHERE BDATE LIKE '17%';
DELETE FROM PERSONS WHERE BDATE LIKE '17%';
SELECT COUNT(*)
FROM PERSONS
WHERE BDATE LIKE '17%';

SELECT COUNT(*) FROM RELIGIONS;
DELETE FROM RELIGIONS
WHERE RELIGION LIKE '%ORTHODOX%';
SELECT COUNT(*) FROM RELIGIONS;

ROLLBACK;
SELECT * FROM JOBS;
SELECT COUNT(*) FROM RELIGIONS;

DELETE FROM PERSONS;
DELETE FROM ARMIES;
SELECT * FROM PERSONS;
SELECT * FROM ARMIES;
ROLLBACK;
SELECT * FROM PERSONS;
SELECT * FROM ARMIES;

INSERT
INTO PERSONS (PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
VALUES (500, 'name', 'bdate', 'gen', 'country', 'job');
COMMIT;
ROLLBACK;
SELECT * FROM PERSONS WHERE PERSON = 500;
DELETE FROM PERSONS WHERE PERSON = 500;
COMMIT;

CREATE TABLE SCIENTISTS
(
    NAME CHAR(20),
    BDATE DATE,
    GENDER CHAR(6),
    COUNTRY CHAR(20)
);

INSERT
INTO SCIENTISTS (NAME, BDATE, GENDER, COUNTRY)
VALUES ('name', 'bdate', 'gen', 'country');

SELECT * FROM SCIENTISTS;
DROP TABLE SCIENTISTS;

CREATE TABLE SCIENTISTS
(
    NAME CHAR(20),
    BDATE DATE,
    GENDER CHAR(6),
    COUNTRY CHAR(20)
);

INSERT
INTO SCIENTISTS (NAME, BDATE, GENDER, COUNTRY)
(SELECT NAME, BDATE, GENDER, COUNTRY
 FROM PERSONS
 WHERE JOB = 'S' AND GENDER = 'FEMALE');
-- 1 female scientist

INSERT
INTO SCIENTISTS (NAME, BDATE, GENDER, COUNTRY)
(SELECT NAME, BDATE, GENDER, COUNTRY
 FROM PERSONS
 WHERE JOB = 'S' AND GENDER = 'MALE');
SELECT COUNT(*) FROM SCIENTISTS;
-- 29 scientists

INSERT
INTO SCIENTISTS (NAME, BDATE, GENDER, COUNTRY)
(SELECT NAME, BDATE, GENDER, COUNTRY
 FROM PERSONS
 WHERE JOB = 'T');
SELECT COUNT(*) FROM SCIENTISTS;
-- 42 rows total

DROP TABLE SCIENTISTS;

CREATE TABLE THEOLOGIANS
(
    NAME CHAR(20),
    BDATE DATE,
    GENDER CHAR(6) NOT NULL DEFAULT 'male',
    COUNTRY CHAR(20),
    CHECK (GENDER IN ('male', 'female')),
    PRIMARY KEY (NAME),
    FOREIGN KEY (COUNTRY)
        REFERENCES COUNTRIES (COUNTRY)
);

INSERT
INTO THEOLOGIANS (NAME, BDATE, GENDER, COUNTRY)
(SELECT NAME, BDATE, GENDER, COUNTRY
 FROM PERSONS
 WHERE JOB = 'T');

INSERT INTO THEOLOGIANS (NAME, BDATE, GENDER, COUNTRY)
VALUES ('name', 'bdate', 'gen', 'Prussia');

INSERT INTO THEOLOGIANS (NAME, BDATE, GENDER, COUNTRY)
VALUES ('name', 'bdate', NULL, 'country');

DROP TABLE THEOLOGIANS;

CREATE TABLE REGIONS (NAME CHAR(10));
INSERT INTO REGIONS(NAME) VALUES ('north');
INSERT INTO REGIONS(NAME) VALUES ('south');
INSERT INTO REGIONS(NAME) VALUES ('east');
INSERT INTO REGIONS(NAME) VALUES ('west');

INSERT INTO REGIONS(NAME)
(SELECT NAME FROM REGIONS);
-- Execute the preceding self-insert about 14 times.

SELECT NAME, COUNT(*)
FROM REGIONS
GROUP BY NAME;

CREATE INDEX RX ON REGIONS(NAME);

DROP INDEX RX;
DROP TABLE REGIONS;

CREATE VIEW IV
(PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
AS
SELECT *
FROM PERSONS
WHERE COUNTRY = 'ISRAEL';

SELECT * FROM IV;

INSERT
INTO IV (PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
VALUES (502, 'name', 'bdate', 'gen', 'Israel', 'job');

INSERT
INTO IV (PERSON, NAME, BDATE, GENDER, COUNTRY, JOB)
VALUES (503, 'name', 'bdate', 'gen', 'USA', 'job');

CREATE VIEW IJV (NAME, BDATE, COUNTRY, TITLE)
AS
SELECT NAME, BDATE, COUNTRY, TITLE
FROM IV, JOBS
WHERE IV.JOB = JOBS.JOB;

DROP VIEW IJV;
DROP VIEW IV;