--select program.datum, film.nazev
--from kino inner join program on kino.id_kino= program.kino
--	inner join film on program.film = film.id_film
--where kino.nazev= 'Blan'


select count(film.id_film) 
from kino inner join program on kino.id_kino = program.kino
   inner join film on film.id_film= program.film
where kino.nazev= 'vesmir'

--spocitej kolik filmu hrajou v kinech
select count (film.id_film) as pocet, kino.nazev
from kino inner join program on kino.id_kino = program.kino
	inner join film on film.id_film = program.film
group by kino.od_kino, kino.nazev

--spocitej kolik ruznych filmu hrajou v kinech 
select count (distinct film.id_film) as pocet, kino.nazev
from kino inner join program on kino.id_kino = program.kino
	inner join film on film.id_film = program.film
group by kino.od_kino, kino.nazev

--filmy ktere nikdy nebyly hrany v zadnek kine
select film.nazev
from film left join program on film.id_film = program.film


--filmy ktere nikdy nebyly hrany v zadnem kine
select *
from film left join program on film.id_film = program.film
where program.film is null

--spocitej kolik byly celkove prijmy kina z kina id  1
select sum (program.cena * program.divaci) as trzba
 from program
where program.kino = '001'

--spocitej kolik byly celkove prijmy jednotlivych kin
select program.kino,  sum (program.cena * program.divaci) as trzba
 from program
group by kino


--spocitej ktera z kin maj prijem > 1 000 000
select program.kino,  sum (program.cena * program.divaci) as trzba
 from program
group by kino
having sum (program.cena * program.divaci) > 1000000
 order by trzba DESC
 
 -- vypis filmy ktere nikdy nehrali v kone vesmir
select *
from film
where film.id_film not in (select distinct program.film
						from program inner join kino on program.kino= kino.id_kino
			where kino.nazev= 'blaník' )


select *
from film left join ( select distinct program.film 
						from program inner join kino
					on program.kino= kino.id_kino
					where kino.nazev = 'Blaník') as nehrane
	on film.id_film= nehrane.film
where nehrane.film is null


-- vypis jmena filmu, kina kde byly hrany a pocet predstaveni v danem kine
select film.nazev, kino.nazev, count (program.film)
from kino inner join program on kino.id_kino= program.kino
		inner join film on film.id_film= program.film
group by film.id_film,film.nazev,kino.id_kino,kino.nazev
order by id_film, id_kino