Who work in the department located in sydney

Assignment Help Database Management System

Reference no: EM133707700 , Length: word count:2000

Assessment - Demonstration and analysis - Part II

Your Task

This demonstration assessment is to be completed individually.
You will be given a database schema, data, and a scenario.
You are to carefully analyse the database, ensuring you understand the stored data.
You must write an SQL query for each question below.
You must also answer the additional questions at the end of the assessment.

Assessment Description

University of Australia (UoA) is a university with 24 departments located all across Australia. These departments act as the work-space for each student and employee, with a total of 20,000 employees working at UoA.

You have been hired by UoA as a Database Administrator to do some data retrieval on their database. You have been given the database schema and data, as seen below.

Chris Taverly, the member of the leadership team at UoA was impressed with your previous work and has approached you for a second time, asking you more questions. Chris has asked you for to retrieve more data from the database and has also asked you to examine the integrity of the database. You are asked to use MySQL to find the answers to the questions below.

Data Files

Once found on MyKBS, you must download the following files:

Assessment_Database.sql Assessment_Employee_Data.sql Assessment_Data.sql

Assessment Instructions

As an individual, you must download the database and data files and load them into MySQL. Once loaded, you must develop queries for the following questions:

Note: You should include a comment above each query, specifying which question you are answering.

Question 1: Add a new attribute called "EmployeeInitials" to the Employees table

Question 2: Display the first names of all employees who work in the department located in Sydney

Question 3: Display employee first and last names and work out the original salary for all employees and their new salary after their (10%) bonus is applied

Question 4: Display how many employees will not receive a performance bonus

Question 5: Write a query that returns the employees with initials that are a palindrome and also return employees without palindrome initials, but output 'not a palindrome!'

Question 6: Write a query that populates EmployeeInitials, based off the existing stored names

Question 7: Which joins did you use in your queries and why? Would other joins have worked?

Question 8: Write 250 words explaining the integrity of this database. Is it up to standards? If yes, explain why and how. If no, what is wrong with the database's integrity?

Reference no: EM133707700

Questions Cloud

Identify social science principles that apply to your issue : Identify the social science principles that apply to your issue. In other words, which principles of social science apply to the issue you selected?

Adult mental health coordinator working closely with clients : I have chosen this as a desired position from gaining experience as an adult mental health coordinator working closely with clients

What are some of the pros and cons of your choice : What are some of the pros and cons of your choice? What are the ethical issues? What issues may arise by inaction to address?

Desired position from gaining experience : Desired position from gaining experience as an adult mental health coordinator working closely with clients and dealing with all needs of clients

Who work in the department located in sydney : Examine the integrity of the database. You are asked to use MySQL to find the answers to the questions - Display the first names of all employees who work

What are positions and interests of each party to conflict : What are the positions and interests of each party to the conflict? Who needs to participate in the mediation, and how would you involve/approach them?

Preparing stock solution of oral medication : As a Nurse in a long-term healthcare facility, you're tasked with preparing a stock solution of oral medication for a unit.

Good impact objectives for adolescent obesity program : What are good impact objectives for an adolescent obesity program trying to implement more enhanced school-based physical activity

Chronic obstructive pulmonary disease : A patient is being discharged from acute care following a chronic obstructive pulmonary disease (COPD) exacerbation.

User Account

All Pages