Database Languages
eXercise #3
Introducing the
SELECT Statement
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 etc.)
MAKE your own copy of this database using this script or use the copy in the Public Database Portal
Download
an Access 2000 Version of this Database
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
| Child |
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 |
|
Now write select
statements (and the number of rows in the answer table) for the following DML requests:
- list the name
and birthyear of each person
- list the names
of all people in the database
- list all nationalities
ONCE
- list all the
parents
- list all the
children
- list the names
of all the Australians
- list the names
of all non-Australians
- list all males
- list the parents
of 'Joe'
- list the children
of 'Mary'
- list all persons
born in 1963
- list all persons
born after 1950
- list all persons
born between 1950 and 1970
- list those persons
whose name begins with a 'P'
- list all persons
who have an 'a' in their name
- list all persons
whose name ends in an 'a'
- list all persons
who have a letter 'e' as the second letter of their name
- list the children
of 'Frank' or 'Mary'
- list all second
generation persons (ie. those on the second row of the tree)
- list everybody
in alphabetical order
- list everybody
as above but with MALES before FEMALES
- list everybody
FEMALES before MALES but within each gender, the names should be alhpabetically
arranged
- list all children
in alphabetical order
- list everyone
and their birthyear from oldest to youngest
- list everyone
and their nationality, arranged in national groups
- list the NUMBER
of people in the database
- list the NUMBER
of different nationalities there are represented in the database
- list the NUMBER
of children there are in the database
- list the NUMBER
of parents that have names exactly 5 letters long
- list the NUMBER
of children 'Frank' has
- list the average
birthyear of people in the database
- output the average
age of people in the database
- list the average
birthyear of people in the database using SUM and COUNT functions
- list the oldest
age
- list the youngest
age
- list the NUMBER
of people born before 1950
- list the NUMBER
of people born between 1950 and 1970
- list the NUMBER
of males born after 1960
- list the NUMBER
of Australians there are in the database
- list the NUMBER
of British females there are in the database
- list the NUMBER
of people in each of the nationalities
- list the NUMBER
of people in each sex
- list all children
together with the number of parents recorded for them
- list all parents
together with the number of children recorded for them
- list all parents
who have more than one child in Alphabetical order.
SOLUTIONS
