Assignment 4 1. Model the following challenge: Gabriella and Natalia decided to put their culinary skills to commercial use. Both were energetic and imaginative, in addition to being talented at food preparation. Moreover, they were both personable and well liked among their friends and neighbors. Simply Delicious has several customers who request catering services on a regular basis. This business has also irregular customers and those trying the service for the first time. Gabriella and Natalia develop estimates for each job as follows: the type of food, its preparation, and the service are first determined. The job is then priced on a per-serving basis, and this amount is multiplied by the maximum number of expected guests. This information is recorded on a job estimate sheet and then entered into a computer. Twenty percent of the revenues from each job are earmarked for employee compensation. If more than one employee works a job, the 20 percent is divided among them. Employees are paid every two weeks. Gabriella and Natalia are responsible for purchasing the food supplies. Gabriela keeps track of who works on each job and determines the amounts to be paid to each employee. Natalia takes responsibility for paying all other bills. All purchases are made on account with various vendors in the area. Gabriela usually pays bills within 30 days of billing unless there is a discount available for early payment Employees are assigned to each job according to skills and availability. On occasion, additional part-time help is required, but drafting members from the families usually satisfies those needs. Gabriella usually makes job assignments at least one week in advance. When the job is completed, the supervisor (who could be Gabriella, Natalia or an employee) completes a job sheet listing the food used, who worked on the job, and their hours. 2. Model the following challenge: A show ticket is purchased from an agent, the box office, or the Internet. A ticket has a description, an event, a date and a price. An agent has a name and a phone number. The box office has an address and a phone number. The Internet has a URL address. Write SQL statements that will produce the desired output. 1. For each employee display the employee name and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number. 2. Create a query that will display the employee name and commission amount. If the employee does not earn commission, put “No Commission.”. Label the column COMM. 3. Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, Average, and the difference between the highest and lowest salaries respectively. Round your results to the nearest whole number. 4. Determine the number of managers without listing them. Label the column Number of Managers. 5. Write a query that will display the employee name, department name for all employees that earn a commission. Write a query to display the name, job, department number, and department name for all employees who work in Seattle. 7. Display the employee name and employee number along with their manager’s name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. 8. Modify the previous query to display all employees including those who have no manager. 9. Create a query to display the name and hire date of any employee hired before their manager.