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

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. (You will notice that by doing a select * from Orders there are ShipVia foreign keys as NULLs. As a result the count will exclude it while counting.) */

/* Question 3: Select the total number of DISTINCT Shippers for Orders (Hint: use COUNT(Distinct ShipVia) to return non-null counts. (You will notice that by doing a select * from Orders there are ShipVia foreign keys as NULLs and there are duplicate ShipVia. As a result the count distinct will exclude both while counting.) */

/* Question 4: Select all non-null total price from Order Details where OrderID = 10248 (Hint Use SUM(UnitPrice * Quantity) as OrderTotal) */

/* Question 5: Select all non-null distinct non-null freight charges from the Orders table (Hint: Use SUM(DISTINCT Freight) as TotalFreight where CustomerID = ‘VINET’ */

/* Question 6: Select all non-null distinct total products that were ordered from the Order Details table (Hint: Use COUNT(DISTINCT ProductID) as TotalProducts */

/* 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

(Hint: Use inner join to join Orders and Shippers and then use Group By CompanyName refer to the video and lecture notes)

*/

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.