In the following section, the SQL scripts must correctly manage transactions. You should also ensure that any related data impacted by your script actions are correctly managed.
Copies of your SQL scripts should be placed in the
1. A new accountant is to be added (assume it has not been updated from the HR system). His name is Matthias Window. He has expertise in Taxation and Corporate affairs and will be charged out at a rate of $160.00 per hour from today (16th September 2014).
Include this new employee and his details on the CQR database. Assume that the employee number for this new employee is 1 more than the largest employee.
2. His first work is to give some tax advice to an existing client who he thinks is Bill Wilson. He spends an hour from 8:30 doing so but can't find the service type code so creates a new one for donations and uses that. He then moves on to some research for half an hour for an existing client Richard Smith. In both cases he forgets to key in an end time.
Include the details of this sequence of billing in the database.
3. At 10:00 Matthias realises he actually worked for Bill Watson initially not Bill Wilson, that he should have used the communication code for that work and that he should have completed the times correctly.
Include the details of these corrections in the database.
4. Matthias is then tasked with completing a corporate return for 2 Boy's Plumbing Pty. Ltd. He gets the time wrong and keys in 9:30am and spends 2 hours completing the return, updating the completion time to 11:30 when he is finished. As part of the process he raises an on-cost record for the lodgement fee ($110.00) for the return.
Include the above changes in the database.
For ITECH5006 students, prevent the duplication of start times (he has already recorded a 9:30am time for another client) in your SQL.
5. Alan Counting reduces Matthias Window's billing rate by 25%.
For ITECH5006 students, also remove his domain expertise in taxation.
D.Querying of Database using SQL Statements
1. Display the full details for all clients - the name details (firstname and lastname) should be shown in one column called 'Client Name' and the address details (street1, street2, city, state and postcode) in one column called ‘Client Address'.
2. Display the full details for all clients who have a TAS or a NT address.
3. Display the full details for all service types in the CQR service type table which have the word 'advice' (upper or lowercase) in any of their columns.
4. Alan is considering what the effect of increasing the billing rate on all employees by 5% would be. Display the employee number, name, effective date and increased billing rate of all employees in CQR.
5. Display the full details for the cheapest billing employee provided by CQR.
6. Display the details of all clients for whom no billing on cost records exist for the current year (2014). Display in client concatenated firstname, lastname order.
7. Provide the total number of employees, total billing rate, average billing rate for the practice.
8. Calculate the total charges, excluding on-costs per client for the month of September 2014.
9. Display the employee number, client number, service ID and billingstarttime for service types of "Advice" or "Communication" type where the actual charge on any billing record is cheaper than $150 and the time duration is between 15 and 30 minutes. Order the list such that the billingrecords which are least expensive are listed first.
10. For all clients currently in the CQR system, display details about the clients and those with sector records and those without sector records:
• for each client with sectors: display the string 'With sectors', the client number, client first name concatenated with the client last name, and the total number of sectors the client is involved in, and
• for each client without sector records: display the string 'Without sectors', the client number, client first name concatenated with the client last name, and the total number of sectors as a string of ‘Not applicable'.
• Note that that the results from this listing should be displayed in a single result output. Hint: Make use of the "UNION" relational operator as part of your SQL statement.
Complete the following.
11. Display details of all employees (number, name) for whom billingrecords have been created when they are not identified as having that expertise i.e. those employees who have charged to a service that they do not have a domain record for.
12. Select employee number and the distinct service from the billingrecords where any record for that service took in excess of 1 hour to complete. You should only display one employee number, service and description combination even when multiple records qualify.
13. Display the structure details for which the smallest total billable activity (time) has been performed in the last month
14. Report the average number of billing records per employee per day.