/*

It is your responsibility to provide a meaningful column name for the return value of the function.

These statements will NOT use GROUP BY or HAVING. Those keywords are introduced in the next module. One cell

result sets (one column and one row) do not need to have an ORDER BY clause.

Recall that sales to resellers are stored in the FactResellerSales table and sales to individual customers

are stored in the FactInternetSales table. When asked to find Internet sales or sales to 'customers', you

will be using the FactInternetSales table. */

-- Do not remove the USE statement

USE AdventureWorksDW2012;

/* In the parts of question 1 we are looking at some customer demographics. Be sure give each derived

field an appropriate alias.*/

-- 1.a. (3) Find the count of customers who are married. Be sure give each derived field an

-- appropriate alias.

--1.b. (2) Check your result. Write queries to determine if the answer to 1.a. is correct.

-- You should be doing proofs for all of your statements. This is a reminder to check our work.

--1.c. (3) Find the total children (sum) and the total cars owned (sum) for customers who are

-- married and list their education level as High School (use EnglishEducation).

--1.d. (2) Find the total children, total cars owned, and average yearly income for customers who are

-- married and list their education level as High School (use EnglishEducation).

/* In the next set of questions you are looking for sales to resellers and sales to individual customers.

We will look at the information by total sales and then for sales by geographic locations for specific time

frames. Recall that sales to business customers (Resellers) is stored in the FactResellerSales table and sales

to individuals (Customers) is stored in the FactInternetSales table.*/

--2.a. (2) List the total dollar amount (SalesAmount) for sales to Resellers. Round to two decimal places.

--2.b. (3) List the total dollar amount (SalesAmount) for 2008 sales to resellers who have an address in

-- the state/province of Saarland in Germany. Show only the total sales--one row, one column--rounded

-- to two decimal places.

--3.a. (2) List the total dollar amount (SalesAmount) for sales to Customers. Round to two decimal places.

--3.b. (3) List the total dollar amount (SalesAmount) for 2005 sales to customers physically located in

-- Queensland, Australia. Show only the total sales--one row, one column--rounded to two decimal places.

/* In the next group of requests we are answering questions about bikes. We are asked to create

information about bikes either by a specific subcategory or in total. It is important here that

you recall how to find bikes and subcategories of bikes within the tables. */

--4. (4) List the average unit price for a touring bike sold to customers. Round to two

-- decimal places.

--5. (5) List bikes that have a list price less than the average list price for all bikes. Show

-- product alternate key, English product name, and list price. Order descending by list price.

--6. (4) List the lowest list price, the average list price, the highest list price, and product count

-- for road bikes.

/* In the next requests we are digging into the products sold by AdventureWorks by finding the highest

priced products, the profit or loss on products sold to dealers, and the products with the largest

profit on dealer price. */

-- 7. (4) List the product alternate key, product name, and list price for the product(s)

-- with the lowest List Price. There can be multiple products with the lowest list price.

-- 8.a. (4) List the product alternate key, product name, list price, standard cost and the

-- difference (calculated field) between the list price and the standard cost for all product(s).

-- Show all money values to 2 decimal places. Sort on difference from highest to lowest.

-- 8.b. (3) As we learned in prior modules, some products are not intended to be sold and some products in the

-- table have been updated and are no longer sold. Follow the same specifications as 8.a. for this statement.

-- Also eliminate from your list all products that are not intended for sale and those no longer for sale.

-- 8.c. (3) Use the statement from 8.b. and modify to find the currently sold product(s) with the largest

-- difference between the list price and the standard cost of all currently sold products. Show all

-- money values to 2 decimal places. Hint: There will be records in the results set.

--9. (2) In your own words, write a business question that you can answer by querying the data warehouse

-- and using an aggregate function. Be sure to write your question as a comment.

-- Then write the complete SQL query that will provide the information that you are seeking.