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	-- 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
9	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	AND (p2.personid = p.motherid OR p2.personid = p.fatherid) -- Brain haemorrhage occurred at this point
24	AND p.personid = '&1';
25
26	EXIT