/[cvs.andrew.net.au]/uni/COMP2400/q10.sql
ViewVC logotype

Contents of /uni/COMP2400/q10.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.3 - (show annotations)
Wed Aug 11 22:48:11 2004 UTC (18 years ago) by apollock
Branch: MAIN
CVS Tags: HEAD
Changes since 1.2: +1 -1 lines
Base questions ready for submission

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

  ViewVC Help
Powered by ViewVC 1.1.22