Mohito

create database Mohito

use Mohito

create table Proizvodi (
ProizvodiID int not null identity(1,1) constraint PK_Proizvodi primary key,
Naziv nvarchar(50) not null,
Kategorija nvarchar(50) not null,
Cijena decimal(18,2) not null
)

create table Narudzbe (
NarudzbaID int not null identity(1,1) constraint PK_Narudzbe primary key,
DatumNarudzbe date not null,
DatumIsporuke date not null,
Drzava nvarchar(50) not null,
Ukupno decimal(18,2) not null
)

create table StavkeNarudzbe (
NarudzbaID int not null constraint fk_StavkeNarudzbe_Narudzbe foreign key references Narudzbe(NarudzbaID),
ProizvodID int not null constraint fk_StavkeNarudzbe_Proizvodi foreign key references Proizvodi(ProizvodiID),
primary key (NarudzbaID, ProizvodID),
Kolicina int not null,
Cijena decimal(18,2) not null,
Iznos decimal(18,2) not null
)

--2
use NORTHWND
go

set identity_insert Mohito.dbo.Proizvodi on
insert into Mohito.dbo.Proizvodi (ProizvodiID, Naziv, Kategorija, Cijena)
select distinct P.ProductID, P.ProductName, C.CategoryName, P.UnitPrice
from Products as P inner join Categories as C
    on P.CategoryID = C.CategoryID inner join [Order Details] as OD
    ON OD.ProductID=P.ProductID INNER JOIN Orders AS O
    ON O.OrderID=OD.OrderID
where YEAR(O.OrderDate) = 1996
set identity_insert Mohito.dbo.Proizvodi off

set identity_insert Mohito.dbo.Narudzbe on
insert into Mohito.dbo.Narudzbe (NarudzbaID, DatumNarudzbe, DatumIsporuke, Drzava, Ukupno)
select distinct O.OrderID, O.OrderDate, O.ShippedDate, O.ShipCountry,
        sum(OD.Quantity*OD.UnitPrice*OD.Discount)
from [Order Details] as OD inner join Orders as O
on O.OrderID=OD.OrderID
WHERE  YEAR(O.OrderDate) = 1996
group by O.OrderID, O.OrderDate, O.ShippedDate, O.ShipCountry
set identity_insert Mohito.dbo.Narudzbe off


insert into Mohito.dbo.StavkeNarudzbe(NarudzbaID, ProizvodID, Kolicina, Cijena, Iznos)
select O.OrderID, P.ProductID, OD.Quantity, OD.UnitPrice, OD.Quantity*OD.UnitPrice
from [Order Details] as OD inner join Orders as O
on O.OrderID=OD.OrderID INNER JOIN Products as P on P.ProductID = OD.ProductID
WHERE  YEAR(O.OrderDate) = 1996

--3

use Mohito


CREATE TABLE Skladista (
SkladisteID int not null identity(1,1) constraint PK_Skladista primary key,
Naziv nvarchar(20)
)

create table SkladistaProizvodi (
ProizvodID int not null constraint fk_SkladistaProizvodi_Proizvodi foreign key references Proizvodi(ProizvodiID),
SkladisteID int not null constraint fk_SkladistaProizvodi_Skladista foreign key references Skladista(SkladisteID),
PRIMARY KEY (ProizvodID, SkladisteID),
Kolicina int not null
)

--4

insert into Skladista
VALUES ('Mostar'),
('Sarajevo'),
('Banja Luka')

insert into SkladistaProizvodi
select P.ProizvodiID, (select SkladisteID from Skladista where Naziv='Mostar'), 0
from Proizvodi as P

insert into SkladistaProizvodi
select P.ProizvodiID, (select SkladisteID from Skladista where Naziv='Sarajevo'), 0
from Proizvodi as P

insert into SkladistaProizvodi
select P.ProizvodiID, (select SkladisteID from Skladista where Naziv='Banja Luka'), 0
from Proizvodi as P


select* from SkladistaProizvodi

--5

update Proizvodi
set Cijena=Cijena+5
where Kategorija like 'Confections'

select*from Proizvodi
where Kategorija like 'Confections'


--6

select sum(N.Ukupno) as Zarada, month(N.DatumIsporuke) as Mjesec
from Narudzbe as N
group by MONTH(N.DatumIsporuke)

--7
select TOP 5 P.Naziv, sum(SN.Kolicina) as Prodano
from Proizvodi as P inner join StavkeNarudzbe as SN
    on P.ProizvodiID=SN.ProizvodID INNER JOIN Narudzbe AS N
    ON N.NarudzbaID=SN.NarudzbaID
GROUP BY P.Naziv
order by Prodano desc

--8
select P.Naziv, sum(SN.Kolicina), SUM(SN.Iznos) as Zarada
from Proizvodi as P inner join StavkeNarudzbe as SN
    on P.ProizvodiID=SN.ProizvodID INNER JOIN Narudzbe AS N
    ON N.NarudzbaID=SN.NarudzbaID
where month(N.DatumIsporuke)=7 or month(N.DatumIsporuke)=8
GROUP BY  P.Naziv
order by Zarada desc

--9
UPDATE SkladistaProizvodi
SET Kolicina = 100
from Proizvodi as P inner join SkladistaProizvodi as SP
on SP.ProizvodID=P.ProizvodiID
WHERE P.Kategorija LIKE 'Confections'

--10

delete StavkeNarudzbe
from StavkeNarudzbe as SN inner join Narudzbe as N
ON SN.NarudzbaID=N.NarudzbaID
WHERE N.Drzava LIKE 'Brazil'

delete
from Narudzbe
where Drzava like 'Brazil'


select*from StavkeNarudzbe