Display the details of all the courses in the tblCourses table, that can accommodate 100 or more students - NSC Information Technology - Question 2 - 2023 - Paper 1

To display the LecturerName, LecturerSurname, and the first letter of the gender of all the lecturers, the SQL query should be written as:

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

In order to display the CourseID and CourseName of all courses that have a multilingual lecturer, the following SQL query can be used:

SELECT CourseID, CourseName
FROM tblCourses
INNER JOIN tblLecturers ON tblCourses.LecturerID = tblLecturers.LecturerID
WHERE tblLecturers.Multilingual = TRUE
ORDER BY CourseName;

To display the LecturerID and total salary, formatted to currency, the SQL command would be:

SELECT LecturerID,
FORMAT(COUNT(*) * 10000, 'CURRENCY') AS Salary
FROM tblCourses
GROUP BY LecturerID;

To find the average duration of courses per lecturer, the SQL query should be structured as follows:

SELECT tl.LecturerID, tl.LecturerName, tl.LecturerSurname,
AVG(tc.Duration) AS 'Average duration of courses'
FROM tblLecturers tl
LEFT JOIN tblCourses tc ON tl.LecturerID = tc.LecturerID
GROUP BY tl.LecturerID, tl.LecturerName, tl.LecturerSurname;

To add a new record to the tblLecturers table for a new lecturer, the SQL command would be:

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