NSC Information Technology CRUD operations: The database EmployeesDB contain

Question

The database EmployeesDB contains the information of the employees of an import and export company. The database contains two tables called tblEmployees and tblHourLogs.

The data papers attached at the end of this question paper provide information on the design of the database and the content of the tables.

Do the following:

1. Open the incomplete project file called Question_2.pdpr in the Question 2 folder.
2. Use your examination number as a comment in the first line of the Question 2.pdpr file.
3. Compile and debug the program. The program has functionality currently.
4. The first four lines of each of the tables are displayed on the tab sheet Question 2.1 - Delphi code, as shown below.

Note:
- Use ONLY SQL statements to answer QUESTION 2.1.1 to QUESTION 2.1.5.
- SQL statements are also to be included in the code to answer QUESTION 2.2.1, QUESTION 2.2.2 and QUESTION 2.2.3.

2.1 Button (2.1.1 - List of employees)
Display ALL the information of employees from the tblEmployees table, sorted in descending order according to the JobTitle and HourlyWage.

Example of output:

EmployeeID	LastName	FirstName	JobTitle	HourlyWage
1	Smith	John	Manager	25.00
2	Jones	Lisa	Engineer	20.00
...

2.1.2 Button (2.1.2 - Engineers)
Display the EmployeeID, LastName and FirstName fields of employees whose job title contains the word 'Engineer.'

Example of output:

EmployeeID	LastName	FirstName
3	Brown	Mike
4	Williams	Sara
...

2.1.3 Button (2.1.3 - Job titles)
Display a list of all the different job titles at the company. Each job title must be displayed ONE only.

Example of output:

JobTitle
Manager
Engineer
...

2.1.4 Button (2.1.4 - Remove records)
A number of records with incorrect data on the hours worked have been entered in the tblHourLogs table.
Remove all the records in the tblHourLogs table where the HoursWorked is 99.

2.1.5 Button (2.1.5 - Overtime)
Employees are paid overtime for each hour exceeding 8 hours per day. Calculate and display the total amount each employee has been paid for overtime worked, formatted as currency. Display them below the employee details, after the LastName and the calculated OvertimeAmt is outputted.

Example of output:

LastName	OvertimeAmt
Smith	100.00
Jones	50.00
...

2.2.1 Button (2.2.1 - Employees with first aid)
Write code to do the following:
- Display the EmployeeID, LastName and job title of ALL employees who have completed first aid training in the rich edit component reader.
- Count the number of employees who completed first aid training and display the result below the employee details, as shown in the example below.

Example of output:

EmployeeID	LastName	JobTitle
2	Johnson	Clerk
...
Total number of employees who completed first aid training: 3

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

Worked Solution & Example Answer:The database EmployeesDB contains the information of the employees of an import and export company - NSC Information Technology - Question 2 - 2020 - Paper 1

Button (2.1.1 - List of employees)

Button (2.1.2 - Engineers)

Button (2.1.3 - Job titles)

Button (2.1.4 - Remove records)

Button (2.1.5 - Overtime)

Button (2.2.1 - Employees with first aid)

Join the NSC students using SimpleStudy...