Description
1. Display the employee number, full employee name, job title, and hire date of all employees hired in September with the most recently hired employees displayed first.
. The company wants to see the total sale amount per sales person (salesman) for all orders. Assume that online orders do not have any sales representative. For online orders (orders with no salesman ID), consider the salesman ID as 0. Display the salesman ID and the total sale amount for each employee. Sort the result according to employee number. 3. Display customer Id, customer name and total number of orders for customers that the value of their customer ID is in values from 35 to 45. Include the customers with no orders in your report if their customer ID falls in the range 35 and 45. Sort the result by the value of total orders. 4. Display customer ID, customer name, and the order ID and the order date of all orders for customer whose ID is 44. a. Show also the total quantity and the total amount of each customer’s order. b. Sort the result from the highest to lowest total order amount. 3 | P a g e 5. Display customer Id, name, total number of orders, the total number of items ordered, and the total order amount for customers who have more than 30 orders. Sort the result based on the total number of orders. 6. Display Warehouse Id, warehouse name, product category Id, product category name, and the lowest product standard cost for this combination. • In your result, include the rows that the lowest standard cost is less then $200. • Also, include the rows that the lowest cost is more than $500. • Sort the output according to Warehouse Id, warehouse name and then product category Id, and product category name. 7. Display the total number of orders per month. Sort the result from January to December. 4 | P a g e 8. Display product Id, product name for products that their list price is more than any highest product standard cost per warehouse outside Americas regions. (You need to find the highest standard cost for each warehouse that is located outside the Americas regions. Then you need to return all products that their list price is higher than any highest standard cost of those warehouses.) Sort the result according to list price from highest value to the lowest. 9. Write a SQL statement to display the most expensive and the cheapest product (list price). Display product ID, product name, and the list price. 10. Write a SQL query to display the number of customers with total order amount over the average amount of all orders, the number of customers with total order amount under the average amount of all orders, number of customers with no orders, and the total number of customers. See the format of the following result. Example Submission — *********************** — Student Name: Your Name — Student1 ID: ######### — Date: The date of assignment completion — Purpose: Assignment 1 – DBS311 — *********************** — Question 1 – Copy the question from above (excluding result set) — Q1 SOLUTION — 5 | P a g e SELECT * FROM TABLE1; — Question 2 – Copy the question from above (excluding result set) — Q2 SOLUTION — SELECT * FROM TABLE2;
