Finished 8 as wellHEADmaster

1 files changed, 13 insertions, 2 deletions

diff --git a/db_2017_cw1.sql b/db_2017_cw1.sql
index 3523f37..13f7be8 100644
--- a/db_2017_cw1.sql
+++ b/db_2017_cw1.sql
@@ -72,10 +72,21 @@ ORDER BY name
 
 
 -- Q7 returns (father,mother,child,born)
-
+SELECT father, mother, name AS child,
+       RANK() OVER (PARTITION BY father ORDER BY dob) as born
+FROM person
+WHERE father IS NOT NULL
+AND mother IS NOT NULL
+ORDER BY father, mother, born
 ;
 
 -- Q8 returns (father,mother,male)
-
+SELECT father, mother,
+       ROUND(100 * COUNT(CASE WHEN gender = 'M' THEN name ELSE NULL END) / COUNT(name), 0) as male
+FROM person
+GROUP BY father, mother
+HAVING father IS NOT NULL
+AND mother IS NOT NULL
+ORDER BY father, mother
 ;