IPT home IPT A Virtual Approach by Peter Whitehouse
Quick Links:
 
 
IIS home
Information and Intelligent Systems Social and Ethical Implications Human Computer Interaction Software and Systems Engineering eXercise Files Course Outline and Assessment A-Z of Geeky Acronyms Terrace Work Program 2004 Sillybus FAQ = Frequently Asked Questions Help
 
 

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:

  1. list the name and birthyear of each person
  2. list the names of all people in the database
  3. list all nationalities ONCE
  4. list all the parents
  5. list all the children
  6. list the names of all the Australians
  7. list the names of all non-Australians
  8. list all males
  9. list the parents of 'Joe'
  10. list the children of 'Mary'
  11. list all persons born in 1963
  12. list all persons born after 1950
  13. list all persons born between 1950 and 1970
  14. list those persons whose name begins with a 'P'
  15. list all persons who have an 'a' in their name
  16. list all persons whose name ends in an 'a'
  17. list all persons who have a letter 'e' as the second letter of their name
  18. list the children of 'Frank' or 'Mary'
  19. list all second generation persons (ie. those on the second row of the tree)
  20. list everybody in alphabetical order
  21. list everybody as above but with MALES before FEMALES
  22. list everybody FEMALES before MALES but within each gender, the names should be alhpabetically arranged
  23. list all children in alphabetical order
  24. list everyone and their birthyear from oldest to youngest
  25. list everyone and their nationality, arranged in national groups
  26. list the NUMBER of people in the database
  27. list the NUMBER of different nationalities there are represented in the database
  28. list the NUMBER of children there are in the database
  29. list the NUMBER of parents that have names exactly 5 letters long
  30. list the NUMBER of children 'Frank' has
  31. list the average birthyear of people in the database
  32. output the average age of people in the database
  33. list the average birthyear of people in the database using SUM and COUNT functions
  34. list the oldest age
  35. list the youngest age
  36. list the NUMBER of people born before 1950
  37. list the NUMBER of people born between 1950 and 1970
  38. list the NUMBER of males born after 1960
  39. list the NUMBER of Australians there are in the database
  40. list the NUMBER of British females there are in the database
  41. list the NUMBER of people in each of the nationalities
  42. list the NUMBER of people in each sex
  43. list all children together with the number of parents recorded for them
  44. list all parents together with the number of children recorded for them
  45. list all parents who have more than one child in Alphabetical order.

SOLUTIONS


 

wonko@wonko.info
©Copyright t 1992..2017+. Edition 25.150117
wonkosite
Creative Commons License
This work is licensed under a
Creative Commons Attribution-NonCommercial-ShareAlike 2.1 Australia License
.