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 display the names and towns of clubs in Gauteng that are SA affiliated, use the following SQL query:
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 fetch the name, surname, and date of birth of members born in 2002, the SQL query would be:
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 list the surnames and names of members from a specific club, implement the following SQL query using the selected club name:
SELECT MemberSurname, MemberName
FROM tblMembers
INNER JOIN tblClubs ON tblClubs.ClubID = tblMembers.ClubID
WHERE tblClubs.ClubName = '<Selected Club Name>';
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 in various provinces, execute this SQL query:
SELECT Province,
FORMAT(AVG(MemFee), 'Currency') AS AvgFee
FROM tblClubs
GROUP BY Province
HAVING AVG(MemFee) > 400;
Step 5
Button [2.2.1 - Outstanding fees]
97%
117 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 club name, annual membership fee, and outstanding fees for each member, utilize the following pseudocode:
FOR EACH club IN tblClubs DO
FOR EACH member IN tblMembers WHERE member.ClubID = club.ClubID DO
outstandingFee = member.MemFee - member.AmountPaid;
DISPLAY club.ClubName, member.MemFee, outstandingFee;
END FOR;
END FOR;
