uni/COMP2400/q10.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

-- Find the names of all the siblings (brothers and sisters) of a given person; 
-- include only full siblings (both parents the same). Order them by birthdate. 
-- (Two input parameters - FirstName and Surname) 

TTITLE "COMP2400: Assignment 1: Question 10|Andrew Pollock (4137129)"

COLUMN Name HEADING "Name"

SELECT INITCAP(pe.firstname || ' ' || pe.surname) AS Name
FROM parents pa 
JOIN person pe ON (pa.personid = pe.personid)
WHERE pa.motherid = (SELECT motherid
FROM parents pa
JOIN person pe ON (pe.personid = pa.personid)
WHERE pe.firstname = '&1'
AND pe.surname = '&2')
AND pa.fatherid = (SELECT fatherid
FROM parents pa
JOIN person pe ON (pe.personid = pa.personid)
WHERE pe.firstname = '&1'
AND pe.surname = '&2')
AND NOT (pe.firstname = '&1' AND pe.surname = '&2')
ORDER BY pe.birthdate;

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	-- Find the names of all the siblings (brothers and sisters) of a given person;
8	-- include only full siblings (both parents the same). Order them by birthdate.
9	-- (Two input parameters - FirstName and Surname)
10
11	TTITLE "COMP2400: Assignment 1: Question 10\|Andrew Pollock (4137129)"
12
13	COLUMN Name HEADING "Name"
14
15	SELECT INITCAP(pe.firstname \|\| ' ' \|\| pe.surname) AS Name
16	FROM parents pa
17	JOIN person pe ON (pa.personid = pe.personid)
18	WHERE pa.motherid = (SELECT motherid
19	FROM parents pa
20	JOIN person pe ON (pe.personid = pa.personid)
21	WHERE pe.firstname = '&1'
22	AND pe.surname = '&2')
23	AND pa.fatherid = (SELECT fatherid
24	FROM parents pa
25	JOIN person pe ON (pe.personid = pa.personid)
26	WHERE pe.firstname = '&1'
27	AND pe.surname = '&2')
28	AND NOT (pe.firstname = '&1' AND pe.surname = '&2')
29	ORDER BY pe.birthdate;
30
31	EXIT