Problem I. 135. (May 2006)
I. 135. An SQL database is used to maintain data concerning summer camps of a secondary school. Different camp terms have different starting and ending dates. The amount of money to be paid by teachers and students is also different. Upon request of the headmaster, the following questions (among others) should be answered:
1. List the name of camp terms, their dates, and number of boy, girl and teacher participants.
2. List the boys participating in the third camp.
3. Count how many students and teachers will be present in the camp on August 5.
4. Determine for each term the total amount of money already paid by the participants.
5. Determine for each term the amount of money students still owe.
6. List for each student the amount of money already paid along with the additional amount that needs to be paid.
You should construct tables of the database in such a way that one SQL query (without any subqueries) should be enough in each case. You should also give these queries.
The definition of the database tables and the six SQL queries should be submitted in plain text files (i135def.txt, i135quer.txt).
Deadline expired on 15 June 2006.