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 ClientNumber, ArticleId, SalesPrice, DeliverTo, GETDATE() FROM DeliveryHistory;
```

```SQL
SELECT ClientName, ArticleName, SalesPrice, DeliverTo, GETDATE() FROM DeliveryHistory;
```

Consider the following query:

```SQL
INSERT INTO Orders (CustomerId, ItemId, Price, Address, OrderDate)
```

**(Select all that apply.)** Which option(s) are valid for the above `INSERT INTO` statement?


<p>Consider the following query:</p>
<pre><code class="language-SQL">INSERT INTO Orders (CustomerId, ItemId, Price, Address, OrderDate)
</code></pre>
<p><strong>(Select all that apply.)</strong> Which option(s) are valid for the above <code>INSERT INTO</code> statement?</p>


```SQL
SELECT MAX(Score) FROM Participants;
```

```SQL
SELECT Score FROM Participants
WHERE Score <= ALL(SELECT Score FROM Participants);
```

```SQL
SELECT Score FROM Participants
WHERE Score > ANY(SELECT Score FROM Participants);
```

```SQL
SELECT Score FROM Participants
WHERE Score >= ALL(SELECT Score FROM Participants);
```

Consider the following `Participants` table.

| Id | Name | Score |
| -- | -- | -- |
| 1 | Seth Johnson | 70 |
| 2 | Carson Lewis | 83 |
| 3 | Jack Nicks | 66 |
| 4 | Kate Park | 87 |


**(Select all that apply.)** Which option(s) achieves the same result?


<p>Consider the following <code>Participants</code> table.</p>
<table>
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Seth Johnson</td>
<td>70</td>
</tr>
<tr>
<td>2</td>
<td>Carson Lewis</td>
<td>83</td>
</tr>
<tr>
<td>3</td>
<td>Jack Nicks</td>
<td>66</td>
</tr>
<tr>
<td>4</td>
<td>Kate Park</td>
<td>87</td>
</tr>
</tbody>
</table>
<p><strong>(Select all that apply.)</strong> Which option(s) achieves the same result?</p>


```SQL
SELECT PlayerId FROM Tournament1
WHERE PlayerId IN (SELECT Score FROM Tournament2);
```

```SQL
SELECT PlayerId FROM Tournament1 AS T1
WHERE EXISTS (SELECT PlayerId FROM Tournament2 AS T2 WHERE T1.PlayerId = T2.PlayerId);
```

```SQL
SELECT T1.PlayerId FROM Tournament1 AS T1
JOIN Tournament2 AS T2 ON T1.PlayerId = T2.PlayerId;
```

```SQL
SELECT PlayerId FROM Tournament1 AS T1
WHERE EXISTS (SELECT PlayerId FROM Tournament2 AS T2 WHERE T1.PlayerId = T2.Score);
```

Consider these two tables.

`Tournament1`:

| PlayerId | Score |
| -- | -- |
| 2 | 80 |
| 7 | 56 |
| 4 | 89 |
| 12 | 68 |
| 3 | 76 |

`Tournament2`:

| PlayerId | Score |
| -- | -- |
| 1 | 79 |
| 5 | 91 |
| 3 | 57 |
| 6 | 93 |
| 7 | 45 |

**(Select all that apply.)** Which option(s) select players who took part in both tournaments?


<p>Consider these two tables.</p>
<p><code>Tournament1</code>:</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>80</td>
</tr>
<tr>
<td>7</td>
<td>56</td>
</tr>
<tr>
<td>4</td>
<td>89</td>
</tr>
<tr>
<td>12</td>
<td>68</td>
</tr>
<tr>
<td>3</td>
<td>76</td>
</tr>
</tbody>
</table>
<p><code>Tournament2</code>:</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>5</td>
<td>91</td>
</tr>
<tr>
<td>3</td>
<td>57</td>
</tr>
<tr>
<td>6</td>
<td>93</td>
</tr>
<tr>
<td>7</td>
<td>45</td>
</tr>
</tbody>
</table>
<p><strong>(Select all that apply.)</strong> Which option(s) select players who took part in both tournaments?</p>


```SQL
SELECT MIN(Score) FROM Tournament2
WHERE Score > (SELECT MIN(Score) FROM Tournament1);
```

```SQL
SELECT Score FROM Tournament2
WHERE Score > ANY(SELECT Score FROM Tournament1);
```

```SQL
SELECT MIN(Score) FROM Tournament2
WHERE Score > ALL(SELECT Score FROM Tournament1);
```

```SQL
SELECT MIN(Score) FROM Tournament2
WHERE Score > ANY(SELECT Score FROM Tournament1);
```

Consider these two tables.

`Tournament1`:

| PlayerId | Score |
| -- | -- |
| 2 | 80 |
| 7 | 56 |
| 4 | 89 |
| 12 | 68 |
| 3 | 76 |

`Tournament2`:

| PlayerId | Score |
| -- | -- |
| 1 | 79 |
| 5 | 91 |
| 3 | 57 |
| 6 | 93 |
| 7 | 45 |

**(Select all that apply.)** Which option(s) find the lowest `Score` in `Tournament2` that is higher than the lowest `Score` in `Tournament1`?

<p>Consider these two tables.</p>
<p><code>Tournament1</code>:</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<tr>
<td>2</td>
<td>80</td>
</tr>
<tr>
<td>7</td>
<td>56</td>
</tr>
<tr>
<td>4</td>
<td>89</td>
</tr>
<tr>
<td>12</td>
<td>68</td>
</tr>
<tr>
<td>3</td>
<td>76</td>
</tr>
</tbody>
</table>
<p><code>Tournament2</code>:</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>Score</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>5</td>
<td>91</td>
</tr>
<tr>
<td>3</td>
<td>57</td>
</tr>
<tr>
<td>6</td>
<td>93</td>
</tr>
<tr>
<td>7</td>
<td>45</td>
</tr>
</tbody>
</table>
<p><strong>(Select all that apply.)</strong> Which option(s) find the lowest <code>Score</code> in <code>Tournament2</code> that is higher than the lowest <code>Score</code> in <code>Tournament1</code>?</p>


```SQL
SELECT * FROM Students
WHERE EXISTS (SELECT StudentId FROM FinalExamScores);
```

```SQL
SELECT * FROM Students AS S
WHERE EXISTS (SELECT StudentId FROM FinalExamScores AS F WHERE S.Id = F.StudentId);
```

```SQL
SELECT * FROM Students
WHERE Id IN (SELECT Score FROM FinalExamScores);
```

```SQL
SELECT * FROM Students
WHERE Id IN FinalExamScores;
```

Consider the following query:

```SQL
SELECT * FROM Students
WHERE Id IN (SELECT StudentId FROM FinalExamScores);
```

Which query is equivalent to the one above?


<p>Consider the following query:</p>
<pre><code class="language-SQL">SELECT * FROM Students
WHERE Id IN (SELECT StudentId FROM FinalExamScores);
</code></pre>
<p>Which query is equivalent to the one above?</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: Subqueries