-----------------------------------------------------------------------------------
-- prebacivanje na bazu AdventureWorksOBP
USE AdventureWorksOBP
GO

-----------------------------------------------------------------------------------
-- Vježbe 1.

-- Vježba 1.1.
-- 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 * FROM p6
GO

-- 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