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

Consider this `dbo.Items` table and data:

| Id | Title | Price | QuantityLeft |
| -- | -- | -- | -- |
| 1 | Phone | 250 | 5 |
| 2 | Headphones | 12 | 20 |
| 3 | Case | 5 | 23 |

Consider the following trigger:

```SQL
CREATE TRIGGER dbo.Items_InsteadOfUpdate ON dbo.Items INSTEAD OF UPDATE
AS
BEGIN
    IF deleted.Id = 2
    BEGIN
        UPDATE dbo.Items
        SET Price = 15
        WHERE Id = 2
    END
END;
```

What is the price of the headphones after running the following query?

```SQL
UPDATE dbo.Items
SET Price = 25
WHERE Id = 2
```

<p>Consider this <code>dbo.Items</code> table and data:</p>
<table>
<thead>
<tr>
<th>Id</th>
<th>Title</th>
<th>Price</th>
<th>QuantityLeft</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Phone</td>
<td>250</td>
<td>5</td>
</tr>
<tr>
<td>2</td>
<td>Headphones</td>
<td>12</td>
<td>20</td>
</tr>
<tr>
<td>3</td>
<td>Case</td>
<td>5</td>
<td>23</td>
</tr>
</tbody>
</table>
<p>Consider the following trigger:</p>
<pre><code class="language-SQL">CREATE TRIGGER dbo.Items_InsteadOfUpdate ON dbo.Items INSTEAD OF UPDATE
AS
BEGIN
    IF deleted.Id = 2
    BEGIN
        UPDATE dbo.Items
        SET Price = 15
        WHERE Id = 2
    END
END;
</code></pre>
<p>What is the price of the headphones after running the following query?</p>
<pre><code class="language-SQL">UPDATE dbo.Items
SET Price = 25
WHERE Id = 2
</code></pre>


Consider the following query:

```SQL
DECLARE @Number AS INT
SET @Number = 71

IF @Number * 2 > 150
BEGIN
    IF @Number / 2 < 35
    BEGIN
        PRINT '02'
    END
    ELSE
    BEGIN
        PRINT '01'
    END
END
ELSE
BEGIN
    IF @Number / 2 < 35
    BEGIN
        PRINT '00'
    END
    ELSE
    BEGIN
        PRINT '04'
    END
END
```

What will be printed?


<p>Consider the following query:</p>
<pre><code class="language-SQL">DECLARE @Number AS INT
SET @Number = 71

IF @Number * 2 &gt; 150
BEGIN
    IF @Number / 2 &lt; 35
    BEGIN
        PRINT '02'
    END
    ELSE
    BEGIN
        PRINT '01'
    END
END
ELSE
BEGIN
    IF @Number / 2 &lt; 35
    BEGIN
        PRINT '00'
    END
    ELSE
    BEGIN
        PRINT '04'
    END
END
</code></pre>
<p>What will be printed?</p>


The order and number of columns do not match between the cursor declaration and fetching.

A table has been created using the following query:

```SQL
CREATE TABLE dbo.Items 
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Title NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    TaxRate DECIMAL NOT NULL,
    TotalPrice DECIMAL(18,2) NULL
);
```

The following query is not running:

```SQL
DECLARE @CurrentId INT
DECLARE @CurrentPrice DECIMAL(18,2)
DECLARE @CurrentTaxRate DECIMAL
DECLARE @CurrentTotalPrice DECIMAL(18,2)

DECLARE ItemsCursor CURSOR FOR
SELECT Id, Price, TaxRate FROM dbo.Items

FETCH NEXT FROM ItemsCursor INTO @CurrentId, @CurrentPrice, @CurrentTaxRate

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentTotalPrice = @CurrentPrice * (1 + @CurrentTaxRate / 100)

    UPDATE dbo.Items SET TotalPrice = @CurrentTotalPrice
    WHERE Id = @CurrentId

    FETCH NEXT FROM ItemsCursor INTO @CurrentId, @CurrentPrice, @CurrentTaxRate
END

CLOSE ItemsCursor
DEALLOCATE ItemsCursor
```

What could be the problem?

<p>A table has been created using the following query:</p>
<pre><code class="language-SQL">CREATE TABLE dbo.Items 
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Title NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    TaxRate DECIMAL NOT NULL,
    TotalPrice DECIMAL(18,2) NULL
);
</code></pre>
<p>The following query is not running:</p>
<pre><code class="language-SQL">DECLARE @CurrentId INT
DECLARE @CurrentPrice DECIMAL(18,2)
DECLARE @CurrentTaxRate DECIMAL
DECLARE @CurrentTotalPrice DECIMAL(18,2)

DECLARE ItemsCursor CURSOR FOR
SELECT Id, Price, TaxRate FROM dbo.Items

FETCH NEXT FROM ItemsCursor INTO @CurrentId, @CurrentPrice, @CurrentTaxRate

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CurrentTotalPrice = @CurrentPrice * (1 + @CurrentTaxRate / 100)

    UPDATE dbo.Items SET TotalPrice = @CurrentTotalPrice
    WHERE Id = @CurrentId

    FETCH NEXT FROM ItemsCursor INTO @CurrentId, @CurrentPrice, @CurrentTaxRate
END

CLOSE ItemsCursor
DEALLOCATE ItemsCursor
</code></pre>
<p>What could be the problem?</p>


The value is `1`, because `XACT_ABORT` is set to `ON`.

The value is `2`, because the transaction completes successfully.

The value is `2`, because of the `RAISERROR()` function.

The value is `1`, because a transaction is rolled back automatically upon error.

Consider the following stored procedure:

```SQL
CREATE PROCEDURE dbo.UpdateStatus
    @ItemId INT,
    @NewStatus INT
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.Items WHERE Id = @ItemId)
    BEGIN
        RAISERROR('Item does not exist', 16, 1)
    END
    ELSE
    BEGIN
        UPDATE dbo.Items
        SET Status = @NewStatus
        WHERE Id = @ItemId
    END
END;
```

The stored procedure above is used within the following transaction:

```SQL
SET XACT_ABORT ON
BEGIN TRANSACTION

    UPDATE dbo.Items
    SET Status = 2
    WHERE Id = 7

    EXEC dbo.UpdateStatus 11, 0
    
COMMIT TRANSACTION;
```

There is no item with an `Id` of `11`. What is the value of the `Status` column for a row with an `Id` of `7` if the value was `1` before running the transaction?

<p>Consider the following stored procedure:</p>
<pre><code class="language-SQL">CREATE PROCEDURE dbo.UpdateStatus
    @ItemId INT,
    @NewStatus INT
AS
BEGIN
    IF NOT EXISTS (SELECT * FROM dbo.Items WHERE Id = @ItemId)
    BEGIN
        RAISERROR('Item does not exist', 16, 1)
    END
    ELSE
    BEGIN
        UPDATE dbo.Items
        SET Status = @NewStatus
        WHERE Id = @ItemId
    END
END;
</code></pre>
<p>The stored procedure above is used within the following transaction:</p>
<pre><code class="language-SQL">SET XACT_ABORT ON
BEGIN TRANSACTION

    UPDATE dbo.Items
    SET Status = 2
    WHERE Id = 7

    EXEC dbo.UpdateStatus 11, 0
    
COMMIT TRANSACTION;
</code></pre>
<p>There is no item with an <code>Id</code> of <code>11</code>. What is the value of the <code>Status</code> column for a row with an <code>Id</code> of <code>7</code> if the value was <code>1</code> before running the transaction?</p>


No, because the performance cost increases as the isolation level increases.

No, because the performance cost increases as the isolation level decreases.

Yes, because there is no performance cost associated with different levels of isolation.

Yes, because the performance cost is always negligible.

The `SERIALIZABLE` isolation level addresses all issues. Therefore, we should always set this isolation level for our transactions.

<p>The <code>SERIALIZABLE</code> isolation level addresses all issues. Therefore, we should always set this isolation level for our transactions.</p>


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

Quiz Yourself: Additional Programming Constructs