43 lines
939 B
Transact-SQL
43 lines
939 B
Transact-SQL
USE AdventureWorks;
|
|
|
|
-- Óïðàæíåíèå 1
|
|
GO
|
|
CREATE VIEW HumanResources.vEmployeeDetails
|
|
WITH SCHEMABINDING AS
|
|
SELECT
|
|
e.EmployeeID
|
|
,c.Title
|
|
,c.FirstName
|
|
,c.MiddleName
|
|
,c.LastName
|
|
,c.Suffix
|
|
,e.Title AS JobTitle
|
|
,c.Phone
|
|
,c.EmailAddress
|
|
,c.EmailPromotion
|
|
,a.AddressLine1
|
|
,a.AddressLine2
|
|
,a.City
|
|
,sp.Name AS StateProvinceName
|
|
,a.PostalCode
|
|
,cr.Name AS CountryRegionName
|
|
,c.AdditionalContactInfo
|
|
FROM HumanResources.Employee e
|
|
INNER JOIN Person.Contact c
|
|
ON c.ContactID = e.ContactID
|
|
INNER JOIN HumanResources.EmployeeAddress ea
|
|
ON e.EmployeeID = ea.EmployeeID
|
|
INNER JOIN Person.Address a
|
|
ON ea.AddressID = a.AddressID
|
|
INNER JOIN Person.StateProvince sp
|
|
ON sp.StateProvinceID = a.StateProvinceID
|
|
INNER JOIN Person.CountryRegion cr
|
|
ON cr.CountryRegionCode = sp.CountryRegionCode;
|
|
GO
|
|
|
|
SELECT * FROM HumanResources.vEmployeeDetails;
|
|
|
|
--Óïðàæíåíèå 2
|
|
GO
|
|
CREATE UNIQUE CLUSTERED INDEX IX_vEmployeeDetails
|
|
ON HumanResources.vEmployeeDetails (EmployeeID); |