question

maddiestupinski avatar image
maddiestupinski asked

SQL Statement Generation

ER Diagram Attached. - Show the number of years between the youngest and most senior employee (according to hire date). To accomplish this, make use of the months_between function and round to the nearest whole number. Name the field “Old Vs. Young”. - Show the customer name, the “Total Sales”, number of orders they’ve placed as “Orders”, and finally the average of total sales per month they’ve been a member. You’ll need to use the shipped field in the order1 table to determine when a customer placed their first order. Call this fourth field “Avg_per_month” and sort by this field descending. - Show the customer name, order number, and employee name for orders where the employee who performed the order was in the top 3 salaries. - Customers get a discount if their zip code starts with a 4 or a 5. The discount is located in the Part table. This should be a percentage discount off the regular price. Show the customer name, order number, “Regular Order Total” and the “Discounted Order Total” for all orders of customers who have a zip code starting with a 4 or a 5, and who placed their order with an employee from the IT department. - List the employee name from the IT department, along with the number of years of experience they had on the day they handled their first order. You can use the shipped field in the order1 table to determine when an employee from the IT department handled their first order. Round to nearest whole number. Order your list by “Years Experience” at time of their first order ascending. - Show the part name, regular price, discount price, and number of individual customers who have purchased that part. Order the results by the highest number of customers descending. - Show customers (all fields) whose total order is more than the average of all customer orders. Use revenue here for your comparison. - Write a query to get the product(s) whose total order is the lowest in the organization. Base the total order on revenue. - Write a query to show the customer details and revenue generated from each customer in descending order.
sqloracle
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Homework? From Indiana?
1 Like 1 ·
sjimmo avatar image sjimmo commented ·
Please show us what you have tried for each one.
1 Like 1 ·

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.