Photo AI
The database PaymentsDB contains the information of the staff members of a restaurant - NSC Information Technology - Question 2 - 2018 - Paper 1
Question 2
The database PaymentsDB contains the information of the staff members of a restaurant. The database contains two tables, namely tblEmployees and tblPayments, with da... show full transcript
Worked Solution & Example Answer:The database PaymentsDB contains the information of the staff members of a restaurant - NSC Information Technology - Question 2 - 2018 - Paper 1
Step 1
Button [2.1.1 – Alphabetical list]
Answer
To display an alphabetical list of employees sorted by surname, the SQL query to be executed is:
SELECT * FROM tblEmployees ORDER BY Surname ASC;
This query selects all fields from the tblEmployees table and orders the results by the Surname field in ascending order.
Step 2
Button [2.1.2 – Number of children of permanent employees]
Answer
To find the number of children of permanent employees, use the following SQL statement:
SELECT Surname, FirstName, Children FROM tblEmployees
WHERE Children > 3 AND Permanent = TRUE;
This retrieves the Surname, FirstName, and number of Children for employees where the Children count is greater than 3 and they are marked as permanent.
Step 3
Button [2.1.3 – Employees paid on selected date]
Answer
To find employees who were paid on a specific date, the SQL query would be:
SELECT PaymentNumber, IDNumber
FROM tblPayments, tblEmployees
WHERE tblEmployees.EmployeeNumber = tblPayments.EmployeeNumber AND PaymentDate = '2017/01/17';
This query selects PaymentNumber and IDNumber by joining tblPayments with tblEmployees on EmployeeNumber, while also filtering by the PaymentDate.
Step 4
Step 5
Button [2.1.5 – Total net salaries per month]
Answer
To calculate the total net salaries paid per month, the SQL command to execute is:
SELECT Month(PaymentDate) AS MonthNum,
FORMAT(SUM(GrossSalary - Deductions), 'Currency') AS TotalAmountPaid
FROM tblPayments
GROUP BY Month(PaymentDate);
This query retrieves the month from PaymentDate, calculates the net salary by subtracting Deductions from GrossSalary, and groups the results by month.