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	apollock	1.2	TTITLE "COMP2400: Assignment 1: Question 7"
2	apollock	1.1
3	apollock	1.2	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