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.)
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
| 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 |
|
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.
