CIS276 Lab2 using Microsoft SQL Server 2012

1. How are all of the salespeople doing? Projection: SALESPERSONS.EmpID, SALESPERSONS.Ename, SUM(ORDERITEMS.Qty*INVENTORY.Price) Instructions: Display the total dollar value that each and every sales person has sold. List in dollar value descending. 2. What is the dollar value for each of every order order? Projection: ORDERS.OrderID, SUM(ORDERITEM.Qty*INVENTORY.Price) Instructions: Display the dollar value for each and every order. List in dollar value descending. 3. Which orders contain widgets? Projection: ORDERS.OrderID, ORDERS.SalesDate Instructions: The word ''widget'' may not be the only word in the part description (use a wildcard). Display the orders where a ''widget'' part appears in at least one ORDERITEMS rows for the order. List in sales date sequence with the newest first. Do not use the EXISTS clause. 4. Which orders contain widgets? Projection: ORDERS.OrderID, ORDERS.SalesDate Instructions: The word ''widget'' may not be the only word in the part description (use a wildcard). Display the orders where a ''widget'' part appears in at least one ORDERITEMS rows for the order. List in sales date sequence with the newest first. Use the EXISTS clause. 5. What are the gadget and gizmo only orders? Projection: OrderID Instructions: The words ''gadget'' and ''gizmo'' may not be the only word in the part description. Code accordingly. List in ascending order of OrderID. 6. Who are the profit-less customers? Projection: CUSTOMERS.CustID, CUSTOMERS.Cname Instructions: Display the customers that have not placed orders. Show in customer name order (either ascending or descending). Use the EXISTS clause. 7. What is the average dollar value of an order? Projection: "Orders Average", "OrderItems Average"Instructions: Columns to display are determined by whether your output is horizontal (two columns) or vertical (one column holding both averages on separate lines with a literal string telling what each average is). There are two possible averages on this question. There are a certain number of orders in the ORDERS table, but not all of these orders are in the ORDERITEMS table. Write one query that produces both averages. To get this answer, you need to add up all the order values and divide by the number of orders. The number of orders in the ORDERS table is different from the number of orders in the ORDERITEMS table.Do not use AVG(). 8. Who is our most profitable salesperson? Projection: SALESPERSONS.EmpID, SALESPERSONS.Ename, (SUM(ORDERITEMS.Qty*INVENTORY.Price) - SALESPERSONS.Salary) Instructions: A salesperson''s profit (or loss) is the difference between what the person sold and what the person earns: ((SUM(ORDERITEMS.Qty*INVENTORY.Price) - SALESPERSONS.Salary)). If the value is positive then there is a profit, otherwise there is a loss. The most profitable salesperson, therefore, is the person with the greatest profit or smallest loss. Display the most profitable salesperson (there can be more than one). 9. Who is our second-most profitable salesperson?Projection: SALESPERSONS.EmpID, SALESPERSONS.Ename, (SUM(ORDERITEMS.Qty*INVENTORY.Price) - SALESPERSONS.Salary)Instructions: A salesperson''s profit (or loss) is the difference between what the person sold and what the person earns:((SUM(ORDERITEMS.Qty*INVENTORY.Price) - SALESPERSONS.Salary)). If the value is positive then there is a profit, otherwise there is a loss. The most profitable salesperson, therefore, is the person with the greatest profit or smallest loss. The second-most profitable salesperson is the person with the next greatest profit or next smallest loss. Display the second-mostprofitable salesperson (there can be more than one). Do not hard-code the results of a previous query - that simply creates a data-dependent query. What are the discounts for each line item on orders of five or more units?Projection: Orderid, Partid, Description, Qty, "Unit Price", "Original Price", "Quantity Deduction", and "Final Price"Instructions: We have decided to give quantity discounts to encourage more sales. • If an order contains five or more units of a given product we will give a two percent discount for that line item. • If an order contains ten or more units of a given product we will give a five percent discount on that line item.Produce output that prints the OrderID, PartID, Description, Qty ordered, unit Price, the total original Price(Qty ordered * Price), the total discount value (shown as money or percent), and the total final Price of the product after the discount. Display only those line items subject to the discount in ascending order by the OrderID and PartID. Use the CASE statement.