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
2.1. Alphabetical list
96%
114 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To retrieve the list of employees sorted by surname, we can use the following SQL command:
SELECT * FROM tblEmployees ORDER BY Surname ASC;
Step 2
2.12 - Number of children of permanent employees
99%
104 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To find employees who are permanent and have more than three children, we use:
SELECT Surname, FirstName, Children FROM tblEmployees WHERE Children > 3 AND Permanent = TRUE;
Step 3
2.13 - Employees paid on selected date
96%
101 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To find payments made to employees on a specific date:
SELECT PaymentNumber, IDNumber
FROM tblPayments, tblEmployees
WHERE tblEmployees.EmployeeNumber = tblPayments.EmployeeNumber AND PaymentDate LIKE '2017/01/17';
Step 4
2.14 - Delete payment
98%
120 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To delete a specific payment from tblPayments based on the payment number, we execute:
DELETE FROM tblPayments WHERE PaymentNumber = 110;
Step 5
2.15 - Total net salaries per month
97%
117 rated
Only available for registered users.
Sign up now to view full answer, or log in if you already have an account!
Answer
To calculate total net salaries per month:
SELECT Month(PaymentDate) AS MonthNum,
FORMAT(SUM(GrossSalary - Deductions), 'Currency') AS TotalAmountPaid
FROM tblPayments
GROUP BY Month(PaymentDate);
