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

When we have relationships between tables, it is not unusual that we may need to view data from several tables combined. Let’s assume we have the following tables.

In `Departments`, we have information about departments, and `Employees` contains data about employees. Each employee is associated with some department through a foreign key that references the `Id` column of the `Departments` table. In the visual representation above, we can see that, for example, `Tob Gibson` is in the `Finance` department. However, if we select data from the `Employees` table, we only get the `DepartmentId` and no information on how this department is called.


CREATE DATABASE CompanyDatabase;
USE CompanyDatabase;

CREATE TABLE Departments
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Name NVARCHAR(100) NOT NULL
);

CREATE TABLE Employees
(
    Id INT PRIMARY KEY IDENTITY(1, 1),
    Name NVARCHAR(100) NOT NULL,
    DepartmentId INT REFERENCES Departments (Id)
);

INSERT INTO dbo.Departments (Name)
VALUES ('Finance'), ('Marketing'), ('IT'), ('Sales');

INSERT INTO dbo.Employees (Name, DepartmentId)
VALUES  ('Aidil Umarov', 3), ('Tom Gibson', 1), ('Ian Rob', 1), ('Jake Nates', 4);

SELECT * FROM dbo.Employees;

import React from 'react';
import { shallow, configure } from 'enzyme';
import Adapter from 'enzyme-adapter-react-16';

configure({ adapter: new Adapter() });

import HelloWorld from './app';

var TestResult = function() {
    this.succeeded = false;
    this.reason = "";
    this.input = "";
    this.expected_output = "";
    this.actual_output = "";
}

export const executeTests = function() {

  var results = [];

  result = new TestResult();
  result.input = 'HelloWorld Component';
  result.expected_output = "span containing text 'Hello World'"

  let wrapper = shallow(<HelloWorld />);

  // Call your Challenge function here.

  let type = wrapper.type();
  let testSuccessful = true;
  let failureReason;

  if (type !== 'span') {
    testSuccessful = false;
    failureReason = "You need to render exactly one span HTML element";
  } else if (wrapper.props().children != "Hello World") {
     testSuccessful = false;
     failureReason = "You have rendered wrong message in your span element";
  }

  result.actual_output = wrapper.html();

  if (testSuccessful) {
    result.succeeded = true;
    result.reason = "Succeeded"
  } else {
    result.succeeded = false;
    result.reason = failureReason;
  }

  results.push(result);

  return results;
}


React

Dart

GoJS React

Typescript React

Vue.js

The `DepartmentId` column of the `Employees` table stores integers. Therefore, we don’t see department names in the output. We have to join the `Employees` table with `Departments` to output department names and employee information.


## Joining the tables

To address the shortcoming above, we can join two tables to see department names and employee data. **Joining** combines rows of one table with the rows of another based on a related column. The basic syntax is as follows:

```SQL
SELECT [ColumnNames] FROM TableSchema.TableName1
JOIN TableSchema.TableName2
ON TableName1.Table1ColumnName = TableName2.Table2ColumnName;
```

We start with a `SELECT` statement, followed by the `JOIN` command where we specify which table we want to combine. After the `ON` keyword, we put our condition for joining. As a result, we get the rows from both tables with matching values for the corresponding columns.

### Example

Let’s consider an example to better understand the `JOIN` command. The `Employees` table has a column named `DepartmentId` that references the `Id` column of the `Departments` table. If we join `Employees` with `Departments` on `DepartmentId` and `Id`, then for each row from `Employees`, we’ll get a row from `Departments` with an `Id` that matches the `DepartmentId`:

The `DepartmentId` column of the `Employees` table stores integers. Therefore, we don’t see department names in the output. We have to join the `Employees` table with `Departments` to output department names and employee information.


# Joining the tables

To address the shortcoming above, we can join two tables to see department names and employee data. **Joining** combines rows of one table with the rows of another based on a related column. The basic syntax is as follows:

```SQL
SELECT [ColumnNames] FROM TableSchema.TableName1
JOIN TableSchema.TableName2
ON TableName1.Table1ColumnName = TableName2.Table2ColumnName;
```

We start with a `SELECT` statement, followed by the `JOIN` command where we specify which table we want to combine. After the `ON` keyword, we put our condition for joining. As a result, we get the rows from both tables with matching values for the corresponding columns.

## Example

Let’s consider an example to better understand the `JOIN` command. The `Employees` table has a column named `DepartmentId` that references the `Id` column of the `Departments` table. If we join `Employees` with `Departments` on `DepartmentId` and `Id`, then for each row from `Employees`, we’ll get a row from `Departments` with an `Id` that matches the `DepartmentId`:

Learn to retrieve data from multiple tables and combine the results using joins.

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

Querying with Joins