Finished until 5

1 files changed, 60 insertions, 0 deletions

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)
+
+;
+