/* Kod za DEMO (slide 5) */

-- 1. Kreiranje baze.
CREATE DATABASE Predavanje01Demo
GO

USE Predavanje01Demo
GO

-- 2. Kreiranje osnovne tablice i punjenje podacima.
CREATE TABLE Klijent
(
	IDKlijent int CONSTRAINT PK_Klijent PRIMARY KEY IDENTITY, 
	Ime nvarchar(50) NOT NULL, 
	Prezime nvarchar(50) NOT NULL, 
	Tel1 nvarchar(50) NULL, 
	Tel2 nvarchar(50) NULL, 
	Tel3 nvarchar(50) NULL
)
GO 

INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Miro', 'Mirić', '091/111-222', null, null)
INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Ana', 'Anić', '091/222-333', '092/222-555', null)
INSERT INTO Klijent (Ime, Prezime, Tel1, Tel2, Tel3) VALUES ('Juro', 'Jurić', '091/333-222', '092/333-333', '098/333-444')
GO

SELECT * FROM Klijent
GO

-- 3. Normalizacija. Prvo kreiramo dvije nove tablice.
CREATE TABLE KlijentOsoba
(
	IDKlijentOsoba int CONSTRAINT PK_KlijentOsoba PRIMARY KEY IDENTITY, 
	Ime nvarchar(50) NOT NULL, 
	Prezime nvarchar(50) NOT NULL
)

CREATE TABLE KlijentTelefon
(
	IDKlijentTelefon int CONSTRAINT PK_KlijentTelefon PRIMARY KEY IDENTITY, 
	KlijentOsobaID int CONSTRAINT FK_KlijentTelefon_KlijentOsoba FOREIGN KEY REFERENCES KlijentOsoba(IDKlijentOsoba) NOT NULL,
	BrojTelefona nvarchar(50) NOT NULL, 
	Rbr int NOT NULL
)
GO

-- 4. Migriramo podatke (ignorirajući potrebnu eksplicitnog umetanja vrijednosti primarnog ključa).
INSERT INTO KlijentOsoba (Ime, Prezime)
SELECT Ime, Prezime
FROM Klijent

INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
SELECT IDKlijent, Tel1, 1 AS Rbr FROM Klijent WHERE Tel1 IS NOT NULL

INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
SELECT IDKlijent, Tel2, 2 AS Rbr FROM Klijent WHERE Tel2 IS NOT NULL

INSERT INTO KlijentTelefon (KlijentOsobaID, BrojTelefona, Rbr)
SELECT IDKlijent, Tel3, 3 AS Rbr FROM Klijent WHERE Tel3 IS NOT NULL
GO

-- 5. Uklanjamo staru tablicu (i time "skršimo" sve njene korisnike)
DROP TABLE Klijent
GO

-- 6. Izraðujemo pogled istog naziva.
CREATE VIEW Klijent
AS
SELECT 
	ko.IDKlijentOsoba AS IDKlijent,
	ko.Ime,
	ko.Prezime,
	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 1) AS Tel1,
	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 2) AS Tel2,
	(SELECT kt.BrojTelefona FROM KlijentTelefon AS kt WHERE kt.KlijentOsobaID = ko.IDKlijentOsoba AND kt.Rbr = 3) AS Tel3
FROM KlijentOsoba AS ko
GO

-- Aplikacija sad može i dalje koristiti objekt naziva Klijent (ali samo za dohvaćanje podataka; INSERT/UPDATE/DELETE je druga priča koja će biti objašnjena u nastavku)
SELECT * FROM Klijent
GO

USE master
DROP DATABASE Predavanje01Demo
GO

-- Primjeri 1, 2, 3, 4.
USE AdventureWorksOBP
GO 

CREATE VIEW p1 
AS
SELECT * FROM Proizvod
GO

SELECT * FROM p1 
SELECT * FROM p1 WHERE PotkategorijaID = 13
SELECT Boja, COUNT(*) AS BrojProizvoda FROM p1 GROUP BY Boja ORDER BY BrojProizvoda DESC

SELECT p1.Naziv, pk.Naziv AS PotkategorijaNaziv
FROM p1
INNER JOIN Potkategorija AS pk ON p1.PotkategorijaID = pk.IDPotkategorija
GO

ALTER VIEW p1 
AS
SELECT IDProizvod, Naziv AS NazivProizvoda, BrojProizvoda, Boja, MinimalnaKolicinaNaSkladistu, CijenaBezPDV, PotkategorijaID FROM Proizvod
GO

SELECT * FROM p1 

DROP VIEW p1
GO

-- Primjeri 5, 6.
CREATE VIEW p2
AS
SELECT
	k.Ime,
	k.Prezime,
	SUM(s.Kolicina) AS KolicinaProizvoda
FROM Komercijalist AS k
INNER JOIN Racun AS r on k.IDKomercijalist = r.KomercijalistID
INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
GROUP BY k.Ime, k.Prezime
GO

SELECT * FROM p2 ORDER BY KolicinaProizvoda DESC
GO

DROP VIEW p2
GO

-- Primjeri 7, 8.
CREATE VIEW p3
AS
SELECT *
FROM KreditnaKartica AS kk
WHERE kk.Tip = 'Diners'
GO

SELECT * FROM p3
SELECT * FROM p3 WHERE Tip = 'Diners'
SELECT * FROM p3 WHERE Tip = 'Visa'
GO

DROP VIEW p3
GO

-- Primjeri 9, 10, 11.
CREATE VIEW p4
AS
SELECT *
FROM Proizvod AS p
WHERE (SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) > 2000
GO

SELECT * FROM p4
GO

ALTER VIEW p4
AS
SELECT 
	*,
	(SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) AS Prodano
FROM Proizvod AS p
WHERE (SELECT SUM(Kolicina) FROM Stavka AS s WHERE s.ProizvodID = p.IDProizvod) > 2000
GO

SELECT * FROM p4
GO

DROP VIEW p4
GO

-- Primjeri 12.
CREATE VIEW p5
AS
SELECT * FROM Kupac
GO

SELECT * FROM p5

INSERT INTO p5 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Miro', 'Miriæ', NULL, '042/111-222', 9)

UPDATE p5 SET Email = 'miro@miro.com' WHERE IDKupac = 19986

DELETE FROM p5 WHERE IDKupac = 19986
GO

-- Primjeri 13.
ALTER VIEW p5
AS
SELECT IDKupac, Ime, Email, Telefon, GradID FROM Kupac
GO

INSERT INTO p5 (Ime, Email, Telefon, GradID) VALUES ('Janko', NULL, '042/222-333', 8)

UPDATE p5 SET Email = 'ana@ana.com' WHERE IDKupac = 19985

DELETE FROM p5 WHERE IDKupac = 19985
GO

DROP VIEW p5
GO

-- Primjeri 14.
CREATE VIEW p6
AS
SELECT 
	k.Ime,
	k.Prezime,
	g.*
FROM Kupac AS k
INNER JOIN Grad AS g ON k.GradID = g.IDGrad
GO

SELECT * FROM p6

INSERT INTO p6 (Naziv, DrzavaID) VALUES ('Velika Gorica', 1)

SELECT * FROM p6
SELECT * FROM Grad

UPDATE p6 SET Naziv = 'Velika Gorica PROMIJENJENO!' WHERE IDGrad = 10

DELETE FROM p6 WHERE IDGrad = 69
GO

DROP VIEW p6
GO

-- Primjeri 15, 16, 17.
CREATE VIEW p7
AS
SELECT * FROM Kupac WHERE GradID = 9
GO

SELECT * FROM p7

INSERT INTO p7 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Lana', 'Laniæ', NULL, NULL, 1)

SELECT * FROM p7
GO

ALTER VIEW p7
AS
SELECT * FROM Kupac WHERE GradID = 9
WITH CHECK OPTION
GO

INSERT INTO p7 (Ime, Prezime, Email, Telefon, GradID) VALUES ('Vana', 'Vaniæ', NULL, NULL, 1)
GO

DROP VIEW p7
GO

-- Primjeri 18.
CREATE TABLE Osoba
(
	IDOsoba int CONSTRAINT PK_Osoba PRIMARY KEY IDENTITY,
	Ime nvarchar(50),
	Prezime nvarchar(50),
	OdjelID int,
	Placa money 
)
GO
INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Miro', 'Miriæ', 1, 5000)
INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Ana', 'Aniæ', 1, 8500)
INSERT INTO Osoba (Ime, Prezime, OdjelID, Placa) VALUES ('Juro', 'Juriæ', 2, 3850)
GO

CREATE VIEW p8
AS
SELECT IDOsoba, Ime, Prezime, OdjelID, Placa FROM Osoba
GO

SELECT * FROM p8
GO

ALTER TABLE Osoba DROP COLUMN OdjelID
GO

SELECT * FROM p8
GO

ALTER VIEW p8 WITH SCHEMABINDING
AS
SELECT IDOsoba, Ime, Prezime, Placa FROM dbo.Osoba
GO

ALTER TABLE Osoba DROP COLUMN Placa
GO

DROP VIEW p8
GO

-- Primjeri 19.
CREATE VIEW p9
AS
SELECT
	k.Ime,
	k.Prezime,
	d.Naziv AS DrzavaNaziv
FROM Kupac AS k
INNER JOIN Grad AS g ON k.GradID = g.IDGrad
INNER JOIN Drzava AS d ON d.IDDrzava = g.DrzavaID
GO

SELECT * FROM p9
GO

EXECUTE sp_helptext p9
GO

ALTER VIEW p9 WITH ENCRYPTION
AS
SELECT
	k.Ime,
	k.Prezime,
	d.Naziv AS DrzavaNaziv
FROM Kupac AS k
INNER JOIN Grad AS g ON k.GradID = g.IDGrad
INNER JOIN Drzava AS d ON d.IDDrzava = g.DrzavaID
GO

SELECT * FROM p9
GO

EXECUTE sp_helptext p9
GO

ALTER VIEW p9 WITH ENCRYPTION, SCHEMABINDING
AS
SELECT
	k.Ime,
	k.Prezime,
	d.Naziv AS DrzavaNaziv
FROM dbo.Kupac AS k
INNER JOIN dbo.Grad AS g ON k.GradID = g.IDGrad
INNER JOIN dbo.Drzava AS d ON d.IDDrzava = g.DrzavaID
GO

ALTER VIEW p9 WITH ENCRYPTION, SCHEMABINDING
AS
SELECT
	k.Ime,
	k.Prezime,
	d.Naziv AS DrzavaNaziv
FROM dbo.Kupac AS k
INNER JOIN dbo.Grad AS g ON k.GradID = g.IDGrad
INNER JOIN dbo.Drzava AS d ON d.IDDrzava = g.DrzavaID
WITH CHECK OPTION
GO

SELECT * FROM p9
GO

DROP VIEW p9
GO
