Assignment 2 – SQL SOLVED

1
INFO20003 A2
Assignment 2 – SQL

Weighting: 10% of your total assessment. The assignment will be graded out of 20 marks.
The Human Resources Database
The Human Resources (HR) 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
2
INFO20003 A2-S1-2018
Assignment 2 SET UP
To set up the database in your 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.
NOTE: The script is designed to run against your account on the Engineering IT server. Uncomment
the BYOD section at the beginning of the script if you want to install the schema on your own device.
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 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 five 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. (2 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. Print job titles, the date the jobs ended, and the current manager’s first and last name
for all jobs that ended in 2006 which are currently not managed by Steven King. (3
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
3
INFO20003 A2-S1-2018
average value of maximum salaries for all positions they held in the past prior to their
current position. (3 marks)
Submission Details:
Submit a single PDF showing your answers to all questions to the Assessment page on LMS by
midnight on the due date of Friday 20th of April. Name your file 987654.pdf, where 987654
corresponds to YOUR student id. Other formatting requirements are listed below.
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 (not 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;
107 Rows returned
4
INFO20003 A2-S1-2018
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: INFO20003 Hurdle Requirements
To pass INFO20003 you must pass two hurdles:
 Hurdle 1: Obtain at least 50% (15/30) or higher for the three individual assignments (each worth
10%)
 Hurdle 2: Obtain a grade of 50% (35/70) or higher for the MST Mid Semester Test (10) and the
End of Semester Exam (60)