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

SET PAGESIZE 25

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

COLUMN PersonId FORMAT A8 HEADING 'PersonID'
COLUMN FirstName FORMAT A9 HEADING 'Firstname'
COLUMN Surname FORMAT A9 HEADING 'Surname'
COLUMN Sex FORMAT A4 HEADING 'Sex'
COLUMN CountryOfBirth FORMAT A10 HEADING 'Birth|Country'
COLUMN BirthDate HEADING 'Birthdate'
COLUMN DeathDate HEADING 'Deathdate'

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';

SELECT PersonId, INITCAP(FirstName) AS Firstname, INITCAP(Surname) AS Surname, UPPER(Sex) AS Sex, CountryOfBirth, BirthDate, DeathDate
FROM Person
WHERE (UPPER(CountryOfBirth) != 'AUSTRALIA') or CountryOfBirth is null
ORDER BY CountryOfBirth;

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	SET PAGESIZE 25
8
9	TTITLE "COMP2400: Assignment1: Question 1\|Andrew Pollock (4137129)"
10
11	COLUMN PersonId FORMAT A8 HEADING 'PersonID'
12	COLUMN FirstName FORMAT A9 HEADING 'Firstname'
13	COLUMN Surname FORMAT A9 HEADING 'Surname'
14	COLUMN Sex FORMAT A4 HEADING 'Sex'
15	COLUMN CountryOfBirth FORMAT A10 HEADING 'Birth\|Country'
16	COLUMN BirthDate HEADING 'Birthdate'
17	COLUMN DeathDate HEADING 'Deathdate'
18
19	ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY';
20
21	SELECT PersonId, INITCAP(FirstName) AS Firstname, INITCAP(Surname) AS Surname, UPPER(Sex) AS Sex, CountryOfBirth, BirthDate, DeathDate
22	FROM Person
23	WHERE (UPPER(CountryOfBirth) != 'AUSTRALIA') or CountryOfBirth is null
24	ORDER BY CountryOfBirth;
25
26	EXIT;