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

```SQL
SELECT OrderId FROM dbo.Orders
WHERE OrderId = 1 OR OrderId = 3;
```

```SQL
SELECT OrderId FROM dbo.Orders
WHERE Quantity < 4;
```

```SQL
SELECT OrderId FROM dbo.Orders
WHERE OrderId = 1 AND OrderId = 3;
```

```SQL
SELECT OrderId FROM dbo.Orders
WHERE Quantity < 6;
```

Consider the following `dbo.Orders` table.


| OrderId | ItemId | Quantity |
|---|---|---|
| 1 | 25 | 4 |
| 2 | 2 | 7 |
| 3 | 11 | 1 |


**(Select all that apply.)** Which of the given option(s) will result in the following output?


| OrderId |
|---|
| 1 |
| 3 |





<p>Consider the following <code>dbo.Orders</code> table.</p>
<table>
<thead>
<tr>
<th>OrderId</th>
<th>ItemId</th>
<th>Quantity</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>25</td>
<td>4</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
<td>7</td>
</tr>
<tr>
<td>3</td>
<td>11</td>
<td>1</td>
</tr>
</tbody>
</table>
<p><strong>(Select all that apply.)</strong> Which of the given option(s) will result in the following output?</p>
<table>
<thead>
<tr>
<th>OrderId</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
</tr>
<tr>
<td>3</td>
</tr>
</tbody>
</table>


Rows with `ItemId` 1 and 3 will be removed.

Rows with `ItemId` 3 and 4 will be removed.

Rows with `ItemId` 5 and 1 will be removed.

Rows with `ItemId` 5 and 2 will be removed.

Consider the following `dbo.Stock` table.

| ItemId | QuantityLeft |
| --- | --- |
| 1 | 0 |
| 2 | 59 |
| 3 | 21 |
| 4 | 87 |
| 5 | 0 |

What is the result of the following query?

```SQL
DELETE FROM dbo.Stock
WHERE QuantityLeft = 0;
```

<p>Consider the following <code>dbo.Stock</code> table.</p>
<table>
<thead>
<tr>
<th>ItemId</th>
<th>QuantityLeft</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>0</td>
</tr>
<tr>
<td>2</td>
<td>59</td>
</tr>
<tr>
<td>3</td>
<td>21</td>
</tr>
<tr>
<td>4</td>
<td>87</td>
</tr>
<tr>
<td>5</td>
<td>0</td>
</tr>
</tbody>
</table>
<p>What is the result of the following query?</p>
<pre><code class="language-SQL">DELETE FROM dbo.Stock
WHERE QuantityLeft = 0;
</code></pre>


| Id | Name | Address |
| --- | --- | --- |
| 1 | Ron Taylor | NULL |

| Id | Name | Address |
| --- | --- | --- |
| 1 | John Grant | NULL |

| Id | Name | Address |
| --- | --- | --- |
| 1 | Ron Taylor | NULL |
| 2 | John Grant | NULL |

Consider a table created using this query:

```SQL
CREATE TABLE Customers
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Name NVARCHAR(100) NOT NULL,
    Address NVARCHAR(100) NOT NULL
);
```

What is the state of the `dbo.Customers` table after running the following code?

```SQL
INSERT INTO dbo.Customers (Name)
VALUES 
    ('Ron Taylor'),
    ('John Grant');
```

<p>Consider a table created using this query:</p>
<pre><code class="language-SQL">CREATE TABLE Customers
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Name NVARCHAR(100) NOT NULL,
    Address NVARCHAR(100) NOT NULL
);
</code></pre>
<p>What is the state of the <code>dbo.Customers</code> table after running the following code?</p>
<pre><code class="language-SQL">INSERT INTO dbo.Customers (Name)
VALUES 
    ('Ron Taylor'),
    ('John Grant');
</code></pre>


| Id | Title | Description | Price | Stock |
| -- | -- | -- | -- | -- |
| 1 | Headphones | 3.5 mm headphones | 25 |  11 |
| 2 | Case | White case cover | 15 | 7 |
| 3 | Protection Glass | Protection glass for 6-inch screen | 15 | 3 |
| 4 | Case | Black case cover | 15 | 4 |
| 5 | Headphones | Type-C headphones | 25 | 9 |

| Id | Title | Description |
| --| -- | -- |
| 1| Headphones | 3.5 mm headphones |
| 2 | Case | White case cover |
| 3 | Protection Glass | Protection glass for 6-inch screen |
| 4 | Case | Black case cover |
| 5 | Headphones | Type-C headphones |

Consider the following `Items` table.

| Id | Title | Description | Price | Stock |
| -- | -- | -- | -- | -- |
| 1 | Headphones | 3.5 mm headphones | 25 |  11 |
| 2 | Case | White case cover | 15 | 7 |
| 3 | Protection Glass | Protection glass for 6-inch screen | 15 | 3 |
| 4 | Case | Black case cover | 15 | 4 |
| 5 | Headphones | Type-C headphones | 25 | 9 |

What is the output of the following code?

```SQL
SELECT DISTINCT Title, Description
FROM dbo.Items;
```



<p>Consider the following <code>Items</code> table.</p>
<table>
<thead>
<tr>
<th>Id</th>
<th>Title</th>
<th>Description</th>
<th>Price</th>
<th>Stock</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Headphones</td>
<td>3.5 mm headphones</td>
<td>25</td>
<td>11</td>
</tr>
<tr>
<td>2</td>
<td>Case</td>
<td>White case cover</td>
<td>15</td>
<td>7</td>
</tr>
<tr>
<td>3</td>
<td>Protection Glass</td>
<td>Protection glass for 6-inch screen</td>
<td>15</td>
<td>3</td>
</tr>
<tr>
<td>4</td>
<td>Case</td>
<td>Black case cover</td>
<td>15</td>
<td>4</td>
</tr>
<tr>
<td>5</td>
<td>Headphones</td>
<td>Type-C headphones</td>
<td>25</td>
<td>9</td>
</tr>
</tbody>
</table>
<p>What is the output of the following code?</p>
<pre><code class="language-SQL">SELECT DISTINCT Title, Description
FROM dbo.Items;
</code></pre>


```SQL
INSERT INTO Orders(ItemId, CustomerId, Quantity)
VALUES (1, 1, 1);
```

```SQL
INSERT INTO Orders(Notes, ItemId, Quantity)
VALUES ('First order', 1, 1);
```

The following code creates the `Orders` table:

```SQL
CREATE TABLE Orders
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    ItemId INT NOT NULL,
    CustomerId INT NOT NULL,
    Quantity INT NOT NULL,
    Notes NVARCHAR(100)
); 
```

Which query results in an error?


<p>The following code creates the <code>Orders</code> table:</p>
<pre><code class="language-SQL">CREATE TABLE Orders
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    ItemId INT NOT NULL,
    CustomerId INT NOT NULL,
    Quantity INT NOT NULL,
    Notes NVARCHAR(100)
); 
</code></pre>
<p>Which query results in an error?</p>


T-SQL Basics Quiz

<p>T-SQL Basics Quiz</p>


Test your knowledge of the basic syntax of T-SQL.

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: T-SQL Basics