Chapter 7 Homework To create MSU Human Resources database, use MSU_HR_MySQL script provided. Before answering the questions, be familiar with the database structure by referring to the database tables (MSU Human Resources Database Tables) and database model (ERD for MSU Human Resources Database) documents. Submission: Due date: March 1 (Monday) 11:59PM Your answer document MUST contain SQL statement and query results for each question. Copy and paste SQL statements and their results to Microsoft Word or PDF file and submit the Word or PDF file o Put question #, too. I strongly recommend you start this homework earlier since it will take time to do. There will be NO extension or make-up given. Note: You MUST use the SQL commands used in the textbook and the field (column) names in the tables provided; otherwise, you will NOT get any credits for the question. Except for Question #13, when you join tables, you must use Join on syntax or old-style join which is covered in Chapter 7. You may read pages 275-276 if you don't know what is cold- style join. o Do Not use Natural Join, Inner Join", or "Join Using" syntax For download, installation, and tutorials information of MySQL, refer Chapter 7 Materials and Resources folder in the Course Materials section. Answer the following questions. Use the operator, join syntax, and SQL statement asked. Otherwise, you will have zero () points for the question. 1. List the location ID, city, and state of each location in the United States. 2. List the first name and last name who is the child of employee ID 111, 118, 146, or 200. Use IN operator. Do not use OR operator. 3. List employee first name, last name, and salary whose salaries are between $5,000 and $7,000. Use BETWEEN operator. Do NOT use AND or OR operator. 4. List employee ID, employee name, and his/her job title. 5. List city name and department name, where is NOT located in Seattle. Use Not operator. 6. You want to track the region names within the Country table. List the region names without duplicating output rows. 7. List the employee's first and last name, whose last name ends with the letter "y". You have to use last name column in WHERE statement. 8. Minimum salary for each job increases to 10%. List job title, the old salary, and the new salary of each job title. 9. List department ID and department name. Sort the output by department name in descending order. 10.List the number of employees and the total salary of all employees. 11. Find the department name whose location (Country_ID) is CA. Use the IN operator and subquery in your SQL statement. 12. Find the employee ID, and first and last name, and salary for each employee whose salary is more than $10,000 or who works in the accounting department. Use UNION set operator. 13.List the country ID, country name, city, and state/province. Be sure all country names are included in the result, regardless of whether there is no matching city and state/province. You have to use outer join. 14.For each department, list department ID and name and the average salary of employees in the department, but consider only those departments for which the average employee salary is more than $10,000.00 15. List department name, city, state/province, and country name. ERD for MSU Human Resources Database jobs * job_id job_title min_salary max_salary dependents *dependent_id first_name last_name relationship employee_id countries country_id country_name region_id ++ employees * employee_id first_name last_name email phone_number hire_date job_id salary manager_id department_id departments department_id department_name location_id locations * location_id street_address postal_code OH city state_province country_id Note: * symbol indicates primary key Tables Countries Country ID AR AU BE BR CA CH CN DE DK Country Name Argentina Australia Belgium Brazil Canada Switzerland China Germany Denmark Egypt France Hong Kong Israel India Italy Japan Kuwait Mexico Nigeria Netherlands Singapore United Kingdom United States of America Zambia Zimbabwe Departments Department_ID Department Name 1 Administration 2 Marketing 3 Purchasing 4 Human Resources 5 Shipping IT 7 Public Relations 8 Sales 9 Executive 10 Finance 11 Accounting EG Region Name Americas Asia Europe Americas Americas Europe Asia Europe Europe Middle East and Africa Europe Asia Middle East and Africa Asia Europe Asia Middle East and Africa Americas Middle East and Africa Europe Asia Europe Americas Middle East and Africa Middle East and Africa Location ID 1700 1800 1700 2400 1500 1400 2700 2500 1700 1700 1700 FR HK IL IN IT JP KW MX NG NL SG UK US ZM ZW Dependents Dependent ID 1 2 3 4 First Name Last Name Penelope Gietz Nick Higgins Ed Whalen Jennifer King Johnny Kochhar Bette De Haan Grace Faviet Relationship Employee ID Child 206 Child 205 Child 200 Child 100 Child 101 Child 102 Child 109 6 7 8 9 10 11 12 13 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 Matthew Joe Christian Zero Karl Uma Vivien Cuba Fred Helen Dan Bob Lucille Kirsten Elvis Sandra Cameron Kevin Rip Chen Child Sciarra Child Urman Child Popp Child Greenberg Child Mavris Child Hunold Child Ernst Child Austin Child Pataballa Child Lorentz Child Hartstein Child Fay Child Baer Child Khoo Child Baida Child Tobias Child Himuro Child Colmenares Child Raphaely Child Russell Child Partners Child Taylor Child 110 111 112 113 108 203 103 104 105 106 107 201 202 204 115 116 117 118 119 114 145 146 176 Woody Alec Sandra Employees Emplyee_ID First Name Steven Last Name King Email steven.king@sqltutorial.org Phone Number Hire Date 515.123.4567 1987-06-17 Manager ID Department ID Job ID Salary 4 24000.00 100 9 9 101 102 103 104 6 6 105 6 Neena Lex Alexander Bruce David Valli Diana Nancy Daniel John Ismael Jose Manuel Luis Den Alexander Shelli Sigal 106 107 108 109 110 111 112 113 Kochhar De Haan Hunold Emst Austin Pataballa Lorentz Greenberg Faviet Chen Sciarra Urman Popp Raphaely Khoo Baida Tobias neena.kochhar@ sgltutorial.org 515.123.4568 lex.de haan@sqltutorial.org 515.123.4569 alexander hunolda sq Itutorial.org 590.423.4567 bruce.ersta sqltutorial.org 590.423.4568 david.austin@sqltutorial.org 590.423.4569 valli.pataballa sqltutorial.org 590.423.4560 diana.lorentz@sqltutorial.org 590.423.5567 nancy.greenberg sqltutorial.org 515.124.4569 daniel.faviet@sqltutorial.org 515.124.4169 john.chen@sqltutorial.org 515.124.4269 ismael.sciarra sqltutorial.org 515.124.4369 jose manuel.urman@sqltutorial.org 515.124.4469 luis.poppa sqltutorial.org 515.124.4567 den.raphaelya sqltutorial.org 515.127.4561 alexander khool sq tutorial.org 515.127.4562 shelli.baida@sqltutorial.org 515.127.4563 sigal.tobiasla sqltutorial.org 515.127.4564 5 5 9 9 9 9 9 7 6 1989-09-21 1993-01-13 1990-01-03 1991-05-21 1997-06-25 1998-02-05 1999-02-07 1994-08-17 1994-08-16 1997-09-28 1997-09-30 1998-03-07 1999-12-07 1994-12-07 1995-05-18 1997-12-24 1997-07-24 17000.00 100 17000.00 100 9000.00 102 6000.00 103 4800.00 103 4800.00 103 4200.00 103 12000.00 101 9000.00 108 8200.00 108 7700.00 108 7800.00 108 6900.00 108 11000.00 100 3100.00 114 2900.00 114 2800.00 114 6 6 6 6 6 10 10 10 10 10 10 3 3 3 3 115 116 117 13 13 13 118 119 120 121 122 123 126 Guy Karen Matthew Adam Payam Shanta Irene Himuro guy.himuro@sqltutorial.org Colmenares karen.colmenares@sqltutorial.org Weiss matthew.weissl sq tutorial.org Fripp adam.fripp@sqltutorialorg Kaufling payam.kauflingsaltutorial.org Vollman shanta vollman@sqltutorial.org Mikkilineni irene.mikkilineni@sqltutorial.org 515.127.4565 515.127.4566 650.123.1234 650.123.2234 650.123.3234 650.123.4234 650.124.1224 1998-11-15 1999-08-10 1996-07-18 1997-04-10 1995-05-01 1997-10-10 1998-09-28 13 13 19 19 19 19 18 2600.00 2500.00 8000.00 8200.00 7900.00 6500.00 2700.00 114 114 100 100 100 100 120 3 3 5 5 5 5 5 8 100 100 8 145 146 176 177 John Karen Jonathon Jack Russell john.russella sqltutorial.org Partners karen partners@sqltutorial.org Taylor jonathon.taylor@sqltutorial.org Livingston jack.livingston@sqltutorial.org 1996-10-01 1997-01-05 1998-03-24 1998-04-23 15 15 16 16 14000.00 13500.00 8600.00 8400.00 8 100 100 8 178 8 Kimberely Charles Grant Johnson kimberely granta sqltutorial.org charles.johnsona sqltutorial.org 1999-05-24 2000-01-04 16 16 7000.00 6200.00 100 100 179 8 Sarah Britney Jennifer Michael 192 193 200 201 202 203 204 205 206 Bell Everett Whalen Hartstein Fay Mavris Baer Higgins Gietz sarah.bell@sqltutorial.org britney.everett@sqltutorial.org jennifer.whalena saltutorial.org michael hartstein@sqltutorial.org pat.fay@sqltutorial.org susan.mavris@sqltutorial.org hermann.baer@ sgltutorial.org shelley.higginsla sqltutorial.org william.gietzsqltutorial.org Pat 650.501.1876 650.501.2876 SI5.123.4444 515.123.5555 603.123.6666 515.123.7777 515.123.8888 515.123.8080 515.123.8181 1996-02-04 1997-03-03 1987-09-17 1996-02-17 1997-08-17 1994-06-07 1994-06-07 1994-06-07 1994-06-07 17 17 3 10 11 8 12 2 4000.00 123 3900.00 123 4400.00 101 13000.00 100 6000.00 201 6500.00 101 10000.00 101 12000.00 101 8300.00 205 5 5 1 2 2 4 7 11 Susan Hermann Shelley William Jobs Job ID Job Title Min Salary Max Salary 1 Public Accountant 4200.00 9000.00 2 Accounting Manager 8200.00 16000.00 3 Administration Assistant 3000.00 6000.00 4 President 20000.00 40000.00 5 Administration Vice President 15000.00 30000.00 6 Accountant 4200.00 9000.00 7 Finance Manager 8200.00 16000.00 8 Human Resources Representative 4000.00 9000.00 9 Programmer 4000.00 10000.00 10 Marketing Manager 9000.00 15000.00 11 Marketing Representative 4000.00 9000.00 12 Public Relations Representative 4500.00 10500.00 13 Purchasing Clerk 2500.00 5500.00 Purchasing Manager 8000.00 15000.00 15 Sales Manager 10000.00 20000.00 16 17 18 19 Sales Representative Shipping Clerk Stock Clerk Stock Manager 6000.00 2500.00 2000.00 5500.00 12000.00 5500.00 5000.00 8500.00 Locations Location_ID Street Address 1400 2014 Jabberwocky Rd 1500 2011 Interiors Blvd 1700 2004 Charade Rd 1800 147 Spadina Ave Postal Code City State_Province Country_ID 26192 Southlake Texas US 99236 South San Francisco California US 98199 Seattle Washington US MSV 2L7 Toronto Ontario CA 2400 London UK 2500 2700 8204 Arthur St Magdalen Centre, The Oxford Science Park Ox99ZB Schwanthalerstr. 7031 80925 Oxford Munich Oxford Bavaria UK DE