-- Prebacimo se na bazu LosFilm
USE LosFilm;

-- Primjer 1.
-- Ispišimo sve filmove i datume kada su posuđivani
SELECT 
	Film.Naziv, Posudba.DatumPosudbe
	FROM Film
	LEFT OUTER JOIN Posudba
		ON Film.ID = Posudba.FilmID;

		
-- Zadatak 1. 
-- Napišite sličan upit korištenjem unutarnjeg spajanja i usporedite s gornjim.
SELECT 
	Film.Naziv, Posudba.DatumPosudbe
	FROM Film
	INNER JOIN Posudba
		ON Film.ID = Posudba.FilmID;		

		
-- Primjer 2.
-- Ispišimo sve žanrove i filmove koji pripadaju tom žanru
SELECT 
	Film.Naziv AS 'Naziv filma', 
	Zanr.Naziv AS 'Žanr'	
	FROM Film		
	RIGHT OUTER JOIN Zanr
		ON Zanr.ID = Film.ZanrID;


-- Primjer 3.
-- Generirajmo izvještaj koji sadrži nazive svih filmova, ime i prezime člana kome je film posuđen (ako je posuđen, inače NULL), te članove koji nisu posuđivali filmove
SELECT 
	Film.Naziv, 
	DatumPosudbe, 
	--DatumVracanja,
	Clan.ImePrezime AS 'Ime i prezime'
	FROM Film
	LEFT OUTER JOIN Posudba 
		ON Film.ID = Posudba.FilmID
	full  OUTER JOIN Clan
		ON Posudba.ClanID = Clan.ID;


-- Zadatak 1

-- Zadatak 1.1
-- Upišite da je član Pero Perić danas rezervirao filmove Pulp Fiction i Fight Club.
use LosFilm
insert into Rezervacija (FilmID, DatumRezervacije, ClanID)
values
(3,getdate(),1),
(6,getdate(),1)


-- Zadatak 1.2
-- Ispišite popis filmova u bazi s pripadnim rezervacijama. 
-- Ukoliko rezervacija postoji, ispišite i datum rezervacije.
SELECT 
	Film.Naziv, 
	Rezervacija.DatumRezervacije
	FROM Film
	LEFT OUTER JOIN Rezervacija 
		ON Film.ID = Rezervacija.FilmID;
		
-- Zadatak 1.3
-- Dopunite prethodni upit tako da ispišete i ime i prezime člana 
-- koji je napravio rezervaciju.
SELECT 
	Film.Naziv,
	Clan.ImePrezime, 
	Rezervacija.DatumRezervacije
	FROM Film
	LEFT OUTER JOIN Rezervacija 
		ON Film.ID = Rezervacija.FilmID
	LEFT OUTER JOIN Clan 
		ON Rezervacija.ClanID = Clan.ID;
		
-- Zadatak 1.4
-- Ispišite popis svih mjesta te sve članove koje žive u danom mjestu. 
-- Ukoliko nema članova, ispišite NULL.
SELECT 
	Mjesto.Naziv,
	Clan.ImePrezime
	FROM Mjesto
	LEFT OUTER JOIN Clan 
		ON Mjesto.ID = Clan.MjestoID;
		
-- Zadatak 1.5
-- Ispišite popis svih mjesta u koja su posuđivani filmovi te koji su to filmovi.
SELECT 
	Mjesto.Naziv AS 'Mjesto',
	Film.Naziv AS 'Film'
	FROM Mjesto
	INNER JOIN Clan 
		ON Mjesto.ID = Clan.MjestoID
	INNER JOIN Posudba 
		ON Posudba.ClanID = Clan.ID
	INNER JOIN Film 
		ON Posudba.FilmID = Film.ID;
-- a ovako bismo dobili sva mjesta te posuđene filmove, ako je bilo posudbi
SELECT 
	Mjesto.Naziv AS 'Mjesto',
	Film.Naziv AS 'Film'
	FROM Mjesto
	LEFT OUTER JOIN Clan 
		ON Mjesto.ID = Clan.MjestoID
	LEFT OUTER JOIN Posudba 
		ON Posudba.ClanID = Clan.ID
	LEFT OUTER JOIN Film 
		ON Posudba.FilmID = Film.ID;
		
-- Zadatak 1.6
-- Dopunite prethodni upit tako da ispišete i popis žanrova (a za žanrove koji nisu posuđivani, ispišite NULL)
SELECT 
	Mjesto.Naziv AS 'Mjesto',
	Film.Naziv AS 'Film',
	Zanr.Naziv AS 'Žanr'
	FROM Mjesto
	LEFT OUTER JOIN Clan 
		ON Mjesto.ID = Clan.MjestoID
	LEFT OUTER JOIN Posudba 
		ON Posudba.ClanID = Clan.ID
	LEFT OUTER JOIN Film 
		ON Posudba.FilmID = Film.ID
	FULL OUTER JOIN Zanr 
		ON Zanr.ID = Film.ZanrID;


-- Zadatak 2
USE AdventureWorksOBP;

-- Zadatak 2.1
-- Koristeći lijevo vanjsko spajanje dohvatiti imena svih kupaca koji nisu ništa kupili.
select k.Ime, k.Prezime, r.IDRacun 
from kupac as k
left outer join racun as r
on r.KupacID=k.IDKupac
where r.IDRacun is null

-- Zadatak 2.2
-- Koristeći desno vanjsko spajanje dohvatiti imena svih kupaca koji nisu ništa kupili.
select k.Ime, k.Prezime, r.IDRacun 
from racun as r
right outer join kupac as k
on r.KupacID=k.IDKupac
where r.IDRacun is null

-- Zadatak 2.3
-- Koristeći puno vanjsko spajanje ispisati nazive država i nazive gradova.
select g.Naziv as Grad, d.Naziv as Država
from drzava as d
full outer join grad as g
on d.IDDrzava=g.DrzavaID

-- Zadatak 2.4
-- Upišite državu Indiju te grad London, bez navođenja kojoj državi pripada. Koristeći puno vanjsko spajanje ispisati nazive država i nazive gradova. Ispisati samo one gradove koji nemaju definiranu državu i one države koji nemaju upisanih gradova.
insert into grad (naziv)
values ('London')

insert into drzava (naziv)
values ('Indija')

select g.Naziv as Grad, d.Naziv as Država
from drzava as d
full outer join grad as g
on d.IDDrzava=g.DrzavaID
where g.naziv is null
or d.Naziv is null


-- Zadatak 3
USE AdventureWorksOBP;

-- Zadatak 3.1
-- Ispišite sve kupce koji su kupovali proizvode (ime, prezime, datum narudžbe i ukupan iznos prodaje)
SELECT DISTINCT
	Kupac.Ime,
	Kupac.Prezime,
	Racun.DatumIzdavanja AS 'Datum',
	Stavka.UkupnaCijena AS 'Ukupan iznos'
FROM Racun
INNER JOIN Kupac  ON Kupac.IDKupac  = Racun.KupacID
INNER JOIN Stavka ON Stavka.RacunID = Racun.IDRacun;

-- Zadatak 3.2
-- Ispišite sve proizvode koji su prodani po nekom računu.
SELECT
	Proizvod.*
FROM Racun
INNER JOIN Stavka   ON Stavka.RacunID = Racun.IDRacun
INNER JOIN Proizvod ON Proizvod.IDProizvod = Stavka.ProizvodID
WHERE Racun.IDRacun = 43659;

-- Zadatak 3.3
-- Ispišite sve prodane proizvode po jednom računu 
-- te ispišite u koju kategoriju i potkategoriju svaki proizvod pripada.
SELECT
	Proizvod.*,
	Kategorija.Naziv AS 'Kategorija',
	Potkategorija.Naziv AS 'Potkategorija'
FROM Racun
INNER JOIN Stavka   ON Stavka.RacunID = Racun.IDRacun
INNER JOIN Proizvod ON Proizvod.IDProizvod = Stavka.ProizvodID
LEFT OUTER JOIN Potkategorija ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
LEFT OUTER JOIN Kategorija    ON Potkategorija.KategorijaID = Kategorija.IDKategorija
WHERE Racun.IDRacun = 43659;

-- Zadatak 3.4
-- Ispišite popis modela složen po kategorijama (za svaki proizvod ispišite pripadnu 
-- potkategoriju i kategoriju kojoj pripada).
SELECT
	Proizvod.*,
	Kategorija.Naziv as Kategorija,
	Potkategorija.Naziv as Potkategorija
FROM Proizvod
LEFT OUTER JOIN Potkategorija ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
LEFT OUTER JOIN Kategorija    ON Potkategorija.KategorijaID = Kategorija.IDKategorija;

-- Zadatak 3.5
-- Ispišite za svaku državu u koju je išao neki račun popis kategorija proizvoda koji su tamo prodani.
SELECT DISTINCT
	Drzava.Naziv AS 'Država',
	Kategorija.Naziv AS 'Kategorija proizvoda koji su prodani'
FROM Kupac
INNER JOIN Grad ON Kupac.GradID = Grad.IDGrad
INNER JOIN Drzava ON Grad.DrzavaID = Drzava.IDDrzava
INNER JOIN Racun    ON Kupac.IDKupac  = Racun.KupacID
INNER JOIN Stavka   ON Stavka.RacunID = Racun.IDRacun
INNER JOIN Proizvod ON Proizvod.IDProizvod = Stavka.ProizvodID
LEFT OUTER JOIN Potkategorija ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
LEFT OUTER JOIN Kategorija ON Potkategorija.KategorijaID = Kategorija.IDKategorija
order by 1

-- Zadatak 3.6
-- Ispišite popis kupaca i pripadne potkategorije proizvoda koje su kupovali.
SELECT DISTINCT
	Kupac.Ime+' '+Kupac.Prezime AS 'Kupac',
	Potkategorija.Naziv AS 'Model'
FROM Racun
INNER JOIN Kupac    ON Kupac.IDKupac  = Racun.KupacID
INNER JOIN Stavka   ON Stavka.RacunID = Racun.IDRacun
INNER JOIN Proizvod ON Proizvod.IDProizvod = Stavka.ProizvodID
LEFT OUTER JOIN Potkategorija ON Proizvod.PotkategorijaID = Potkategorija.IDPotkategorija
order by 1