|
Execute the following
SQL Server T-SQL scripts in Management Studio Query Editor to create a view and test it with a SELECT query.
-- SQL create view - SQL inner join - SQL select from view
-- SQL string concatenation - T-SQL inner join
USE AdventureWorks;
GO
CREATE VIEW [HumanResources].[vEmpDeptHistory]
AS
SELECT c.[LastName] + ', ' + c.[FirstName] AS [Name],
cm.[LastName] + ', ' + cm.[FirstName] AS Manager,
s.[Name] AS [Shift],
d.[Name] AS [Department],
d.[GroupName],
convert(VARCHAR,edh.[StartDate],110) AS StartDate,
convert(VARCHAR,edh.[EndDate],110) AS EndDate
FROM [HumanResources].[Employee] e
INNER JOIN [HumanResources].[Employee] m
ON e.ManagerID = m.EmployeeID
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = e.[ContactID]
INNER JOIN [Person].[Contact] cm
ON cm.[ContactID] = m.[ContactID]
INNER JOIN [HumanResources].[EmployeeDepartmentHistory] edh
ON e.[EmployeeID] = edh.[EmployeeID]
INNER JOIN [HumanResources].[Department] d
ON edh.[DepartmentID] = d.[DepartmentID]
INNER JOIN [HumanResources].[Shift] s
ON s.[ShiftID] = edh.[ShiftID];
GO
-- Test view
SELECT *
FROM [HumanResources].[vEmpDeptHistory]
ORDER BY Name
GO
/* Partial results
Name Manager Shift Department
Abbas, Syed Welcker, Brian Day Sales
Abercrombie, Kim Brown, Jo Day Production
Abolrous, Hazem Hamilton, James Day Quality Assurance
Ackerman, Pilar Krebs, Peter Day Shipping and Receiving
Adams, Jay Campbell, John Evening Production
Ajenstat, F… Trenary, Jean Day Information Services
Alberts, Amy Welcker, Brian Day Sales
Alderson, Greg Kane, Lori Night Production
*/
-- Cleanup
DROP VIEW [HumanResources].[vEmpDeptHistory]
GO
Related article:
CREATE VIEW (Transact-SQL)
|
