Reference no: EM13718188

Fully qualify all your field names. Rename and order as appropriate.

For this assignment, you'll have to answer some fairly complex queries. I'm going to ask the question first, then ask sequence of questions to lead you up to the big query. Hopefully, you'll get some practice with how to approach a bigger, more complex query.

How many different customers have ordered products in each category? Two columns, categoryname and count of distinct customers. Make sure to include EVERY category.

1. Show the CategoryName and ProductName of every product. Order by CategoryName
2. Copy #1. Show CategoryName, ProductName, and Discount for every product on every order detail. (JOIN in the [Order Details] table)
3. Copy #2.Show the CategoryName, ProductName, and CustomerID for every Order Detail. (JOIN in the Orders table, and show the CustomerID from the Orders table)
4. Copy #3. Make this change. In the FROM statement, separate out the JOINs of Products, [Order Details] and Orders from the Categories. So it will look something like this:

FROM        Categories 
INNER JOIN (  Products 
INNER JOIN    [Order Details] ON ...
INNER JOIN    Orders          ON ... 
           )                  ON Categories.CategoryID = Products.CategoryID

1. Copy #4. Now make sure that EVERY Category is shown, even those that don't match up with a Product/Order. (Just change one of te INNER JOINs to a LEFT JOIN. You should see some NULLs appear in ProductName, Discount,etc.)
2. Copy #5. Show the CategoryName, and the count of the number of CustomerIDs.
3. Copy #6. Show the CategoryName, and the DISTINCT count of the number of CustomerIDs. (This answers the original question)

Give a count of orders by sales region and category for the year 1997. Three columns, Region.RegionDescription, CategoryName, CountOfOrders.

1. Show a list of Regions and the names of employees that have territories in those regions.
2. Copy #1. Show a list of Regions, and OrderIds from Orders in 1997 from each region.
3. Copy #2. Show a list of Regions, and ProductIDs from Orders in 1997 from each region.
4. Copy #3. Show a list of Regions, and the Category Name of Products from Orders in 1997 from each region.
5. Copy #4. Show the Region, Category, and Count of Orders in 1997.
6. Copy #5. Show the DISTINCT count of orders.