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."

Select particular Columns,Case Keyword, Coalesce Keyword and Concatenation

Select particular Columns and use of Case Keyword

I believe the below will better help in understending the query. Think of row by row execution. For convenience the execution of India, Sri Lanka, Nepal and Pakistan are only shown. Hope the readers are smart enough to understend the execution of the remaining rows.

Now a question might arise. Only when DensityPerKmSq is greater than 200, the second condition gets executed because if DensityPerKmSq < 200 , Density Factor of 'Low' is executed and the execution of this row stops here in the first condition itself and the execution of the next row starts by checking from the first condition. So it is unnecessary to include DensityPerKmSq > 200 for Density Factor of 'Medium' in the second condition because Density Factor of 'Medium' is checked only if first condition is false which means DensityPerKmSq is greater than (>) 200  . Hence the below will also give the same result as the above.

  

Consider the below query. Note it closely. You may find two new points in it.

The first point is if you want to concatenate a constant string and a column , ' + ' sign is used as in 'AreaInKmSq : ' + AreaInKmSq .The AreaInKmSq in quotes is the constant which will be prefixed in every column.The right hand side of  ' + ' has AreaInKmSq which is the column name which fetches the value for that particular row. Similarly the same thing can be applied for  'Mayor             : ' + Mayor. In addition please have in mind that two or more columns can also be concatenated using ' + '. The second Point is that For the same column in the output 'AreaInKmSq or Mayor' , the values of various columns ('AreaInKmSq' , 'Mayor') from the base table can be provided. I believe the below will better help in understanding the query.  

An Aliter for the above is below which also gives the same output.

The coalesce Keyword first checks whether the first value is not null and if it is true, then the first column

value is returned. If the first value is null, it checks for the second value whether it is not null and if it is true, then the second column value is returned. If the first and second value is null, then it checks for the third value and so on.If the last value is a constant and if all the columns in the coalesce expression are null except the last column which is a constant, then the constant is returned. If there is no constant at the end and if all the columns in the coalesce expression are null, then NULL is returned. Please note that while checking whether the first value is null or not, the prefixed or suffixed constant value is not taken into account. In our case, in the first column, the prefixed AreaInKmSq which is in quotes is not taken into account while checking for null. Similarly, the prefixed Mayor in Quotes in the second is also not taken into account while checking for null. Furthermore please note that we are checking whether the values are null or not. That's why coalesce is used. If the condition is some other thing other than null, then coalesce can't be used.

Consider the query below.

The readers may be annoyed with this example as there is nothing new. The purpose of this query is to show the Aliter below which also gives the same output as above.

For the first time we are going to see inner Join. Inner join is also known as Simple join or Equi join. Consider we want to check the names of the Mayors of the cities again as a double check from another table called mayortable. Consider the query below.

The purpose of this query is to show that even in 'where' condition the various values from different columns can be assigned using the 'case' or 'Coalesce' keyword for the same condition to fetch the rows. The below will help in understanding the query.

The left hand side denotes the left side condition of the Query which is 'coalesce' and the right hand side denotes the right side condition of the Query which is 'case'. Only when all the conditions are satisfied the rows are fetched. Furthermore, for the double arrowed line in green, explaination is given only for three. I believe giving explanation for all will make the diagram too much congested. Hence I believe the readers can understand for the double arrowed green lines that they are matching between the two tables. An Aliter is below which also gives the same Output.

Case Sensitivity

If Case Sensitivity is required in search "where" condition, query is below

Note : As you could see, country = 'India' is fetched, but country = 'INdia' is not fetched. Thus case sensitivity is achieved with the help of CAST and varbinary. The 5 within brackets is the length of the word which is searched.[In our case,"India(INdia)" is the word which is searched].

Simple where Condition

Simple where condition, query is below

                                            

Note : Here you could see the letters in "where" condition is 'INdia'. But still the output has 'India'. My point here is by default, the letters are case insensitive

I believe the below will better help in understanding the query.

Count Function. To find Number of Rows in Table

 To find the number of rows in the table, query is below

The column name can be set as desired using the key word " as "(shown in the SQL).

Basic Select Query

Suppose there is a table named countrytable. Here * displays all the columns of the countrytable (In our case, there are four columns viz SerialNo, Country, Continent, DensityPerKmSq). There is no condition in the query. Hence all the rows of the countrytable are fetched(In our case, there are twelve rows).

Note: The single smallest unit in a table is called cell. For example "9" is in a cell,"Germany" is in another cell,etc.  The intersection of a row and a column is a cell. For example "9" is in 9th row 1st column. "Germany" is in 9th row 2nd column,etc.
If a cell has NULL, it means the content of the cell is an unknown value. The value of a cell having NULL is different from ZERO, may or may not be space, is not an empty value, it can be any thing.
Two NULL values may or may not be equal.


Let there be another table named citytable.The contents of the table are below.

As you could see the single horizontal values constitute a row and a single vertical values constitute a column. The values are ordered by Continent in ascending order(Default is ascending order) and Country in descending order as the keyword "desc" is given for Country column. If there is a tie in continent values, then rows are ordered by country as shown in descending order.For example there is America continent four times. So the next consideration is that the values are ordered by Country in descending order i.e first USA and then Brazil.In order by clause, first comes continent and then comes country.So first preference in ordering is given to continent and then to country.

Let there be another table named Mayor table.The mayors of cities should be checked again with this table. The contents of the table is below.

Important Note : All the queries are compatible with MS-SQL SERVER 2008