Priprema4

create database Priprema4 on primary
( NAME = 'Priprema4',
  FILENAME = 'C:\BP2\data\Priprema4.mdf',
  size = 10MB,
  maxsize =  unlimited,
  filegrowth = 5%
)
log on (
NAME = 'Priprema4_log',
  FILENAME = 'C:\BP2\log\Priprema4_log.ldf',
  size = 10MB,
  maxsize =  unlimited,
  filegrowth = 5%
)

use Priprema4

create table Kupci (
KupacID int not null identity(1,1) constraint PK_Kupci primary key,
Ime nvarchar(35) not null,
Prezime nvarchar(35) not null,
JMBG nvarchar(13) not null,
DatumRegistracije datetime null
)

create table Proizvodi (
ProizvodID int not null identity(1,1) constraint PK_Proizvodi primary key,
Naziv nvarchar(35) not null)

create table Narudzbe(
NarudzbaID int not null identity(1,1) constraint PK_Narudzbe primary key,
KupacID int not null constraint FK_Narudzbe_Kupci foreign key references Kupci(KupacID),
ProizvodID int not null constraint FK_Narudzbe_Proizvodi foreign key references Proizvodi(ProizvodID),
DatumNarudzbe datetime not null,
Kolicina int not null,
Cijena int not null
)

--3
insert into Kupci
values ('Amer', 'Stovrag', '111-111-1111', getdate())

insert into Proizvodi
values ('iPhone X')

--4
use AdventureWorks2017
select P.FirstName as 'Ime', P.LastName as 'Prezime',
 substring(convert(nvarchar(50),P.rowguid),1,13) as 'JMBG', getdate() as 'Datum'
into #TempPriprema4_1
from Person.Person as P
where P.Title is not null and P.MiddleName is not null

--5

use Priprema4
insert into Kupci
select *
from #TempPriprema4_1

select*from Kupci

insert into Narudzbe
select KupacID, (select ProizvodID from Proizvodi), getdate(),
2, 1200
from Kupci

select * from  Narudzbe

--6
create view view_KupciNarudzbe
as
select K.Ime, K.Prezime, P.Naziv, N.DatumNarudzbe,
 N.Cijena*N.Kolicina as 'Ukupna cijena'
from Kupci as K inner join Narudzbe as N
    on K.KupacID=N.KupacID INNER JOIN Proizvodi as P
    on P.ProizvodID=N.ProizvodID

--7

create procedure usp_KupciUpdate
(@KupacID int,
@Ime nvarchar(35),
@Prezime nvarchar(35),
@JMBG nvarchar(13),
@DatumRegistracije datetime=null
)
as
begin
update Kupci
set Ime=@Ime, Prezime=@Prezime, JMBG=@JMBG,
DatumRegistracije=@DatumRegistracije
where KupacID=@KupacID
end

--8

SELECT*FROM Kupci

EXEC usp_KupciUpdate 1, 'Azra', 'Turkic', '111-222-2222'

--9

create procedure usp_KupciDelete
(@KupacID int)
as begin
delete Narudzbe
from Narudzbe as N inner join Kupci as K
on N.KupacID = K.KupacID and K.KupacID=@KupacID

delete from Kupci
where KupacID=@KupacID
end

--10

EXEC usp_KupciDelete 1