uni/COMP2400/q6.sql

-- I have read the ANU Code on "Academic Honesty in Learning and Teaching",
-- and I declare that, except where appropriately attributed, the content
-- of the file I have submitted for this assignment is entirely my own
-- work. It has not been produced, in whole or in part, by another person.
-- (signed) Andrew Pollock 4137129

-- Find the couple(s) with the most children

TTITLE "COMP2400: Assignment 1: Question 6|Andrew Pollock (4137129)"

COLUMN Mother HEADING "Mother"
COLUMN Father HEADING "Father"
COLUMN Children HEADING "Number|of children"

SELECT mother.firstname || ' ' || mother.surname AS Mother, father.firstname || ' ' || father.surname AS Father, COUNT(p.personid) AS Children
FROM parents p, person mother, person father
WHERE p.motherid = mother.personid
AND p.fatherid = father.personid
GROUP BY mother.firstname, mother.surname, father.firstname, father.surname
HAVING COUNT(p.personid) = (SELECT MAX(COUNT(personid))
                           FROM parents
                           GROUP BY motherid, fatherid);

EXIT;
1	-- I have read the ANU Code on "Academic Honesty in Learning and Teaching",
2	-- and I declare that, except where appropriately attributed, the content
3	-- of the file I have submitted for this assignment is entirely my own
4	-- work. It has not been produced, in whole or in part, by another person.
5	-- (signed) Andrew Pollock 4137129
6
7	-- Find the couple(s) with the most children
8
9	TTITLE "COMP2400: Assignment 1: Question 6\|Andrew Pollock (4137129)"
10
11	COLUMN Mother HEADING "Mother"
12	COLUMN Father HEADING "Father"
13	COLUMN Children HEADING "Number\|of children"
14
15	SELECT mother.firstname \|\| ' ' \|\| mother.surname AS Mother, father.firstname \|\| ' ' \|\| father.surname AS Father, COUNT(p.personid) AS Children
16	FROM parents p, person mother, person father
17	WHERE p.motherid = mother.personid
18	AND p.fatherid = father.personid
19	GROUP BY mother.firstname, mother.surname, father.firstname, father.surname
20	HAVING COUNT(p.personid) = (SELECT MAX(COUNT(personid))
21	FROM parents
22	GROUP BY motherid, fatherid);
23
24	EXIT;