uni/COMP2400/q7.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 7|Andrew Pollock(4137129)"

COLUMN GRANDCHILD FORMAT A15 HEADING "Grandchild"
COLUMN GRANDFATHER FORMAT A20 HEADING "Grandfather"
COLUMN GRANDMOTHER FORMAT A20 HEADING "Grandmother"

BREAK ON GRANDCHILD

SELECT child.firstname || ' ' || child.surname AS GrandChild, grandfather.firstname || ' ' || grandfather.surname AS grandfather, grandmother.firstname || ' ' || grandmother.surname AS Grandmother
FROM parents p, person mother, person father, person child, person grandmother, person grandfather, parents p2, person grandchild
WHERE p.personid = child.personid
AND father.personid = p.fatherid
AND mother.personid = p.motherid
AND p2.personid = grandchild.personid
AND grandfather.personid = p2.fatherid
AND grandmother.personid = p2.motherid
AND (p2.personid = p.motherid OR p2.personid = p.fatherid) -- Brain haemorrhage occurred at this point
AND p.personid = '&1';

EXIT
1	apollock	1.3	-- 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 7\|Andrew Pollock(4137129)"
8	apollock	1.1
9	apollock	1.2	COLUMN GRANDCHILD FORMAT A15 HEADING "Grandchild"
10			COLUMN GRANDFATHER FORMAT A20 HEADING "Grandfather"
11			COLUMN GRANDMOTHER FORMAT A20 HEADING "Grandmother"
12
13			BREAK ON GRANDCHILD
14
15			SELECT child.firstname \|\| ' ' \|\| child.surname AS GrandChild, grandfather.firstname \|\| ' ' \|\| grandfather.surname AS grandfather, grandmother.firstname \|\| ' ' \|\| grandmother.surname AS Grandmother
16			FROM parents p, person mother, person father, person child, person grandmother, person grandfather, parents p2, person grandchild
17			WHERE p.personid = child.personid
18			AND father.personid = p.fatherid
19			AND mother.personid = p.motherid
20			AND p2.personid = grandchild.personid
21			AND grandfather.personid = p2.fatherid
22			AND grandmother.personid = p2.motherid
23	apollock	1.3	AND (p2.personid = p.motherid OR p2.personid = p.fatherid) -- Brain haemorrhage occurred at this point
24	apollock	1.2	AND p.personid = '&1';
25
26			EXIT