--Funkcije

--Zaokruživanje
--ROUND( numerički_izraz, duljina )

--2.
SELECT ROUND(42.1699944, 3)

--najveći cijeli broj manji ili jednak danom numeričkom
--FLOOR( numerički_izraz )
SELECT FLOOR(42.45), FLOOR(-42.16), CEILING(42.45), CEILING(-42.16)


--kvadrat broja
--3. 
SELECT SQRT(169), SQUARE(42)

--potencija broja
--POWER( float_izraz, y)
--4.
SELECT POWER(2, 10)

--apsolutna vrijednost broja
SELECT ABS(-10)

--vraća -1 za negativni broj, 0 za 0 i 1 za pozitivni broj
SELECT SIGN(-3), SIGN(0), SIGN(10)  

--slučajni broj između 0 i 1 (granice isključene) 
SELECT RAND()


--string funkcije
--5. 
SELECT LEFT('http://www.racunarstvo.hr', 4) as Protokol,
	SUBSTRING('http://www.racunarstvo.hr', 8, 3) as Servis,
	substring('http://www.racunarstvo.hr', 12, 11) as Naziv,
	Right('http://www.racunarstvo.hr', 2) as Domena

--6. 
SELECT UPPER('www.racunarstvo.hr'), lower('www.racunarstvo.hr')

--7.
SELECT 
	'    Pero ide u šumu     ',
	LTRIM('    Pero ide u šumu     '),
	RTRIM('    Pero ide u šumu     '),
	TRIM('    Pero ide u šumu     ')
	

SELECT REPLACE('Pero ide u šumu', 'Pero', 'Jura')
SELECT LEN('www.racunarstvo.hr')

SELECT CHARINDEX('www.', 'www.racunarstvo.hr'),
	CHARINDEX('.hr', 'www.racunarstvo.hr'),
	CHARINDEX('ovog nema', 'www.racunarstvo.hr'),
	CHARINDEX('r', 'www.racunarstvo.hr'),
	CHARINDEX('r', 'www.racunarstvo.hr',6)

--8. 
USE Losfilm

-- v1
SELECT 
imeprezime, 
CHARINDEX(' ',ImePrezime,1) as GdjeJePraznina, 
LEN(ImePrezime) as UkupnaDuljina,
	LEFT(ImePrezime,CHARINDEX(' ',ImePrezime,1)-1) AS Ime,
	SUBSTRING(ImePrezime, CHARINDEX(' ',ImePrezime,1)+1, LEN(ImePrezime)-CHARINDEX(' ',ImePrezime,1)) as Prezime
FROM Clan
ORDER BY Prezime

-- v2
select LEFT(ImePrezime,CHARINDEX(' ',ImePrezime,1)-1) AS Ime,
REVERSE
(
	LEFT(REVERSE(Imeprezime), CHARINDEX(' ',REVERSE(Imeprezime),1)-1)
) as Prezime
from clan


-- funkcije pretvaranja i provjere podataka
SELECT CAST(45.67 as nvarchar(5))
SELECT CONVERT(VARCHAR(12),GETDATE(), 104) -- 4

SELECT CAST(GETDATE() AS DATE)
SELECT CAST(GETDATE() AS TIME)

SELECT GETDATE()
SELECT cast(GETDATE() as float)
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS datetime)

SELECT ISDATE('2015-12-5')
SELECT telefon from kupac where ISNUMERIC(telefon)=1

SELECT isnull(boja, 'N/A') from proizvod


--datumske funkcije
SELECT GETDATE()

--9.
SELECT DATEPART(year, GETDATE() ) 'Godina',
	DATEPART(month, GETDATE() ) 'Mjesec',
	DATEPART(day, GETDATE() ) 'Dan',
	DATEPART(week, GETDATE() ) 'Tjedan'
		--year, quarter, month, dayofyear, day, week, weekday, hour, minute, second

SELECT YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE())

--10.
SELECT 
	DATEADD(day, 1282, GETDATE() ),
	DATEADD(month, 37, GETDATE() ),
	DATEADD(year, 11, GETDATE() )

SELECT DATEADD(year, 1, '20150216')
SELECT DATEADD(year, 1, '20160216')
SELECT DATEADD(year, 1, '20160229')
SELECT DATEADD(day, 365, '20150216')
SELECT DATEADD(day, 365, '20160216')

--11.
SELECT 
DATEDIFF(day, '19901016', GETDATE()) AS 'Razlika u danima',
DATEDIFF(month, '19901016', GETDATE() ) AS 'Razlika u mjesecima',
DATEDIFF(year, '19901016', GETDATE() ) AS 'Razlika u godinama',
DATEDIFF(minute, '19901016', GETDATE() ) AS 'Razlika u minutama'



-- 12. Vratite je li 'danas' ispravan datum.
SELECT ISDATE('danas')
GO
 
-- 13. Vratite je li '2011-08-15' ispravan datum.
SELECT ISDATE('2011-08-15')
GO

-- 14. Vratite je li '15.08.2011.' ispravan datum.
SELECT ISDATE('15.08.2011.')
GO

-- 15. Vratite je li 'abcd' ispravan broj. 
SELECT ISNUMERIC('abcd')
GO

-- 16. Vratite je li '67.55' ispravan broj.
SELECT ISNUMERIC('67.55')
GO
 
-- 17. Vratite je li '67,55' ispravan broj.
SELECT ISNUMERIC('67,55')
GO



-- 18. Vratite nazive svih posuđenih filmova i uz svaki ispišite datum vraćanja ako je vraćen, odnosno "NIJE VRAĆEN" ako nije.
SELECT
	Film.Naziv,
	ISNULL(CAST(Posudba.DatumVracanja AS CHAR), 'NIJE VRACEN') AS 'Datum vracanja'
FROM Film
INNER JOIN Posudba ON Posudba.FilmID = Film.ID


-- 19. Ispišite ukupnu vrijednost računa izdanih u svibnju 2004
select
sum(s.UkupnaCijena)
from Racun as r
inner join Stavka as s on s.RacunID = r.IDRacun
where year(r.DatumIzdavanja) = 2004
and month(r.DatumIzdavanja) = 5


-- 20. Ispišite koliko proizvoda nema upisanu boju.
select count(*) as BrojProizvodaBezBoje
from proizvod
where boja is null

-- 21. Ispišite broj kupaca iz pojedinog grada, sortirano padajuće po broju kupaca.
select
Grad.Naziv,
COUNT(IDKupac) AS BrojKupaca
from Kupac
inner join Grad on Grad.IDGrad = Kupac.GradID
group by Grad.Naziv
order by BrojKupaca DESC


-- 22. Ispišite prosječno trajanje svih filmova.
SELECT AVG(Trajanje) N'Prosječno trajanje filmova' FROM Film
GO

-- 23. Ispišite prosječno trajanje filmova žanra SF.
SELECT AVG(Trajanje) N'Prosječno trajanje filmova žanra SF' 
	FROM Film 
	INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
		WHERE Zanr.Naziv = 'SF'
GO

-- 24. Ispišite u kojim sve mjesecima su posuđivani filmovi.
SELECT DISTINCT
	MONTH(DatumPosudbe) AS N'Mjeseci u kojima su posuđivani filmovi'
	FROM Posudba
GO

-- 25. Ispišite koliko filmova je posuđeno u travnju.
SELECT 
	COUNT(*) AS N'Broj filmova posuđenih u travnju'
	FROM Posudba
		WHERE MONTH(DatumPosudbe)=4
GO

-- Zad 26: Vratite broj svih proizvoda.

select count(*) as UkupanBrojProizvoda
from proizvod as p

-- Zad 27: Vratite broj proizvoda koji imaju definiranu boju.

select count(*) as UkupanBrojProizvoda
from proizvod as p
where p.boja is not null

-- Zad 28: Vratite najvišu cijenu proizvoda.

select max(p.CijenaBezPDV) as NajvišaCijena
from proizvod as p

-- Zad 29: Vratite prosječnu cijenu proizvoda iz potkategorije 16.

select avg(p.CijenaBezPDV) as ProsječnaCijena
from proizvod as p
where p.PotkategorijaID=16

-- Zad 30: Vratite datume najstarijeg i najnovijeg računa izdanog kupcu 131.

select min(r.DatumIzdavanja) as NajstarijiRačun, max(r.DatumIzdavanja) as NajnovijiRačun
from racun as r
where r.KupacID=131


--zadatak 31
-- Ispišite koliko filmova je snimljeno na koji medij.
SELECT 
	Medij.Naziv AS 'Medij',
	COUNT(Medij.Naziv) AS 'Broj filmova'
	FROM Film 
	INNER JOIN Medij ON Film.MedijID = Medij.ID
	GROUP BY Medij.Naziv

--zadatak 32
-- Ispišite prosječno trajanje filmova koji su žanra SF ili Drama.
SELECT 
	Zanr.Naziv AS N'Žanr',
	AVG(Film.Trajanje) AS 'Prosječno trajanje filmova'
	FROM Film 
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
		WHERE Zanr.Naziv = 'SF' OR Zanr.Naziv = 'Drama'
	GROUP BY Zanr.Naziv

--zadatak 33
-- Ispišite broj posuđenih filmova u mjesecu ožujku, složeno po žanru.
SELECT	Zanr.Naziv N'Žanr', 
		COUNT(*) N'Broj posuđenih filmova'
	FROM Film 
		INNER JOIN Posudba ON Film.ID = Posudba.FilmID
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	WHERE MONTH(Posudba.DatumPosudbe) = 3
	GROUP BY Zanr.Naziv


	   
-- Zad 34: Ispišite sve boje proizvoda i pokraj svake napišite koliko proizvoda ima tu boju.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja

-- Zad 35: Promijenite prethodni upit tako da sortirate padajuće prema broju proizvoda.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja
order by KolikoProizvodaTeBoje desc

-- Zad 36: Promijenite prethodni upit tako da isključite nedefiniranu boju.

select p.boja, count(*) as KolikoProizvodaTeBoje
from proizvod as p
group by p.Boja
having p.boja is not null

-- Zad 37: Ispišite koliko proizvoda svake boje ima u svakoj od potkategorija. Sortirajte prema potkategoriji i prema boji.

select pk.Naziv, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
inner join Potkategorija as pk
on p.PotkategorijaID=pk.IDPotkategorija
group by pk.naziv, p.Boja
order by pk.Naziv, p.Boja

-- Zad 38: Promijenite prethodni upit tako da ispišete 10 kategorija i boja s najviše proizvoda.

select top 10 p.PotkategorijaID, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
group by p.PotkategorijaID, p.Boja
order by KolikoProizvodaTeBoje desc

-- Zad 39: Promijenite prethodni upit tako da umjesto ID potkategorije ispišete njen naziv.

select top 10 pk.Naziv, p.Boja, count(*) as KolikoProizvodaTeBoje
from Proizvod as p
left join Potkategorija as pk
on p.PotkategorijaID=pk.IDPotkategorija
group by pk.naziv, p.Boja
order by KolikoProizvodaTeBoje desc


-- Zad 40: Ispišite nazive svih kategorija i pokraj svake napišite koliko potkategorija je u njoj.

select k.Naziv, count(pk.IDPotkategorija) as KolikoPotkategorijaUKategoriji
from Kategorija as k
inner join Potkategorija as pk
on pk.KategorijaID=k.IDKategorija
group by k.Naziv

-- Zad 41: Ispišite nazive svih kategorija i pokraj svake napišite koliko proizvoda je u njoj.

select k.Naziv, count(p.IDProizvod) as KolikoProizvodaUKategoriji
from Kategorija as k
inner join Potkategorija as pk
on pk.KategorijaID=k.IDKategorija
inner join Proizvod as p
on p.PotkategorijaID=pk.IDPotkategorija
group by k.Naziv

-- Zad 42: Ispišite sve različite cijene proizvoda i napišite koliko proizvoda ima svaku cijenu.

select p.CijenaBezPDV, count(*) as KolikoProizvodaSTomCijenom
from proizvod as p
group by p.CijenaBezPDV
order by 1

-- Zad 43: Ispišite koliko je računa izdano koje godine.

select year(r.DatumIzdavanja) as MjesecRačuna, count(*) as KolikoRačuna
from Racun as r
group by year(r.DatumIzdavanja)
order by 1

-- Zad 44: Ispišite brojeve svih račune izdane kupcu 377 i pokraj svakog ispišite ukupnu cijenu po svim stavkama.

select r.BrojRacuna, sum(s.UkupnaCijena) as UkupnaCijenaPoRačunu
from racun as r
inner join stavka as s
on s.RacunID=r.IDRacun
where r.KupacID=377
group by r.BrojRacuna


--zadatak 45
-- Ispišite popis mjesta u koja su posuđivani filmovi te koliko je filmova posuđeno u koje mjesto.
SELECT
	Mjesto.Naziv N'Mjesto', 
	COUNT(Posudba.FilmID) N'Broj posuđenih filmova'
	FROM Mjesto
		INNER JOIN Clan ON Mjesto.ID = Clan.MjestoID
			INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Mjesto.Naziv
				
--zadatak 46
-- Ispišite samo mjesta u koja je posuđeno više od jednog filma.
SELECT
	Mjesto.Naziv N'Mjesto', 
	COUNT(Posudba.FilmID) N'Broj posuđenih filmova'
	FROM Mjesto
		INNER JOIN Clan ON Mjesto.ID = Clan.MjestoID
			INNER JOIN Posudba ON Clan.ID = Posudba.ClanID
	GROUP BY Mjesto.Naziv
		HAVING COUNT(Posudba.FilmID) > 1

--zadatak 47
-- Ispišite popis žanrova kod kojih je maksimalno trajanje filma (strogo) veće od 138 minuta.
SELECT
	Zanr.Naziv, 
	max(Film.Trajanje)
	FROM Film
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	GROUP BY
		Zanr.Naziv
--		Film.Trajanje 
			HAVING MAX(Film.Trajanje) > 138	
		
--zadatak 48
-- Ispišite popis žanrova kojima je prosječno trajanje veće za barem 2% od minimalnog trajanja filma u tom žanru.
SELECT
	Zanr.Naziv, 
	AVG(Trajanje) 'Prosječno trajanje', 
	MIN(Trajanje) 'Min. tr.',
	MAX(Trajanje)'Max. tr.', 
	1.02 * MIN(Trajanje) 'Min. tr. uvećano za 2%',
	COUNT(*)
	FROM Film
		INNER JOIN Zanr ON Film.ZanrID = Zanr.ID
	GROUP BY Zanr.Naziv
		HAVING AVG(Trajanje) > (1.02 * MIN(Trajanje))

-- Zad 49: Ispišite sve boje koje imaju više od 40 proizvoda.

select p.Boja, count(*) as KolikoProizvoda
from Proizvod as p
group by p.Boja
having count(*)>40

-- Zad 50: Ispišite nazive svih potkategorija koje sadržavaju više od 10 proizvoda.

select pk.Naziv, count(*) as KolikoProizvoda
from Potkategorija as pk
group by pk.Naziv
having count(*)>10

-- Zad 51: Ispišite ukupno zarađene iznose i broj prodanih primjeraka za svaki od ikad prodanih proizvoda.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv

-- Zad 52: Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2000 primjeraka.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv
having sum(s.Kolicina)>2000

-- Zad 53: Ispišite ukupno zarađene iznose za svaki od proizvoda koji je prodan u više od 2.000 primjeraka ili je zaradio više od 2.000.000 dolara.

select p.Naziv, sum(s.UkupnaCijena) as Zarađeno, sum(s.Kolicina) as ProdanoPrimjeraka
from Proizvod as p
inner join stavka as s
on s.ProizvodID=p.IDProizvod
group by p.Naziv
having sum(s.Kolicina)>2000
or sum(s.UkupnaCijena)>2000000
