Pt1420 Final Exam

Rondebosch Boy’s High School
INFORMATION TECHNOLOGY – GRADE 11
7 February 2012
PRACTICAL ClASS TEST
Examiner: G.Cilliers Time: 60 Minutes
-------------------------------------------------
Marks:50

QUESTION 1.

Examine the Database called ResidenceDataBase found in the folder called Question 1 SQL and answer the questions that follow: Save your work on your username in a folder called your Name and Surname.

| Question | Answer (to be written in SQL) | Mark | 1.1 | Write a query to select all the Residence names only once in the Residence TB | | 3 | 1.2 | Write a query to display the cell number of the primaria of Protea |
| 3 | 1.3 | Write a query that displays the name, surnames and town of origin of all the primaria who has a cell number starting with 082 | | 4 | 1.4 | Formulate anSQL statement to display all the fields in the ResidenceTb sorted alphabetically according to the names of the Residences. | | 3 | 1.5 | Formulate an SQL statement to display the ResidenceName, NumberOfPersons and CostPerPerson fields for lodgings which are Double Flats who are more than R13000 per persone. | | 4 | 1.6 | Write a quiery to count the number of residence in the residence TB | | 3 | 1.7 | Write a query that displays the Cell number and names of the Primaria who are from Johannesburg. | | 3 | 1.8 | Write a SQL statement to select all fields of all items containing the word “DF” in the TypeOfRoomOrFlat field in the ResidenceTb table and which have a percentage for meals less or equal to 45 | | 5 | 1.9 | Some of the entries in the field named TypeOfRoomOrFlat are entered as 'SR' (Single Room) in the ResidenceTb.Formulate an SQL statement to replace all entries of 'SR' with 'DR' (Double Room).
| | 4 | 1.10 | Formulate an SQL statement to insert a new record into the PirmariiTb table with the values 'Heemstede', ‘Gerda Otto', '0760072008' and 'Sasolburg'. Display all the records and fields in the PirmariiTb table after the new record has been added. | | 3 | 1.11 | Formulate an SQL statement to display the ResidenceName, CostPerPerson, AantalPersone and TipeKamerOfWoonstel fields of the lodgings of which the cost per persone falls between R10000 and R15000 en where the Primaria’s Selphone Number starts with 083. Display readable headings. NB: You will need to link the tables with an appropriate where clause to be able to do this. (6) | | 7 | 1.12 | Write a query to to list the ResidenceName, TypeOfRoomOrFlat, CostPerPerson, NumberOfPersons and CostOfMeals fields. Cost of meals is a calculated field which reflects the cost of meals. The cost of meals is calculated as follows: Percentage For Meals times the Cost Per Person. The values in the CostOfMeals fieldmust be neatly displayed, including the currency | | 5
| 1.13 | Some of the entries in the field named TypeOfRoomOrFlat are entered as 'SR' (Single Room) in the ResidenceTb.Formulate an SQL statement to replace all entries of 'SR' with 'DR' (Double Room). | | 3 |

Total : [50]