Create a function that finds the average salary of employees

Assignment Help Database Management System

Reference no: EM133826643

Database Design and Management

Assessment - SQL Demonstration and analysis

Assessment - Coding task and Video presentation

Task

This assessment evaluates your SQL querying proficiency and your ability to justify and explain query logic. You will receive a database schema, data, and a scenario. You can use Gen AI tools for specific tasks only. The assessment consists of a SQL query task (Part A) and a detailed video explanation (Part B).

Assessment Description

In this assessment, you will use a case study provided as a supplementary document. Your task is to retrieve data on the given database using MySQL to find answers to the questions in the Assessment Instructions. The database contains thousands of rows of data, making it impractical to find answers manually. Get Expert Writing Service Now!

Learning outcome 1: Investigate the use of Relational Database Management Systems in organisations.
Learning outcome 2: Construct database models.
Learning outcome 3: Create queries to manage data using a database query language.

Assessment Instructions

Part A: SQL Querying
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. In addition, you can use Gen AI tools to get an idea about how to answer Questions (1 - 10) only, and responses to the SQL queries should be restricted to only the clauses used in workshop materials.

Question 1: Display the first names of all employees who work in the department located in Sydney in alphabetical order.

Question 2: Show employees who have worked at the University the shortest amount of time. Question 3: Display the names of all employees whose age is a palindrome.

Question 4: Create a procedure to list the departments along with the number of employees in each department.

Question 5: Create a function that finds the average salary of employees in each department. Question 6: Find the names and salaries of employees who are older than 40 years.

Question 7: Show the employee details and their department names for those employees who have a "Lecturer" title.

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

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

Question 10: Write a query that populates "EmployeeInitials", based off the existing stored names. Question 11: Write 250 words explaining the integrity of this database. Is it up to standard? If yes, explain why and how. If not, what is wrong with the database's integrity?

Part B: Video Explanation
You should record a video discussing the following:
- Justify each SQL query in detail, including the syntax, the purpose of the query, and the appropriate output.
- Ensure your video is clear and audible.
- Show your SQL queries and the database schema while you explain.

Reference no: EM133826643

Questions Cloud

Which attribute of possible living of the client illness : A clients experience heart attack 2 years ago since then they have. Which attribute of possible living of the client illness could this client exemplify?

What is most likely the patients diagnosis : An 80 yo female presents to the outpatient clinic with her daughter who states, I think my mother is having a drug. What is most likely the patient's diagnosis?

What is the medical team must ensure that informed consent : The medical team must ensure that informed consent is obtained in a manner that respects the patient's cultural values.

Diverse organisational stakeholders : Describe the potential impact of these problems on user satisfaction, organisational goals, and business outcomes - Tailor your presentation to diverse

Create a function that finds the average salary of employees : Create a procedure to list the departments along with the number of employees in each department and Create a function that finds the average salary

How you ensure that health center adheres to patient bill : How can you ensure that the health center adheres to the Patient Bill of Rights by providing clear and comprehensive information to empower patients?

What would you suspect : If a patient presents with sudden onset of paralysis on one side of his face and the patient cannot wrinkle his forehead, what would you suspect?

What is one implication for your community related to a life : The community you serve exhibits disparities in life expectancy, influenced. What is one implication for your community related to a longer life expectancy?

Influencing the intention to use mobile banking in australia : Short review of an appropriate body of literature and discussing the contributions of this potential research. Please follow the two steps below to prepare

User Account

All Pages