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 MIN(Grade) FROM Grades;
```

```SQL
SELECT Grade FROM Grades
WHERE Grade = 74;
```

```SQL
SELECT MIN(Grade) FROM Grades
WHERE Grade <> 68;
```

```SQL
SELECT MIN(Grade) FROM Grades
WHERE Grade > 68;
```

Consider the following `Grades` table.



| StudentId | Grade |
| -- | -- |
| 1 | 79 |
| 2 | 84 |
| 3 | 74 | 
| 4 | 82 |
| 5 | 68 |
| 6 | 77 |

**(Select all that apply.)** We want to output the second lowest grade. Which option(s) can we use?


<p>Consider the following <code>Grades</code> table.</p>
<table>
<thead>
<tr>
<th>StudentId</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>2</td>
<td>84</td>
</tr>
<tr>
<td>3</td>
<td>74</td>
</tr>
<tr>
<td>4</td>
<td>82</td>
</tr>
<tr>
<td>5</td>
<td>68</td>
</tr>
<tr>
<td>6</td>
<td>77</td>
</tr>
</tbody>
</table>
<p><strong>(Select all that apply.)</strong> We want to output the second lowest grade. Which option(s) can we use?</p>


Consider the following `Grades` table.

| StudentId | Grade |
| -- | -- |
| 1 | 79 |
| 2 | 84 |
| 3 | 74 | 
| 4 | 82 |
| 5 | 68 |
| 6 | 77 |

What is the result of the following query?

```SQL
SELECT AVG(Grade) + MAX(Grade)
FROM Grades
WHERE Grade = 84 OR Grade = 82; 
```

<p>Consider the following <code>Grades</code> table.</p>
<table>
<thead>
<tr>
<th>StudentId</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>2</td>
<td>84</td>
</tr>
<tr>
<td>3</td>
<td>74</td>
</tr>
<tr>
<td>4</td>
<td>82</td>
</tr>
<tr>
<td>5</td>
<td>68</td>
</tr>
<tr>
<td>6</td>
<td>77</td>
</tr>
</tbody>
</table>
<p>What is the result of the following query?</p>
<pre><code class="language-SQL">SELECT AVG(Grade) + MAX(Grade)
FROM Grades
WHERE Grade = 84 OR Grade = 82; 
</code></pre>


Consider the following `Grades` table.

| StudentId | Grade |
| -- | -- |
| 1 | 79 |
| 2 | 84 |
| 3 | 74 | 
| 4 | 82 |
| 5 | 68 |
| 6 | 77 |

Now, consider the following query:

```SQL
SELECT Grade, Grade - MAX(Grade) OVER() AS DistanceToMax
FROM Grades
ORDER BY StudentId DESC
```

What are the values of the fifth row in the output?

<p>Consider the following <code>Grades</code> table.</p>
<table>
<thead>
<tr>
<th>StudentId</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>2</td>
<td>84</td>
</tr>
<tr>
<td>3</td>
<td>74</td>
</tr>
<tr>
<td>4</td>
<td>82</td>
</tr>
<tr>
<td>5</td>
<td>68</td>
</tr>
<tr>
<td>6</td>
<td>77</td>
</tr>
</tbody>
</table>
<p>Now, consider the following query:</p>
<pre><code class="language-SQL">SELECT Grade, Grade - MAX(Grade) OVER() AS DistanceToMax
FROM Grades
ORDER BY StudentId DESC
</code></pre>
<p>What are the values of the fifth row in the output?</p>


```SQL
SELECT COUNT(*) FROM PlayersTeams;
```

```SQL
SELECT COUNT(PlayerId) FROM PlayersTeams
WHERE TeamId = 5;
```

```SQL
SELECT COUNT(*) FROM PlayersTeams
WHERE TeamId = 5;
```

```SQL
SELECT COUNT(TeamId) FROM PlayersTeams;
```

Consider the structure of the `PlayersTeams` table.

| PlayerId | TeamId |
| -- | -- |
| 1  | 4 |
| 2 | 2 |
|3 | 1 |
| 4 | 4 |


Neither column accepts `NULL` values.


**(Select all that apply.)** Which option(s) will show how many players are on Team 5?


<p>Consider the structure of the <code>PlayersTeams</code> table.</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>TeamId</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>4</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>1</td>
</tr>
<tr>
<td>4</td>
<td>4</td>
</tr>
</tbody>
</table>
<p>Neither column accepts <code>NULL</code> values.</p>
<p><strong>(Select all that apply.)</strong> Which option(s) will show how many players are on Team 5?</p>


```SQL
SELECT COUNT(*) FROM PlayersTeams
GROUP BY TeamId;
```

```SQL
SELECT TeamId, COUNT(*) FROM PlayersTeams
GROUP BY TeamId;
```

```SQL
SELECT TeamId, COUNT(PlayerId) FROM PlayersTeams;
```

```SQL
SELECT TeamId, COUNT(PlayerId) FROM PlayersTeams
GROUP BY TeamId;
```


Consider the structure of the `PlayersTeams` table.

| PlayerId | TeamId |
| -- | -- |
| 1  | 4 |
| 2 | 2 |
|3 | 1 |
| 4 | 4 |

Neither column accepts `NULL` values.

**(Select all that apply.)** Which option(s) will output team numbers, and how many players are on these teams?

<p>Consider the structure of the <code>PlayersTeams</code> table.</p>
<table>
<thead>
<tr>
<th>PlayerId</th>
<th>TeamId</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>4</td>
</tr>
<tr>
<td>2</td>
<td>2</td>
</tr>
<tr>
<td>3</td>
<td>1</td>
</tr>
<tr>
<td>4</td>
<td>4</td>
</tr>
</tbody>
</table>
<p>Neither column accepts <code>NULL</code> values.</p>
<p><strong>(Select all that apply.)</strong> Which option(s) will output team numbers, and how many players are on these teams?</p>


Consider the following `Grades` table.

| StudentId | Grade |
| -- | -- |
| 1 | 79 |
| 2 | 84 |
| 3 | 74 | 
| 4 | 82 |
| 5 | 68 |
| 6 | 77 |

What values will the third output column (`MaxGrade`) contain if we run the following query?

```SQL
SELECT StudentId, Grade, MAX(Grade) OVER(PARTITION BY StudentId) AS MaxGrade
FROM Grades
ORDER BY StudentId ASC
```

<p>Consider the following <code>Grades</code> table.</p>
<table>
<thead>
<tr>
<th>StudentId</th>
<th>Grade</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>79</td>
</tr>
<tr>
<td>2</td>
<td>84</td>
</tr>
<tr>
<td>3</td>
<td>74</td>
</tr>
<tr>
<td>4</td>
<td>82</td>
</tr>
<tr>
<td>5</td>
<td>68</td>
</tr>
<tr>
<td>6</td>
<td>77</td>
</tr>
</tbody>
</table>
<p>What values will the third output column (<code>MaxGrade</code>) contain if we run the following query?</p>
<pre><code class="language-SQL">SELECT StudentId, Grade, MAX(Grade) OVER(PARTITION BY StudentId) AS MaxGrade
FROM Grades
ORDER BY StudentId ASC
</code></pre>


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: Grouping and Aggregation