[Solved] SOLVED:CIS276 Lab1 using Microsoft SQL Server 2012

CIS276_Lab1Which parts cost between ten and seventeen dollars (inclusive)?Projection: INVENTORY.PartID, INVENTORY.Description, INVENTORY.Price Instructions: Display the partid, description, and price of all parts where the price is between the numbers given (inclusive). Sort on descending order of price. Use the BETWEEN clause in your selection criteria.CIS276_Lab1.Who earns less than or equal to $1,750?Projection: SALESPERSONS.Ename, SALESPERSONS.SalaryInstructions: Display the name and salary for all salespersons whose salary is less than or equal to $1,750. Sort on salary low to high.

CIS276_Lab13. What is the highest priced part and what is the lowest priced part?Projection: INVENTORY.PartID, INVENTORY.Description, INVENTORY.Price Instructions: Display the partid, description, and price for the highest and lowest priced parts in our inventory. Sort on partid ascending. ONE QUERY!CIS276_Lab14. Which part descriptions begin with the letter G (or g)?Projection: INVENTORY.PartID, INVENTORY.Description Instructions: Display the partid and description of all parts where the description begins with the letter G (thats either G or g). Show the output in descending order of price.CIS276_Lab15. Which parts need to be ordered from our supplier now?Projection: INVENTORY.PartID, INVENTORY.Description, and (INVENTORY.ReorderPnt INVENTORY.StockQty) Instructions: Display the partid and description of all parts where the stock quantity is less than the reorder point. For each part where this is true also display the amount that the stock quantity is below the reorder point. Display in descending order of calculated differences.

CIS276_Lab16. Which sales people have NOT sold anything? Subquery version.Projection: SALESPERSONS.EnameInstructions: Display all employees that are not involved with an order, i.e. where the empid of the salesperson does not appear in the ORDERS table. Display the names in alphabetical order. Do not use JOINs use subqueries only. You should be able to write both a correlated and a non-correlated subquerybut only one query is needed to answer the questionCIS276_Lab17. Which sales people have NOT sold anything? JOIN version.Projection: SALESPERSONS.Ename Instructions: Display all employees that are not involved with an order, i.e. where the empid of the sales person does not appear in the ORDERS table. Display the names in alphabetical order. Do not use sub-queries use only JOINs.CIS276_Lab18. Who placed the most orders?Projection: CUSTOMERS.CustID, CUSTOMERS.Cname, COUNT(DISTINCT ORDERS.OrderID) Instructions: Display the customer ID, customer name, and number of orders for the customer who has placed the most orders, i.e. the customer(s) who appear(s) the most times in the ORDERS table. Sort on orders then IDs.CIS276_Lab19. Who ordered the most quantity?Projection: CUSTOMERS.CustID, CUSTOMERS.Cname, SUM(ORDERITEMS.Qty)Instructions: Display the customer ID, customer name, and total quantity of parts ordered by the customer who has ordered the greatest quantity. For this query you will sum the quantity for all order items of all orders associated with each customer to determine which customer has ordered the most (largest) quantity. Sort on quantity high to low.CIS276_Lab110. Who ordered the most value?Projection: CUSTOMERS.CustID, CUSTOMERS.Cname, and SUM(INVENTORY.Price * ORDERITEMS.Qty) Instructions: Display the customer id, customer name, and total value of all orders for the customer(s) whose orders total the highest value. To find the total value for a customer you need to sum the price times quantity for each line item of each order associated with the customer. Sort on valuehigh to low.