The database EmployeesDB contains the information of the employees of an import and export company - NSC Information Technology - Question 2 - 2020 - Paper 1

To select and display employees whose job title contains the word 'Engineer', the following SQL query can be used:

SELECT EmployeeID, LastName, FirstName FROM tblEmployees WHERE JobTitle LIKE '%Engineer%';

To display a unique list of job titles from the employees table, use the following SQL query:

SELECT DISTINCT JobTitle FROM tblEmployees;

To remove records from the tblHourLogs table where HoursWorked equals 99, execute:

DELETE FROM tblHourLogs WHERE HoursWorked = 99;

To calculate and display the total amount each employee has earned in overtime, the following SQL query should be used:

SELECT LastName, FORMAT(SUM((HoursWorked - 8) * HourlyWage * 2), 'Currency') AS OvertimeAmt
FROM tblEmployees te, tblHourLogs th
WHERE te.EmployeeID = th.EmployeeID AND HoursWorked > 8
GROUP BY LastName;

To list the employees who completed first aid training, and count their number, implement the following code:

Writeln('EmployeeID', #9, 'LastName', #9, 'JobTitle');
Counter := 0;
for each record in tblEmployees do
begin
  if tblEmployees[FirstAidTraining] = True then
  begin
    Writeln(tblEmployees[EmployeeID], #9, tblEmployees[LastName], #9, tblEmployees[JobTitle]);
    Counter := Counter + 1;
  end;
end;
Writeln('Total number of employees who completed first aid training: ', Counter);

To add a new record for the employee Robert Laubscher with an ID and wage of EMP0986 and 15.00 respectively, use the following code:

tblEmployees.Insert;
tblEmployees[EmployeeID] := 'EMP0986';
tblEmployees[FirstName] := 'Robert';
tblEmployees[LastName] := 'Laubscher';
tblEmployees[HourlyWage] := 15.00;
tblEmployees[JobTitle] := 'Marine Engineer';
tblEmployees.Post;

To update the hours worked in the tblHourLogs table, select the record and then input the new value:

var
  hours: Integer;
begin
  hours := StrToInt(Edit1.Text);  // Assume Edit1 is the input field
  tblHourLogs.Edit;
  tblHourLogs[HoursWorked] := hours;
tblHourLogs.Post;
end;