The database HikingDB contains information of members of different hiking clubs - NSC Information Technology - Question 2 - 2021 - Paper 1
Question 2
The database HikingDB contains information of members of different hiking clubs. The database contains two tables, namely tblClubs and tblMembers.
The data pages at... show full transcript
Worked Solution & Example Answer:The database HikingDB contains information of members of different hiking clubs - NSC Information Technology - Question 2 - 2021 - Paper 1
Step 1
Button [2.1.1 - Clubs from Gauteng and SA affiliated]
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To retrieve the names and towns of clubs from Gauteng that are SA affiliated, use the following SQL statement:
SELECT ClubName, ClubTown
FROM tblClubs
WHERE Province = 'GP'
AND SA_Affiliated = True;
Step 2
Button [2.1.2 - Birth year]
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To display the name, surname, and date of birth of all members born in 2002, write the SQL statement as follows:
SELECT MemberName, MemberSurname, BirthDate
FROM tblMembers
WHERE YEAR(BirthDate) = 2002;
Step 3
Button [2.1.3 - Display members]
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To display the surname and name of members from a selected club, you need to use the SQL statement:
SELECT MemberSurname, MemberName
FROM tblMembers
WHERE ClubID = (SELECT ClubID FROM tblClubs WHERE ClubName = @ClubName);
Step 4
Button [2.1.4 - Average membership fee]
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To calculate the average membership fee for clubs grouped by province, the following SQL statement can be used:
SELECT Province, FORMAT(AVG(MemFee), 'Currency') AS AvgFee
FROM tblClubs
GROUP BY Province
HAVING AVG(MemFee) > 400;
