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	apollock	1.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	apollock	1.4	TTITLE "COMP2400: Assignment 1: Question 5\|Andrew Pollock (4137129)"
8	apollock	1.2
9	apollock	1.3	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	apollock	1.2
19			EXIT;