flaki

Question

-- I4 --
-- Zad. 1.

-- prvi dio zadatka, do "Ako postoji država..."
CREATE PROC Zad1I4 
@NazivDrzave nvarchar (50), 
@izlazni int output
AS
INSERT INTO Drzava (Naziv) VALUES (@NazivDrzave)
SELECT  @izlazni = Drzava.IDDrzava FROM Drzava
WHERE Drzava.Naziv = @NazivDrzave
GO

DECLARE @var int
EXEC Zad1I4 'TestnaDrzava', @var output

-- procedura i sa drugim dijelom, gdje se provjerava dali Drzava.Naziv vec postoji u bazi
ALTER PROC Zad1I4 
@NazivDrzave nvarchar (50), 
@izlazni int output
AS
IF EXISTS (SELECT Drzava.Naziv FROM Drzava WHERE Drzava.Naziv = @NazivDrzave)
BEGIN 
PRINT -1
RETURN -1
END
ELSE
BEGIN
INSERT INTO Drzava (Naziv) VALUES (@NazivDrzave)
SELECT  @izlazni = Drzava.IDDrzava FROM Drzava
WHERE Drzava.Naziv = @NazivDrzave
PRINT @izlazni -- radi testiranja
END
GO


DECLARE @var int
EXEC Zad1I4 'TestnaDrzava', @var output
GO
-- isprintano 7, drzava je dodana pod ID-em 7.

DECLARE @var int
EXEC Zad1I4 'TestnaDrzava', @var output
GO
-- isprintano i vraćeno -1 jer Drzava pod timo Nazivom već postoji

DELETE FROM Drzava WHERE Naziv = 'TestnaDrzava'


-- Zad. 2

CREATE FUNCTION Zad2I4 
(
@IDKategorije int
)
RETURNS int
AS
BEGIN
DECLARE @var int

SELECT @var = COUNT (Kategorija.IDKategorija) FROM Proizvod, Kategorija JOIN Potkategorija ON Kategorija.IDKategorija = Potkategorija.KategorijaID WHERE IDKategorija = @IDKategorije 
GROUP BY Proizvod.PotkategorijaID, Potkategorija.IDPotkategorija
HAVING Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija



IF @var <= 0 OR @var = NULL
BEGIN
RETURN 0
END
IF @var > 0
BEGIN 
RETURN @var
END

RETURN @var
END
GO 

DECLARE @poziv int
SET @poziv = dbo.Zad2I4 (0)
SELECT @poziv
GO



SELECT * FROM Kategorija

-- Zad. 3

SELECT * FROM Drzava

CREATE PROC Zad3I4 (@naredba char(1), @IDDrzave int, @Naziv nvarchar(50))
AS
IF @naredba = 'I'
BEGIN
INSERT INTO Drzava (Naziv) VALUES (@Naziv)
END
ELSE IF @naredba = 'U'
BEGIN
UPDATE Drzava 
SET Naziv = @Naziv
WHERE Drzava.IDDrzava = @IDDrzave
END
ELSE IF @naredba = 'D'
BEGIN
DELETE FROM Drzava
WHERE Drzava.IDDrzava = @IDDrzave
END
ELSE IF @naredba = 'R'
BEGIN
SELECT * FROM Drzava 
WHERE Drzava.IDDrzava = @IDDrzave

END

SELECT * FROM Drzava

EXEC Zad3I4 'I', NULL, 'TestDrzava1'
EXEC Zad3I4 'U', 8, 'TestDrzava2'
EXEC Zad3I4 'R', 8, NULL
EXEC Zad3I4 'D', 8, NULL


CREATE FUNCTION Zad3I4 
(
@naredba char(1), @IDDrzave int, @Naziv nvarchar(50)
)
RETURNS int
AS
BEGIN
DECLARE @tabl TABLE (IDDrzave int, Naziv nvarchar(50))
DECLARE @print as varchar(max)
SET @print = (SELECT * FROM @tabl WHERE IDDrzave = @IDDrzave)
IF @naredba = 'I'
BEGIN
INSERT INTO @tabl (IDDrzave) VALUES (@Naziv)
END
ELSE IF @naredba = 'U'
BEGIN
UPDATE Drzava 
SET Naziv = @Naziv
WHERE Drzava.IDDrzava = @IDDrzave
END
ELSE IF @naredba = 'D'
BEGIN
DELETE FROM @tabl
WHERE IDDrzave = @IDDrzave
END
ELSE IF @naredba = 'R'
BEGIN
return @print
END
RETURN @tabl
END


-- I5 --
-- Zad. 1 --

CREATE TABLE Zapisnik (Datum_i_vrijeme datetime, Sadrzaj nvarchar(500))
GO

DROP TABLE Zapisnik

CREATE TRIGGER Zad4I5
ON Drzava
AFTER INSERT, UPDATE, DELETE

AS
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) 
BEGIN
INSERT INTO Zapisnik (Sadrzaj, Datum_i_vrijeme)
SELECT 'Promijenjen je redak s IDDrzava ' +  CAST(IDDrzava as nvarchar), getdate() FROM inserted
END

IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS (SELECT * FROM deleted) 
BEGIN
INSERT INTO Zapisnik (Sadrzaj, Datum_i_vrijeme)
SELECT 'Umetnut je novi redak s IDDrzava ' +  CAST(IDDrzava as nvarchar), getdate() FROM inserted
END
IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
INSERT INTO Zapisnik (Sadrzaj, Datum_i_vrijeme)
SELECT 'Obrisan je redak s IDDrzava ' + CAST(IDDrzava as nvarchar), getdate() FROM deleted
END
GO

INSERT INTO Drzava (Naziv) VALUES ('TestnaDrzava_5')

UPDATE Drzava
SET Naziv = 'Kanada'
WHERE IDDrzava = 10

DELETE FROM Drzava WHERE IDDrzava = 10

SELECT * FROM Drzava
SELECT * FROM Zapisnik


-- Zad 2. 

CREATE TRIGGER Zad2I5
ON KreditnaKartica
AFTER UPDATE
AS
DECLARE @ukupnoInsertanih int
DECLARE @ukupnoDeleteanih int

SELECT @ukupnoInsertanih = COUNT (*) FROM inserted
SELECT @ukupnoDeleteanih = COUNT (*) FROM deleted

PRINT @ukupnoInsertanih
PRINT @ukupnoDeleteanih

IF @ukupnoInsertanih >= 2 AND @ukupnoDeleteanih >= 2
BEGIN
INSERT INTO Zapisnik (Sadrzaj) 
VALUES ('Promijenjeno je trajanje više kartica (' + CAST(@ukupnoInsertanih as nvarchar) + ')')
RETURN
END
ELSE IF @ukupnoInsertanih = 1 AND @ukupnoDeleteanih = 1
BEGIN
INSERT INTO Zapisnik (Sadrzaj)
SELECT 'Promijenjeno trajanje kartice pod rednim brojem ' + deleted.Broj + 
'. Stara vrijednost trajanja je ' + CAST(deleted.IstekGodina as nvarchar) + '-' + CAST(deleted.IstekMjesec as nvarchar) +  
' nova vrijednost trajanja je ' + CAST(inserted.IstekGodina as nvarchar) + '-' + CAST(inserted.IstekMjesec as nvarchar)
FROM inserted,deleted
RETURN
END
GO

UPDATE KreditnaKartica 
SET Tip = 'Visa'
WHERE IDKreditnaKartica = 1 OR IDKreditnaKartica = 2 OR IDKreditnaKartica = 3 OR IDKreditnaKartica = 4

UPDATE KreditnaKartica 
SET Tip = 'Visa', IstekGodina = '2030', IstekMjesec = '03'
WHERE IDKreditnaKartica = 4

SELECT * FROM KreditnaKartica

SELECT * FROM Zapisnik

--/6?__-

alter PROC Zad1I6
( @xml xml )
AS
DECLARE @Drzava nvarchar (50)

SET @Drzava = (SELECT Tablica.Stupac.value ('@Naziv','nvarchar(50)') AS Drzava  FROM @xml.nodes('/Drzava') AS Tablica (Stupac)) 

if not exists (select * from drzava where naziv = @Drzava)
begin
insert Drzava (naziv) values (@drzava)
end

declare @IDDrzave int = (SELECT IDDrzava FROM Drzava WHERE Naziv = @Drzava) 


INSERT INTO Grad (Grad.Naziv, Grad.DrzavaID) 
SELECT Tablica.Stupac.value('.', 'nvarchar(50)') AS Grad, @IDDrzave  FROM @xml.nodes('/Drzava/Grad') AS Tablica (Stupac)
GO


DECLARE @xml xml
SET @xml = N'<Drzava Naziv = "Hrvatska">
<Grad>Omiš</Grad>
<Grad>Primošten</Grad>
<Grad>Brodarica</Grad>
</Drzava>'

exec Zad1I6 @xml

-- I6 --
-- Zad 1.

CREATE PROC Zad1I6
AS
DECLARE @Drzava nvarchar (50)
DECLARE @Grad nvarchar (50)
DECLARE @IDDrzave int
DECLARE @xml xml
SET @xml = N'<Drzava Naziv = "Hrvatska">
<Grad>Omiš</Grad>
<Grad>Primošten</Grad>
<Grad>Brodarica</Grad>
</Drzava>'

SET @Drzava = (SELECT Tablica.Stupac.value ('@Naziv','nvarchar(50)') AS Drzava  FROM @xml.nodes('/Drzava') AS Tablica (Stupac)) 
SET @Grad = (SELECT Tablica.Stupac.value ('@Grad','nvarchar(50)') AS Grad FROM @xml.nodes('/Drzava/Grad') AS Tablica (Stupac)) 

SET @IDDrzave = (SELECT Drzava.IDDrzava FROM Drzava WHERE Drzava.Naziv = @Drzava) 

IF EXISTS (SELECT Drzava.Naziv FROM Drzava WHERE Drzava.Naziv = @Drzava)  
BEGIN
INSERT INTO Grad (Grad.Naziv, Grad.DrzavaID) SELECT Tablica.Stupac.value ('@Grad','nvarchar(50)') AS Grad, @IDDrzave FROM @xml.nodes('/Drzava/Grad') AS Tablica (Stupac)
PRINT 'Insertam Grad, drzava ' + @Drzava + ' postoji!'
END
ELSE IF EXISTS (SELECT Grad.Naziv, Drzava.Naziv FROM Grad, Drzava WHERE Grad.Naziv = @Grad AND Drzava.Naziv = @Drzava)
BEGIN
PRINT 'Postoje Grad i Drzava, ne insertam ništa!'
END
ELSE
BEGIN
INSERT INTO Drzava (Drzava.Naziv) SELECT Tablica.Stupac.value ('@Naziv','nvarchar(50)') AS Drzava  FROM @xml.nodes('/Drzava') AS Tablica (Stupac)
INSERT INTO Grad (Grad.Naziv, Grad.DrzavaID) SELECT Tablica.Stupac.value ('@Grad','nvarchar(50)') AS Grad, @IDDrzave  FROM @xml.nodes('/Drzava/Grad') AS Tablica (Stupac)
PRINT 'Ne postoji nista, insertam Grad i Drzavu!'
END

PRINT @Drzava
PRINT @Grad
GO


EXEC Zad1I6

SELECT * FROM Grad

-- I7 --

-- Zad. 1

CREATE PROC Zad3I7 (@IDRacun int)
AS
DECLARE @IDRacuna int

DELETE FROM Racun WHERE Racun.IDRacun = @IDRacun
GO

EXEC Zad3I7 291
EXEC Zad3I7 290
EXEC Zad3I7 289

GO

-- Zad 2.
-- Za vrijeme istovremenog pokušaja čitanja objekata iz baze, ako transakcija nije postavljena na READ UNCOMMITTED, drugi korisnik če moći pročitati podatke, ali oni neče biti ažurni.
-- Te se radi tog problema transakcija mora postaviti na READ COMMITED, REPEATABLE READ ili SERIALZABLE kako bi zaoobišli taj problem.
-- Sa naredbom: SET TRANSACTION ISOLATION LEVEL READ COMMITTED


-- I8


EXEC sp_addumpdevice
@devtype = 'DISK',
@logicalname = 'Moja_lokacija3',
@physicalname = 'D:\MI3_I8.bak'
GO

BACKUP DATABASE OBP TO Moja_lokacija3
WITH 
NAME = 'Prva kopija AWKSOBP baze',
DESCRIPTION = 'Prva kopija baze OBP AW',
STATS

RESTORE DATABASE OBP AdventureWorksOBP_Vraceno
GO
---


CREATE USER vjenceslav 
GO

GRANT SELECT ON Proizvod TO vjenceslav

EXEC sp_addrolemember
@rolename = 'zad2I8',
@membername = 'vjenceslav'
GO 
CREATE ROLE zad2I8
GO


GRANT SELECT ON Proizvod TO zad2I8
GO

REVOKE SELECT, INSERT, UPDATE, DELETE ON Proizvod TO zad2I8
GO
EXEC sp_addrolemember @rolename = 'zad2I8', @membername = 'vjenceslav'
GO