/*************************  EXAMPLE AND INSTRUCTIONS ********************   NORTHWIND

Using the example below based on a dummy table and its values and after watching the video on aggregates answer the following questions (Each question carries equal points of 10 points each):

Various Aggregate Functions

1) Count()

2) Sum()

3) Avg()

4) Min()

5) Max()

 

Now let us understand each Aggregate function with a example:

Id     Name     Salary

-----------------------

1       A        80

2       B        40

3       C        60

4       D        70

5       E        60

6       F        Null

 

Count():

 

Count(*): Returns total number of records .i.e 6.

Count(salary): Return number of Non Null values over the column salary. i.e 5.

Count(Distinct Salary):  Return number of distinct Non Null values over the column salary .i.e 4

 

Sum():

 

sum(salary):  Sum all Non Null values of Column salary i.e., 310

sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

 

Avg():

 

Avg(salary) = Sum(salary) / count(salary) = 310/5

Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

 

Min():

 

Min(salary): Minimum value in the salary column except NULL i.e., 40.

Max(salary): Maximum value in the salary i.e., 80.

 

***********************************************/

 

/* Question 1: Select the total number of Products in the Products table */

/* Question 2: Select the total number of Shippers for Orders (Hint: use COUNT(ShipVia) as TotalProducts to return non-null counts. */

/* Question 3: Select the total number of DISTINCT Shippers for Orders */

/* Question 4: Select all non-null total price from Order Details where OrderID = 10248 */

/* Question 5: Select all non-null distinct non-null freight charges from the Orders table */

/* Question 6: Select all non-null distinct total products that were ordered from the Order Details table */

/* Question 7: Select average unitprice of Products */

/* Question 8: Select ProductName and UnitPrice pf Products that have an above average price: 

(Hint: Use a sub-query similar to one done during class exercise to first write sub-query to select Average UnitPrice of Products and

then using a where clause (UnitPrice > (subquery)) as part of the outer query select ProductName and UnitPrice)

*/

/* 

Question 9a: Select Product having the maximum UnitPrice 

Question 9b: Select Product having the minimum UnitPrice

*/

/* Question 10: Select CompanyName, count(OrderID) as NumberOfOrders grouped by shippers

*/