Min Function. To find the value of lowest DensityPerKmSq.

To find the value of lowest DensityPerKmSq, query is below.

Max Function. To find the value of highest DensityPerKmSq.

To find the value of highest DensityPerKmSq, query is below.

If you want to find the country with highest DensityPerKmSq, please refer the post on "Max Function.To find the country with highest DensityPerKmSq"

To find the Nth densest Country.

Consider for example, To find the 6th densest country, query is below.

Row Number,Rank and Dense Rank Functions

We can now see about 'Row_Number' , 'Rank' and 'Dense_Rank' Functions.Query is below.

Hope the query can be understood with the below explaination.I have partitioned the output by continent.Say for example,within the continent of America, the Row Number (,Rank and Dense Rank) are distributed among the countries of USA and Brazil. Similarly, the Row Number (, Rank and Dense Rank) are once again starting from the beginning for the other countries of Asia, Australia and Europe.We can see that by default the countries are ordered by ascending order by the column 'country'.

The difference between 'Row_Number' , 'Rank' and 'Dense_Rank' can be understood by our same countrytable example by removing the partition by clause. Query is below.

Please note that here the rows are ordered by continent and not by country. Now the difference between 'Row_Number' , 'Rank' and 'Dense Rank' are clearly visible. There are two rows with continent name 'America'. Even though there is a tie(between both the rows having America as continent), both have different Row Numbers but same Ranks and Dense Ranks. Since there are two countries with same Continent 'America' ,the Rank 2 is skipped and since there are five Asian countries, Ranks 4, 5, 6, 7 are skipped(There are four European countries. Hence ranks 10, 11, 12 are skipped). Dense Rank as the name suggests, there is no gap between the Ranks.

It is also possible that if there is a tie with same continent,then the 'Ranks' (, 'Row_Numbers' and 'Dense_Ranks') can consider the other columns for numbering.Consider the below query.

For Rank Column, We could see the columns are ordered by continent in ascending order(America, Asia, Australia, Europe). If there is a tie, say two rows with continent 'America', then rank is given by countrywise in desending order(USA and then Brazil).Similarly Asian contries are also ordered as Sri Lanka, Pakistan, Nepal, India, China(Descending order). European countries are also in descending order(UK, Italy, Germany, France).     



  

Avg Function. Group By, Having Keywords.

To find the average DensityPerKmSq for each continent,Query is below.

Note :  Please note that for continent Europe,eventhough there are 4 countries, the average is taken for only 3 countries. This is because the country France has Null Value for DensityPerKmSq (which means that it is an unknown value). Hence it is not taken into account.

Further more ,Please note that eventhough for continent Europe the Average value for DensityPerKmSq is 228.67,by default the value is rounded off to the lower value of 228(and not to 229)

Further more, Please note that apart from the aggregate function (Aggregate function is avg(a.DensityPerKmSq)), the column in select query is a.continent. So it should be in group by clause.

The having clause is equivalent to where clause but the having clause is always used along with group by clause.

Max Function in Corelated subquery. To find the country with highest DensityPerKmSq in each continent.

In the below queries, 'a' and 'b' are alias names. The below query is known as corelated Subquery. This is because the inner query is dependent on the outer query. For each continent, the maximum of b.DensityPerKmSq is fetched in the inner query and is equalled to the a.DensityPerKmSq of the outer query.

Here in the second query, 'where b.DensityPerKmSq is not null' is included because null is an unknown value and hence '>=ALL' (select b.DensityPerKmSq from countrytable b where b.continent = a.continent) means '>=ALL' (ALL the Known values of b.DensityPerKmSq for that continent and also the unknown values for that continent).Hence the query will return zero rows for those continents which has atleast one country whose DensityPerKmSq is null, if 'where b.DensityPerKmSq is not null' is not included. In our case, if 'where b.DensityPerKmSq is not null' is not included, then the output will have only two rows(for continents Asia and America).This is because Australia has null value for DensityPerKmSq and Europe has null value for DensityPerKmSq for the country France. Hence the continents, Australia and Europe will not come in the output if 'where b.DensityPerKmSq is not null' is not included.

Further more, '>=ALL' is included because '>=' alone can handle only single value. But the query,
'(select b.DensityPerKmSq from countrytable b where b.DensityPerKmSq is not null and b.continent = a.continent)' returns multiple values.To handle multiple values, '>=ALL' is included.



Max Function in Independent subquery.To find the country with highest DensityPerKmSq

In the below queries, 'a' and 'b' are alias names. Here the inner Query is independent of the outer query. First the inner query is executed and then the outer query is executed. This is known as independent Subquery.

Here in the second query, 'where b.DensityPerKmSq is not null' is included because null is an unknown value and hence '>=ALL' (select b.DensityPerKmSq from countrytable b) means '>=ALL' (ALL the Known values of b.DensityPerKmSq and also the unknown values).Hence the query will return zero rows if 'where b.DensityPerKmSq is not null' is not included.

Further more, '>=ALL' is included because '>=' alone can handle only single value. But the query,
'(select b.DensityPerKmSq from countrytable b where b.DensityPerKmSq is not null)' returns multiple values.To handle multiple values, '>=ALL' is included.

Further more, Viewers may think it would be better if the countries with maximum DensityPerKmSq for each continent is displayed. For that, Please refer the post on "Max Function in Corelated subquery. To find the country with highest DensityPerKmSq in each continent."