Sample Content
Question 1
The bicycle shop has devised two plans, A and B, for bicycle hiring. Plan A is on a daily basis and plan B is on an hourly basis. The charging rate for plan A is HKD50 per day, that for plan B is HKD15 per hour.
The shop owner wants to computerise the hiring system. He labels every bicycle with a unique code. For each new customer, a record is created in the table User.dbf to store the name and ID card number.
For each transaction, a new record is created in the table Hire.dbf to store the date of transaction and plan of hiring. The tables involved have the following structure:
User.dbf Field Name Type Width Dec User_id Char 10 Name Char 20
Hire.dbf Field Name Type Width Dec User_id Char 10 Bicycle_id Char 5 Date Date 8 Plan Char 1 Hours Numeric 2
The field Hours records the number of hours in which the bicycle is hired.
a. Using an SQL statement, produce a list of users.
b. Using an SQL statement, determine the total number of visits by ‘David Chan’.
c. Determine the total income of the shop in one week starting from 25th March, 2010.
d. Using an SQL statement, determine the total income earned by each bicycle.
Question 2
A hair saloon records the performance of the hairdressers in the following tables:
Staff.dbf
|Field Name |Type |Width |Dec |
|Staff_id |Char |4 | |
|Name |Char |20 | |
|Address |Char |60 | |
|Phone |Char |10 | |
Perform.dbf
The bicycle shop has devised two plans, A and B, for bicycle hiring. Plan A is on a daily basis and plan B is on an hourly basis. The charging rate for plan A is HKD50 per day, that for plan B is HKD15 per hour.
The shop owner wants to computerise the hiring system. He labels every bicycle with a unique code. For each new customer, a record is created in the table User.dbf to store the name and ID card number.
For each transaction, a new record is created in the table Hire.dbf to store the date of transaction and plan of hiring. The tables involved have the following structure:
User.dbf Field Name Type Width Dec User_id Char 10 Name Char 20
Hire.dbf Field Name Type Width Dec User_id Char 10 Bicycle_id Char 5 Date Date 8 Plan Char 1 Hours Numeric 2
The field Hours records the number of hours in which the bicycle is hired.
a. Using an SQL statement, produce a list of users.
b. Using an SQL statement, determine the total number of visits by ‘David Chan’.
c. Determine the total income of the shop in one week starting from 25th March, 2010.
d. Using an SQL statement, determine the total income earned by each bicycle.
Question 2
A hair saloon records the performance of the hairdressers in the following tables:
Staff.dbf
|Field Name |Type |Width |Dec |
|Staff_id |Char |4 | |
|Name |Char |20 | |
|Address |Char |60 | |
|Phone |Char |10 | |
Perform.dbf