LQuery-Spring 2018 Extra Credit (Protected View)- Word This is an extra credit assignment, which is designed to let you have a hands-on experience writing advanced SQL query statements -subqueries and complex joins. Subqueries: one way to nest or a cascade query is to stick a query in the where clause, find the names of employees worked at the department Rescarch. This is a powerful way to take advantage of the fact that any SOL query returns a table, which can then be the starting point of another SQL query Complex Joins: the where clause can become quite complex with many joins and related and' and or conditions . Consider the following database schema for Greensboro Shop. DepartmentsideptiD string, name string, location string, telNamber strimg, budget real manager string) Supplsens(supID strng, name string, addeess string, eity string, astate string) Orders(dD string.oedDate date, shipAddress string) Emplayee s(emplD string, fintName string, lastName string,sastrng, telNumber string, salary real) Carries(deptiD atring, itemD string) Workaln(depdD string. emplD string) In this assignment, your task is to write SQL queries that answer the following questions based on the database GShop in the ANSI-style JOIN. Each question should be answered by one SQL statement only. The result of each statement must not contain duplicates, but you should use the SQL keyword distinct only when necessary. For clarity, each output column should be properly renamed. For example, the output column of name from the table departments may be renamed as Department Name Please put your SQL query statements and their results into a SQL script file. Run your SQL statements on the MySQL Database Server. Make sure that your SQL script file does not contain any syntax esnors befoxe sobmitting to the Assignment Lank-Extra Credit 1. Fiand the ids of items that are carried by Junioe Clothing and Giul's Teen Clothing. Note that Gisl's Teen Clothing and Junioe Clothing are department names You mast use a nested query with two SELECT statements name ofe man that its average sale price is 40% more ean anetage unt Find cost 2. 3. Find the name of items that are camied jby Jenior Clothing but not Giel's Teen Clothing Note that Garl's Teen Clothing and Junsor Clothing are department names You must use a nested query with three SELECT statements