1) Izpisi vse recepte, za katere potrebujemo noz
select recept.ID, recept.Naslov, recept.Podnaslov, recept.Cas_priprave, pripomocek.Naziv as Pripomocek
from recept
left join pripomocek_recept on pripomocek_recept.Recept_ID = recept.ID
inner join pripomocek on pripomocek.ID = pripomocek_recept.ID
group by recept.ID
having pripomocek.Naziv = "Noz"

2) Izpisi recept, ki vsebuje en ali vec korakov in vsebuje vec kot 10g vitaminov

select recept.ID, recept.Naslov,
count(korak.id) as SteviloKorakov
from recept
left join korak on korak.ID = recept.ID
left join sestavina_recept on sestavina_recept.ID = recept.ID
inner join sestavina on sestavina.ID = sestavina_recept.ID
inner join hranilna_vrednost on Hranilna_vrednost_ID = sestavina_recept.ID
where  hranilna_vrednost.Vitamini > 10
group by recept.ID
having SteviloKorakov >= 1


3) Izberi recepte glavnih jedi, ki jih je pripravia kuharica anja

select  recept.ID, recept.Naslov, recept.stevilo_oseb,
        recept.Podnaslov, recept.Cas_priprave,
        kategorija.Naziv as Kategorija, kuhar.Uporabnisko_ime as Kuharica
from recept
inner join kuhar on kuhar.ID = recept.Kuhar_ID
inner join kategorija on kategorija.ID = recept.Kategorija_ID
group by recept.ID
having Kategorija = "Glavna jed" and kuhar.Uporabnisko_ime = "anja"

4) Za recept "Okusna solata" izpisi stevilo korakov, stevilo sestavin, skupno vrednost mascob

select  recept.Naslov as Recept,
        count(korak.ID) as StKorakov,
        count(sestavina.ID) StSestavin,
        sum(hranilna_vrednost.Mascobe) as Mascobe
from recept
left join korak on korak.Recept_ID = recept.ID
left join sestavina_recept on sestavina_recept.Recept_ID = recept.ID
inner join sestavina on sestavina.ID = sestavina_recept.Sestavina_ID
inner join hranilna_vrednost on hranilna_vrednost.ID = sestavina.Hranilna_vrednost_ID
group by recept.ID
having Recept = "Okusna solata"

5) Vrni najbolje ocenjen recept, ki vsebuje manj kot 100g kalorij

select recept.ID, recept.Naslov,
        sum(hranilna_vrednost.Kalorije) as StKalorij,
        utez.Stevilo as Ocena
from recept
left join sestavina_recept on sestavina_recept.Recept_ID = recept.ID
inner join sestavina on sestavina.ID = sestavina_recept.Sestavina_ID
inner join hranilna_vrednost on hranilna_vrednost.ID = sestavina.Hranilna_vrednost_ID
inner join kuhar on kuhar.ID= recept.Kuhar_ID
inner join komentar on komentar.Kuhar_ID= kuhar.ID
inner join ocena on ocena.ID = komentar.Ocena_ID
inner join utez on utez.ID = ocena.Utez_ID
where utez.Stevilo = 5
group by recept.ID
having StKalorij < 100 and Ocena = 5