SQL:NORTHWIND

Query #1: Display the following customer info company name contact name SQL is fun + c contact title for customers in the following countries: (United States, Mexico, Spain, France, or Canada) and whos contact title indicates the contact title is in a manager role (the customers contact title has the word manager or owner in the title). Sort the result set by country in descending order and then by company name in ascending order.

#2: Using the same criteria from Query #1, display the customers who have not placed orders. You MUST use an outer join to produce the result set. Query

#3: Using the same criteria from Query #1, display the customers who have not placed orders. You MUST use a sub-query to produce the result set.

Query #4: Display every employee who has at least one territory assigned to them. Display the employees (LastName, FirstName+g) as EmployeeName, the territories they are responsible for (TerritoryDescription) as Territory, and their immediate supervisor (LastName, FirstName) as Supervisor. If they do NOT have an immediate supervisor, DO NOT display that employee. Sort the result set by Territory and then by EmployeeName. (hint: this query needs 3 tables plus a table that is joined to itself) Query

#5: Display Have In Stock as a calculated column, Product Name, and Units In Stock from the products table for all products that have an Units In Stock amount greater than zero. In the same result set display Not In Stock as a calculated column, Product Name, and 0 (zero) from the products table for all products that have an Units In Stock amount equal to zero. Order the result set by product name. (hint: you must use the union operator) Query

#6: Display Category Name, Product Name, Customer Company Name and TotalPrice which is a calculated column (UnitPrice * Quantity * (1 Discount)). Use the Order Details table to get the UnitPrice. Only return rows that have a category name of Produce, Seafood, or Beverages and have a TotalPrice greater than or equal to $200+99. Sort the result set by Category Name and then by Product Name and then by TotalPrice from highest to lowest. (hint: this query needs 5 tables)

Query #7: Using the same criteria from Query #6, display the number of line items (Order Details table) for each category and the average TotalPrice for each category.