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

To retrieve the EmployeeID, LastName, and FirstName fields of employees whose job title contains the word 'Engineer', the SQL statement is:

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

To display a list of all unique job titles at the company, the SQL statement is:

SELECT DISTINCT JobTitle FROM tblEmployees;

To remove all records in the tblHourLogs table where the HoursWorked is 99, the SQL statement used is:

DELETE FROM tblHourLogs WHERE HoursWorked = 99;

To calculate the overtime payments for employees, the SQL statement to execute is:

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

To display the EmployeeID, LastName, and JobTitle of all employees who have completed first aid training, the Delphi code should loop through the tblEmployees table and check the FirstAidTraining field. The implementation can include a counter to track the number of employees:

// Display column headings
Memo1.Lines.Add('EmployeeID | LastName | JobTitle');
// Initialize counter
docount := 0;
while not tblEmployees.Eof do begin
  if tblEmployees.FieldByName('FirstAidTraining').AsBoolean then begin
    Memo1.Lines.Add(Format('%s | %s | %s', [tblEmployees.FieldByName('EmployeeID').AsString, tblEmployees.FieldByName('LastName').AsString, tblEmployees.FieldByName('JobTitle').AsString]));
    inc(docount);
  end;
  tblEmployees.Next;
end;
Memo1.Lines.Add('Total number of employees who completed first aid training: ' + IntToStr(docount));

To add a new record in the tblEmployees table, the following Delphi code can be used:

tblEmployees.Insert;
tblEmployees.FieldByName('EmployeeID').AsString := 'EMP986';
tblEmployees.FieldByName('FirstName').AsString := 'Robert';
tblEmployees.FieldByName('LastName').AsString := 'Laubscher';
tblEmployees.FieldByName('HourlyWage').AsFloat := 145.00;
tblEmployees.FieldByName('JobTitle').AsString := 'Marine Engineer';
tblEmployees.FieldByName('FirstAidTraining').AsBoolean := True;
tblEmployees.Post;

To update the number of hours worked in the tblHourLogs table, you would use the following Delphi code:

// Assuming selected LogID from the grid is already known
selectedLogID := 174;
if tblHourLogs.Locate('LogID', selectedLogID, []) then begin
  tblHourLogs.Edit;
  tblHourLogs.FieldByName('HoursWorked').AsInteger := newHoursWorked; // newHoursWorked is user input
  tblHourLogs.Post;
end;