Coursework 1

-- Q1
CREATE TABLE monarch
       (name VARCHAR(35) NOT NULL,
        house VARCHAR(8),
        accession DATE NOT NULL,
        coronation DATE,
        CONSTRAINT monarch_pkey PRIMARY KEY (name),
        CONSTRAINT monarch_name_fkey FOREIGN KEY (name) REFERENCES person
        );


-- Q2

SELECT DISTINCT
        personA.name, personB.name, personA.born_in
        FROM person AS personA
        JOIN person AS personC ON personA.name = personC.father
        JOIN person AS personB     ON personB.name = personC.mother
                                AND personA.born_in = personB.born_in;


-- Q3
SELECT name
    FROM monarch
    WHERE house IS NOT NULL AND coronation IS NULL;

-- Q4
SELECT personA.name, personB.name AS father, personC.name AS mother
    FROM person AS personA
        JOIN person AS personB ON personA.dod < personB.dod AND personA.father = personB.name
        JOIN person AS personC ON personA.dod < personC.dod AND personA.mother = personC.name;

-- Q5

    SELECT name
    FROM prime_minister
UNION
    SELECT name
    FROM monarch
    WHERE monarch.house IS NOT NULL;

-- Q6
SELECT DISTINCT monarch1.name            -- Distinct because an abdicated monarch can outlive the accession of more than one future monarchs and will be returned twice
    FROM monarch AS monarch1 JOIN person ON monarch1.name = person.name
        JOIN monarch AS monarch2     ON monarch2.accession > monarch1.accession             -- Monarch2 is to come after monarch1
                                    AND monarch2.accession < person.dod -- And then monarch2 has an accession before monarch1 has died
                                    AND monarch1.house IS NOT NULL;


-- Q7
SELECT personA.name
    FROM person AS personA
    WHERE NOT EXISTS    (
                        SELECT personB.name
                            FROM person AS personB
                            WHERE    personB.father = personA.name
                                    OR personB.mother = personA.name
                        );

-- Q8
SELECT monarch_list.monarch_name, pm_list.pm_name
FROM
    (SELECT monarch1.name AS monarch_name, monarch1.accession AS reign_start,
        CASE WHEN
            monarch2.accession <> monarch1.accession
        THEN
            monarch2.accession
        ELSE
            NULL        -- if monarch is living, set as NULL for comparsion purpose for the outer query
                        -- if we can use CURRENT_DATE (which is standard SQL unsupported by MS SQL), will only create four cases for the outer query
        END AS reign_end
    FROM monarch AS monarch1 JOIN monarch AS monarch2
        ON monarch2.accession = (SELECT MIN(accession) FROM monarch WHERE monarch.accession > monarch1.accession)
        -- the OR clause will cause monarch2 to match monarch1 in case monarch1 is still reigning. Then we will set reign_end accordingly with the CASE clause in SELECT
        OR ( monarch2.accession > ALL (SELECT accession FROM monarch WHERE name <> monarch2.name) AND (SELECT MIN(accession) FROM monarch WHERE monarch.accession > monarch1.accession) IS NULL)
    WHERE monarch1.house IS NOT NULL)
    AS monarch_list
JOIN
    (SELECT pm1.name AS pm_name, pm1.entry AS term_start,
        CASE WHEN
            pm2.entry <> pm1.entry
        THEN
            pm2.entry
        ELSE
            NULL        -- if the PM is still in office, set as NULL for outer query purposes
                        -- if we can use CURRENT_DATE (which is standard SQL unsupported by MS SQL), will only create four cases for the outer query
        END AS term_end
    FROM prime_minister AS pm1 JOIN prime_minister AS pm2
        ON pm2.entry = (SELECT MIN(entry) FROM prime_minister WHERE prime_minister.entry > pm1.entry)
        -- the OR clause will cause pm2 to match pm1 in case pm1 is still in office. Then we will set term_end accordingly with the CASE clause in SELECT
        OR ( pm2.entry > ALL (SELECT entry FROM prime_minister WHERE name <> pm2.name AND entry <> pm2.entry) AND (SELECT MIN(entry) FROM prime_minister WHERE prime_minister.entry > pm1.entry) IS NULL)
    ) AS pm_list
    -- Five mutually exclusive cases --
    ON (pm_list.term_start BETWEEN monarch_list.reign_start AND monarch_list.reign_end) -- PM term began within monarch reign and ended either during or after reign
    OR (pm_list.term_start < monarch_list.reign_start
        AND pm_list.term_end BETWEEN monarch_list.reign_start AND monarch_list.reign_end)    -- PM term began before monarch reign but continues during the monarch reign
    OR (pm_list.term_start < monarch_list.reign_start
        AND pm_list.term_end > monarch_list.reign_end)    -- PM term is more than monarch reign i.e. Edward VIII
    OR (pm_list.term_start >= monarch_list.reign_start
        AND monarch_list.reign_end IS NULL)               -- monarch is currently reigning and PM term begins during monarch reign and has either ended or still in office
    OR (pm_list.term_start < monarch_list.reign_start
        AND monarch_list.reign_end IS NULL
        AND (pm_list.term_end > monarch_list.reign_start
                OR pm_list.term_end IS NULL))             -- PM's term began before currently reigning monarch and has either ended or still in office

ORDER BY monarch_list.reign_start, pm_list.term_start; -- Human readability