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

-- List persons whose parents were not married (to each other) at the time of 
-- the person's birth.

ALTER SESSION SET NLS_DATE_FORMAT = 'dd/mm/yyyy';

TTITLE "COMP2400: Assignment 1: Question 9|Andrew Pollock (4137129)"
COLUMN NAME HEADING "Name"
COLUMN BIRTHDATE HEADING "Birthdate"
COLUMN MARRIAGEDATE FORMAT A15 HEADING "Parent's|marriage date"

--SELECT INITCAP(pe.firstname || ' ' || pe.surname) AS Name, pe.birthdate, m.marriagedate
SELECT INITCAP(pe.firstname || ' ' || pe.surname) AS Name
FROM parents pa
JOIN marriage m ON (pa.motherid = m.wifeid)
JOIN person pe ON (pe.personid = pa.personid)
WHERE pe.birthdate NOT BETWEEN m.marriagedate AND m.terminationdate;

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	-- List persons whose parents were not married (to each other) at the time of
8	-- the person's birth.
9
10	ALTER SESSION SET NLS_DATE_FORMAT = 'dd/mm/yyyy';
11
12	TTITLE "COMP2400: Assignment 1: Question 9\|Andrew Pollock (4137129)"
13	COLUMN NAME HEADING "Name"
14	COLUMN BIRTHDATE HEADING "Birthdate"
15	COLUMN MARRIAGEDATE FORMAT A15 HEADING "Parent's\|marriage date"
16
17	--SELECT INITCAP(pe.firstname \|\| ' ' \|\| pe.surname) AS Name, pe.birthdate, m.marriagedate
18	SELECT INITCAP(pe.firstname \|\| ' ' \|\| pe.surname) AS Name
19	FROM parents pa
20	JOIN marriage m ON (pa.motherid = m.wifeid)
21	JOIN person pe ON (pe.personid = pa.personid)
22	WHERE pe.birthdate NOT BETWEEN m.marriagedate AND m.terminationdate;
23
24	EXIT