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)
--------------------------------------
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,pi.Quantity
from AdventureWorks2017.Production.Product as p
inner join AdventureWorks2017.Production.ProductSubcategory as psc on psc.ProductSubcategoryID= p.ProductSubcategoryID
inner join AdventureWorks2017.Production.ProductCategory as pc on pc.ProductCategoryID= psc.ProductCategoryID
inner join AdventureWorks2017.Production.ProductInventory as pi on pi.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= pi.LocationID
where st.[Group] ='Europe'
order by    p.ProductID
set identity_insert proizvodi oFF

select* from proizvodi