Using Access

Questions 1. Design the query to do the following. Save the queries as 1a, 1b, 1c, -. Note that you may need to modify the SQL view for some of these queries. a. List the ID, first name and last name of employees who earn more salary than their supervisor and the amount by which the employee's salary is higher. b. Enable the user to enter the name of a product and list the ID and product names of products having a different Quantity Per Unit from that of the product entered. C. Enable the user to enter the first name and last name of an employee and list the ID, first name, last name and hire date of all employees hired within one year after the employee entered was hired. For example, if the name entered was hired on March 20, 2020, all employees hired from then up until March 20, 2021 must be listed. d. List all Customer IDs, the Order ID of the orders they have submitted if any, and the employee ID that handled the order. All employees (including those who have never handled an order) and all customer IDs (including those who have never submitted an order) must also be listed. The list must be ordered in ascending order of the employeeld, and then in ascending order of the order (if the employee is listed more than once). A sample of the expected result from a different dota set is provided below: Customer ID ABCD has never submitted an order and employee ID 55 and employee ID 4 have never handled an order. Customerid|Orderid Employeeld ABCD DCBA 11231 1 pcBA 10101 2 11123) 2 55 1 0 5 5 e. List each product name, the number of units in stock for the product and the total number of products that has same units in stock as the product. The list must be in ascending order of the units in stock values and then in ascending order of the product name (if more than one product has the same units in stock). A sample of the expected result is provided below. Product Name Units in Stock Products with similar Unitsinstock Alice Mutton Chef Anton's Gumbo Mix 0 Gorgonzola Telino 5 Perth Pasties Thringer Rostbratwurst Sir Rodney's Scones 3 1 Longlife Tofu Louisiana Hot Spiced Okra Rgede sild Northwoods Cranberry Sauce 6 2 Scottish Longbreads 0 5 0 5 4 4 5 2 2 1 6 2 f. List the names of products and the total number of times the product has been ordered by customers in a specific country. The name of the country must be provided by the user and the products with the highest quantity sold in that country must be listed first. Questions 1. Design the query to do the following. Save the queries as 1a, 1b, 1c, -. Note that you may need to modify the SQL view for some of these queries. a. List the ID, first name and last name of employees who earn more salary than their supervisor and the amount by which the employee's salary is higher. b. Enable the user to enter the name of a product and list the ID and product names of products having a different Quantity Per Unit from that of the product entered. C. Enable the user to enter the first name and last name of an employee and list the ID, first name, last name and hire date of all employees hired within one year after the employee entered was hired. For example, if the name entered was hired on March 20, 2020, all employees hired from then up until March 20, 2021 must be listed. d. List all Customer IDs, the Order ID of the orders they have submitted if any, and the employee ID that handled the order. All employees (including those who have never handled an order) and all customer IDs (including those who have never submitted an order) must also be listed. The list must be ordered in ascending order of the employeeld, and then in ascending order of the order (if the employee is listed more than once). A sample of the expected result from a different dota set is provided below: Customer ID ABCD has never submitted an order and employee ID 55 and employee ID 4 have never handled an order. Customerid|Orderid Employeeld ABCD DCBA 11231 1 pcBA 10101 2 11123) 2 55 1 0 5 5 e. List each product name, the number of units in stock for the product and the total number of products that has same units in stock as the product. The list must be in ascending order of the units in stock values and then in ascending order of the product name (if more than one product has the same units in stock). A sample of the expected result is provided below. Product Name Units in Stock Products with similar Unitsinstock Alice Mutton Chef Anton's Gumbo Mix 0 Gorgonzola Telino 5 Perth Pasties Thringer Rostbratwurst Sir Rodney's Scones 3 1 Longlife Tofu Louisiana Hot Spiced Okra Rgede sild Northwoods Cranberry Sauce 6 2 Scottish Longbreads 0 5 0 5 4 4 5 2 2 1 6 2 f. List the names of products and the total number of times the product has been ordered by customers in a specific country. The name of the country must be provided by the user and the products with the highest quantity sold in that country must be listed first