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

A **transaction** is a set of commands executed as a single unit. If one of the commands fails, the whole transaction is canceled and no changes are applied to the database. Transactions are irreplaceable when we have several commands that are executed one after another and are logically tied to each other. They allow the database to remain consistent even if an error occurred during the execution of a query.

# Implicit transactions

Every command we run against a database can be considered a transaction. This is known as implicit transaction. However, because such transactions consist of only a single statement, such as a single `INSERT` command, they do not allow us to yield the power of using transactions. It is possible to start and end transactions explicitly when we have multiple commands to execute as a single unit.
# Why transactions?

Let’s consider the following scenario. We are developing a banking application that allows us to transfer funds from one account to another. A transfer essentially consists of two operations:

* Taking money from one account.
* Debiting the funds to the second account.

These two operations can be completed using two `UPDATE` statements:

```SQL
UPDATE Accounts
SET Balance = Balance - @AmountTransferred
WHERE AccountId = @From;

UPDATE Accounts
SET Balance = Balance + @AmountTransferred
WHERE AccountId = @To;
```

In the snippet above, `@From` is the account ID from which we’ll be deducting funds, while `@To` is the account ID to which we’ll be transferring funds. Imagine the first `UPDATE` ran fine, but there was an unexpected error while executing the second `UPDATE`. Funds will be deducted from the first account but are never credited to the second one. Money will be lost.

To avoid such situations, we must wrap interrelated queries within a transaction.


# Syntax

In its simplest form, a transaction is created with the help of two commands:

```SQL
BEGIN TRANSACTION
    [Commands to execute]
COMMIT TRANSACTION;
```

If we want to put the previous example within a transaction, we will do the following:

```SQL
BEGIN TRANSACTION
    UPDATE Accounts
    SET Balance = Balance - @AmountTransferred
    WHERE AccountId = @From

    UPDATE Accounts
    SET Balance = Balance + @AmountTransferred
    WHERE AccountId = @To
COMMIT TRANSACTION;
```

Now, let's make one of the statements fail so that we can see that no changes are applied.

# Example

First, we'll show an example without a transaction. We deduct the amount from the first account but make the second `UPDATE` statement fail:


Learn to keep the database state consistent by creating and running transactions.


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

Transactions

Implicit transactions