USE AdventureWorks2014
GO
CREATE VIEW HumanResources.view_Employees with SCHEMABINDING AS
SELECT E.BusinessEntityID as 'ZaposlenikID',
P.FirstName AS 'Ime',
P.LastName AS 'Prezime'
FROM HumanResources.Employee AS E INNER JOIN Person.Person AS P ON E.BusinessEntityID = P.BusinessEntityID
--
SELECT * FROM HumanResources.view_Employees
ORDER BY ZaposlenikID

--
CREATE TABLE Uposlenik (
	UposlenikID int,
	NacionalniID NVARCHAR(15) NOT NULL,
	LoginID NVARCHAR(256) NOT NULL,
	RadnoMjesto NVARCHAR(50) NOT NULL
);
--
CREATE TABLE Osoba (
	OsobaID INT,
	VrstaUposlenika NVARCHAR(2) NOT NULL,
	Prezime NVARCHAR(50) NOT NULL,
	Ime NVARCHAR(50) NOT NULL
);
--
INSERT INTO dbo.Uposlenik(UposlenikID, NacionalniID, LoginID, RadnoMjesto)
SELECT E.BusinessEntityID, E.NationalIDNumber, E.LoginID, E.JobTitle
FROM HumanResources.Employee as E
WHERE JobTitle LIKE '%eng%'
--
INSERT INTO dbo.Osoba(OsobaID,VrstaUposlenika,Prezime,Ime)
SELECT P.BusinessEntityID, P.PersonType, P.LastName,P.FirstName
FROM Person.Person as P
WHERE PersonType LIKE 'EM'
--
SELECT * FROM Uposlenik
SELECT * FROM Osoba

--
CREATE VIEW dbo.UposlenikOsoba with SCHEMABINDING AS
SELECT U.UposlenikID AS 'ID',
O.Prezime as 'Prezime',
O.Ime as 'Ime',
U.NacionalniId as 'Nacionalni ID',
U.LoginID as 'Login ID',
U.RadnoMjesto AS 'RadMj',
O.VrstaUposlenika AS 'VrstaUp'
FROM dbo.Uposlenik AS U INNER JOIN dbo.Osoba AS O ON U.UposlenikID = O.OsobaID
--
CREATE UNIQUE CLUSTERED INDEX IX_UpOS ON dbo.UposlenikOsoba (ID)
SELECT * FROM dbo.UposlenikOsoba
--