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.