Category Archives: SQL Tutorials

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

• This page is divided into categories thematically –
  SQL Basics » | Handling tables » | SQL Clauses » | SQL Commands » | SQL Functions » | SQL Operators » | Useful SQL scripts » | SQL Online Quiz »
• Zobrazit příspěvky z kategorie:
  List of Articles in SQL Tutorials category »

For Beginners – How to Gradually Learn T-SQL

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:

• Microsoft Certification – All About MCSA: SQL
• Careers Data Analyst – Job Description or
  SQL Developer – Job Description

Before studying, I recommend installing the necessary tools (in this order) so that you can immediately practice the newly acquired knowledge:

• SQL Server (Express or Developer) – link to video (in English)
• SQL Server Management Studio – link to video (in English)
• AdventureWorks database (Microsoft’s practice database) – link to video (in English)

1) Introduction to T-SQL – Introduction, Basic Orientation

After studying these articles, you will get acquainted with the basic syntax of the T-SQL language and build simple SQL queries:

• Examples of Simple SQL Queries
• SQL Basics – The Most Commonly Used Statements for Beginners
• First Encounter with SQL Server Management Studio – The Tool for Writing Queries
• Use Chat GPT to learn T-SQL basics

2) Basic SQL Clauses (SQL Clauses Category) – Basic Structural Elements of SQL Queries

After studying these articles, you will be able to independently write simple query scripts for one table:

• SELECT – for selecting columns
• SELECT DISTINCT – for removing duplicities from select
• WHERE – for specifying conditions (restricting records from the table)
• GROUP BY – for aggregation (if we use aggregation functions – e.g., SUM)
• ORDER BY – for sorting records in ascending or descending order

3) Joining Tables (FROM) – Joining

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):

• INNER JOIN – common records from both tables based on join criteria
• LEFT JOIN – all from the left table and matches from the right table based on join criteria
• RIGHT JOIN – all from the right table and matches from the left table based on join criteria
• FULL JOIN – all from both tables

4) Introduction to SQL Functions (SQL Functions Category)

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:

• SQL Aggregation Functions – SUM, COUNT, MAX, MIN, AVG – aggregation functions; for these functions, the GROUP BY clause is mandatory
• SQL String Functions – Comprehensive Overview of Text Functions – text manipulation functions (trimming, cleaning, text extraction, etc.)
• SQL Date and time data types
• SQL ISNULL and COALESCE Functions – Differences and Usage – replacing empty values (NULL) with something we want to substitute
• SQL Ranking Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE – numbering records

5) DDL (Creating, Deleting, Cleaning Tables) and DML (Changing Records in Tables)

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:

• SQL CREATE TABLE – How to Create a Table in a Database
• SQL ALTER TABLE – Adding, changing data types, deleting columns
• SQL INSERT – Inserting rows into a database table (3 ways) + Common mistakes
• SQL DELETE table – Deleting and the difference between DELETE vs. TRUNCATE
• SQL TRUNCATE table – Don’t use DELETE if you don’t have to
• SQL UPDATE table – Syntax and example UPDATE
• SQL MERGE Statement – INSERT, UPDATE, DELETE simultaneously

6) Connecting Data to Excel or Reporting Platforms

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:

• How to Run SQL Query in Excel? Guide
• How to Run SQL Procedure in Excel with Parameters + Example
• Financial Report in Power BI – Dashboard with Balance Sheet and Income Statement
• SSRS | How to Create an Amortization Calculator in SQL Server – Including a Report with Parameters