sqlhack#4

-- Napravite pogled koji æe dohvaæati sve iz tablice Kupac
CREATE VIEW p1
AS
SELECT * FROM Kupac
GO
-- Vježba 1.1.a)
-- Iskoristite pogled za dohvaæanje svih zapisa
SELECT * FROM p1
GO
-- Vježba 1.1.b)
-- Iskoristite pogled za dohvaæanje onih osoba èije ime zapoèinje sa “L” i prezime završava na “a”
SELECT * FROM p1
WHERE Ime LIKE 'L%' AND Prezime LIKE '%a'
GO
-- Vježba 1.1.c)
-- Iskoristite pogled za ispis svih ID-eva gradova i broja osoba koje žive u tom gradu,
-- padajuæe prema broju osoba
-- (pomoæu grupiranja i pomoæu podupita)
-- pomoæu grupiranja
SELECT GradID, COUNT(*) AS BrojOsoba
FROM p1
GROUP BY GradID
ORDER BY BrojOsoba DESC
GO
-- pomoæu podupita
SELECT DISTINCT
    GradID,
    (SELECT COUNT(*)
     FROM p1 AS sq
     WHERE ISNULL(sq.GradID, '') = ISNULL(mq.GradID, '')) AS BrojOsoba
FROM p1 AS mq
ORDER BY BrojOsoba DESC
GO

-- Vježba 1.1.d)
-- Iskoristite pogled tako da ispišete ime i prezime te pokraj svakoga
-- ispišite njegov naziv grada i naziv države
SELECT
    p1.Ime,
    p1.Prezime,
    g.Naziv as 'Grad',
    d.Naziv as 'Država'
FROM p1
LEFT JOIN Grad AS g ON p1.GradID = g.IDGrad
LEFT JOIN Drzava AS d ON g.DrzavaID= d.IDDrzava
GO

-- Vježba 1.2)
-- Promijenite pogled tako da ne ukljuèuje stupce Email, Telefon i GradID
ALTER VIEW p1
AS
SELECT IDKupac, Ime, Prezime FROM Kupac
GO

-- provjera
SELECT * FROM p1
GO

-- Vježba 1.2)
-- Uklonite pogled
DROP VIEW p1
GO


-- Vježbe 2.

-- Vježba 2.1)
-- Pripremite sljedeæe izvještaje u obliku pogleda:

-- Vježba 2.1.a)
-- Ispišite nazive svih kupaca, te za svakoga ispišite email, grad i naziv države u kojoj je smješten
-- Tablice: Kupac, Grad, Drzava

CREATE VIEW p2 AS
SELECT
    k.Ime,
    k.Prezime,
    k.Email,
    g.Naziv AS Grad,
    d.Naziv AS Drzava
FROM Kupac AS k
LEFT JOIN Grad AS g ON k.GradID = g.IDGrad
LEFT JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
GO
-- provjera
SELECT * FROM p2
GO

-- Vježba 2.1.b)
-- Ispišite sve države i za svaku od njih ispišite koliko kupaca iz nje postoji
-- Tablice: Kupac, Grad, Drzava

CREATE VIEW p3 AS
SELECT
    d.Naziv AS Drzava,
    COUNT(k.IDKupac) AS BrojKupaca
FROM Kupac AS k
LEFT JOIN Grad AS g ON k.GradID = g.IDGrad
LEFT JOIN Drzava AS d ON g.DrzavaID = d.IDDrzava
GROUP BY d.Naziv
GO
-- provjera
SELECT * FROM p3
GO

-- Vježba 2.1.c)
-- Ispišite nazive svih proizvoda koje je kupilo >300 kupaca
-- Tablice: Kupac, Racun, Stavka i Proizvod

CREATE VIEW p4 AS
SELECT
    p.Naziv,
    COUNT(k.IDKupac) AS BrojKupaca
FROM Proizvod AS p
INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
INNER JOIN Racun AS r ON s.RacunID = r.IDRacun
INNER JOIN Kupac AS k ON r.KupacID = k.IDKupac
GROUP BY p.Naziv
HAVING COUNT(k.IDKupac) > 300
GO
-- provjera
SELECT * FROM p4
GO

-- Vježba 2.1.d)
-- Ispišite nazive i zaradu 5 proizvoda koji se najbolje prodaju
-- Tablice: Stavka i Proizvod

CREATE VIEW p5 AS
SELECT TOP 5
    p.Naziv,
    SUM(s.UkupnaCijena) AS Zarada
FROM Proizvod AS p
INNER JOIN Stavka AS s ON s.ProizvodID = p.IDProizvod
GROUP BY p.Naziv
ORDER BY Zarada DESC
GO
-- provjera
SELECT * FROM p5
GO

-- Vježba 2.1.e)
-- Uklonite sve poglede
DROP VIEW p2
DROP VIEW p3
DROP VIEW p4
DROP VIEW p5
GO



-- Vježbe 3.

-- Vježba 3.1)
-- Napravite pogled koji vraæa imena i prezimena te e-mailove svih kupaca iz Zagreba.

CREATE VIEW p6 AS
SELECT
    k.Ime,
    k.Prezime,
    k.Email,
    g.Naziv AS Grad
FROM Kupac AS k
INNER JOIN Grad AS g ON k.GradID = g.IDGrad
WHERE g.Naziv = 'Zagreb'
GO
-- provjera
SELECT * FROM p6
GO

-- Vježba 3.2)
-- Promijenite pogled tako da dohvaæa i sve kupce iz Splita.

ALTER VIEW p6 AS
SELECT
    k.Ime,
    k.Prezime,
    k.Email,
    g.Naziv AS Grad
FROM Kupac AS k
INNER JOIN Grad AS g ON k.GradID = g.IDGrad
WHERE g.Naziv = 'Zagreb' OR g.Naziv = 'Split'
GO
-- provjera
SELECT * F

-- Vježba 3.3)
-- Koristeæi pogled ispišite broj kupaca iz Zagreba i broj kupaca iz Splita.

SELECT
    Grad,
    COUNT(*) AS Broj
FROM p6
GROUP BY Grad
GO

-- Vježba 3.4)
-- Uklonite pogled.

DROP VIEW p6
GO



--VJ 2
-- Napravite pogled koji æe dohvaæati sve stupce i retke iz tablice Kategorija
CREATE VIEW v1 AS
SELECT * FROM Kategorija
GO

-- Ispišite nazive kategorija, potkategorija i proizvoda (koristite kreirani pogled)
SELECT
    v1.Naziv as 'Kategorija',
    Potkategorija.Naziv as 'Potkategorija',
    Proizvod.Naziv as 'Proizvod'
FROM v1
RIGHT JOIN Potkategorija ON Potkategorija.KategorijaID = v1.IDKategorija
RIGHT JOIN Proizvod ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
GO

-- Pomoæu pogleda umetnite kategoriju naziva “Alarmi”
INSERT INTO v1 (Naziv) VALUES ('Alarmi')
GO

-- Pomoæu pogleda promijenite kategoriji “Alarmi” naziv u “Aktivna zaštita”
UPDATE v1 SET Naziv = 'Aktivna zaštita' WHERE Naziv = 'Alarmi'
GO

-- Pomoæu pogleda obrišite kategoriju “Aktivna zaštita”
DELETE FROM v1 WHERE Naziv = 'Aktivna zaštita'
GO

-- Uklonite pogled
DROP VIEW v1
GO


-- Napravite pogled koji æe dohvaæati naziv grada, naziv države u kojoj se nalazi te sve
-- podatke o kupcima koji im pripadaju (tablice Grad, Drzava, Kupac).
CREATE VIEW v2 AS
SELECT
    Drzava.Naziv as 'Država',
    Grad.Naziv as 'Grad',
    Kupac.*
FROM Kupac
LEFT JOIN Grad ON Kupac.GradID = Grad.IDGrad
LEFT JOIN Drzava ON Grad.DrzavaID = Drzava.IDDrzava
GO
-- provjera
SELECT * FROM v2
GO

-- Probajte pomoæu pogleda umetnuti novi grad. Što se dogodilo?
INSERT INTO v2 (Grad) VALUES ('Bedekovèina')
GO
SELECT * FROM Grad
GO

-- Probajte pomoæu pogleda umetnuti novu državu. Što se dogodilo?
INSERT INTO v2 (Država) VALUES ('Uzbekistan')
GO
SELECT * FROM Drzava
GO

-- Probajte pomoæu pogleda umetnuti novog kupca. Što se dogodilo? Možete li vidjeti novododanog kupca kroz pogled? Postoji li on u tablici?
INSERT INTO v2 (Ime, Prezime)
    VALUES ('Pero','Periæ')
GO
SELECT * FROM Kupac
GO
SELECT * FROM v2
GO

-- Uklonite pogled
DROP VIEW v2
GO




-- Napravite pogled koji æe dohvaæati sve kreditne kartice koje su tipa Visa ili MasterCard (tablica KreditnaKastica)
CREATE VIEW v3 AS
SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
GO

-- Umetnite zapis o kreditnoj kartici tipa American Express.
-- Dohvatiti umetnuti redak pomoæu pogleda. Što se dogodilo? Je li redak uspješno umetnut u tablicu?
INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina)
VALUES ('American Express', '111222333444', 12, 2012)
GO
SELECT * FROM v3 WHERE Tip = 'American Express'
GO

-- Promijenite pogled tako da ne dopušta umetanje/izmjenu redaka koji neæe biti vidljivi kroz njega.
ALTER VIEW v3 AS
SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
WITH CHECK OPTION
GO
INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina)
VALUES ('American Express', '111222333444', 12, 2012)
GO

-- Umetnite zapis o kreditnoj kartici tipa MasterCard. Što se dogodilo? Je li redak uspješno umetnut u tablicu?
INSERT INTO v3 (Tip, Broj,IstekMjesec, IstekGodina)
VALUES ('MasterCard', '111222333444', 12, 2012)
GO

-- Promijenite pogled tako da dopušta umetanje/izmjenu redaka koji neæe biti vidljivi kroz njega.
ALTER VIEW v3 AS
SELECT * FROM KreditnaKartica WHERE Tip IN ('Visa', 'MasterCard')
GO

-- Uklonite pogled
DROP VIEW v3
GO




-- Vježbe 4.
/*
Napravite tablicu Film sa stupcima IDFilm, Naziv, GodinaProizvodnje, TrajanjeMinuta i KratkiOpis. Umetnite koji redak.
Napravite pogled koji dohvaæa sve iz tablice Film
Iskoristite pogled za dohvaæanje podataka
Uklonite stupac TrajanjeMinuta iz tablice
Iskoristite pogled za dohvaæanje podataka
Promijenite pogled tako da bude èvrsto vezan uz objekte koje koristi
Iskoristite pogled za dohvaæanje podataka
Uklonite stupac GodinaProizvodnje iz tablice. Je li uklanjanje uspjelo? Zašto?
Uklonite pogled i tablicu
*/
CREATE TABLE Film
(
    IDFilm int CONSTRAINT PK_Film PRIMARY KEY IDENTITY,
    Naziv nvarchar(200) NOT NULL,
    GodinaProizvodnje int NOT NULL,
    TrajanjeMinuta int NOT NULL,
    KratkiOpis nvarchar(max) NOT NULL
)
GO
INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis)
    VALUES (N'O maèkama i psima 2: Osveta Kitty Galore', 2010, 85, N'U vjeènoj bitci maèaka i pasa, jedna poludjela maèka odvest æe stvari jednu šapu predaleko. Kitty Galore, bivša agentica špijunske organizacije MIJAU, pobjegla je i skovala ðavolji plan u kojem ne samo da æe pokušati pripitomiti svoje vjeène neprijatelje, veæ i svoje bivše kolege maèke, ali i cjelokupno èovjeèanstvo. Suoèeni s ovom prijetnjom bez presedana, maèke i psi biti æe prisiljeni prvi put u povijesti udružiti snage kako bi spasili sebe – ali i svoje vlasnike')
INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis)
    VALUES (N'Shrek uvijek i zauvijek', 2010, 95, N'Nakon što se borio s opakim zmajem, spasio prekrasnu princezu i oslobodio èitavo kraljevstvo – kakav bi zadatak još mogao postojati u životu slavnog Shreka? Odgovor je jasan: obitelj, odnosno svakodnevna bitka s malim nestašnim Shrekiæima. Meðutim, sve æe to biti kratka vijeka, jer æe Shrek ubrzo upoznati lukavog Rumpelstiltskina i iznenada se naæi u potpuno drugoj dimenziji, alternativnoj buduænosti u kojoj se on i Fiona nikad nisu susreli, u kojoj je baš on ugrožena vrsta, a Rumpelstiltskin kralj! Naravno, jedino je Shrek taj koji æe moæi ispraviti novonastalu situaciju, spasiti prijatelje, obnoviti kraljevstvo i ponovno pronaæi svoju pravu, jedinu ljubav.')
INSERT INTO Film (Naziv, GodinaProizvodnje, TrajanjeMinuta, KratkiOpis)
    VALUES (N'Resident Evil: Drugi svijet', 2010, 90, N'Svijet je poharan virusnom infekcijom od koje oboljeli postaju zombiji. Alice (Milla Jovovich) pronalazi preživjele i spašava ih. Njezina bitka s Umbrella Corporation dostiže smrtonosne razine, no u pomoæ joj priskaèe stari prijatelj. U nadi da æe pronaæi sigurno mjesto odlaze prema Los Angelesu. No, grad je preplavljen tisuæama zombija i oni æe naletjeti u zamku')
GO

CREATE VIEW v4 AS
SELECT * FROM Film
GO

SELECT * FROM v4
GO

ALTER TABLE Film DROP COLUMN TrajanjeMinuta
GO

ALTER VIEW v4
WITH SCHEMABINDING
AS
SELECT IDFilm, Naziv, GodinaProizvodnje, KratkiOpis FROM dbo.Film
GO

SELECT * FROM v4
GO

ALTER TABLE Film DROP COLUMN GodinaProizvodnje
GO

DROP VIEW v4
DROP TABLE Film
GO




-- Vježbe 5.
/*
Napravite pogled koji dohvaæa 10 proizvoda koji su najviše prodavani. Stupci koje pogled vraæa neka budu ID i naziv te ukupna kolièina prodanih proizvoda.
Pogledajte SELECT upit pogleda kroz suèelje i pomoæu sistemske procedure sp_helptext
Zaštitite pogled
Pogledajte SELECT upit pogleda kroz suèelje i pomoæu sistemske procedure sp_helptext
Promijenite pogled tako da bude zaštiæen i èvrsto vezan uz tablice
Promijenite pogled tako da bude zaštiæen, èvrsto vezan uz tablice i da ne dopušta izmjene koje neæe biti vidljive kroz pogled
Uklonite pogled
*/
CREATE VIEW v5 AS
SELECT TOP 10
    Proizvod.IDProizvod AS 'ID',
    Proizvod.Naziv,
    SUM(Stavka.Kolicina) AS 'UkupnaKolicina'
FROM Proizvod
INNER JOIN Stavka ON Stavka.ProizvodID = Proizvod.IDProizvod
GROUP BY Proizvod.IDProizvod, Proizvod.Naziv
ORDER BY 3 DESC
GO

SELECT * FROM v5
GO

EXECUTE sp_helptext v5
GO

ALTER VIEW v5
WITH ENCRYPTION
AS
SELECT TOP 10
    Proizvod.IDProizvod AS 'ID',
    Proizvod.Naziv,
    SUM(Stavka.Kolicina) AS 'UkupnaKolicina'
FROM Proizvod
INNER JOIN Stavka ON Stavka.ProizvodID = Proizvod.IDProizvod
GROUP BY Proizvod.IDProizvod, Proizvod.Naziv
ORDER BY 3 DESC
GO

EXECUTE sp_helptext v5
GO

ALTER VIEW v5
WITH ENCRYPTION, SCHEMABINDING
AS
SELECT TOP 10
    dbo.Proizvod.IDProizvod AS 'ID',
    dbo.Proizvod.Naziv,
    SUM(dbo.Stavka.Kolicina) AS 'UkupnaKolicina'
FROM dbo.Proizvod
INNER JOIN dbo.Stavka ON dbo.Stavka.ProizvodID = dbo.Proizvod.IDProizvod
GROUP BY dbo.Proizvod.IDProizvod, dbo.Proizvod.Naziv
ORDER BY 3 DESC
GO

ALTER VIEW v5
WITH ENCRYPTION, SCHEMABINDING
AS
SELECT TOP 10
    dbo.Proizvod.IDProizvod AS 'ID',
    dbo.Proizvod.Naziv,
    SUM(dbo.Stavka.Kolicina) AS 'UkupnaKolicina'
FROM dbo.Proizvod
INNER JOIN dbo.Stavka ON dbo.Stavka.ProizvodID = dbo.Proizvod.IDProizvod
GROUP BY dbo.Proizvod.IDProizvod, dbo.Proizvod.Naziv
ORDER BY 3 DESC
WITH CHECK OPTION
GO

DROP VIEW v5
GO



--VJ_3
-- Zadatak 1.
/*
VARIJABLE
Deklarirajte varijable @Ime i @Prezime i dodijelite im neke vrijednosti.
Ispišite dodijeljene vrijednosti.
*/
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SET @Ime = 'Miro'
SET @Prezime = 'Miriæ'

PRINT @Ime
PRINT @Prezime
PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO


-- Zadatak 2.
/*
VARIJABLE
Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti iz tablice Kupac za IDKupac jednak 8812.
Ispišite dodijeljene vrijednosti.
*/
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac WHERE IDKupac = 8812

PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO


-- Zadatak 3.
/*
VARIJABLE
Deklarirajte varijable @Ime i @Prezime i dodijelite im vrijednosti iz tablice Kupac tako da odaberete sve retke iz tablice.
Ispišite dodijeljene vrijednosti.
*/
DECLARE @Ime nvarchar(50)
DECLARE @Prezime nvarchar(50)

SELECT @Ime = Ime, @Prezime = Prezime FROM Kupac

PRINT 'Korisnik: ' + @Ime + ' ' + @Prezime
GO


-- Zadatak 4.
/*
IF-ELSE IF-ELSE i SCOPE_IDENTITY()
Provjerite broj zapisa u tablici Kupac.
Ako ih ima više ili jednako 20000, ispišite “Postoji više od 20000 kupaca :)”.
Ako ih ima manje, ispišite “Još nismo dostigli 20000 kupaca :(”
*/
DECLARE @Broj int
SELECT @Broj = COUNT(*) FROM dbo.Kupac
IF @Broj >= 20000
    PRINT 'Postoji više od 20000 kupaca :)'
ELSE
    PRINT 'Još nismo dostigli 20000 kupaca :('
GO


-- Zadatak 5.
/*
IF-ELSE IF-ELSE i SCOPE_IDENTITY()
Umetnite zapis u tablicu Drzava, generiranu IDENTITY vrijednost dodijelite nekoj varijabli pa je ispišite.
*/
DECLARE @NoviID int
INSERT INTO Drzava VALUES ('Gruzija')
SET @NoviID = SCOPE_IDENTITY()
PRINT @NoviID
GO


-- Zadatak 6.
/*
IF-ELSE IF-ELSE i SCOPE_IDENTITY()
Umetnite zapis u tablicu Drzava i u varijablu spremite generiranu IDENTITY vrijednost.
Iskoristite tu vrijednost da biste za tu državu umetnuli dva grada.
*/
DECLARE @IDDrzavaNovi int
INSERT INTO Drzava VALUES ('Kina')
select SCOPE_IDENTITY()
SET @IDDrzavaNovi = SCOPE_IDENTITY()

INSERT INTO Grad VALUES ('Šangaj', @IDDrzavaNovi)
INSERT INTO Grad VALUES ('Peking', @IDDrzavaNovi)
GO


-- Izrada log tablice.
CREATE TABLE Zapisnik
(
    IDZapisnik int IDENTITY(1,1) PRIMARY KEY,
    Poruka nvarchar(max),
    Vrijeme datetime DEFAULT getdate()
)
GO


--VJ_04

-- Zadatak 1.
/*
Napravite okidaè kojim æete svako umetanje retka u tablicu Grad zapisati u tablicu Zapisnik.
Umetnite redak.
*/
CREATE TRIGGER Okidac1 ON Grad AFTER INSERT
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Umetnut redak u Grad!')
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 1', 1)

SELECT * FROM Zapisnik
GO


-- Zadatak 2.
/*
Promijenite okidaè tako da zapiše ID i naziv umetnutog grada u Zapisnik.
Umetnite redak.
*/
ALTER TRIGGER Okidac1 ON Grad AFTER INSERT
AS
DECLARE @ID int
DECLARE @Naziv nvarchar(50)
SELECT @ID = IDGrad, @Naziv = Naziv FROM inserted

INSERT INTO Zapisnik (Poruka) VALUES ('Umetnut redak u Grad. IDGrad: ' + CAST(@ID as nvarchar(50)) + ', Naziv: ' + @Naziv)
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 2', 1)

SELECT * FROM Zapisnik
GO


-- Zadatak 3.
/*
Promijenite okidaè tako da se veže uz sve dogaðaje i u Zapisnik zapisuje broj redaka u inserted i deleted tablicama.
Umetnite dva nova grada, promijenite im države i na kraju ih obrišite.
*/
ALTER TRIGGER Okidac1 ON Grad AFTER INSERT, UPDATE, DELETE
AS
DECLARE @i int
DECLARE @d int
SELECT @i = COUNT(*) FROM inserted
SELECT @d = COUNT(*) FROM deleted

INSERT INTO Zapisnik (Poruka) VALUES ('inserted: ' + CAST(@i as nvarchar(20)) + ', deleted: ' + CAST(@d as nvarchar(20)))
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 3', 1)
INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 4', 1)
SELECT * FROM Zapisnik

UPDATE Grad SET DrzavaID = 2 WHERE Naziv LIKE 'Grad%'
SELECT * FROM Zapisnik

DELETE FROM Grad WHERE Naziv LIKE 'Grad%'
SELECT * FROM Zapisnik
GO


-- Zadatak 4.
/*
Promijenite okidaè tako da upisuje staru i novu vrijednost promijenjenog naziva grada u Zapisnik.
Promijenite naziv jednom gradu.
*/
ALTER TRIGGER Okidac1 ON Grad AFTER UPDATE
AS
DECLARE @old nvarchar(50)
DECLARE @new nvarchar(50)
SELECT @old = Naziv FROM deleted
SELECT @new = Naziv FROM inserted

INSERT INTO Zapisnik (Poruka) VALUES ('Stara: ' + @old + ', nova: ' + @new)
GO

UPDATE Grad SET Naziv = 'Daruvar' WHERE IDGrad = 1
SELECT * FROM Zapisnik
GO


-- Zadatak 5.
/*
Onemoguæite okidaè iz prethodnih primjera i promijenite neki redak u tablici.
Ponovno ga omoguæite.
Napravite opet promjenu.
Uklonite okidaè.
*/
DISABLE TRIGGER Okidac1 ON Grad
GO

UPDATE Grad SET Naziv = 'Bjelovar' WHERE IDGrad = 1
SELECT * FROM Zapisnik
GO

ENABLE TRIGGER Okidac1 ON Grad
GO

UPDATE Grad SET Naziv = 'Vukovar' WHERE IDGrad = 1
SELECT * FROM Zapisnik
GO

DROP TRIGGER Okidac1
GO

-- Zadatak 6.
/*
Dodajte novi okidaè na tablicu Grad i vežite ga uz sva tri dogaðaja.
U okidaèu saznajte koji dogaðaj ga je pozvao i tu informaciju upišite u Zapisnik.
Napravite umetanje, izmjenu i brisanje nekog retka.
Uklonite okidaè.
*/
CREATE TRIGGER Okidac2 ON Grad AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN
    -- INSERT.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se INSERT.')
END
ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
    -- DELETE.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se DELETE.')
END
ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
    -- UPDATE.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se UPDATE.')
END
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 5', 1)
SELECT * FROM Zapisnik

UPDATE Grad SET Naziv = 'Ilok' WHERE IDGrad = 1
SELECT * FROM Zapisnik

DELETE FROM Grad WHERE IDGrad = 76
SELECT * FROM Zapisnik
GO

DROP TRIGGER Okidac2
GO


-- Zadatak 7.
/*
Dodajte novi okidaè i vežite ga uz UPDATE dogaðaj na tablici Grad.
Neka okidaè zapiše u Zapisnik da se desio dogaðaj samo ako je promijenjen stupac DrzavaID.
Uklonite okidaè.
*/
CREATE TRIGGER Okidac3 ON Grad AFTER UPDATE
AS
IF UPDATE(DrzavaID) BEGIN
    INSERT INTO Zapisnik (Poruka) VALUES ('Promijenjen je stupac DrzavaID.')
END
GO

UPDATE Grad SET Naziv = 'Šibenik' WHERE IDGrad = 1
SELECT * FROM Zapisnik

UPDATE Grad SET DrzavaID = 3 WHERE IDGrad = 1
SELECT * FROM Zapisnik
GO

DROP TRIGGER Okidac3
GO



-- Zadatak 8.
/*
Dodajte 4 nova okidaèa koji u zapisnik ispisuju "Pozdrav iz broja n" nakon umetanja retka.
Umetnite redak.
Posložite redoslijed okidaèa tako da bude 4, 2, 3, 1.
Umetnite redak.
Vratite originalni redoslijed.
Umetnite redak.
Uklonite okidaèe.
*/
CREATE TRIGGER Okidac1 ON Grad AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 1')
GO

CREATE TRIGGER Okidac2 ON Grad AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 2')
GO

CREATE TRIGGER Okidac3 ON Grad AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 3')
GO

CREATE TRIGGER Okidac4 ON Grad AFTER INSERT AS
INSERT INTO Zapisnik (Poruka) VALUES ('Pozdrav iz broja 4')
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 6', 1)
SELECT * FROM Zapisnik
GO

EXEC sp_settriggerorder 'Okidac4', 'FIRST', 'INSERT'
EXEC sp_settriggerorder 'Okidac1', 'LAST', 'INSERT'
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 7', 1)
SELECT * FROM Zapisnik
GO

EXEC sp_settriggerorder 'Okidac4', 'NONE', 'INSERT'
EXEC sp_settriggerorder 'Okidac1', 'NONE', 'INSERT'
GO

INSERT INTO Grad (Naziv, DrzavaID) VALUES ('Grad 8', 1)
SELECT * FROM Zapisnik
GO

DROP TRIGGER Okidac1
DROP TRIGGER Okidac2
DROP TRIGGER Okidac3
DROP TRIGGER Okidac4
GO


-- Zadatak 9.
/*
Napravite tablice Tbl1 i Tbl2 s proizvoljnim stupcima.
Na Tbl1 napravite okidaè vezan uz INSERT koji umeæe retke u Tbl2 i u Zapisnik.
Na Tbl2 napravite okidaè vezan uz INSERT koji umeæe retke u Tbl1 i u Zapisnik.
Umetnite jedan redak u Tbl1.
Što piše u svakoj od tablica?
*/
CREATE TABLE Tbl1
(
    Stupac1 int PRIMARY KEY IDENTITY,
    Stupac2 nvarchar(50)
)
CREATE TABLE Tbl2
(
    Stupac1 int PRIMARY KEY IDENTITY,
    Stupac2 nvarchar(50)
)
GO

CREATE TRIGGER Okidac1 ON Tbl1 AFTER INSERT
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Okidaè 1 na tablici 1')
INSERT INTO Tbl2 VALUES ('Upis u drugu tablicu')
GO

CREATE TRIGGER Okidac2 ON Tbl2 AFTER INSERT
AS
INSERT INTO Zapisnik (Poruka) VALUES ('Okidaè 2 na tablici 2')
INSERT INTO Tbl1 VALUES ('Upis u prvu tablicu')
GO

INSERT INTO Tbl1 VALUES ('Upis u prvu tablicu')
SELECT * FROM Zapisnik
GO

SELECT * FROM Tbl1
SELECT * FROM Tbl2
SELECT * FROM Zapisnik


--VJ_05
-----------------------------------------------------------------------------------
-- Zadatak 1.
-----------------------------------------------------------------------------------
create FUNCTION UkupnaKolicina
(
    @ID int
)
RETURNS int
AS
BEGIN
    DECLARE @Ukupno int
    SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID
    RETURN @Ukupno
END
GO

DECLARE @Rez int
SET @Rez = dbo.UkupnaKolicina(776)
PRINT @Rez

SELECT
    Naziv,
    Boja,
    dbo.UkupnaKolicina(IDProizvod) AS Prodano
FROM Proizvod
GO

ALTER FUNCTION UkupnaKolicina
(
    @ID int
)
RETURNS int
AS
BEGIN
    DECLARE @Ukupno int

    SELECT @Ukupno = Sum(Kolicina) FROM Stavka WHERE ProizvodID = @ID

    RETURN CASE
                WHEN @Ukupno IS NOT NULL THEN @Ukupno
                ELSE 0
            END
END
GO

SELECT
    Naziv,
    Boja,
    dbo.UkupnaKolicina(IDProizvod) AS Prodano
FROM Proizvod
GO


-----------------------------------------------------------------------------------
-- Zadatak 2.
-----------------------------------------------------------------------------------
CREATE FUNCTION Skrati
(
    @s nvarchar(max)
)
RETURNS nvarchar(10)
AS
BEGIN
    RETURN CASE
        WHEN LEN(@s) <= 10 THEN @s
        ELSE SUBSTRING(@s, 1, 7) + '...'
    END
END
GO

PRINT dbo.Skrati('Zvonko')
PRINT dbo.Skrati('Zvonko Telefonko')

SELECT
    Naziv,
    dbo.Skrati(Naziv) AS NazivSkraceni
FROM Proizvod
GO


-----------------------------------------------------------------------------------
-- Zadatak 3.
-----------------------------------------------------------------------------------
CREATE FUNCTION GetNajnoviji
(
    @IDKupac int
)
RETURNS datetime
AS
BEGIN
    DECLARE @Datum datetime

    SELECT TOP 1 @Datum = DatumIzdavanja FROM dbo.Racun
    WHERE KupacID = @IDKupac
    ORDER BY DatumIzdavanja DESC

    RETURN @Datum
END
GO

SELECT
    *,
    dbo.GetNajnoviji(IDKupac) AS NajnovijaKupnja
FROM Kupac

CREATE NONCLUSTERED INDEX i1 ON dbo.Racun(KupacID) INCLUDE (DatumIzdavanja)
GO

-----------------------------------------------------------------------------------
-- Zadatak 4.
-----------------------------------------------------------------------------------
CREATE FUNCTION DohvatiOsobe
(
    @PrezimeLike nvarchar(50)
)
RETURNS TABLE
AS
RETURN
    SELECT IDKupac, Ime, Prezime
    FROM Kupac
    WHERE Prezime LIKE @PrezimeLike + '%'
GO

SELECT * FROM DohvatiOsobe('Zhu')

SELECT *
FROM DohvatiOsobe('Zhu') AS os
INNER JOIN Racun AS r ON os.IDKupac = r.KupacID
GO


-----------------------------------------------------------------------------------
-- Zadatak 5.
-----------------------------------------------------------------------------------
CREATE FUNCTION DohvatiRacune
(
    @D1 datetime,
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
    SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
    FROM Racun AS r
    LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
    WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacune('20040601', '20040603')
GO

ALTER FUNCTION DohvatiRacune
(
    @D1 datetime,
    @D2 datetime
)
RETURNS TABLE
AS
RETURN
    SELECT r.BrojRacuna, CONVERT(char(10), r.DatumIzdavanja, 104) AS DatumIzdavanja, k.Ime, k.Prezime
    FROM Racun AS r
    LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
    WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2
GO

SELECT * FROM DohvatiRacune('20040601', '20040603')
GO


-----------------------------------------------------------------------------------
-- Zadatak 6.
-----------------------------------------------------------------------------------
CREATE FUNCTION DohvatiRacuneSloz
(
    @D1 datetime,
    @D2 datetime
)
RETURNS @RetVal TABLE ( BrojRacuna nvarchar(25), DatumIzdavanja datetime, Ime nvarchar(50), Prezime nvarchar(50) )
AS
BEGIN
    INSERT INTO @RetVal (BrojRacuna, DatumIzdavanja, Ime, Prezime)
    SELECT r.BrojRacuna, r.DatumIzdavanja, k.Ime, k.Prezime
    FROM Racun AS r
    LEFT JOIN dbo.Kupac AS k ON r.KupacID = k.IDKupac
    WHERE r.DatumIzdavanja BETWEEN @D1 AND @D2

    RETURN
END
GO

SELECT * FROM DohvatiRacuneSloz('20040601', '20040603')
GO

-----------------------------------------------------------------------------------
-- Zadatak 7.
-----------------------------------------------------------------------------------
CREATE FUNCTION F4
(
    @Cijena money
)
RETURNS @rez TABLE ( Naziv nvarchar(50), Cijena money )
AS
BEGIN
    IF @Cijena IS NULL BEGIN
        INSERT INTO @rez (Naziv, Cijena)
        SELECT Naziv, CijenaBezPDV FROM Proizvod
    END
    ELSE BEGIN
        INSERT INTO @rez (Naziv, Cijena)
        SELECT Naziv, CijenaBezPDV FROM Proizvod WHERE CijenaBezPDV > @Cijena
    END

    RETURN
END
GO

SELECT * FROM F4(NULL)
SELECT * FROM F4(3000)
GO

-----------------------------------------------------------------------------------
-- Zadatak 8.
-----------------------------------------------------------------------------------
CREATE FUNCTION GetDatume
(
    @Datum datetime
)
RETURNS @RetVal TABLE ( Datum datetime )
AS
BEGIN
    DECLARE @i int = 1
    WHILE @i <= 5 BEGIN
        INSERT INTO @RetVal (Datum)
        VALUES (Dateadd(day, @i, @Datum))

        SET @i += 1
    END

    RETURN
END
GO

SELECT * FROM GetDatume(GETDATE())
SELECT * FROM GetDatume('20111229')
GO


--VJ 07
-- Zadatak 1.
-- Optimizirajte upit:
SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12

-- a.) Koliko stranica pregled RDBMS?
SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8

-- b.) Napravite indeks koji optimizira upit
CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
GO

-- c.) Koliko sad stranica pregled RDBMS?
SELECT PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2

-- d.) Uklonite indeks
DROP INDEX Proizvod.i1
GO



-- Zadatak 2.
-- Optimizirajte upit: SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12

-- a.) Koliko stranica pregled RDBMS?
SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8

-- b.) Napravite indeks koji optimizira upit
CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
GO

-- c.) Koliko sad stranica pregled RDBMS?
SELECT IDProizvod, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2

-- d.) Uklonite indeks
DROP INDEX Proizvod.i1
GO


-- Zadatak 3.
-- Optimizirajte upit:
-- SELECT ProductID, Name, ProductSubcategoryID FROM Production.Product WHERE ProductSubcategoryID = 12

SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8

CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID)
GO

SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8

-- a) Pomaže li nam indeks iz prethodnog primjera? Što uèiniti sa stupcem Naziv?
-- logical reads 8 - mora iæi na klasterirani indeks jer mu nedostaje vrijednost za Naziv.
DROP INDEX Proizvod.i1
GO

CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (naziv)
GO

SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12
-- (28 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Proizvod.i1
GO



-- Zadatak 4.
-- Optimizirajte upit:
-- SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'

SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'
-- (8 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8 - mora iæi na klasterirani indeks jer mu nedostaje vrijednost za Naziv.
CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (naziv)
GO

SELECT IDProizvod, Naziv, PotkategorijaID FROM Proizvod WHERE PotkategorijaID = 12 AND Naziv LIKE 'ML%'
-- (8 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Proizvod.i1
GO



-- Zadatak 5.
-- Optimizirajte upit:
-- SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC

SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC
-- (2 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 8 - mora iæi na klasterirani indeks jer mu nedostaje vrijednost za Boja.
CREATE NONCLUSTERED INDEX i1 ON Proizvod(PotkategorijaID) INCLUDE (Boja)
GO

SELECT Boja, COUNT(*) AS Cnt FROM Proizvod WHERE PotkategorijaID = 12 GROUP BY Boja ORDER BY Cnt DESC
-- (2 row(s) affected)
-- Table 'Proizvod'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Proizvod.i1
GO




-- Zadatak 6.
-- Optimizirajte upit:
-- SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'

-- a.) Koliko stranica pregled RDBMS?
SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

-- b.) Napravite indeks koji optimizira upit
CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
GO

-- c.) Koliko sad stranica pregled RDBMS?
SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2

-- d.) Uklonite indeks
DROP INDEX Racun.i1
GO



-- Zadatak 7.
-- Optimizirajte upit:
-- SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'

-- a.) Koliko stranica pregled RDBMS?
SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

-- b.) Napravite indeks koji optimizira upit
CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
GO
-- c.) Koliko sad stranica pregled RDBMS?
SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2

-- d.) Uklonite indeks
DROP INDEX Racun.i1
GO



-- Zadatak 8.
-- Optimizirajte upit:
-- SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'

SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja)
GO

SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

-- a) Pomaže li nam indeks iz prethodnog primjera? Što uèiniti sa stupcem BrojRacuna?
-- logical reads 202 - mora iæi na klasterirani indeks jer mu nedostaje vrijednost za BrojRacuna.
DROP INDEX Racun.i1
GO

CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
GO

SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Racun.i1
GO




-- Zadatak 9.
-- Optimizirajte upit:
-- SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%'

SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)
GO

SELECT IDRacun FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' AND BrojRacuna LIKE 'S%'
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Racun.i1
GO

-- Zadatak 10.
-- Optimizirajte upit:
-- SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC

SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 202

CREATE NONCLUSTERED INDEX i1 ON Racun(DatumIzdavanja) INCLUDE (KupacID)
GO

SELECT KupacID, COUNT(*) AS Cnt  FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59' GROUP BY KupacID ORDER BY Cnt DESC
-- (4 row(s) affected)
-- Table 'Racun'. Scan count 1, logical reads 202, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX Racun.i1
GO


--VJ_08
-- Zadatak 1.
/*
PRETVARANJE TIPOVA PODATAKA
Dohvatite brojeve računa i datume izdavanja za kupca s ID-em 378.
Datume izdavanja formatirajte na hrvatski naèin.
*/
SELECT
    BrojRacuna,
    CONVERT(char(10), DatumIzdavanja, 104) AS DatumIzdavanja
FROM Racun WHERE KupacID = 378
GO


-- Zadatak 2.
/*
PRETVARANJE TIPOVA PODATAKA
Napišite proceduru za umetanje zapisa u tablicu Drzava.
Neka procedura kroz izlazni parametar vrati vrijednost primarnog kljuèa novog zapisa.
Pozovite proceduru i ispišite vraæenu vrijednost u formatu:
“Umetnuta je država s ID-em n”, gdje je n vrijednost primarnog kljuèa.
*/
CREATE PROC p1
    @Naziv nvarchar(50),
    @ID int OUTPUT
AS
INSERT INTO Drzava (Naziv) VALUES (@Naziv)
SET @ID = SCOPE_IDENTITY()
GO

DECLARE @n int
EXEC p1 'Gruzija', @n OUTPUT
PRINT 'Umetnuta je država s ID-em ' + CAST(@n AS nvarchar(10))
GO

-- Zadatak 3.
/*
PRETVARANJE TIPOVA PODATAKA
Dohvatite nazive svih proizvoda i uz svaki naziv u zagradi
ispišite i njegov ID te cijenu,
npr. “HL Road Rear Wheel (ID = 828, Cijena = 357.06)”
*/
SELECT
    Naziv + ' (ID = ' + CAST(IDProizvod AS nvarchar(50)) + ', Cijena = ' + CAST(CijenaBezPDV AS nvarchar(50)) + ')' AS Naziv
FROM dbo.Proizvod


-- Zadatak 4.
/*
NAREDBA CASE I WHILE
Dohvatite nazive svih proizvoda i uz svaki naziv ispišite i naziv potkategorije.
Ako neke potkategorije nema, napišite “Nije definirana”.
*/
SELECT
    p.Naziv,
    CASE
        WHEN pk.Naziv IS NULL THEN 'Nije definirana'
        ELSE pk.Naziv
    END AS NazivPotkategorije
FROM Proizvod AS p
LEFT JOIN Potkategorija AS pk ON p.PotkategorijaID = pk.IDPotkategorija


-- Zadatak 5.
/*
NAREDBA CASE I WHILE
Dohvatite naziv i cijene svih proizvoda.
Za cijene koje su ispod 1000, napišite “Jeftino”, izmeðu 1000 i 2000
napišite “Prihvatljivo”, za sve ostale napišite “Skupo”.
*/
SELECT
    Naziv,
    CASE
        WHEN CijenaBezPDV < 1000 THEN 'Jeftino'
        WHEN CijenaBezPDV BETWEEN 1000 AND 2000 THEN 'Prihvatljivo'
        ELSE 'Skupo'
    END AS Procjena
FROM Proizvod
GO


-- Zadatak 6.
/*
NAREDBA CASE I WHILE
Napravite tablicu Proba sa stupcima IDProba (primarni kljuè i IDENTITY) i Vrijednost (int).
U stupac Vrijednost unesite vrijednosti izmeðu 10.000.000 i 10.015.000.
*/
CREATE TABLE Proba ( IDProba int PRIMARY KEY IDENTITY, Vrijednost int )
GO

DECLARE @i int = 10000000
WHILE @i <= 10015000 BEGIN
    INSERT INTO Proba VALUES (@i)
    SET @i += 1
END

SELECT * FROM Proba
GO


-- Zadatak 7.
/*
STRUKTURIRANO HVATANJE POGREŠAKA
Napravite proceduru koja prima dva broja i kroz izlazni parametar
vraæa prvi broj podijeljen drugim.
Ako se desi greška, neka procedura u izlazni parametar upiše 0
i neka ispiše tekst greške.
Pozovite proceduru i ispišite rezultat dijeljenja.
*/
create PROC pp2
    @a int,
    @b int,
    @c int OUTPUT
AS
BEGIN TRY
    SET @c = @a / @b
END TRY
BEGIN CATCH
    SET @c = 0
    PRINT ERROR_MESSAGE()
END CATCH
GO

DECLARE @Rez int
EXEC pp2 32, 4, @Rez OUTPUT
PRINT @Rez

EXEC pp2 32, 0, @Rez OUTPUT
PRINT @Rez
GO


-- Zadatak 8.
/*
STRUKTURIRANO HVATANJE POGREŠAKA
Napravite tablicu Zivotinja koja ima stupce IDZivotinja
(primarni kljuè, ali nije IDENTITY) i Naziv.
Napravite proceduru koja prima IDZivotinja i Naziv i umeæe ih u tablicu.
Pozovite proceduru dva puta s vrijednostima 20 i "Čaplja".
Implementirajte TRY/CATCH izvan procedure i pozovite je.
Implementirajte TRY/CATCH unutar procedure i pozovite je.
*/
CREATE TABLE Zivotinja
(
    IDZivotinja int PRIMARY KEY,
    Naziv nvarchar(50)
)
GO

CREATE PROC InsertZivotinja
    @IDZivotinja int,
    @Naziv nvarchar(50)
AS
INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
GO

EXEC InsertZivotinja 20, 'Èaplja'
EXEC InsertZivotinja 20, 'Èaplja'
GO

BEGIN TRY
    EXEC InsertZivotinja 20, 'Èaplja'
    EXEC InsertZivotinja 20, 'Èaplja'
END TRY
BEGIN CATCH
    PRINT 'Error message: ' + cast(ERROR_MESSAGE() as nvarchar(100))
    PRINT 'Error number: ' + cast(ERROR_NUMBER() as nvarchar(100))
    PRINT 'Error severity: ' + cast(ERROR_SEVERITY() as nvarchar(100))
    PRINT 'Error line ' + cast(ERROR_LINE() as nvarchar(100))
    PRINT 'Error procedure: ' + cast(ERROR_PROCEDURE() as nvarchar(100))
END CATCH
GO

ALTER PROC InsertZivotinja
    @IDZivotinja int,
    @Naziv nvarchar(50)
AS
BEGIN TRY
    INSERT INTO Zivotinja (IDZivotinja, Naziv) VALUES (@IDZivotinja, @Naziv)
END TRY
BEGIN CATCH
    PRINT 'Desila se greška: ' + ERROR_MESSAGE()
    PRINT 'Životinja nije upisana.'
END CATCH
GO

EXEC InsertZivotinja 20, 'Čaplja'
GO



-- Zadatak 9.
/*
CRUD OPERACIJE
Svaka operacija posebno.
Napravite procedure koje rade CRUD operacije
na tablici Student tako da svakoj operaciji
dodijelite posebnu proceduru.
Iskoristite procedure za umetanje, izmjenu,
dohvaćanje i brisanje zapisa.
*/

CREATE PROC InsertStudent
    @IDStudent int OUTPUT,
    @Ime nvarchar(50),
    @Prezime nvarchar(50),
    @JMBAG char(11)
AS
INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
SET @IDStudent = SCOPE_IDENTITY()
GO

CREATE PROC UpdateStudent
    @IDStudent int,
    @Ime nvarchar(50),
    @Prezime nvarchar(50),
    @JMBAG char(11)
AS
UPDATE Student
SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
WHERE IDStudent = @IDStudent
GO

CREATE PROC DeleteStudent
    @IDStudent int
AS
DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC GetStudent
    @IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

EXEC GetStudent 1

DECLARE @NoviIDStudenta int
EXEC InsertStudent @IDStudent = @NoviIDStudenta OUTPUT,
    @Ime = 'Ana', @Prezime = 'Aniæ', @JMBAG = '11224451253'
PRINT @NoviIDStudenta

EXEC GetStudent 1

EXEC UpdateStudent 1, 'Ana', 'Aniæ Miriæ', '11224451253'

EXEC GetStudent 1

EXEC DeleteStudent 1

EXEC GetStudent 1
GO



-- Zadatak 10.
/*
CRUD OPERACIJE
INSERT/UPDATE zajedno, ostalo posebno.
Napravite procedure koje rade CRUD operacije na tablici Student
tako da operacije umetanja i izmjene obavite u jednoj proceduri,
a druge dvije operacije obavite u posebnim procedurama.
Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
*/
CREATE PROC MergeStudent
    @IDStudent int OUTPUT,
    @Ime nvarchar(50),
    @Prezime nvarchar(50),
    @JMBAG char(11)
AS
IF Exists(SELECT * FROM Student WHERE IDStudent = @IDStudent)
    UPDATE Student
    SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
    WHERE IDStudent = @IDStudent
ELSE
    INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
    SET @IDStudent = SCOPE_IDENTITY()
GO

CREATE PROC DeleteStudent
    @IDStudent int
AS
DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC GetStudent
    @IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

EXEC GetStudent 1

DECLARE @NoviIDStudenta int
EXEC MergeStudent @NoviIDStudenta OUTPUT, 'Ana', 'Anić', '11224451253'
PRINT @NoviIDStudenta

EXEC GetStudent 2

EXEC MergeStudent 2, 'Ana', 'Aniæ Mirić', '11224451253'

EXEC GetStudent 2

EXEC DeleteStudent 2

EXEC GetStudent 2
GO



-- Zadatak 11.
/*
CRUD OPERACIJE
UPDATE/INSERT/DELETE zajedno.
Napravite procedure koje rade CRUD operacije na tablici Student
tako da operacije umetanja, izmjene i brisanja obavite u jednoj proceduri,
a dohvaćanje u drugoj.
Iskoristite procedure za umetanje, izmjenu, dohvaæanje i brisanje zapisa.
*/
CREATE PROC ChangeStudent
    @Operacija char(1),
    @IDStudent int OUTPUT,
    @Ime nvarchar(50),
    @Prezime nvarchar(50),
    @JMBAG char(11)
AS
IF @Operacija = 'U'
    UPDATE Student
    SET Ime = @Ime, Prezime = @Prezime, JMBAG = @JMBAG
    WHERE IDStudent = @IDStudent
ELSE IF @Operacija = 'I' BEGIN
    INSERT INTO Student (Ime, Prezime, JMBAG) VALUES (@Ime, @Prezime, @JMBAG)
    SET @IDStudent = SCOPE_IDENTITY()
END
ELSE IF @Operacija = 'D'
    DELETE FROM Student WHERE IDStudent = @IDStudent
GO

CREATE PROC GetStudent
    @IDStudent int
AS
SELECT * FROM Student WHERE IDStudent = @IDStudent
GO

EXEC GetStudent 3

DECLARE @NoviIDStudenta int
EXEC ChangeStudent 'I', @NoviIDStudenta OUTPUT, 'Ana', 'Aniæ', '11224451253'
PRINT @NoviIDStudenta

EXEC GetStudent 3

EXEC ChangeStudent 'U', 3, 'Ana', 'Aniæ Miriæ', '11224451253'

EXEC GetStudent 3

EXEC ChangeStudent 'D', 3, null, null, null

EXEC GetStudent 3
GO

-- Zadatak 12.
/*
CRUD OPERACIJE
Za tablicu Student koja se sastoji od IDStudent, Ime, Prezime i JMBAG
implementirajte tabličnom funkcijom operaciju SELECT.
Iskoristite funkciju za dohvaćanje zapisa.
*/

create function fStudent
(
    @IDStudent int
)
returns table
as
return
    select *
    from Student
    where IDStudent=@IDStudent
go

select * from dbo.fStudent(5)
------------------------------------
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 1.
/*
RAD SA STRINGOVIMA (1)
Napisati funkciju koja prima string i vraæa novi string sastavljen od:
    druga polovica stringa + prva polovica stringa.
Ako ulazni string ima neparan broj znakova,
neka u prvoj polovici bude manji broj znakova
(primjerice, za "Slon" treba vratiti "onSl", za "Mirko" treba "rkoMi").
*/
CREATE FUNCTION dbo.Obrni
(
    @s nvarchar(max)
)
RETURNS nvarchar(max)
AS
BEGIN
    DECLARE @d1 nvarchar(max)
    DECLARE @d2 nvarchar(max)
    DECLARE @duljina1 int
    DECLARE @duljina2 int

    SET @duljina1 = LEN(@s) / 2
    SET @duljina2 = LEN(@s) - @duljina1

    SET @d1 = SUBSTRING(@s, 1, @duljina1)
    SET @d2 = SUBSTRING(@s, @duljina1 + 1, @duljina2)

    RETURN @d2 + @d1
END
GO
-- Pozvati funkciju s nekim parametrom i ispisati rezultat.
PRINT dbo.Obrni('Slon')
PRINT dbo.Obrni('Mirko')
-- Ispisati obrnuti naziv svakog proizvoda iz tablice Proizvod.
SELECT dbo.Obrni(Naziv) FROM Proizvod
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 2.
/*
RAD SA STRINGOVIMA (2)
Napisati funkciju koja prima string i koja vraæa broj pojavljivanja slova 'a' u tom stringu.
*/
CREATE FUNCTION dbo.BrojSlovaA
(
    @s nvarchar(max)
)
RETURNS int
AS
BEGIN
    DECLARE @curr int
    DECLARE @next int
    DECLARE @broj int

    SET @curr = 1
    SET @next = 1
    SET @broj = 0
    WHILE @next > 0 BEGIN
        SET @next = CHARINDEX('a', @s, @curr)
        IF @next > 0
            SET @broj = @broj + 1

        SET @curr = @next + 1
    END
    RETURN @broj
END
GO
-- Pozvati funkciju s nekim parametrom i ispisati rezultat.
PRINT dbo.BrojSlovaA('Juro')
PRINT dbo.BrojSlovaA('Ana')
PRINT dbo.BrojSlovaA('Ankica')
PRINT dbo.BrojSlovaA('Anakonda')
-- Dohvatiti sve proizvode iz i pokraj svakog ispisati broj pojavljivanja slova 'a' u nazivu.
SELECT *, dbo.BrojSlovaA(Naziv) AS BrojSlovaA FROM Proizvod
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 3.
/*
RAD SA STRINGOVIMA (2)
Napisati funkciju koja prima string i koja vraæa broj samoglasnika u tom stringu.
*/
CREATE FUNCTION dbo.BrojSamoglasnika
(
    @s nvarchar(max)
)
RETURNS int
AS
BEGIN
    DECLARE @i int
    DECLARE @broj int
    DECLARE @znak char(1)

    SET @i = 1
    SET @broj = 0
    WHILE @i <= LEN(@s) BEGIN
        SET @znak = SUBSTRING(@s, @i, 1)
        IF @znak IN ('a', 'e', 'i', 'o', 'u')
            SET @broj = @broj + 1

        SET @i = @i + 1
    END
    RETURN @broj
END
GO
-- Pozvati funkciju s nekim parametrom i ispisati rezultat.
PRINT dbo.BrojSamoglasnika('Juro')
PRINT dbo.BrojSamoglasnika('Ana')
PRINT dbo.BrojSamoglasnika('Ankica')
PRINT dbo.BrojSamoglasnika('Anakonda')
-- Dohvatiti sve proizvode iz i pokraj svakog ispisati broj samoglasnika u nazivu.
SELECT *, dbo.BrojSamoglasnika(Naziv) AS BrojSamoglasnika FROM Proizvod
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 4.
/*
XML (1)
Napisati proceduru koja prima XML dokument oblika:
<Gradovi>
    <Grad>Karlovac</Grad>
    …
</Gradovi>
Neka procedura vrati tablicu s nazivima svih gradova.
*/
CREATE PROC IspisiNazive
    @gradovi xml
AS
SELECT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS Grad
FROM @gradovi.nodes('/Gradovi/Grad') AS Tablica(Stupac)
GO
-- Pozvati proceduru  s nekim parametrom.
EXEC IspisiNazive '<Gradovi><Grad>Karlovac</Grad><Grad>Sisak</Grad><Grad>Kutina</Grad></Gradovi>'
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 5.
/*
XML (1)
Proširiti prethodni XML dokument tako da svaki grad sadržava atribut "PostanskiBroj".
Promijeniti proceduru tako da uz naziv grada ispisuje i poštanski broj.
*/
ALTER PROC IspisiNazive
    @gradovi xml
AS
SELECT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS Grad,
    Tablica.Stupac.value('@Pbr', 'nvarchar(5)') AS Pbr
FROM @gradovi.nodes('/Gradovi/Grad') AS Tablica(Stupac)
GO
-- Pozvati proceduru  s nekim parametrom.
EXEC IspisiNazive '<Gradovi><Grad Pbr="10000">Zagreb</Grad><Grad Pbr="31000">Osijek</Grad></Gradovi>'
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 6.
/*
XML (2)
Napisati proceduru koja prima XML dokument oblika:
<Kupci>
    <Kupac>
        <Ime>Mirko</Ime>
        <Prezime>Mirkiæ</Prezime>
        <Grad>Osijek</Grad>
    </Kupac>
    …
</Kupci>
Neka procedura vrati dva skupa redaka:
- Prezimena svih kupaca poslagana abecednim redom
- Nazive gradova. Ako ima više kupaca iz istog grada, grad treba ispisati samo jednom.
*/
CREATE PROC Zad6
    @dok xml
AS
SELECT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS Prezime
FROM @dok.nodes('/Kupci/Kupac/Prezime') AS Tablica(Stupac)
ORDER BY Prezime

SELECT DISTINCT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS Grad
FROM @dok.nodes('/Kupci/Kupac/Grad') AS Tablica(Stupac)
GO
-- Pozvati proceduru  s nekim parametrom. Provjera da li vraæa dva skupa redaka.
EXEC Zad6 '
    <Kupci>
        <Kupac>
            <Ime>Mirko</Ime>
            <Prezime>Mirkiæ</Prezime>
            <Grad>Osijek</Grad>
        </Kupac>
        <Kupac>
            <Ime>Ana</Ime>
            <Prezime>Aniæ</Prezime>
            <Grad>Osijek</Grad>
        </Kupac>
    </Kupci>'
GO
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 7.
/*
XML (3)
Napisati proceduru koja prima XML dokument oblika:
<Osobe>
    <Osoba ID="1">Ana Aniæ</Osoba>
    <Osoba ID="2">Mira Miriæ</Osoba>
    …
</Osobe>
Neka procedura vrati imena i prezimena te ID-eve svih osoba.
Svaki ID treba nadopuniti s nulama s lijeve strane na duljinu od 5 znakova
(Primjerice, za ID = 1 treba ispisati “00001”, za ID = “176” treba ispisati “00176”)
*/
CREATE PROC Zad7
    @dok xml
AS
SELECT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS ImePrezime,
    CASE
        WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 1 THEN '0000' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
        WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 2 THEN  '000' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
        WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 3 THEN   '00' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
        WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 4 THEN    '0' + Tablica.Stupac.value('@ID', 'nvarchar(50)')
        WHEN LEN(Tablica.Stupac.value('@ID', 'nvarchar(50)')) = 5 THEN          Tablica.Stupac.value('@ID', 'nvarchar(50)')
    END AS ID
FROM @dok.nodes('/Osobe/Osoba') AS Tablica(Stupac)
GO
-- Pozvati proceduru  s nekim parametrom.
EXEC zad7
    '<Osobe>
        <Osoba ID="1">Ana Aniæ</Osoba>
        <Osoba ID="35">Maja Majiæ</Osoba>
        <Osoba ID="158">Tanja Tanjiæ</Osoba>
        <Osoba ID="85002">Vera Veriæ</Osoba>
    </Osobe>'
GO
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 1.
/*
KORISNIÈKI DEFINIRANI TABLIÈNI TIPOVI
Napišite proceduru koja dohvaæa sve kupce èiji ID-evi su proslijeðeni
pomoæu korisnièki definiranog tabliènog tipa.
*/
-- Kreiranje korisnièki definiranog tipa
CREATE TYPE MyKupac AS TABLE ( IDKupac int )
GO
-- Stvaranje procedure
CREATE PROC zad8
    @Kupci MyKupac READONLY
AS
SELECT * FROM dbo.Kupac WHERE IDKupac IN (SELECT * FROM @Kupci)
GO
-- Pozivanje procedure.
DECLARE @t MyKupac
INSERT INTO @t VALUES (1)
INSERT INTO @t VALUES (2)
INSERT INTO @t VALUES (3)
EXEC zad8 @t
GO
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
-- Zadatak 2.
/*
KORISNIÈKI DEFINIRANI TABLIÈNI TIPOVI
Napišite proceduru koja umeæe države proslijeðene
pomoæu korisnièki definiranog tabliènog tipa.
Pozovite proceduru.
*/
-- Kreiranje korisnièki definiranog tipa
CREATE TYPE MyDrzava AS TABLE ( Naziv nvarchar(50) )
GO
-- Stvaranje procedure
CREATE PROC zad9
    @Drzave MyDrzava READONLY
AS
INSERT INTO Drzava
SELECT * FROM @Drzave
GO
-- Pozivanje procedure.
DECLARE @t MyDrzava
INSERT INTO @t VALUES ('Indija')
INSERT INTO @t VALUES ('Pakistan')
INSERT INTO @t VALUES ('Bangladeš')
EXEC zad9 @t
GO
select * from drzava
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 3.
/*
KORISNIÈKI DEFINIRANI TABLIÈNI TIPOVI
Napišite proceduru koja umeæe potkategorije proslijeðene
pomoæu korisnièki definiranog tabliènog tipa.
Umetnuti samo onu potkategoriju koja ne postoji u tablici
(ako postoji nemojte umetati ponovno).
Pozovite proceduru.
*/
-- Kreiranje korisnièki definiranog tipa
CREATE TYPE MyPotkategorija AS TABLE ( KategorijaID int, Naziv nvarchar(50) )
GO
-- Stvaranje procedure
CREATE PROC zad10
    @Potk MyPotkategorija READONLY
AS
INSERT INTO Potkategorija
SELECT KategorijaID, Naziv FROM @Potk WHERE Naziv NOT IN (SELECT Naziv FROM Potkategorija)
GO
-- Pozivanje procedure.
DECLARE @t MyPotkategorija
INSERT INTO @t VALUES (1, 'Mountain Bikes')
INSERT INTO @t VALUES (1, 'Ninja Bikes')
EXEC zad10 @t
GO

select * from Potkategorija
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 4.
/*
Napisati proceduru koja prima JSON dokument oblika:

{"OSOBE":
    [
        {"OSOBA":
            {"ime": "Pero", "prezime": "Perić"}},
        {"OSOBA":
            {"ime": "Iva", "prezime": "Ivić"}}    ]
}

Neka procedura vrati tablicu s imenima i prezimenima osoba.
Pozvati proceduru s gornjim parametrom.
*/

create proc p11
    @json nvarchar(max)
as
select ime, prezime
from openjson(@json, '$.OSOBE')
with
(
    Ime nvarchar(50) '$.OSOBA.ime',
    Prezime nvarchar(50) '$.OSOBA.prezime'
)


declare @json nvarchar(max)
set @json=
'{"OSOBE":
        [
            {"OSOBA":
                    {
                        "ime": "Pero",
                        "prezime": "Perić"
                    }
            },
            {"OSOBA":
                    {
                        "ime": "Iva",
                        "prezime": "Ivić"
                    }
            }
        ]}'

exec p11 @json
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 5.
/*
Napisati proceduru koja prima JSON dokument oblika:

N‘ { "OSOBE":
    [ { "OSOBA":
        { "idosoba": 2,  "ime": "Iva",  "prezime": "Ivić",  "ostalo":
            {"email": "iva.ivic@algebra.hr", "telefon": "091 222 3333” } } } ] } '

Neka procedura vrati tablicu s imenima, prezimenima i telefonskim brojevima osoba.
Pozvati proceduru s gornjim parametrom.
*/

create proc p12
    @json nvarchar(max)
as
select ime, prezime, telefon
from openjson(@json, '$.OSOBE')
with
(
    Ime nvarchar(50) '$.OSOBA.ime',
    Prezime nvarchar(50) '$.OSOBA.prezime',
    Telefon nvarchar(50) '$.OSOBA.ostalo.telefon'
)
go

declare @json nvarchar(max)
set @json=
N'{"OSOBE":
        [
            {"OSOBA":
                    {"idosoba": 2,
                     "ime": "Iva",
                     "prezime": "Ivić",
                     "ostalo":
                        {"email": "iva.ivic@algebra.hr",
                         "telefon": "091 222 3333"}}}]}'
exec p12 @json
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 6.
/*
Napišite upit kojim ćete s podacima IDKupac, Ime, Prezime, Email i Telefon iz tablice Kupac
kreirati JSON dokument oblika kako je prikazano niže, za sve kupce koji imaju IDKupac<11:
{"OSOBE":
    [
    {"OSOBA":
        {"idkupac":1,"ime":"Gustavo","prezime":"Achong",
            "ostalo":{"email":"gustavo0@adventure-works.com","telefon":"398-555-0132"}}}
    ]}
*/
select k.IDKupac as [OSOBA.idkupac], k.Ime as [OSOBA.ime], k.prezime as [OSOBA.prezime],
email as [OSOBA.ostalo.email],
telefon as [OSOBA.ostalo.telefon]
from Kupac as k
where k.IDKupac<11
for json path, root('OSOBE')


----------------------------------------------------------------------------------
-- Kreiranje pomoæne tablice
/*
Napravite tablicu Osoba s IDOsoba (PK i IDENTITY), Ime i Prezime.
*/
CREATE TABLE Osoba
(
    IDOsoba int PRIMARY KEY IDENTITY,
    Ime nvarchar(50),
    Prezime nvarchar(50)
)
GO
-----------------------------------------------------------------------------------
-- Zadatak 1.
/*
TRANSAKCIJE (1)
U transakciji umetnite 3 zapisa u Osoba i na kraju odustanite od transakcije.
*/
SELECT * FROM Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    INSERT INTO Osoba VALUES ('Iva', 'Iviæ')
    SELECT * FROM Osoba
ROLLBACK TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 2.
/*
TRANSAKCIJE (1)
U transakciji umetnite 3 zapisa u Osoba i na kraju potvrdite transakciju.
*/
SELECT * FROM Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    INSERT INTO Osoba VALUES ('Iva', 'Iviæ')
    SELECT * FROM Osoba
COMMIT TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 3.
/*
TRANSAKCIJE (1)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis.
Na kraju odustanite od transakcije.
*/

SELECT * FROM Osoba

DELETE Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SELECT * FROM Osoba
ROLLBACK TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 4.
/*
TRANSAKCIJE (1)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis.
Na kraju potvrdite transakciju.
*/

SELECT * FROM Osoba

DELETE Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SELECT * FROM Osoba
COMMIT TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 5.
/*
TRANSAKCIJE (2)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis i postavite kontrolnu toèku.
Na kraju odustanite od transakcije.
*/
SELECT * FROM Osoba

DELETE Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SAVE TRAN cp2
    SELECT * FROM Osoba
ROLLBACK TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 6.
/*
TRANSAKCIJE (2)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis i postavite kontrolnu toèku.
Na kraju potvrdite transakciju.
*/
SELECT * FROM Osoba

DELETE Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SAVE TRAN cp2
    SELECT * FROM Osoba
COMMIT TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 7.
/*
TRANSAKCIJE (2)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis i vratite se na kontrolnu toèku.
Na kraju odustanite od transakcije.
*/
SELECT * FROM Osoba

DELETE Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SELECT * FROM Osoba
    ROLLBACK TRAN cp1
    SELECT * FROM Osoba
ROLLBACK TRAN

SELECT * FROM Osoba
GO

-----------------------------------------------------------------------------------
-- Zadatak 8.
/*
TRANSAKCIJE (2)
U transakciji umetnite 1 zapis u Osoba i postavite kontrolnu toèku.
Umetnite još 1 zapis i vratite se na kontrolnu toèku.
Na kraju potvrdite transakciju.
*/

SELECT * FROM Osoba

BEGIN TRAN
    INSERT INTO Osoba VALUES ('Miro', 'Miriæ')
    SAVE TRAN cp1
    INSERT INTO Osoba VALUES ('Ana', 'Aniæ')
    SELECT * FROM Osoba
    ROLLBACK TRAN cp1
    SELECT * FROM Osoba
COMMIT TRAN

SELECT * FROM Osoba
GO

DELETE Osoba

-----------------------------------------------------------------------------------
-- Zadatak 9.
/*
TRANSAKCIJE (3)
Napišite proceduru za brisanje države. Neka procedura prima 1 parametar, IDDrzava.
Transakciju vodite izvan procedure.
Ispišite uspjeh ili neuspjeh.
- Pozovite 3 puta proceduru s vrijednostima parametara jednakim 50, 51 i 52.
- Pozovite 3 puta proceduru s vrijednostima parametara jednakim 50, 51 i 1.
*/
-- Napišite proceduru za brisanje države. Neka procedura prima 1 parametar, IDDrzava.
CREATE PROC p9
    @IDDrzava int
AS
DELETE FROM Drzava WHERE IDDrzava = @IDDrzava
GO
-- Transakciju vodite izvan procedure.
-- Ispišite uspjeh ili neuspjeh.
-- Pozovite 3 puta proceduru s vrijednostima parametara jednakim 50, 51 i 52.
BEGIN TRY
    BEGIN TRAN
        EXEC p9 50
        EXEC p9 51
        EXEC p9 52
    COMMIT TRAN
    PRINT 'Brisanje uspješno'
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'Greška pri brisanju'
END CATCH
GO
-- Transakciju vodite izvan procedure.
-- Ispišite uspjeh ili neuspjeh.
-- Pozovite 3 puta proceduru s vrijednostima parametara jednakim 50, 51 i 1.
BEGIN TRY
    BEGIN TRAN
        EXEC p9 50
        EXEC p9 51
        EXEC p9 1
    COMMIT TRAN
    PRINT 'Brisanje uspješno'
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'Greška pri brisanju'
END CATCH
GO

-----------------------------------------------------------------------------------
-- Zadatak 10.
/*
TRANSAKCIJE (3)
Napišite proceduru s 3 parametra za brisanje triju država.
Transakciju vodite unutar procedure.
Ispišite uspjeh ili neuspjeh.
Pozovite proceduru s vrijednostima 50, 51 i 52.
Pozovite proceduru s vrijednostima 50, 51 i 1.
*/
create PROC p10
    @IDDrzava1 int,
    @IDDrzava2 int,
    @IDDrzava3 int
AS
BEGIN TRY
    BEGIN TRAN
        DELETE FROM Drzava WHERE IDDrzava = @IDDrzava1
        DELETE FROM Drzava WHERE IDDrzava = @IDDrzava2
        DELETE FROM Drzava WHERE IDDrzava = @IDDrzava3
    COMMIT TRAN
    PRINT 'Brisanje uspješno'
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'Greška pri brisanju: '  + ERROR_MESSAGE()
END CATCH
GO
-- Pozovite proceduru s vrijednostima 50, 51 i 52.
EXEC p10 50, 51, 52
-- Pozovite proceduru s vrijednostima 50, 51 i 1.
EXEC p10 50, 51, 1
GO

-----------------------------------------------------------------------------------
-- Zadatak 11.
/*
TRANSAKCIJE (4)
Unutar vanjske transakcije pozovite prethodnu proceduru s vrijednostima 50, 51 i 52.
Nakon toga odustanite od vanjske transakcije.
Ispišite uspjeh ili neuspjeh.
Je li brisanje napravljeno?
*/
BEGIN TRY
    BEGIN TRAN
        EXEC p10 50, 51, 52
    ROLLBACK TRAN
    PRINT 'Sve OK, ali odustajem od transakcije'
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'Desila se greška pa odustajem od transakcije'
END CATCH
GO
----------------------------------------------------------------
----------------------------------------------------------------

CREATE TABLE Sales ( Country varchar(50), Region varchar(50), Sales int );
INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);
GO

select * from Sales


-- GROUP BY ROLLUP
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by rollup (Country, Region)

-- GROUP BY CUBE
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by cube (Country, Region)

-- GROUP BY GROUPING SETS ()
select Country, Region, SUM(Sales) AS TotalSales
from Sales
group by grouping sets (Country, Region, ())




-- ZADACI.
use AdventureWorksOBP
go

-- 1.
select
    Prezime, Ime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Kupac as k on r.KupacID = k.IDKupac
where k.Prezime in ('Adams', 'Simmons')
group by rollup (k.Prezime, k.Ime)

-- 2.
select
    Prezime, Ime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Kupac as k on r.KupacID = k.IDKupac
where k.Ime in ('Rose', 'Lydia')
group by rollup (k.Ime, k.Prezime)

-- 3.
select
    kom.Ime, kom.Prezime, COUNT(*) as BrojIzdanihRacuna
from Racun as r
inner join Komercijalist as kom on r.KomercijalistID = kom.IDKomercijalist
where kom.Prezime like 'A%'
group by cube (kom.Ime, kom.Prezime)

-- 4.
select
    p.Naziv, p.Boja, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Proizvod as p on s.ProizvodID = p.IDProizvod
where p.Boja is not null
group by GROUPING sets ((p.Naziv), (p.Boja), ())

-- 5.
select
    p.Boja, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Proizvod as p on s.ProizvodID = p.IDProizvod
where p.Boja is not null
group by GROUPING sets ((p.Boja), ())

-- 6.
select
    d.Naziv as Drzava, g.Naziv as Grad, SUM(s.UkupnaCijena) as Zarada
from Stavka as s
inner join Racun as r on r.IDRacun = s.RacunID
inner join Kupac as k on k.IDKupac = r.KupacID
left join Grad as g on g.IDGrad = k.GradID
left join Drzava as d on d.IDDrzava = g.DrzavaID
group by rollup (d.Naziv, g.Naziv)



-- Primjer funkcija rangiranja
select
    Country,
    Region,
    Sales,
    ROW_NUMBER()    over (order by Sales) as Rn,
    RANK()          over (order by Sales) as R,
    DENSE_RANK()    over (order by Sales) as Dr,
    NTILE(2)        over (order by Sales) as N
from Sales
order by Country, Region

-- Primjer funkcija rangiranja s particioniranjem
select
    Country,
    Region,
    Sales,
    ROW_NUMBER() over (partition by Country order by Sales) as Rn
from Sales

-- ZADACI.

-- 1.
select
    *,
    ROW_NUMBER() over (order by Prezime, Ime) as Rbr
from Kupac
order by Prezime, Ime

-- 2.
select
    *,
    ROW_NUMBER() over (partition by GradID order by Prezime, Ime) as Rbr
from Kupac
order by GradID, Prezime, Ime

-- 3.
select
    *,
    RANK() over (order by Kolicina desc) as MjestoNaRangListi
from Stavka
order by Kolicina desc

-- 4.
select
    *,
    NTILE(13) over (order by DatumIzdavanja, BrojRacuna) as Skupina
from Racun
where KomercijalistID = 284
order by DatumIzdavanja

-- Primjer agregatnih funkcija
select
    *,
    SUM(s.Kolicina) over ()
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

select
    *,
    SUM(s.Kolicina) over (order by DatumIzdavanja)
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

select
    *,
    SUM(s.Kolicina) over (partition by year(DatumIzdavanja) order by DatumIzdavanja)
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

-- Primjer running totala.
select
    Country,
    Region,
    Sales,
    AVG(Sales) over (order by Region) as a1,
    SUM(Sales) over (order by Region) as a2
from Sales
order by 2

-- ZADACI.

-- 1.
select
    s.*,
    r.DatumIzdavanja,
    SUM(s.UkupnaCijena) over (partition by year(DatumIzdavanja) order by DatumIzdavanja) as RunningSum
from Racun as r
inner join Stavka s on s.RacunID = r.IDRacun
order by r.DatumIzdavanja, r.IDRacun

-- 2.
select
    *,
    COUNT(*) over (partition by Boja) as ProizvodaBoje
from Proizvod


-- 3.
select
    *,
    (MAX(CijenaBezPDV) over ()) - CijenaBezPDV as Razlika
from Proizvod
where CijenaBezPDV > 0
order by CijenaBezPDV

-- Primjeri analitièkih funkcija
select
    *,
    FIRST_VALUE(Naziv) over (order by Naziv) as a1
from Proizvod
where Boja is not null
order by Naziv

select
    *,
    FIRST_VALUE(Naziv) over (partition by Boja order by boja, Naziv) as a1
from Proizvod
where Boja is not null
order by Boja, Naziv

select
    *,
    LAG(Naziv, 3) over (order by IDProizvod) as a1
from Proizvod
where Boja is not null
order by IDProizvod
go

select
    *,
    LAG(Naziv, 3) over (partition by Boja order by IDProizvod) as a1
from Proizvod
where Boja is not null
order by Boja, IDProizvod

-- ZADACI.

-- 1.
select
    *,
    CijenaBezPDV - LAG(CijenaBezPDV, 1) over (order by CijenaBezPDV) as Delta
from Proizvod
where CijenaBezPDV > 0
order by CijenaBezPDV

-- 2.
create table VozniRed
(
    IDVozniRed int constraint PK_VozniRed primary key identity,
    Stanica nvarchar(50) not null,
    Polazak time not null
)
go
insert into VozniRed (Stanica, Polazak) values ('Zagreb', '11:00')
insert into VozniRed (Stanica, Polazak) values ('Dugo Selo', '11:35')
insert into VozniRed (Stanica, Polazak) values ('Kutina', '13:15')
insert into VozniRed (Stanica, Polazak) values ('Novska', '13:45')
go

select
    *,
    datediff(minute, Polazak, LEAD(Polazak, 1) over (order by Polazak)) as VoznjaDoIduce
from VozniRed
go








--PRIMJERI SOPLVED
--ISHOD_1
--ZADATAK 1

/* Kreirajte pogled koji će se koristiti za dohvat svih računa kojima su kupci
iz Njemačke. Korištenjem kreiranog pogleda prikažite koliko je računa izdano u pojedinom gradu. */

CREATE VIEW pr1
AS
    SELECT DISTINCT g.Naziv, COUNT(r.IDRacun) AS BrojRacuna from Racun as r
    INNER JOIN Kupac AS k ON k.IDKupac = r.KupacID
    INNER JOIN Grad AS g ON g.IDGrad = k.GradID
GROUP BY g.Naziv

SELECT * FROM pr1
DROP VIEW pr1

--ISHOD_1
--ZADATAK_2

/*Kreirajte novu tablicu Zapisnik sa stupcima IDZapisnik i Sadrzaj. Napravite
okidač na tablici Komercijalist i vežite ga uz sva tri događaja. U okidaču
saznajte koji događaj ga je pozvao i tu informaciju upišite u Zapisnik.
Napravite upis jednog novog retka, izmjenu jednog retka i brisanje jednog
retka. Uklonite okidač.*/


CREATE TABLE Zapisnik
(
    IDZapisnik int IDENTITY(1,1) PRIMARY KEY,
    Poruka nvarchar(max),
)
GO

CREATE TRIGGER Okidac3 ON Komercijalist AFTER INSERT, UPDATE, DELETE
AS
IF EXISTS(SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted) BEGIN
    -- INSERT.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se INSERT.')
END
ELSE IF NOT EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
    -- DELETE.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se DELETE.')
END
ELSE IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) BEGIN
    -- UPDATE.
    INSERT INTO Zapisnik (Poruka) VALUES ('Desio se UPDATE.')
END
GO

--277
SELECT * FROM Komercijalist
--43667
SELECT * FROM Racun

SELECT * FROM Stavka

INSERT INTO Komercijalist (Ime,Prezime) VALUES ('test', 'testic')
SELECT * FROM Zapisnik

UPDATE Komercijalist SET Ime = 'pimpek' WHERE IDKomercijalist = 275
SELECT * FROM Zapisnik

DELETE FROM Stavka WHERE RacunID = 43667
DELETE FROM Racun WHERE KomercijalistID = 277
DELETE FROM Komercijalist WHERE IDKomercijalist = 277
SELECT * FROM Zapisnik
GO

--ZADATAK_3
/*Kreirajte pogled koji vraća ID-eve svih komercijalista te ID-eve svih
izdanih računa. Pri tome omogućite da pogled ostane zaštićen od svih
naknadnih promjena tablica o kojima ovisi. */

CREATE VIEW SalesRepresentativesInvoices AS
    SELECT
        SalesRepresentative.ID AS SalesRepresentativeID,
        Invoice.ID AS InvoiceID
    FROM
        SalesRepresentative
    JOIN
        Invoice ON SalesRepresentative.ID = Invoice.SalesRepresentativeID
WITH SCHEMABINDING

--ZADATAK_4
/*Korisnički zahtjevi kažu da se u tablicu Osoba u stupac OIB uvijek
obavezno mora unijeti podatak duljine 11 znamenaka. Osigurajte
navedeno korištenjem okidača.
Onemogućite kreirani okidač.
Omogućite korištenje rekurzivnih okidača.
*/

CREATE TRIGGER tr_EnforceOIBLength ON Person
FOR INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE LEN(OIB) <> 11)
    BEGIN
        RAISERROR('OIB must be 11 characters long', 16, 1);
        ROLLBACK TRANSACTION;
    END
END

DISABLE TRIGGER tr_EnforceOIBLength ON Person

ENABLE TRIGGER tr_EnforceOIBLength ON Person

EXEC sp_configure 'recursive triggers', 1;
RECONFIGURE;


DROP TRIGGER Okidac3
GO

--ISHOD_2
--ZADATAK_1
/* Na koliko stranica su smješteni podaci iz tablice Potkategorija? Na koliko
stranica su smješteni indeksi iz tablice Potkategorija? Koja je vrijednost
IDPotkategorija prve potkategorije na prvoj podatkovnoj stranici? */

DBCC TRACEON(3604)
DBCC IND('AdventureWorksOBP', 'Potkategorija', -1)
-- a. DBCC IND vraca 2 retka, ali samo jedan od njih ima PageType jednak 1.

--ZADATAK_2

/*Kreirajte proceduru koja će za primljeni IDKupac izbrisati dotičnog kupca.
Ukoliko dotični kupac ne postoji, procedura treba kroz RETURN
parametar vratiti -1, inače 0. Zanemarite odnose tablice Kupac s ostalim
tablicama.
Pozovite kreiranu proceduru kako biste prikazali zadanu funkcionalnost. */

CREATE PROC p1
    @id int
AS
IF EXISTS (SELECT Ime FROM Kupac WHERE IDKupac = @id) BEGIN
    DELETE FROM Kupac
    WHERE IDKupac = @id
END
ELSE BEGIN
    RETURN -1
END
    RETURN 0

EXEC p1 1

--ZADATAK_3

select * from Proizvod

/*Kreirajte skalarnu funkciju koja prima boju i računa koliko ima proizvoda
dotične boje.
Prikažite primjenu kreirane funkcije tako da je iskoristite u prikazu svih
proizvoda, za prikaz rezultata funkcije u dodatnom stupcu.*/

SELECT COUNT (*) FROM Proizvod WHERE Boja = 'Crna'

ALTER FUNCTION fakatSeBumUbil
(
    @boja VARCHAR
)
RETURNS INT
AS
BEGIN
    DECLARE @result INT

    SELECT @result= COUNT(*)
    FROM Proizvod
    WHERE Boja = @boja

    RETURN @result
END



SELECT Naziv, Boja, dbo.fakatSeBumUbil(@Rez) AS total_products
FROM Proizvod


DECLARE @Rez NVARCHAR
SET @Rez = 'Crna'
PRINT dbo.ubilBumSe8(@Rez)

--ZADATAK_3
/*Optimizirajte upit što je bolje moguće:
SELECT DISTINCT PotkategorijaID FROM
Proizvod WHERE Naziv LIKE 'P%' */

SET STATISTICS IO ON

SELECT DISTINCT PotkategorijaID FROM
Proizvod WHERE Naziv LIKE 'P%'

CREATE NONCLUSTERED INDEX test5 ON Proizvod(PotkategorijaID)
GO

--ZADATAK_04
/* Kreirajte proceduru koja prima naziv potkategorije i pripadajuće
IDKategorije, upisuje novu potkategoriju u pripadajuću kategoriju i
kroz izlazni parametar vraća vrijednost primarnog ključa upisanog
retka. Ako već postoji potkategorija zadanog imena za tu kategoriju,
ne treba je upisivati ponovno i u tom slučaju kroz izlazni parametar
vratite vrijednost 0.
Pozovite proceduru dva puta s istim nazivom potkategorije i
IDKategorije te ispišite vraćene vrijednosti. */

CREATE PROCEDURE AddSubcategory (@SubcategoryName varchar(50), @CategoryID int, @NewSubcategoryID int OUTPUT)
AS
BEGIN
    DECLARE @ExistingSubcategoryID int

    SELECT @ExistingSubcategoryID = ID
    FROM Subcategory
    WHERE Name = @SubcategoryName AND CategoryID = @CategoryID

    IF @ExistingSubcategoryID IS NULL
    BEGIN
        INSERT INTO Subcategory (Name, CategoryID)
        VALUES (@SubcategoryName, @CategoryID)

        SET @NewSubcategoryID = SCOPE_IDENTITY()
    END
    ELSE
    BEGIN
        SET @NewSubcategoryID = 0
    END
END

DECLARE @SubcategoryID1 int, @SubcategoryID2 int

EXEC AddSubcategory 'Subcategory A', 1, @SubcategoryID1 OUTPUT
EXEC AddSubcategory 'Subcategory A', 1, @SubcategoryID2 OUTPUT

PRINT @SubcategoryID1
PRINT @SubcategoryID2

--ZADATAK_05

/*Kreirajte tabličnu funkciju koja prima podatak IDKomercijalist i
prikazuje sve račune dotičnog komercijaliste.
Prikažite primjenu kreirane funkcije tako da je iskoristite za prikaz
naziva gradova iz kojih su kupci tih računa. */

CREATE FUNCTION GetBillsBySalesperson (@IDKomercijalist INT)
RETURNS TABLE
AS
RETURN (
    SELECT ra.IDRacun, ra.IDKupac, ra.IDKomercijalist, ku.Grad
    FROM Racuni ra
    JOIN Kupci ku
    ON ra.IDKupac = ku.IDKupac
    WHERE ra.IDKomercijalist = @IDKomercijalist
)
GO

SELECT Grad
FROM GetBillsBySalesperson(1)

--I3
--ZADATAK_1

/* Implementirajte CRUD operacije na tablici Kategorija, ako znate da će se
baza podataka koristiti iz programskog jezika koji ne podržava rad s
funkcijama na bazi podataka.
Neka svaka od CRUD operacija bude riješena zasebnim objektom.
Demonstrirajte korištenje napravljenih objekata.*/

CREATE PROC pSelectKategorija
AS
    SELECT * FROM Kategorija
EXEC pSelectKategorija

CREATE PROC pInsertKategorija
    @Naziv NVARCHAR(255)
AS
    INSERT INTO Kategorija(Naziv) VALUES(@Naziv)
EXEC pInsertKategorija 'Automobili'

CREATE PROC pUpdateKateogrija
    @IDKategorija INT,
    @Naziv NVARCHAR(255)
AS
    UPDATE Kategorija
    SET Kategorija.Naziv = @Naziv
    WHERE IDKategorija = @IDKategorija
EXEC pUpdateKateogrija 7, 'Motorla'

CREATE PROC pDeleteKategorija
    @IDKategorija int
AS
    DELETE FROM Kategorija
    WHERE IDKategorija = @IDKategorija
EXEC pDeleteKategorija 7

--ISHOD_4
--ZADATAK_1

/*Napišite proceduru koja prima podatke putem XML, JSON ili tablične
varijable i poslane podatke upisuje u zadanu tablicu. */
/*<Gradovi>
    <Grad>Karlovac</Grad>
    …
</Gradovi>*/

CREATE PROC IspisiNazive
    @gradovi xml
AS
SELECT
    Tablica.Stupac.value('.', 'nvarchar(50)') AS Grad
FROM @gradovi.nodes('/Gradovi/Grad') AS Tablica(Stupac)
GO
-- Pozvati proceduru  s nekim parametrom.
EXEC IspisiNazive '<Gradovi><Grad>Karlovac</Grad><Grad>Sisak</Grad><Grad>Kutina</Grad></Gradovi>'
GO

-- Zadatak 4.
/*
Napisati proceduru koja prima JSON dokument oblika:

{"OSOBE":
    [
        {"OSOBA":
            {"ime": "Pero", "prezime": "Perić"}},
        {"OSOBA":
            {"ime": "Iva", "prezime": "Ivić"}}    ]
}

Neka procedura vrati tablicu s imenima i prezimenima osoba.
Pozvati proceduru s gornjim parametrom.
*/

create proc p11
    @json nvarchar(max)
as
select ime, prezime
from openjson(@json, '$.OSOBE')
with
(
    Ime nvarchar(50) '$.OSOBA.ime',
    Prezime nvarchar(50) '$.OSOBA.prezime'
)


declare @json nvarchar(max)
set @json=
'{"OSOBE":
        [
            {"OSOBA":
                    {
                        "ime": "Pero",
                        "prezime": "Perić"
                    }
            },
            {"OSOBA":
                    {
                        "ime": "Iva",
                        "prezime": "Ivić"
                    }
            }
        ]}'

exec p11 @json
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 5.
/*
Napisati proceduru koja prima JSON dokument oblika:

N‘ { "OSOBE":
    [ { "OSOBA":
        { "idosoba": 2,  "ime": "Iva",  "prezime": "Ivić",  "ostalo":
            {"email": "iva.ivic@algebra.hr", "telefon": "091 222 3333” } } } ] } '

Neka procedura vrati tablicu s imenima, prezimenima i telefonskim brojevima osoba.
Pozvati proceduru s gornjim parametrom.
*/

create proc p12
    @json nvarchar(max)
as
select ime, prezime, telefon
from openjson(@json, '$.OSOBE')
with
(
    Ime nvarchar(50) '$.OSOBA.ime',
    Prezime nvarchar(50) '$.OSOBA.prezime',
    Telefon nvarchar(50) '$.OSOBA.ostalo.telefon'
)
go

declare @json nvarchar(max)
set @json=
N'{"OSOBE":
        [
            {"OSOBA":
                    {"idosoba": 2,
                     "ime": "Iva",
                     "prezime": "Ivić",
                     "ostalo":
                        {"email": "iva.ivic@algebra.hr",
                         "telefon": "091 222 3333"}}}]}'
exec p12 @json
-----------------------------------------------------------------------------------
-----------------------------------------------------------------------------------
-- Zadatak 6.
/*
Napišite upit kojim ćete s podacima IDKupac, Ime, Prezime, Email i Telefon iz tablice Kupac
kreirati JSON dokument oblika kako je prikazano niže, za sve kupce koji imaju IDKupac<11:
{"OSOBE":
    [
    {"OSOBA":
        {"idkupac":1,"ime":"Gustavo","prezime":"Achong",
            "ostalo":{"email":"gustavo0@adventure-works.com","telefon":"398-555-0132"}}}
    ]}
*/
select k.IDKupac as [OSOBA.idkupac], k.Ime as [OSOBA.ime], k.prezime as [OSOBA.prezime],
email as [OSOBA.ostalo.email],
telefon as [OSOBA.ostalo.telefon]
from Kupac as k
where k.IDKupac<11
for json path, root('OSOBE')

--ISHOD 5
/*Opišite i demonstrirajte probleme koji mogu nastati istovremenim
izvršavanjem više transakcija te kako ih riješiti.*/

/* Postoje 3 osovna problema:
    - prljavo čitanje
        1. transakcija promijeni vrijednost retka
        2. transakcija pročita tu vrijednost (ditrty read)
        3. prva transkacija odustane
    - neponovljivo čitanje
        1. transkacija pročita vrijednost stupca u nekom retku (neponovljivo čitanje)
        2. druga trasnakcija promijeni tu vrijednos i potvrdi se
        3. prva transkacija istim SELECTom sada čita drugu vrijednost
    - fantom
        1. prva transkacija zada SELECT koji vrati n redaka
        2. druga transakcija umetne redak u raspon
        3. prva transkacija zada isti SELECT koji sad vrati n+1 redataka (fantom)
*/

-- prljavo čitanje

-- KORAK 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- KORAK 3
BEGIN TRAN
-- KORAK 5
UPDATE Drzava
SET Naziv = 'Croatia'
WHERE IDDrzava = 1
-- KORAK 7
ROLLBACK

-- fix za dirty read
-- 2. KONEKCIJA -- ovaj dio treba stavit u ZASEBNU KONEKCIJU (new query pa kopi pejst ovih parnih koraka)
-- KORAK 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- KORAK 4
BEGIN TRAN
-- KOARK 6
SELECT *
FROM Drzava
WHERE IDDrzava = 1
-- KORAK 8
ROLLBACK

-- fantom
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- Fantome sprječavamo postavljanjem izolacijskog nivoa na SERIALIZABLE.
-- 3.
BEGIN TRAN
-- 5.
SELECT * FROM Drzava WHERE Naziv LIKE 'H%' -- vraća 1 redak i postavlja S lokot na sve retke koji započinju s H kojeg će držati do kraja transakcije.
-- 7.
SELECT * FROM Drzava WHERE Naziv LIKE 'H%' -- vraća 1 redak.
-- 8.
ROLLBACK TRAN

-- 2.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 4.
BEGIN TRAN
-- 6.
INSERT INTO Drzava (Naziv) VALUES ('Haiti') -- Blokiran zbog S lokota. Da nije, umetnuo bi fantoma.
-- 9.
ROLLBACK TRAN

-- neponovljeno čitanje
-- 1.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- Neponovljivo čitanje sprječavamo postavljanjem bilo kojeg izolacijskog nivoa iznad READ COMMITTED.
-- 3.
BEGIN TRAN
-- 5.
SELECT * FROM Drzava WHERE IDDrzava = 1 -- Tu bi se desilo neponovljivo čitanje, ali se neće desiti jer sam sada postavio S lokot i držim ga do kraja transakcije.
-- 7.
SELECT * FROM Drzava WHERE IDDrzava = 1
-- 8.
ROLLBACK TRAN

-- 2.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- 4.
BEGIN TRAN
-- 6.
UPDATE Drzava SET Naziv = 'Hrv' WHERE IDDrzava = 1 -- Blokiran zbog S lokota.
-- 9.
ROLLBACK TRAN

--ZADATAK_02

CREATE PROCEDURE ExampleProcedure
AS
BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO Customers (FirstName, LastName, Email)
    VALUES ('John', 'Doe', 'john.doe@example.com');

    INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
    VALUES (SCOPE_IDENTITY(), GETDATE(), 100.00);

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    DECLARE @ErrorMessage NVARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    -- Log the error to a separate error log table
    INSERT INTO ErrorLog (ErrorMessage, ErrorSeverity, ErrorState, ErrorTime)
    VALUES (@ErrorMessage, @ErrorSeverity, @ErrorState, GETDATE());

    -- Raise an error to the caller to indicate that the operation failed
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;

--ISHOD 6

--ZADATAK_01

/*Prikažite grupirane podatke, sub totale i grand total korištenjem
neke od opcija grupiranja.
Prikažite podatke i dodijelite im redne brojeve, ranking ili pripadnost
pojedinoj grupi zapisa tražene veličine.
Iskoristite agregatne funkcije za prikaz running sum totala.
Iskoristite analitičke funkcije za usporedbu podataka iz različitih
zapisa.*/

/* Prikažite grupirane podatke, sub totale i grand total korištenjem
neke od opcija grupiranja. */

SELECT
    CATEGORY,
    SUM(QUANTITY) AS TotalQuantity,
    SUM(PRICE * QUANTITY) AS TotalSales
FROM
    Orders
GROUP BY
    CATEGORY
WITH ROLLUP

/*Prikažite podatke i dodijelite im redne brojeve, ranking ili pripadnost
pojedinoj grupi zapisa tražene veličine.*/

WITH RankedData AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY [ColumnName] DESC) AS RowNumber,
        DENSE_RANK() OVER (ORDER BY [ColumnName] DESC) AS Rank,
        NTILE(4) OVER (ORDER BY [ColumnName] DESC) AS GroupNumber
    FROM
        Orders
)
SELECT
    *
FROM
    RankedData

/*Iskoristite agregatne funkcije za prikaz running sum totala.*/
WITH RunningTotals AS (
    SELECT
        [ColumnName],
        SUM([ColumnName]) OVER (ORDER BY [ColumnName]) AS RunningTotal
    FROM
        Orders
)
SELECT
    [ColumnName],
    RunningTotal
FROM
    RunningTotals

/* Iskoristite analitičke funkcije za usporedbu podataka iz različitih
zapisa. */
WITH AnalyticalData AS (
    SELECT
        [ColumnName],
        [ColumnName2],
        ROW_NUMBER() OVER (ORDER BY [ColumnName] DESC) AS RowNumber,
        LAG([ColumnName2], 1, 0) OVER (ORDER BY [ColumnName] DESC) AS PrevColumn2Value,
        LEAD([ColumnName2], 1, 0) OVER (ORDER BY [ColumnName] DESC) AS NextColumn2Value
    FROM
        Orders
)
SELECT
    [ColumnName],
    [ColumnName2],
    RowNumber,
    PrevColumn2Value,
    NextColumn2Value
FROM
    AnalyticalData

--ZADATAK_02
WITH AdvancedData AS (
    SELECT
        [ColumnName],
        SUM([ColumnName2]) OVER (ORDER BY [ColumnName] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
    FROM
        Orders
)
SELECT
    [ColumnName],
    RunningTotal
FROM
    AdvancedData