For this assignment you will be writing T-SQL statements in SSMS.

Using the knowledge you gained in the Learning Team Collaborative Discussion this week, create and perform the following T-SQL statements:

Create a query using INNER JOIN that will show all rows and all columns from both tables. You will join the tables using DepartmentID from the Employees table and ID from the Departments table.

Add another department to the Departments table using a T-SQL INSERT statement. Call this department 'Maintenance'. Show the results of this insert by selecting all rows and all columns from the Departments table.

Add a row to the Employees table by adding the data specified in the Week Four tab of data within the Sample Data Excel spreadsheet. Make sure that the Department ID you use for this is NOT a valid Department ID from the Departments table (we are on purpose creating an 'orphan' record.

Create a T-SQL Query using OUTER JOIN to list all Employees that do not have a valid department assignment. (Hint: WHERE Departments.ID IS NULL)

Create a T-SQL Query using OUTER JOIN to list all Departments that have no employees. (Hint: WHERE Employees.ID IS NULL)

Create a T-SQL select statement that will use an INNER JOIN to pull data from both tables based on Departments.ID and Employees.DepartmentID. Return all rows where these columns match. Only show the following columns: DepartmentName, LastName, FirstName, AnnualSalary, and StartDate (in that order).

Based on the query you created in the previous step, create a View called viewDepartmentEmployees. Create a T-SQL statement to use the View.

Using viewDepartmentEmployees, create T-SQL that will order the view results by DepartmentName. Do not modify the view to do this.

Using viewDepartmentEmployees, create T-SQL that will order the view results by Employee LastName. Do not modify the view to do this.

Using viewDepartmentEmployees, create T-SQL that will return only those employees with salaries > $100,000. (Hint: Use a WHERE clause). Do not modify the view to do this.

Below is the information to be added for this assignement:

**Department table, Name: Maintanence, Description: Maintanence Department

**Employees table, LastName:Hansen, FIrstName:Larry MiddleName:____,StartDate:04/01/2017,AnnualSalary:$156,000

The structure and data of the department and employees have not been provided. Create tables with minimum required data and disregard information supplied.