QUESTION
Client Background
AustAcc Pty Limited provides computer networking services to various corporate clients across Australia.
They have asked you, as the Database Administrator, to carry out the following tasks on their database system.
The company’s CEO, Peter Heinz (your teacher) will be your point of contact.
Company’s Mission Statement
AustAcc Pty Limited’s mission is to provide the best networking services to corporate clients across Australia.
Company’s Procedures and Policies
The organisations policy and procedures documentation states that the company Database Administrator is responsible for the following areas …
- Design data tables and indexes
- Enter, modify and delete data
- Provide reports of data for company staff
Essential Personnel
Name Position
Peter Heinz Chief Executive Officer
Tim Jones On-Site Staff Manager
Linda Lee Human Resources/Training Manager
Jane Flinders Financial Controller
In-Class Project
This exercise is meant to be done by you as an in-class project.
You will need to demonstrate your work to your teacher as you progress through each part. This it not only to check your work but to ensure errors are not compounded in later tasks.
Read all questions thoroughly before attempting the tasks.
All questions about staff relate to the AustAcc Sydney Staff unless otherwise stated
SOLUTION
/* 6 */
select * from dbo.staff_sydney
/* 7 */
select Employee_ID, First_Name, Last_Name, Salary from dbo.staff_sydney
union
select Employee_ID, First_Name, Last_Name, Salary from dbo.staff_melbourne
order by Last_name
/* 8 */
select * from
(
select Employee_ID, First_Name, Last_Name, position, Hire_Date from dbo.staff_sydney
union
select Employee_ID, First_Name, Last_Name, position, Hire_Date from dbo.staff_melbourne
) as B
where position like ‘%network%’
order by Last_Name
/* 9 */
select * from
(
select Employee_ID, First_Name, Last_Name, position, Salary from dbo.staff_sydney
union
select Employee_ID, First_Name, Last_Name, position, Salary from dbo.staff_melbourne
) as B
where Salary >= 50000
order by Salary
/* 10 */
select * from
(
select Employee_ID, First_Name, Last_Name, position, Salary from dbo.staff_sydney
union
select Employee_ID, First_Name, Last_Name, position, Salary from dbo.staff_melbourne
) as B
where Salary >= 40000 and Salary <= 60000
order by Salary
/* 11 */
select * from
(
select Employee_ID, First_Name, Last_Name, position, Hire_Date from dbo.staff_sydney
union
select Employee_ID, First_Name, Last_Name, position, Hire_Date from dbo.staff_melbourne
) as B
where year(getdate()) – year(Hire_Date) >=5
order by Last_Name
/* 12 */
select sum(sal)
from (
select sum(salary)as sal from dbo.staff_melbourne
union
select sum(salary)as sal from dbo.staff_sydney
) as B
KH81
But you can order it from our service and receive complete high-quality custom paper. Our service offers Information technology essay sample that was written by professional writer. If you like one, you have an opportunity to buy a similar paper. Any of the academic papers will be written from scratch, according to all customers’ specifications, expectations and highest standards.”
