create database databejz
use databejz

create table proizvodi
(
ProizvodID int not null identity(1,1) constraint PK_ProizvodID  primary key,
 Sifra nvarchar (25) not null constraint UQ_sifra unique,
    Naziv nvarchar (50) not null,
    Kategorija nvarchar (50) null,
 Podkategorija  nvarchar (50) null,
 Boja   nvarchar (15) not null,
 Cijena decimal not null,
 StanjeZaliha   int not null
);

create table prodaja
(
ProdajaID int not null identity(1,1) constraint PK_ProdajaID primary key,
 ProizvodID int not null constraint FK_Proizvodi_ProizvodID foreign key references proizvodi(ProizvodID),
 Godina  int not null,
Mjesec int not null,
 UkupnoProdano int not null,
UkupnoPopust decimal not null,
UkupnoIznos decimal not null


);

insert into proizvodi
(Sifra,Naziv,Kategorija,Podkategorija,Boja,Cijena,StanjeZaliha)
values('ssds','sako',null,null,'roza',34,15)

insert into prodaja
(ProizvodID,Godina,Mjesec, UkupnoProdano,UkupnoPopust,UkupnoIznos)
values(1,2014,3,33, 0.9, 30),(1,2014,7,33, 0.9, 30),(1,2015,3,33, 0.9, 30)
--------------------------------------
--------------------------------------
--------------------------------------

--code below this line throws error about duplicate key
--Violation of PRIMARY KEY constraint 'PK_ProizvodID'. Cannot insert duplicate key in object 'dbo.proizvodi'. The duplicate key value --is (747).

set identity_insert proizvodi ON
insert into proizvodi
(ProizvodID,Sifra,Naziv,Kategorija,Podkategorija,Boja,Cijena,StanjeZaliha)
select distinct p.ProductID, p.ProductNumber,p.[Name], pc.[Name],psc.[Name], p.Color,p.ListPrice,pii.Quantity
from AdventureWorks2017.Production.Product as p
join AdventureWorks2017.Production.ProductSubcategory as psc on psc.ProductSubcategoryID= p.ProductSubcategoryID
join AdventureWorks2017.Production.ProductCategory as pc on pc.ProductCategoryID= psc.ProductCategoryID
inner join AdventureWorks2017.Production.ProductInventory as pii on pii.ProductID= p.ProductID
inner join AdventureWorks2017.Sales.SalesOrderDetail as sod on sod.ProductID= p.ProductID
inner join AdventureWorks2017.Sales.SalesOrderHeader as soh on soh.SalesOrderID= sod.SalesOrderID
inner join AdventureWorks2017.Sales.SalesTerritory as st on st.TerritoryID= soh.TerritoryID
inner join AdventureWorks2017.Production.[Location] as l on l.LocationID= pii.LocationID
where st.[Group] ='Europe'
order by p.ProductID
set identity_insert proizvodi OFF