List the departments along with the number of employees

Assignment Help Database Management System

Reference no: EM133772961

Assessment - SQL Demonstration and analysis

Assessment - Coding task and Video presentation

Your 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.

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.

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 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: List the departments along with the number of employees in each department. Question 5: Find 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.
Keep your explanation concise and focused within a 9-minute to 11-minute timeframe.

Attachment:- Case_Study.rar

Reference no: EM133772961

Questions Cloud

Describe a crowd management problem you have observed : Describe a crowd management problem you have either observed on television or in person. How were they dealt with? Should anything have been done differently?

Research aboriginal and torres strait islander communities : Research Aboriginal and Torres Strait Islander Communities Your Local Area Name of the traditional owners and custodians of the land the service sits on?

How did the work of modern artists like the impressionists : How did the work of modern artists like the Impressionists or Vincent van Gogh differ in style, inspiration, and function from those of academic painters?

What are two health benefits that this food group provides : What is the Key Message? What is the Daily Recommendation for Children 4-8 years old? What are two health benefits that this food group provides.

List the departments along with the number of employees : List the departments along with the number of employees in each department. Question 5: Find the average salary of employees in each department

Develop a uml sequence diagram for course drop : Using Visual Paradigm, Develop a UML sequence diagram for Course Drop that shows the process of student dropping a course they are enrolled in.

What causes the human mind to see coincidence as causation : What is a taboo? How is a taboo different from a fetish? What causes human mind to see coincidence as causation? Specifically, note evidence offered by Gmelch.

What types of events does that community attract : What types of events does that community attract? What resources does the sports commission or the community possess that makes its attractive to events?

Demonstrate read and write data to a persistent storage : TECH1200 Fundamentals of Programming - test your ability to interpret program specifications, develop a high-level model, transfer it into a software

User Account

All Pages