Use Northwind /* 1. Create a stored procedure that produces a list of all Suppliers in a specified Region. The procedure should accept 1 input parameter: Region. The supplier list produced by the procedure should list, for each supplier: SupplierID, CompanyName, Region, and Country. The list should be ordered by CompanyName. The procedure should also provide an OUTPUT parameter that returns the number of suppliers in the specified Region. Note: No error handling or commenting is required this time - just basic functionality. */ /* 2. Execute the above stored procedure for the Qubec region. Make sure to display the value of the output parameter. You must set up a variable to hold the OUTPUT parameter, execute the procedure and then use a Print statement to display the value of the OUTPUT parameter to the Message window. */ /* 3. First: write an insert statement to insert yourself into the Employees table with a hiredate of today's date. Second: Write a Select statement that will retrieve all the Employees who were hired after '1/1/1993' that have not ever sold anything (that means they do not have an order in the orders table). Hint: please review left joins as necessary. */ /* 4. Create a new permanent table called SuppliersTest that has the same schema (same columns, same datatypes) as the Suppliers table. Populate the new table with a copy of the rows from the existing Suppliers table. When you are finished the data in the SuppliersTest table should be the same as the data in the Suppliers table. You must accomplish this task using only one single SQL statement. The Create table statement canNOT be used. Include a statement to drop your table. */ /* 5. List all orders for the month of December 1997 that do NOT contain a line item for product 59, "Raclette Courdavault". List the Order ID, Order Date, Customer ID and Customer Name for each order. (Note: Your answer will have 45 rows, assuming you have made no significant, permanent changes to Northwind's Products, Customers and Orders tables.) */ /* 6. Your boss gives you an assignment: Find our best selling product. You ask questions and determine that the boss would really like to see the top 5 best selling products and that by "best selling" he means those who have the most number of sales (ie., Orders), and not those that have sold the most quantity of products. */ /* 7. Consider order # 10248. Write a script below that will: * write the select statement to list this order in the Orders table * write another separate select statement to list all line items for Order 10248 in the Order Details table. * Write a statement to begin a transaction. * Write statement(s) to delete order 10248 and all its line items. * Write two statements: one to commit the transaction (comment that one out) and one to rollback the transaction */ /* 8. (You had to know this was coming :-) ) Turn your script in the previous question (#7) into a stored procedure that will take any order number as its parameters and then delete and remove any and all associated line items. Include a print message that indicates the sproc has completed successfully. Include error handling (try-catch blocks) within the procedure to catch the error if the deletes do not succeed. As always include proper commenting. Include a call to your sproc that will also use Try-Catch to handle any errors thrown */ /* 9. Create a query that lists customer names together with the first date that they placed an order. Note: You only need to include customers who have actually placed orders. Include CustomerID, CustomerName and first order date as columns in your view. */ /* 10. a) Create a view from the query in question 9. b) Write a simple Select statement that uses this view to retrieve customers placing orders prior to 2-15-1999. */ /* 11. Write a single query that creates a phone list of all Suppliers, Customers, and Employees with only two columns: Name and Phone. */ /* 12. Write a Select statement that displays all Northwind products. Use a Case to add an extra column to the output that indicates categories of pricing using the following categorization: when UnitPrice < 50.00 then 'small ticket' when UnitPrice < 150.00 then 'medium ticket' when UnitPrice < 250.00 then 'large ticket' else 'big ticket' */