use AdventureWorksOBP

--zadatak 2

select p.Naziv,count(s.Kolicina)as 'kol' ,d.Naziv
from stavka as s
inner join Racun as r on
s.RacunID=r.IDRacun
inner join  Kupac as k on
r.KupacID=r.KupacID
inner join Grad as g on
g.IDGrad=k.GradID
inner join Drzava as d on
d.IDDrzava=g.DrzavaID
inner join Proizvod as p on
p.IDProizvod=s.ProizvodID
where d.Naziv='Bosna i Hercegovina'
group by p.Naziv,s.Kolicina,d.Naziv
order by kol asc

--Zad 03
select*from Grad
insert into Kupac(ime,Prezime)
values('Leonard','Cohen')
insert into grad(Naziv)
values('MOntreal')

insert into Drzava(Naziv)
values ('Canada')
select * from kupac

where ime ='Leonard' and Prezime='Cohen'

--Zadatak04
/*create table NajprodavanijiProizvodi
(
 IDproizvod int ,
 Naziv nvarchar(max) ,
 prodanihKomada int

)
insert into NajprodavanijiProizvodi (IDproizvod,Naziv,prodanihKomada)
select TOP(10) p.IDProizvod,p.naziv, (sum(stavka.Kolicina)) as 'Najveca kolicina'
from stavka
inner join racun as r
on r.IDRacun=Stavka.RacunID
inner join Proizvod as p
on p.IDProizvod=stavka.ProizvodID
group by p.IDProizvod,stavka.Kolicina,p.Naziv

select *from NajprodavanijiProizvodi*/







create table najboljiproizvod(
idproizvod int ,
naziv nvarchar(255),
prodani_komadi int


)



insert into najboljiproizvod (idproizvod,naziv,prodani_komadi)

select TOP(10) p.IDProizvod ,  p.Naziv , (sum(s.Kolicina))  as prodani from Stavka as s
inner join Racun as r on
r.IDRacun=s.RacunID
inner join Proizvod as p on
p.IDProizvod=s.ProizvodID
group by p.IDProizvod,p.Naziv,s.Kolicina
select * from najboljiproizvod


--Zadatak05
 create database Pizzerija
 use Pizzerija

create table dostavljac1
(
idDostavljac int constraint pk_dost1 primary key,
ime nvarchar(max),
prezime nvarchar(max),
oib nvarchar unique,
DostavljacID int  constraint fk_dosta1 foreign key references dostava1(iddostava)

)

create table pizza1
(
Idpizza int constraint pk_pizza1 primary key,
naziv nvarchar,
 cijena  money check(cijena between 20 and 40)


)

create table dostava1
(
iddostava int constraint pk_dosta1 primary key,
datum date,
pizzaID int  constraint fk_pizza1 foreign key references pizza1(Idpizza)

)


--ishod 4 ...
use AdventureWorksOBP
go


--zadatak01
CREATE PROC p125
    @naziv nvarchar(50),
    @IDDrzava int OUTPUT
AS
IF NOT EXISTS(SELECT * FROM Grad WHERE Naziv = @naziv and DrzavaID=@IDDrzava)
    BEGIN
        INSERT INTO Grad VALUES (@naziv,@IDDrzava)
        SET @IDDrzava = SCOPE_IDENTITY()
    END
ELSE
    BEGIN
        SET @IDDrzava = -1
    END
GO

DECLARE @IDDrazava int
EXEC p15 'Zagreb', @IDDrazava OUTPUT
PRINT @IDDrzava
GO


--3 zadatak
CREATE PROC ZamjenaKomericajlista
    @Operacija char(1),
    @IDKomercijalist int OUTPUT,
    @Ime nvarchar(50),
    @Prezime nvarchar(50),
    @StalniZaposlenik char(11)
AS
IF @Operacija = 'U'
    UPDATE Komercijalist
    SET Ime = @Ime, Prezime = @Prezime, StalniZaposlenik = @StalniZaposlenik
    WHERE IDKomercijalist = @IDKomercijalist

ELSE IF @Operacija = 'I' BEGIN
    INSERT INTO Komercijalist(Ime, Prezime, StalniZaposlenik) VALUES (@Ime, @Prezime, @StalniZaposlenik)
    SET @IDKomercijalist = SCOPE_IDENTITY()
END
ELSE IF @Operacija = 'D'
    DELETE FROM Komercijalist WHERE IDKomercijalist = @IDKomercijalist
GO

--i7

--Prljavo citanje
sastoji se od mogucih 3 uzroka problema
a to su :
1)prva transakcija promijeni vrijednost retka
2)2.trans procita tu vrijednost-prvljavo citanje
3)prva transakcija odustane



--ishod 8
EXEC sp_addumpdevice
    @devtype = 'DISK',
    @logicalname = 'Moja_lokacija',
    @physicalname = 'D:\BACKUP\OBP2.bak'

    BACKUP DATABASE nasabaza TO Moja_lokacija
WITH
    DIFFERENTIAL,
    NAME = 'Druga kopija',
    DESCRIPTION = 'Ovo je druga kopija',
    STATS

    insert into KreditnaKartica(IDKreditnaKartica...)
    values(...)

    RESTORE DATABASE OBP_Vraceno FROM Moja_lokacija
WITH
    FILE = 3,
    MOVE 'OBP' TO 'D:\backup\OBP_Vraceno.mdf',
    MOVE 'OBP_log' TO 'D:\backup\OBP_Vraceno.ldf',
    RECOVERY,
    STATS

    --2zadatak

    create login vjenci
    with password= '1234'

    create user vjenci from login vjenci

    create role firma
    go
    grant select on proizvod to firma
    go
    exec sp_addrolemember @rolename='firma', @membername ='vjenci'

select Kupac.ime,Kupac.Prezime, d.Naziv from Kupac inner join grad as g on g.IDGrad=Kupac.GradID inner join Drzava as d on d.IDDrzava=g.DrzavaID where d.IDDrzava=1 and Telefon like '%%' and Telefon like '%-%'

select Proizvod.Naziv,count(Stavka.Kolicina) as Koliina from Stavka inner join Racun on Racun.IDRacun=Stavka.RacunID inner join Proizvod on Proizvod.IDProizvod=Stavka.ProizvodID inner join Kupac on Kupac.IDKupac=Racun.KupacID inner join Grad on Grad.IDGrad=Kupac.GradID inner join Drzava on Drzava.IDDrzava=Grad.IDGrad where Drzava.IDDrzava=1 group by Proizvod.Naziv,Stavka.Kolicina order by Koliina

insert into Drzava(Naziv) values ('Canada') insert into Grad(Naziv,DrzavaID) values ('Montreal',3) insert into Kupac(Ime,Prezime,GradID) values ('Leonardo',' Cohen',14) select * from Kupac where Ime ='Leonardo'

select * from Grad

CREATE TABLE Najprodavaniji( IDProizvod int constraint pk_idproizvod primary key identity, Naziv nvarchar(50) not null, ProdanihKomada int ) insert into Najprodavaniji

select top(10) p.IDProizvod,p.Naziv,sum(Stavka.Kolicina) as Kolicina from Stavka inner join Proizvod as p on P.IDProizvod=stavka.ProizvodID group by p.IDProizvod, p.Naziv,Kolicina

SELECT * from Stavka

create table Dostavljac( IDDostavljac int constraint pk_iddostave primary key identity, Ime nvarchar (50), Prezime nvarchar (50), OIB nvarchar(15) unique

)

create table Pizza( IDPizza int constraint pk_iddostave primary key identity, Naziv nvarchar (50), Cijena money check (cijena between 20 and 40), OIB int unique

)

create table Dostava( IDDostave int constraint pk_iddostave primary key identity, Datum date, DostavljacID int constraint fk_dostavljacid foreign key references Dostavljac(IDDostavljac), PizzaID int constraint fk_pizzaid foreign key references Pizza(IDPizza)

)

create table Zapisnik( IDZapisnik int primary key identity, Sadrzaj nvarchar(max)

) go alter trigger triger on Drzava after insert,update,delete

AS DECLARE @naziv nvarchar(50) SELECT @naziv=IDDrzava from inserted IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN

insert into Zapisnik(Sadrzaj) values ('Umetnut je novi redak s IDDrzava :' + @naziv + '"')

END ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN

insert into Zapisnik(Sadrzaj) values ('Obrisan je redak s IDDrzava :' + @naziv + '"') END ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN

insert into Zapisnik(Sadrzaj) values ('Primijenjen je redak s IDDrzava : '+ @naziv + '"') end go select * from Zapisnik insert into Drzava(Naziv) values('Kasokasapa') delete from Drzava where Naziv = 'Kasokasapa' select * from Drzava go

create trigger trigi on KreditnaKartica after update as declare @istekgodina int declare @istekmjesec int select @istekgodina=IstekGodina from inserted select @istekmjesec=IstekMjesec from inserted

IF EXISTS(select @istekgodina) AND EXISTS(select @istekmjesec) BEGIN insert into Zapisnik(Sadrzaj) values ('Primijenjen trajanje vie kartica : '+ @naziv + '"') end go

alter proc procedura

as DECLARE @Grad nvarchar(50) DECLARE @Naziv nvarchar(50) DECLARE @Podaci xml = ' <Drzava Naziv="Hrvatska"> <Grad>"Zagreb"</Grad> <Grad>"Vinkonci"</Grad> <Grad>Koprivnica</Grad> </Drzava> ' INSERT INTO Drzava (Naziv) SELECT

Tablica.Stupac.value('@Naziv', 'nvarchar(50)') FROM @Podaci.nodes('/Drzava') as Tablica(Stupac) INSERT INTO Grad (Naziv) SELECT Tablica.Stupac.value('.', 'nvarchar(50)') FROM @Podaci.nodes('/Drzava/Grad') as Tablica(Stupac)

exec procedura Select * from Grad