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 create an alphabetical list of employees, use the following SQL statement:
SELECT * FROM tblEmployees ORDER BY Surname ASC;
This query retrieves all records from the tblEmployees table and orders them by the surname in ascending order. The ORDER BY clause sorts the results by the specified field.
Step 2
Button [2.1.2 – Number of children of permanent employees]
Answer
To find the number of children of permanent employees who have more than three children, you can use this SQL query:
SELECT Surname, FirstName, Children FROM tblEmployees
WHERE Children > 3 AND Permanent = TRUE;
This query selects the surname, first name, and number of children of employees who meet the condition specified in the WHERE clause.
Step 3
Button [2.1.3 – Employees paid on selected date]
Answer
To retrieve the employees paid on a specific selected date, you can execute the following SQL statement:
SELECT PaymentNumber, IDNumber
FROM tblEmployees, tblPayments
WHERE tblEmployees.EmployeeNumber = tblPayments.EmployeeNumber AND PaymentDate = '2017/01/17';
This query uses a JOIN between tblEmployees and tblPayments, filtering for payments made on the specified date.
Step 4
Step 5
Button [2.1.5 – Total net salaries per month]
Answer
To calculate total net salaries per month, you can run the following SQL query:
SELECT Month(PaymentDate) AS MonthNum,
SUM(GrossSalary - Deductions) AS TotalAmountPaid
FROM tblPayments
GROUP BY Month(PaymentDate);
This query groups payment records by month and computes the total amount paid by subtracting deductions from gross salaries.