Database Languages
eXercise #9
Exercises in Joining
(mostly)
Referring to the
following FAMILY TREE:
It has been decided
to record the above information using the table system outlined below.
One of many advantages of the table system chosen is the ability to
determine a person's relations (eg. Mother, Father, Uncle, Cousin etc.)
PEOPLE
| Person |
Sex |
Born |
Nationality |
| Gerry |
M |
1941 |
Australian |
| Rhonda |
F |
1943 |
British |
| Sybil |
F |
1963 |
Australian |
| Gina |
F |
1985 |
Australian |
| Frank |
M |
1937 |
American |
| Mary |
F |
? |
American |
| Paul |
M |
1962 |
American |
| Peter |
M |
1963 |
Australian |
| Joe |
M |
1984 |
? |
| Steven |
M |
1942 |
British |
| Sue |
F |
1941 |
American |
| Paula |
F |
1964 |
American |
| Harry |
M |
1963 |
? |
| Alan |
M |
1986 |
? |
|
FAMILY
| Person |
Parent |
| Sybil |
Gerry |
| Sybil |
Rhonda |
| Paul |
Frank |
| Paul |
Mary |
| Peter |
Frank |
| Peter |
Mary |
| Paula |
Steven |
| Paula |
Sue |
| Gina |
Sybil |
| Gina |
Paul |
| Joe |
Peter |
| Joe |
Paula |
| Harry |
Frank |
| Harry |
Rhonda |
| Alan |
Paul |
|
These tables exist
in a Database you can download
in Access format
or via the PUBLIC DATABASE PORTAL
Draw DIAGRAMS and then write and test queries that
answer the following:
- List all persons
in the family table, be they child or parent (use a UNION query).
- List all male
children.
- List all children
born after 1965.
- List all children
born between 1960 and 1970.
- List all Australian
children in alphabetical order.
- List all of children
of Mary born after 1962.
- List all children
of undetermined or unknown nationality.
- List all persons
for which no parent is recorded.
- List all persons
for which no children have (yet) been recorded.
- List the children
of Frank and Mary.
- List all persons
in the people table for which no family information is recorded in
the family table.
- List Gina's grandparents.
- List Mary's grandchildren.
- List male parents
who have a child of a different nationality
- List children
who have parents who have different nationalities to each other
- List all siblings
(full or half) of Paul (a sibling is a brother or sister).
- List full siblings
of Paul (same mum AND dad)
- List people with
the same grandparents as Gina.
