--------------------------------------------------------------------------
-- Vježba 1.
--------------------------------
-- Napravite pogled koji će dohvaćati sve stupce i retke iz tablice Kategorija
CREATE VIEW v1 AS
SELECT * FROM Kategorija
GO
--------------------------------

--------------------------------
-- Vježba 1a.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 1b.
--------------------------------
-- Pomoću pogleda umetnite kategoriju naziva “Alarmi”
INSERT INTO v1 (Naziv) VALUES ('Alarmi')
GO
--------------------------------

--------------------------------
-- Vježba 1c.
--------------------------------
-- Pomoću pogleda promijenite kategoriji “Alarmi” naziv u “Aktivna zaštita”
UPDATE v1 SET Naziv = 'Aktivna zaštita' WHERE Naziv = 'Alarmi'
GO
--------------------------------

--------------------------------
-- Vježba 1d.
--------------------------------
-- Pomoću pogleda obrišite kategoriju “Aktivna zaštita”
DELETE FROM v1 WHERE Naziv = 'Aktivna zaštita'
GO
--------------------------------

--------------------------------
-- Vježba 1e.
--------------------------------
-- Uklonite pogled
DROP VIEW v1
GO
--------------------------------

--------------------------------------------------------------------------
-- Vježba 2.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 2a.
--------------------------------
-- Probajte pomoću pogleda umetnuti novi grad. Što se dogodilo?
INSERT INTO v2 (Grad) VALUES ('Bedekovčina')
GO
SELECT * FROM Grad
GO

--------------------------------

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

--------------------------------
-- Vježba 2c.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 2d.
--------------------------------
-- Uklonite pogled
DROP VIEW v2
GO
--------------------------------

--------------------------------------------------------------------------
-- Vježba 3.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 3a i 3b.
--------------------------------
-- 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

--------------------------------

--------------------------------
-- Vježba 3c.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 3d.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 3e.
--------------------------------
-- 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
--------------------------------

--------------------------------
-- Vježba 3f.
--------------------------------
-- 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