uni/COMP2400/q5.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

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

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

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

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	TTITLE "COMP2400: Assignment 1: Question 5\|Andrew Pollock (4137129)"
8
9	COLUMN Father HEADING "Father"
10	COLUMN Mother HEADING "Mother"
11	COLUMN Children HEADING "Number of\|children"
12
13	SELECT father.firstname \|\| ' ' \|\| father.surname AS Father, mother.firstname \|\| ' ' \|\| mother.surname AS Mother, COUNT(p.personid) AS children
14	FROM parents p, person mother, person father
15	WHERE p.motherid = mother.personid
16	AND p.fatherid = father.personid
17	GROUP BY father.firstname, father.surname, mother.firstname, mother.surname;
18
19	EXIT;