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

-- Report the Names and CountryOfBirth of persons who married someone born in a country different from their own.

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

--COLUMN FIRSTNAME HEADING 'Firstname'
--COLUMN SURNAME HEADING 'Surname'
COLUMN NAME HEADING 'Name'
COLUMN COUNTRYOFBIRTH HEADING 'Birth Country'

SELECT INITCAP(p.firstname || ' ' ||  p.surname) AS Name, countryofbirth
FROM person p JOIN marriage m ON (m.wifeid = p.personid)
WHERE p.countryofbirth != (
SELECT p2.countryofbirth
FROM person p2 JOIN marriage m2 ON (m2.husbandid = p2.personid)
WHERE m2.wifeid = m.wifeid
)
UNION
SELECT INITCAP(p.firstname || ' ' ||  p.surname) AS Name, countryofbirth
FROM person p JOIN marriage m ON (m.husbandid = p.personid)
WHERE p.countryofbirth != (
SELECT p2.countryofbirth
FROM person p2 JOIN marriage m2 ON (m2.wifeid = p2.personid)
WHERE m2.husbandid = m.husbandid
);

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	-- Report the Names and CountryOfBirth of persons who married someone born in a country different from their own.
8
9	TTITLE "COMP2400: Assignment 1: Question 8\|Andrew Pollock (4137129)"
10
11	--COLUMN FIRSTNAME HEADING 'Firstname'
12	--COLUMN SURNAME HEADING 'Surname'
13	COLUMN NAME HEADING 'Name'
14	COLUMN COUNTRYOFBIRTH HEADING 'Birth Country'
15
16	SELECT INITCAP(p.firstname \|\| ' ' \|\| p.surname) AS Name, countryofbirth
17	FROM person p JOIN marriage m ON (m.wifeid = p.personid)
18	WHERE p.countryofbirth != (
19	SELECT p2.countryofbirth
20	FROM person p2 JOIN marriage m2 ON (m2.husbandid = p2.personid)
21	WHERE m2.wifeid = m.wifeid
22	)
23	UNION
24	SELECT INITCAP(p.firstname \|\| ' ' \|\| p.surname) AS Name, countryofbirth
25	FROM person p JOIN marriage m ON (m.husbandid = p.personid)
26	WHERE p.countryofbirth != (
27	SELECT p2.countryofbirth
28	FROM person p2 JOIN marriage m2 ON (m2.wifeid = p2.personid)
29	WHERE m2.husbandid = m.husbandid
30	);
31
32	EXIT;