T-SQL (Transact SQL)

SQL (Structured Query Language) is at the Core of many Database systems and platforms, from Access to Oracle, and older IBM AS/400-based systems. It's different to Object programming in the sense that it is not executed in a set order, even though it is declared in set statements, the language evaluates the whole statement before attempting to run it.

Generally, it is split up into sections to SELECT particular columns, FROM a table or tables, WHERE certain conditions are met, GROUP BY a particular column or columns, in ORDER BY a column or columns, in either Ascending or Descending order. In the SELECT statement, this is where most of the work will be done, as it can not only choose the columns required in the query, but also where you can include calculated fields and CASE declarations.

CASE, THEN, ELSE, END statements work in the same way as IF, THEN, ELSE, END IF in VBA, so they be declared in such a way that the results returned vary depending on if a condition is true or false. For example, a column can be checked for a Null value, so a different calculation can be done, maybe on another column, a string value can be checked for a certain value, to have some effect, or compared and/or concatenated as required.

Calculated fields are also a major part of SQL, giving a more useful dataset for the end user or application. SUM and COUNT can be used on columns, maybe even a combination of them with CASE statements inside, and COUNT(DISTINCT) has a role in some cases. MAX and MIN of columns can be returned, and later versions of SQL have a whole range of other functions to enhance datasets.

When using GROUP BY, if you have calculated columns that you wish to check, in the same way as a WHERE statement, you have to use the HAVING statement instead, but it works in the same way. When declaring the FROM statement, i.e. the data source, you can JOIN different tables ON a particular column, which is common to them, in various ways, depending on which records are required, and how they relate to each other.

As I'm relatively new to T-SQL, I'll put some more articles with some of my observations, and code samples on particular topics as and when I write more queries and other functions.