uni/COMP2400/q7.sql

TTITLE "COMP2400: Assignment 1: Question 7"

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)
AND p.personid = '&1';

EXIT
1	TTITLE "COMP2400: Assignment 1: Question 7"
2
3	COLUMN GRANDCHILD FORMAT A15 HEADING "Grandchild"
4	COLUMN GRANDFATHER FORMAT A20 HEADING "Grandfather"
5	COLUMN GRANDMOTHER FORMAT A20 HEADING "Grandmother"
6
7	BREAK ON GRANDCHILD
8
9	SELECT child.firstname \|\| ' ' \|\| child.surname AS GrandChild, grandfather.firstname \|\| ' ' \|\| grandfather.surname AS grandfather, grandmother.firstname \|\| ' ' \|\| grandmother.surname AS Grandmother
10	FROM parents p, person mother, person father, person child, person grandmother, person grandfather, parents p2, person grandchild
11	WHERE p.personid = child.personid
12	AND father.personid = p.fatherid
13	AND mother.personid = p.motherid
14	AND p2.personid = grandchild.personid
15	AND grandfather.personid = p2.fatherid
16	AND grandmother.personid = p2.motherid
17	AND (p2.personid = p.motherid OR p2.personid = p.fatherid)
18	AND p.personid = '&1';
19
20	EXIT