Finished until 5

2 files changed, 144 insertions, 0 deletions

diff --git a/d b/d
new file mode 100644
index 0000000..dda3905
--- /dev/null
+++ b/d
@@ -0,0 +1,84 @@
+           name            | gender |    dob     |    dod     |        father        |        mother         |       born_in       
+---------------------------+--------+------------+------------+----------------------+-----------------------+---------------------
+ Charles I                 | M      | 1600-11-19 | 1649-01-30 | James I              |                       | Dumfermline
+ Charles II                | M      | 1630-06-08 | 1685-02-06 | Charles I            | Henrietta Maria       | London
+ James II                  | M      | 1633-10-24 | 1701-09-16 | Charles I            | Henrietta Maria       | London
+ Henrietta Maria           | F      | 1609-11-25 | 1669-09-10 |                      |                       | Paris
+ Mary (Princess Royal)     | F      | 1631-11-04 | 1660-12-24 | Charles I            | Henrietta Maria       | London
+ Anne Hyde                 | F      | 1638-03-22 | 1671-04-10 |                      |                       | Windsor
+ Mary II                   | F      | 1662-05-10 | 1695-01-07 | James II             | Anne Hyde             | London
+ William II of Orange      | M      | 1626-05-27 | 1650-11-06 |                      |                       | The Hague
+ William III               | M      | 1650-11-14 | 1702-03-08 | William II of Orange | Mary (Princess Royal) | London
+ Anne                      | F      | 1665-02-06 | 1714-08-01 | James II             | Anne Hyde             | London
+ Ernest Augustus           | M      | 1629-11-20 | 1698-01-23 |                      |                       | Herzberg am Harz
+ Palatine Sophia           | F      | 1630-10-14 | 1714-06-08 |                      | Elizabeth of Bohemia  | The Hague
+ Elizabeth of Bohemia      | F      | 1596-08-19 | 1662-02-13 | James I              |                       | Fife
+ James I                   | M      | 1566-06-19 | 1625-03-27 |                      |                       | Fife
+ George I                  | M      | 1660-06-07 | 1727-06-22 | Ernest Augustus      | Palatine Sophia       | Hanover
+ George II                 | M      | 1683-11-09 | 1760-10-25 | George I             |                       | Hanover
+ Frederick                 | M      | 1707-02-01 | 1751-03-20 | George II            |                       | Hanover
+ George III                | M      | 1738-08-21 | 1820-01-29 | Frederick            | Augusta of Saxe-Gotha | London
+ Augusta of Saxe-Gotha     | F      | 1719-11-30 | 1772-02-08 |                      |                       | Gotha
+ Charlotte                 | F      | 1744-05-19 | 1818-11-17 |                      |                       | Mecklenburg-Streliz
+ George IV                 | M      | 1765-08-21 | 1830-06-26 | George III           | Charlotte             | London
+ Frederick (Prince)        | M      | 1763-08-16 | 1827-01-05 | George III           | Charlotte             | London
+ William IV                | M      | 1765-08-21 | 1837-06-20 | George III           | Charlotte             | London
+ Victoria                  | F      | 1819-05-24 | 1901-01-22 | Edward (Prince)      |                       | London
+ Edward (Prince)           | M      | 1767-11-02 | 1820-01-23 | George III           | Charlotte             | London
+ Albert                    | M      | 1819-08-26 | 1861-12-14 |                      |                       | Coburg
+ Victoria (Princess Royal) | F      | 1840-11-21 | 1901-08-05 | Albert               | Victoria              | London
+ Edward VII                | M      | 1841-11-09 | 1910-05-06 | Albert               | Victoria              | London
+ Alexandra (Queen)         | F      | 1844-12-01 | 1925-11-20 |                      |                       | Copenhagen
+ Alice (Princess)          | F      | 1843-04-25 | 1878-12-14 | Albert               | Victoria              | London
+ Alfred                    | M      | 1844-08-06 | 1900-07-30 | Albert               | Victoria              | Windsor Castle
+ Albert Victor             | M      | 1864-01-08 | 1892-01-14 | Edward VII           | Alexandra (Queen)     | Frogmore
+ George V                  | M      | 1865-06-03 | 1936-01-20 | Edward VII           | Alexandra (Queen)     | London
+ Alice (Princess Royal)    | F      | 1867-02-20 | 1931-01-04 | Edward VII           | Alexandra (Queen)     | London
+ Mary of Teck              | F      | 1867-05-26 | 1953-03-24 |                      |                       | London
+ George VI                 | M      | 1895-12-14 | 1952-02-06 | George V             | Mary of Teck          | Sandringham
+ Edward VIII               | M      | 1894-06-23 | 1972-05-28 | George V             | Mary of Teck          | Sandringham
+ Elizabeth                 | F      | 1900-08-04 | 2002-03-30 |                      |                       | London
+ Elizabeth II              | F      | 1926-04-21 |            | George VI            | Elizabeth             | London
+ Margaret                  | F      | 1930-08-21 | 2002-02-09 | George VI            | Elizabeth             | Glamis Castle
+ George I of Greece        | M      | 1845-12-24 | 1913-03-18 |                      |                       | Copenhagen
+ Andrew of Greece          | M      | 1882-02-02 | 1944-12-03 | George I of Greece   |                       | Athens
+ Constantine I of Greece   | M      | 1868-08-02 | 1923-01-11 | George I of Greece   |                       | Athens
+ Alice                     | F      | 1885-02-25 | 1969-12-05 |                      |                       | Windsor
+ Philip                    | M      | 1921-06-10 |            | Andrew of Greece     | Alice                 | Corfu
+ Charles                   | M      | 1948-11-14 |            | Philip               | Elizabeth II          | London
+ Anne (Princess)           | F      | 1950-08-15 |            | Philip               | Elizabeth II          | London
+ Andrew                    | M      | 1960-02-19 |            | Philip               | Elizabeth II          | London
+ Edward                    | M      | 1964-03-10 |            | Philip               | Elizabeth II          | London
+ Frances                   | F      | 1936-01-20 | 2004-06-03 |                      |                       | Sandringham
+ Diana                     | F      | 1961-07-01 | 1997-08-31 |                      | Frances               | Sandringham
+ Henry                     | M      | 1984-09-15 |            | Charles              | Diana                 | London
+ William                   | M      | 1982-06-21 |            | Charles              | Diana                 | London
+ Oliver Cromwell           | M      | 1599-04-25 | 1658-09-03 |                      |                       | Huntingdon
+ Richard Cromwell          | M      | 1626-10-04 | 1659-05-25 | Oliver Cromwell      |                       | Huntingdon
+ David Cameron             | M      | 1966-10-09 |            |                      |                       | London
+ Gordon Brown              | M      | 1951-02-20 |            |                      |                       | Giffnock
+ Tony Blair                | M      | 1953-05-06 |            |                      |                       | Edinburgh
+ John Major                | M      | 1943-03-29 |            |                      |                       | Carshalton
+ Margaret Thatcher         | F      | 1925-10-13 |            |                      |                       | Grantham
+ James Callaghan           | M      | 1912-03-27 | 2005-03-26 |                      |                       | Portsmouth
+ Harold Wilson             | M      | 1916-03-11 | 1995-05-24 |                      |                       | Huddersfield
+ Edward Heath              | M      | 1916-07-09 | 2005-07-17 |                      |                       | Huddersfield
+ Alec Douglas-Home         | M      | 1903-07-02 | 1995-10-09 |                      |                       | Coldstream
+ Harold Macmillan          | M      | 1894-02-10 | 1986-12-29 |                      |                       | London
+ Anthony Eden              | M      | 1897-06-12 | 1977-01-14 |                      |                       | London
+ Winston Churchill         | M      | 1874-11-30 | 1965-01-24 |                      |                       | Woodstock
+ Clement Attlee            | M      | 1883-01-03 | 1967-10-08 |                      |                       | London
+ Neville Chamberlain       | M      | 1869-03-18 | 1940-11-09 |                      |                       | Birmingham
+ Stanley Baldwin           | M      | 1867-08-03 | 1947-12-14 |                      |                       | Bewdley
+ Ramsay MacDonald          | M      | 1866-10-12 | 1937-11-09 |                      |                       | Lossiemouth
+ Andrew Bonar Law          | M      | 1858-09-16 | 1923-10-30 |                      |                       | Rexton
+ David Lloyd George        | M      | 1863-01-17 | 1945-03-26 |                      |                       | Rexton
+ Herbert Henry Asquith     | M      | 1852-09-12 | 1928-02-15 |                      |                       | Morley
+ Henry Campbell-Bannerman  | M      | 1836-09-07 | 1908-04-22 |                      |                       | Glasgow
+ Arthur Balfour            | M      | 1848-07-25 | 1930-03-19 |                      |                       | Whittingehame
+ Lord Salisbury            | M      | 1830-02-03 | 1903-08-22 |                      |                       | Hatfield
+ William Ewart Gladstone   | M      | 1809-12-29 | 1898-05-19 |                      |                       | Liverpool
+ Benjamin Disraeli         | M      | 1804-12-21 | 1881-04-19 |                      |                       | London
+ Earl of Rosebery          | M      | 1847-05-07 | 1929-05-21 |                      |                       | London
+(80 rows)
+
diff --git a/db_2017_cw1.sql b/db_2017_cw1.sql
new file mode 100644
index 0000000..629a02c
--- /dev/null
+++ b/db_2017_cw1.sql
@@ -0,0 +1,60 @@
+-- Q1 returns (name,dod)
+SELECT person_b.name, person.dod
+FROM person JOIN person AS person_b
+ON person.name = person_b.mother
+AND person.dod IS NOT NULL
+;
+
+-- Q2 returns (name)
+SELECT name
+FROM person
+WHERE gender = 'M'
+EXCEPT
+SELECT father
+FROM person
+ORDER BY name
+;
+
+-- Q3 returns (name)
+SELECT mother AS name
+FROM person
+GROUP BY mother
+HAVING COUNT(DISTINCT gender) = (SELECT COUNT(DISTINCT gender)
+       		      	      	 FROM person)
+AND mother IS NOT NULL
+;
+
+-- Q4 returns (name,father,mother)
+SELECT person.name, person.father, person.mother
+FROM person
+WHERE person.dob <= ALL (SELECT person_b.dob
+                         FROM person AS person_b
+                         WHERE person.mother = person_b.mother
+                         AND father = person_b.father)
+AND person.father IS NOT NULL
+AND person.mother IS NOT NULL
+ORDER BY person.name
+;
+
+-- Q5 returns (name,popularity)
+SELECT p.name, COUNT(p.name) AS popularity
+FROM (SELECT SUBSTRING(name FROM '^[a-zA-Z]+') AS name
+      FROM person) AS p
+GROUP BY p.name
+HAVING COUNT(p.name) > 1
+ORDER BY popularity DESC, p.name
+;
+
+-- Q6 returns (name,forties,fifties,sixties)
+
+;
+
+
+-- Q7 returns (father,mother,child,born)
+
+;
+
+-- Q8 returns (father,mother,male)
+
+;
+