SQL Server 70-461 Exams fundamental - Part 1 (Tips from Querying from Microsoft SQL Server Training kit by Itzik-Ben-Gan)

Hello Everybody!!! This time, it is about Microsoft Exam.

When I decided to take up Microsoft certification (MCSA) on Business Intelligence 2012 (I was planning from 3 years but was never that serious to write, Lol!! But now I am ready), I came up with training kit by Itzik-Ben-Gan. This book was very helpful for understanding the fundamentals of T-SQL and its architectures.

However, for my preparation standpoint view, I am summarizing the tips and points from the book which I believe, going through main points, will help lot of them to get small tips instead of reading entire book before day of exam. So let’s start with SQL….!!!

Standard SQL is based on the relational model, which is a mathematical model for data management and manipulations.

The word relational is not about 2 tables related but it is from mathematical concept of Set Relation from “Set theory”.

Relation in the relational model is called a “Table”. (T-SQL is based on multiset theory than on set theory).

For a table to be in Relational format, set of rules are applicable but if not followed, those tables are not really called relational or in other words, the so called relational table deviates from mathematical Set theory. The examples of such deviations are below.

For a table to be in Relational format, set of rules are applicable but if not followed, those tables are not really called relational or in other words, the so called relational table deviates from mathematical Set theory. The examples of such deviations are below.

Ø  Set theory say, A Set cannot have duplicates. This is not true when it comes to Table. T-SQL on querying a set of records, by default will not remove duplicates.

Ø  Adding DISTINCT which remove duplicates will make the table relational.

Ø  Set theory doesn’t believe in order of tuples (sets like a,b,c,z,y) arranged. T-SQL on enforcing the rules like ORDER BY clause, will make the table not relational or deviates from relational model.

(Note: SQL Server doesn’t track the order of records been saved into its memory. It randomly picks the location and no guarantee that SQL Server would save the data on particular address. The SQL Server stores the rows in a volatile fashion).

Ø  According to Relational theory, all the attributes should have names. It deviates in T-SQL where we still can write queries like,

Select EmpID, Firstname+ ‘  ‘ +LastName From Emp.

Where 2^nd column resulting is derived column from Firstname and LastName but have no doesn’t have any name.

This can be overcome by;

 Select EmpID, Firstname+ ‘  ‘ +LastName as Name From Emp.

Ø  Same name of column cannot be a output if it is relational. Example ;

Select E1.ID  , E2.ID from … can result in column ID from 2 tables but this is not going to form relational table under relational model. Each column should be identify distinctly.

Ø  Set theory stands for 2 valued- logic (True or a False) but T-SQL is a three valued-logic system. It follows or predicates TRUE or FALSE OR UNKNOWN (NULL- Meaning missing value).

Ex: Select * From EMP Where Ename IS NULL.