Write a query that returns the employees with initials

Assignment Help Database Management System

Reference no: EM133538995 , Length: word count:2000

Database Design and Management

Demonstration and analysis

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.

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.

Employees(EmployeeID, DepartmentID, SalaryID, Title, EmployeeFirstName , EmployeeLastName, Gender, yearHired, birthyear, PerformanceBonus)
Departments(DepartmentID, DepartmentName, Location) SalaryClass(SalaryID, Salary) DepartmentEmployee(EmployeeID, DepartmentID)

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: EM133538995

Questions Cloud

What are the arguments against the use of dietary supplement : What are the arguments supporting the use of dietary supplements? Who needs them? What are the arguments against the use of dietary supplements?

Develop an advanced customer relationship management : TECH6100 Intermediate Programming - Create unit tests and a test-driven development environment and Use exception handling and debugging to identify and manage

How will you alter your approach next time : Was there information you wished you had available but did not? How will you alter your approach next time?

Understanding of legal concepts and framework explored : The purpose of this portion of the assignment is to assess your understanding of the legal concepts and framework explored in this course.

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

What suggestions do you have for operations managers : What are the most recent trends and challenges that Operations Departments faced in 2021 as related to New product design? Based on the analysis

Is there an issue based in the state the group resides : Is there an issue based in the state the group resides? Are there specific rules applying to government support for either group(s)?

What legal advice would you give to jfc regarding trade mark : Jesus Fried Chicken (JFC) is a Mexican fast food restaurant chain, popular in that country. What legal advice would you give to JFC regarding trade mark?

Discuss main ideas in the article to include in the essay : The Beginner's Guide is a great resource, as well as "In the News," and the latest information on science, legal, and other educational resources.

User Account

All Pages