DEPARTMENTS DeptNo DName Location STAFF StaffID + employ, Ename Job + + Mgr CUSTOMERS CustID Name service Address City State Zip AreaCode Phone ReplD HireDate Salary Comm DeptNo manage y create PRODUCTS ProdID Desc Price ITEMS OrdID "ItemID ProdID + are ORDERS OrdID 4 include OrderDate CustID ShipDate Qty For this assignment, use the Sport Shop Database to write the queries in the file attached, then use your outputs to answer the questions. Make sure you write all the queries first, and in order, before answering any of the assessment questions! *** ***MAKE SURE QUERIES CAN BE RAN IN SQL SERVER FOR ANSWER* Write the SQL scripts (queries) to answer the following questions. Use the Sports Shop database. 1. What products do we sell? Names and price. Highest price first. 2. I need the name and city of the customers from zip code ending in 301. Alphabetically by city. 3. How many customers do we have? Label the result column Total Customers. 4. Which department is in Chicago? List the name of the department and its location. 5. Someone's on the phone and wants to know what tennis balls we sell. I need the id number and price also. List cheapest first. 6. HR needs a list of employee names and the date they were hired for all employees hired in 2012. List by hire date within department, oldest first. 7. Which employees earn more than $2500? We don't need the President. Include the department name, employee name, and salary in the result - highest salary first. Keep all employees in the same department together. 8. List all employees that are in department 20. Include the location of the department and order the result by name. List the location first. 9. Display the employee name, salary, department name, and location of all employees who earned a commission. Highest commission first. Use Commission as the header for commissions. 10. Display the employee name and customer name for all employees who are in departments 20 and 30. List in alphabetical order of employee. 11. List the employees, their jobs and hire dates for those employees who work in Dallas. Order the output by most recent hire date. Include the location in the output. 12. List the number of days delay to ship each order out that was delayed (order date is not the same date as the ship date). Include order number and 'Shipment Delay' in the output - highest number first. What does the output tell you? 13. I was supposed to increase an employee's salary by 20% but I've forgotten which salary I was to increase. I think the employee's name started with the letter M. I also seem to recall that the employee worked in department 10. List any employees that match these conditions. Include department number and name so that I can check for sure. 14. List all employees and their managers alphabetically by manager name. Label the columns appropriately. 15. Which customers have placed orders? Display the customer name (alphabetically) only once if they have ordered multiple times. 16. Display the employee name, department number, and all the employee's colleagues that work in the same department. List alphabetically by department number first, then the Employee name, then the colleague names. Label the columns appropriately. 17. What is the annual salary for each employee? Include the employee's job, name, and annual salary. List the result by salary within job. Use appropriate column headings. (weekly salary is stored) 18. What is the difference between the highest and lowest salary? Label the result Salary Difference. 19. We finally hired a number of employees to work in our operations department. Add the following information to the database. ID Ename Job 8000 Richardson Super 8101 Kennedy Maint 8102 Fredricks Maint 8103 Edwards Maint Mgr HireDate Salary Comm DeptNo 783928-Oct-13 2530 Null 40 8000 25-Oct-13 1938 Null 40 8000 23-Oct-13 1978 Null 40 8000 26-Oct-131 950 Null 40 20. What is the average salary for the operations staff? Label output Average Salary for Operations. Show the output with 2 decimal places