INFO90002程序写作、SQL编程程序

” INFO90002程序写作、SQL编程程序INFO90002 A2 S1 2020 The University of MelbourneINFO90002 Semester 1, 2020 UPDATED v4Assignment 2 SQLDue: 0700H AEST Monday 11th May 2020Weighting: 10% of your total assessment. The assignment will be graded out of 20 marks and apercentage out of 100 awarded to you.The Human Resources Staff DatabaseThe Human Resources (HR) staff database stores information about staff that work in a global technologycompany. 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 andlocations as part of their job history. Staff may belong to a department and may manage one or moredepartments. 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 theircurrent job role as well as their current salary and a commission percentage for those staff that are eligible.The Data ModelThe following is a physical ER model of the database:Figure 1: The HR ER Model (Updated)2INFO90002 A2 S1 2020 The University of MelbourneAssignment 2 SET UPINFO90002 serverTo set up the database on the INFO90002 MySQL server, download the file staff.sql from the Assignmentfolder on LMS and run it in Workbench. This script creates the schema and database tables and populatesthem with data.PLEASE NOTE that there are some tables similarly named the staff department table is plural calleddepartments. The labs2018 department table is singular and called department. Make sure you usedepartments for this assignmentOn your own deviceTo set up the database on the INFO90002 MySQL server, download the file staff-byod.sql from the Assignmentfolder on LMS and run it in Workbench. This script creates the schema and database tables and populatesthem with data.To use the staff data set enteruse staff;INFO90002作业写作、SQL编程语言作业调试、SQL课程设计作业写作INFO90002 A2 S1 2020 The University of MelbourneThe SQL TasksIn 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 resultby 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 oflocations in that region.(2 marks)7. Some staff members are eligible for a commission. Find the names of staff who will exceed themaximum salary for their job title if they achieve their commission. The calculation of a staffmembers total income if the commission is achieved is their salary multiplied by thecommission percent and added onto their original salary. List the staff members first name,last name and the amount by which they will exceed the maximum salary for their current jobrole. 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 Americaand 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 wholenumber).(2 marks)10. Print the first name, last name and current salary for all staff who held more than one jobposition prior to their current position, and whose current salary is below the average value ofmaximum salaries for all positions they held in the past prior to their current position.(3 marks)4INFO90002 A2 S1 2020 The University of MelbourneSubmission Details:Submit a single PDF showing your answers to all questions to the Assessment page on LMS by 0700H Monday11th May 2020Formatting requirements for your submissionFor 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 lastname.SELECT first_name, last_name, salaryFROM staffORDER BY department_id, salary, last_name;118 Rows returned5INFO90002 A2 S1 2020 The University of MelbourneRequesting a submission deadline extension:If you need an extension due to a valid (medical) reason, you will need to provide evidence to support yourrequest. 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 youruniversity email with the extension request and supporting evidence.2. If your submission deadline extension is granted you will receive an email reply granting the newsubmission date. Do not lose this email!Reminder: INFO90002 Hurdle RequirementsTo 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 theexam.GOOD LUCK!ERRATAAn earlier version of this assignment (v3) had incorrect participation relationships in Figure 1 for the followingrelationships:Locations DepartmentsStaff Job_HistoryJobs Job_HistoryDepartments Job_HistoryThis should not affect your approach to any of the SQL required to answer this assignment如有需要，请加QQ：99515681 或邮箱：99515681@qq.com

“