Do the following: 1 - NSC Information Technology - Question 2 - 2023 - Paper 1

To display the LecturerName, LecturerSurname, and calculate the first letter of the gender, use the SQL statement:

SELECT LecturerName, LecturerSurname,
LEFT(Gender, 1) AS 'Gender (M/F)'
FROM tblLecturers;

To retrieve the CourseID and CourseName of all courses that have multilingual lecturers, sort the results alphabetically by course name using the SQL statement:

SELECT CourseID, CourseName
FROM tblCourses
WHERE tblLectures.LecturerID = tblCourses.LecturerID 
AND Multilingual = TRUE
ORDER BY CourseName;

To calculate and display the salary of lecturers based on the number of courses they facilitate, use:

SELECT LecturerID, 
FORMAT(COUNT(*) * 100000, 'C', 'en-ZA') AS Salary
FROM tblCourses
GROUP BY LecturerID;

To calculate the average duration of courses facilitated by each lecturer, use the following approach:

1. Loop through all records in tblLecturers.
2. For each lecturer, calculate the average duration and display as follows:

foreach Lecturer in tblLecturers do
begin
  totalDuration := 0;
  count := 0;
  writeLn(Lecturer.LecturerID + ': ' + Lecturer.LecturerName + ' ' + Lecturer.LecturerSurname);
  foreach Course in tblCourses do
  begin
    if Course.LecturerID = Lecturer.LecturerID then
    begin
      totalDuration += Course.Duration;
      count += 1;
      writeLn(count + '. ' + Course.CourseName);
    end;
  end;
  averageDuration := totalDuration / count;
  writeLn('Average duration of courses: ' + Format('%0.2f', [averageDuration]));
end;

To add a new lecturer record to the tblLecturers table, use:

tblLecturers.Insert();
  tblLecturers['LecturerID'] := 'ZT032';
  tblLecturers['LecturerName'] := 'Zander';
  tblLecturers['LecturerSurname'] := 'Thomas';
  tblLecturers['Gender'] := 'M';
  tblLecturers['Multilingual'] := True;