INFO90002代做、SQL编程语言调试、SQL设计代做

日期：2020-05-12 11:01

INFO90002 A2 S1 2020 ? The University of Melbourne

INFO90002 Semester 1, 2020 UPDATED v4

Assignment 2 – SQL

Due: 0700H AEST Monday 11th May 2020

Weighting: 10% of your total assessment. The assignment will be graded out of 20 marks and a

percentage out of 100 awarded to you.

The Human Resources Staff Database

The Human Resources (HR) staff database stores information about staff that work in a global technology

company. Departments are located in cities in countries that belong to one of four business regions.

Within the database, staff must have a current job and they may have held jobs in other departments and

locations as part of their job history. Staff may belong to a department and may manage one or more

departments. Staff may supervise other staff, but not all staff have a supervisor.

The HR database stores staff first and last names, email and phone number, the day they were hired for their

current job role as well as their current salary and a commission percentage for those staff that are eligible.

The Data Model

The following is a physical ER model of the database:

Figure 1: The HR ER Model (Updated)

2

INFO90002 A2 S1 2020 ? The University of Melbourne

Assignment 2 SET UP

INFO90002 server

To set up the database on the INFO90002 MySQL server, download the file staff.sql from the Assignment

folder on LMS and run it in Workbench. This script creates the schema and database tables and populates

them with data.

PLEASE NOTE that there are some tables similarly named the staff department table is plural called

departments. The labs2018 department table is singular and called department. Make sure you use

departments for this assignment

On your own device

To set up the database on the INFO90002 MySQL server, download the file staff-byod.sql from the Assignment

folder on LMS and run it in Workbench. This script creates the schema and database tables and populates

them with data.

To use the staff data set enter

use staff;

3

INFO90002 A2 S1 2020 ? The University of Melbourne

The SQL Tasks

In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question.

Subqueries and nesting is allowed within a single SQL statement. DO NOT USE VIEWS to answer questions.

1. List the first name, last name, department name and current job title of all staff.

(1 mark)

2. List every region name, country name and the number of staff in each country. Order the result

by country name.

(1 mark)

3. Who has spent the shortest amount of time in a job? Print their name (first and last name),

how long the job lasted in days and the job title.

(1 mark)

4. For all supervisors who supervise seven or more staff, list their first name, last name, job title,

and the number of staff members they supervise.

(2 marks)

5. Print department names of departments that currently have neither a manager nor any staff.

(2 marks)

6. Which region has the most locations? Print the region name, as well as the total number of

locations in that region.

(2 marks)

7. Some staff members are eligible for a commission. Find the names of staff who will exceed the

maximum salary for their job title if they achieve their commission. The calculation of a staff

member’s total income if the commission is achieved is their salary multiplied by the

commission percent and added onto their original salary. List the staff member’s first name,

last name and the amount by which they will exceed the maximum salary for their current job

role. Order the results from the highest amount to lowest.

(3 marks)

8. List the cities, country names and region names for cities outside the United States of America

and Europe where no staff work.

(3 marks)

9. How many years has Troy Anthony been in his current job role.? Include his Full Name (e.g.

'Troy Anthony'), his Current Job Title, and length in years (rounded to the nearest whole

number).

(2 marks)

10. Print the first name, last name and current salary for all staff who held more than one job

position prior to their current position, and whose current salary is below the average value of

maximum salaries for all positions they held in the past prior to their current position.

(3 marks)

4

INFO90002 A2 S1 2020 ? The University of Melbourne

Submission Details:

Submit a single PDF showing your answers to all questions to the Assessment page on LMS by 0700H Monday

11th May 2020

Formatting requirements for your submission

For each question, present an answer in the following format:

? Show the question number and question in black text.

? Show your answer (the SQL statement) in blue text (DO NOT use a screen shot)

? Show a screenshot from Workbench showing output of 10 or fewer lines.

? Show how many rows were actually returned, in red text.

? Show each query on a separate page.

Example:

Qxx. List the first name, last name and salary of all staff. Order the result by the department id, salary and last

name.

SELECT first_name, last_name, salary

FROM staff

ORDER BY department_id, salary, last_name;

118 Rows returned

5

INFO90002 A2 S1 2020 ? The University of Melbourne

Requesting a submission deadline extension:

If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your

request. Medical certificates need to be at least 2 days in length.

To request an extension:

1. Email the Subject Coordinator (deccles@unimelb.edu.au) with your student id, your name and your

university email with the extension request and supporting evidence.

2. If your submission deadline extension is granted you will receive an email reply granting the new

submission date. Do not lose this email!

Reminder: INFO90002 Hurdle Requirements

To pass INFO90002 you must pass two hurdles:

? Hurdle 1: Obtain at least 50% (15/30) or higher for the modelling (20) and SQL assignments (10)

? Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam (70)

Therefore, it is our recommendation to students that you attempt every assignment and every question in the

exam.

GOOD LUCK!

ERRATA

An earlier version of this assignment (v3) had incorrect participation relationships in Figure 1 for the following

relationships:

Locations – Departments

Staff – Job_History

Jobs – Job_History

Departments – Job_History

This should not affect your approach to any of the SQL required to answer this assignment