Gain insights into T-SQL syntax, table creation, data manipulation, and relationships. Delve into advanced constructs like stored procedures, functions, and triggers to enhance your database development skills.

tsql.tar.gz

T-SQL-SPA

T-SQL is a dialect of SQL that is used in Microsoft SQL Server. The popularity of Microsoft's database management system sustains high market demand for people well versed in T-SQL. 

This course teaches the core concepts and syntax of T-SQL. First, you will learn how to create tables, insert data, update existing records, and create relationships between tables. Then, you will be exposed to more advanced constructs of T-SQL, like stored procedures, functions, and triggers. 

By the end of the course, you will be sufficiently adept with T-SQL and be able to perform your day-to-day tasks as software and database developers effectively.

Querying Databases Using Transact-SQL

# Overview

To interact with databases hosted on Microsoft SQL Server (MS SQL), we use Transact-SQL (T-SQL), which is Microsoft’s implementation of the SQL standard. The DBMS runs T-SQL queries. The client (for example, an external program) sends a request in T-SQL using a special API. MS SQL Server executes the request and then sends the result to the client, as shown in the following diagram.


The popularity of the MS SQL Server paved the way for the popularity of T-SQL and because MS SQL Server is used in many large enterprises, it is useful to learn T-SQL.

T-SQL can be used to query data for analysis purposes, and is also a valuable instrument in a data analyst’s toolbox. Additionally, T-SQL is a procedural programming language, which makes it possible for developers to create business logic on top of MS SQL Server databases.

# SQL vs. T-SQL

It is important that we distinguish between Structured Query Language (SQL) and Transact-SQL (T-SQL). This table summarizes the distinctions.


align-center

In other words, T-SQL has derived some things from SQL and introduced new functionality.


# Types of T-SQL commands

Depending on the task that a T-SQL command is trying to accomplish, it can be related to one of the following categories:

 - Data definition language (DDL)
 - Data manipulation language (DML)
 - Data control language (DCL)

## Data definition language

T-SQL can be used to create, alter, and delete various database objects, like the databases themselves, tables, stored procedures, views, and so on. A database object is an entity that can store, reference, or work with data. These commands are part of DDL:

 - `CREATE`: Used for creating new database objects.
 - `ALTER`:  Used for modifying existing database objects.

 - `DROP`: Used for deleting database objects.

 - `TRUNCATE`: Used for deleting data from database objects.

## Data manipulation language
We can use T-SQL to work with data. These commands are part of DML:
- `SELECT`: Used to extract data from tables.
- `INSERT`: Used to insert data into tables.
- `UPDATE`: Allows us to edit the existing data.

- `DELETE`: Used to delete data from tables.


## Data control language

T-SQL also allows us to control what actions can be performed against the database by specific users. It is possible to configure access on the database object level. For example, some users might only access the database in a read-only mode, meaning they could extract data but can’t edit it or insert new information. The DCL part of T-SQL is not within the scope of this course, because software engineers rarely have to work with the security policies of databases. That is a job of database administrators.


Before We Start

T-SQL Basics

Querying Multiple Tables

Grouping and Aggregation

Subqueries

T-SQL Fundamentals Assessment

Built-in Functions

T-SQL Programming Basics

Additional Programming Constructs

Course Comprehension Assessment

Conclusion

Introduction to T-SQL