T-SQL is a language used for working with databases on the
MS SQL Server platform. You can find a lot of articles and tutorials about T-SQL in this section. If you want to learn SQL or just read how to work with SQL function you don’t know yet, check out the SQL tutorials. I want every article to contain an example of usage and a code example too
Below, you will find a basic chronological outline that I would recommend following to learn SQL step by step. Use other resources as well to complement and expand your knowledge. If you thoroughly cover these topics, whether here on the website or elsewhere, it will give you a solid foundation for effective work with databases, automation, BI, reporting, applications, and more. You can lay the foundation for:
Before studying, I recommend installing the necessary tools (in this order) so that you can immediately practice the newly acquired knowledge:
After studying these articles, you will get acquainted with the basic syntax of the T-SQL language and build simple SQL queries:
After studying these articles, you will be able to independently write simple query scripts for one table:
In a relational database, we typically don’t join just one table but several (in the FROM clause). There are several types of table joins. After studying these articles, you will understand the differences between different types of joins and be able to write more complex scripts by joining multiple tables. In practice, understanding and correctly choosing joins in various situations is probably the biggest challenge when learning the SQL language (moving from a complete beginner to moderately advanced):
After understanding the basic structure of the language, you can see how we can work with data (similar to Excel) through functions. These functions are most commonly used in the SELECT clause. Below are a few examples of the most commonly used functions; you can find more in the relevant category on the website:
Until now, we’ve dealt with what’s called DQL (Data Query Language) – querying. After understanding how to query tables, we can move on to creating tables and commands that either modify or delete records in the table:
If we can prepare data in the database and efficiently query it, we can then pull the query results into tools like Excel or Power BI:
SQL Like operator can find string of text based on a certain pattern. It is most usually used in clause WHERE. But it can be applied in any part of SQL query, for example in SELECT, UPDATE or DELETE. T-SQL LIKE has the following syntax: Syntax: SELECT <Column 1> FROM dbo.Table WHERE <Column 1> LIKE… Read More »
T-SQL is not a language designed primarily to do something big with text strings or manipulate with them. That is the reason why we will not find many text-related built-in functions. But we will definitely find use for those that are there. Let’s take a look at SQL String Functions. Text Concatenation (Combining) – CONCAT… Read More »
System stored procedures are useful administrative tools. Below, you may find 10 of them which I consider as the most useful: sp_help – info on objects in database, data types sp_spaceused – displays space on the disk allocated to database and storage for each database objects including number of rows in the table sp_who – returns… Read More »
Comments in SQL query have a simple purpose. They keep the code well-arranged for others and also explain its logic. If you mark some text as a comment it will not be taken as an SQL command and therefore it will not be executed. SQL Comment can be written down in two ways: Comment can… Read More »
OVER clause can belong to commands working with so-called Window functions in SQL. These types of functions enable us to look at the data not only in their aggregated form (using GROUP BY), but we can also look at functional operations via multiple other attributes. We, in fact, create windows of some sort and we… Read More »
GROUP BY clause is used to group data. In most of the cases, we talk of a situation when we need to make an aggregate operation in the table (sum of sales, number of records, average, etc.) GROUP BY in SQL Script SELECT FROM WHERE GROUP BY HAVING ORDER BY Syntax SELECT [Column 1], [Column… Read More »
The order in which are the SQL clauses entered into the query are quite known. However, the order of logical query processing is different than in which we enter it into the script. SQL clauses and their order in the script: SELECT FROM WHERE GROUP BY HAVING ORDER BY but SQL query is logically processed… Read More »
HAVING clause is a special type of SQL command. It works in a similar way as WHERE clause and we use it to define a condition. There are important differences between the two clauses. Let me explain the differences between SQL HAVING and WHERE. HAVING positioning in SQL script SELECT FROM WHERE GROUP BY HAVING… Read More »
SQL aggregate functions AVG, COUNT, MAX, MIN, SUM are used the most. That is because we often need to add, make average or find the highest or lowest value. It is mandatory to use GROUP BY clause anytime when we use aggregate function. (except for operations over windows using OVER()) List of SQL Server Aggregate… Read More »
SQL ranking functions enable us to rank records in the table based on values of a field or multiple fields. Clause OVER() is mandatory for the ranking functions. Ranking functions belongs to the group of Window functions. Aggregate functions and Offset functions (for paging) also belong to this group. List of Ranking Functions ROW_NUMBER ()… Read More »