Notes

/* 1.Vráti žáner a počet filmov, zoradenie podla žanru*/


SELECT ZANER, POCET FROM
(SELECT GENRE.NAME AS ZANER, FILMGENRE.GENREID, COUNT(FILMGENRE.GENREID) AS POCET FROM FILM
LEFT OUTER JOIN FILMGENRE ON FILMGENRE.FILMID = FILM.FILMID
LEFT OUTER JOIN GENRE ON GENRE.GENREID = FILMGENRE.GENRE.GENREID
GROUP BY GENRE.NAME, FILMGENRE.GENREID
) ORDER BY ZANER;

/* 2 Vráti mena a priezviska hercov ktori maju najviac filmov(motze byt ich aj viac)*/

SELECT ACTOR.NAME, ACTOR.SURNAME FROM(
SELECT ACTOR.ACTORID, ACTOR.NAME, ACTOR.SURNAME, COUNT(CHARACTER.FILMID) FROM ACTOR
JOIN CHARACTER ON CHARACTER.ACTORID = ACTOR.ACTORID
HAVING COUNT(CHARACTER.FILMID) = (SELECT MAX(COUNT(FILMID)) FROM CHARACTER
GROUP BY ACTORID)
GROUP BY ACTOR.ACTORID, ACTOR.NAME, ACTOR.SURNAME) ACTOR;


/* 3 Vráti v jednom stlpci, roky vydania filmov a roky narodenia hercov, bez duplikatov */

SELECT RELEASEYEAR AS ROK FROM FILM
UNION ALL
SELECT EXTRACT(YEAR FROM BIRTHDAY) AS ROK FROM ACTOR;

/* 4 dopyt vráti zanre zacinajuce na H*/

SELECT NAME
From GENRE
WHERE NAME LIKE 'H%'
ORDER BY NAME;

/* 5vrati hercov ktori do 1.5.2017 nedovrsili 40 rokov*/

Select Name, Surname
FROM Actor
where FLOOR((date'2017-5-1' - birthday)/365) < 40


/* 6 dopyt vráti žanre začínajúce na H*/

SELECT NAME
From GENRE
WHERE NAME LIKE 'H%'
ORDER BY NAME;

/* 7 dopyt vráti zanre a pocet filmov,stlpce pomenujte ako ZANER A POCET zoradit podla zanrov abecedne, pozor moze existovat aj Žanre ktorě maju rovnaky nazov*/



/* 8 vrati názov filmu a jeho dlžku v hodinách (nezaokruhlujeme)pre filmy ktore majú v názve slovo PINK. stlpce pomenujte NAZOV a HODINY */

SELECT TITLE AS NAZOV, (RUNTIME/120) AS HODINY
FROM FILM
WHERE TITLE LIKE '%Pink%' ;

/* 9 vrati pocet postvav ktoré maju v mene aspon 2 slová(tj. niekde v mene je medzera)*/

/* 1.Vráti žáner a počet filmov, zoradenie podla žanru*/


SELECT ZANER, POCET FROM
(SELECT GENRE.NAME AS ZANER, FILMGENRE.GENREID, COUNT(FILMGENRE.GENREID) AS POCET FROM FILM
LEFT OUTER JOIN FILMGENRE ON FILMGENRE.FILMID = FILM.FILMID
LEFT OUTER JOIN GENRE ON GENRE.GENREID = FILMGENRE.GENRE.GENREID
GROUP BY GENRE.NAME, FILMGENRE.GENREID
) ORDER BY ZANER;



Select genre.name as zaner, count(film.filmid) as pocet
From genre
Left join filmgenre on genre.genreid=filmgenre.genreid
Left join film on filmgenre.filmid=film.filmid
Group by genre.name, genreid
Order by genre.name;




/* 2 Vráti mena a priezviska hercov ktori maju najviac filmov(motze byt ich aj viac)*/

SELECT ACTOR.NAME, ACTOR.SURNAME FROM(
SELECT ACTOR.ACTORID, ACTOR.NAME, ACTOR.SURNAME, COUNT(CHARACTER.FILMID) FROM ACTOR
JOIN CHARACTER ON CHARACTER.ACTORID = ACTOR.ACTORID
HAVING COUNT(CHARACTER.FILMID) = (SELECT MAX(COUNT(FILMID)) FROM CHARACTER
GROUP BY ACTORID)
GROUP BY ACTOR.ACTORID, ACTOR.NAME, ACTOR.SURNAME) ACTOR;




/* 3 Vráti v jednom stlpci, roky vydania filmov a roky narodenia hercov, bez duplikatov */

SELECT RELEASEYEAR AS ROK FROM FILM
UNION
SELECT EXTRACT(YEAR FROM BIRTHDAY) AS ROK FROM ACTOR;



/* 4 dopyt vráti zanre zacinajuce na H*/

SELECT NAME
From GENRE
WHERE NAME LIKE 'H%'
ORDER BY NAME;



/* 5vrati hercov ktori do 1.5.2017 nedovrsili 40 rokov*/
Select Name, Surname
FROM Actor
where FLOOR((date'2017-5-1' - birthday)/365) < 40;




/* 6 dopyt vráti žanre začínajúce na H*/

SELECT NAME
From GENRE
WHERE NAME LIKE 'H%'
ORDER BY NAME;



/* 7 dopyt vráti zanre a pocet filmov,stlpce pomenujte ako ZANER A POCET zoradit podla zanrov abecedne, pozor moze existovat aj Žanre ktorě maju rovnaky nazov*/

Select genre.name as zaner, count(film.filmid) as pocet
From genre
Left join filmgenre on genre.genreid=filmgenre.genre id
Left join film on filmgenre.filmid=film.filmid
Group by genre.name, genreid
Order by genre.name



/* 8 vrati názov filmu a jeho dlžku v hodinách (nezaokruhlujeme)pre filmy ktore majú v názve slovo PINK. stlpce pomenujte NAZOV a HODINY */

SELECT TITLE AS NAZOV, (RUNTIME/120) AS HODINY
FROM FILM
WHERE TITLE LIKE '%Pink%' ;


/* 9 vrati pocet postvav ktoré maju v mene aspon 2 slová(tj. niekde v mene je medzera)*/
SELECT COUNT(character.characterid)
FROM character
WHERE character.name LIKE '% %';




/* 10 vráti názov a rok vydania filmu, ku ktorému je pridaných najviac žánrov.(takych filmov moze byt aj viac !!!*/

SELECT film.title, film.releaseyear
FROM film
LEFT JOIN filmgenre ON filmgenre.filmid=film.filmid
GROUP BY film.title
HAVING COUNT(filmgenre.genreid) IN
(SELECT MAX(COUNT(filmgenre.genreID))
FROM filmgenre
GROUP BY filmgenre.filmid);


/* 11 vráti názov nazvy filmov a mena postav jednom stlpci. Vysledok nech je bez duplikátov a usporiadany podla abecedy od Z po A*/
SELECT TITLE
FROM FILM
UNION
SELECT NAME, SURNAME
FROM CHARACTER
ORDER BY TITLE DESC;


/*12 Nech vráti názvy žanrov ktoré nemaju ziadny film. Cele nazvy zanrov vypiste velkými pismenami */
SELECT UPPER(NAME) FROM GENRE
MINUS
SELECT DISTINCT UPPER(NAME) FROM GENRE
JOIN FILMGENRE ON FILMGENRE.GENREID = GENRE.GENREID;


/*13 dopyt vráti cele záznamy(vsetky stlpce) tabulky actor, pre hercov ktori sa narodili v párny rok . Vysledky usporiadajte od najstarsieho po najmladsieho */
SELECT *
FROM ACTOR
WHERE EXTRACT(MODE(YEAR FROM BIRTHDAY), 2) = 0
ORDER BY BIRTHDAY ASC;



/* 14 dopyt vráti nazvy tich filmov a ich ratingy, na ktorych sa podielalo USA. (USAsa nachadza v stlpci country) vysledky zoradte posdla ratingu od najvyssieho po najnizsi */
SELECT film.title, film.rating
FROM film
WHERE UPPER(film.country) LIKE '%USA%'
ORDER BY rating DESC;




/*v15 rati film kde je release day viac ako rok 2000 a rating viac ako 7*/
SELECT TITLE FROM FILM
WHERE RELEASEDATE > 2000 AND RATING > 7;




/* 16 rati meno, priezvisko a vek herca ktory je najmladsi */
SELECT Name AS MENO, SURNAME AS PRIEZVISKO, FLOOR((current_date-birthday)/365) AS VEK
FROM ACTOR
WHERE FLOOR((current_date-birthday)/365) = (SELECT MAX(FLOOR((current_date-birthday)/365)) FROM ACTOR)
ORDER BY BIRTHDAY ASC;




/* 17 vypise hercoV a ich role. pozor herec moze mat aj viac rol */
SELECT ACTOR.NAME, ACTOR.SURNAME, CHARACTER.NAME FROM ACTOR
JOIN CHARACTER ON CHARACTER.ACTORID = ACTOR.ACTORID;



/* 18 vypis filmy ktore maju najviac žhanrov pridelenych */
SELECT film.title, film.releaseyear
FROM film
JOIN filmgenre ON filmgenre.filmid=film.filmid
GROUP BY film.filmid
HAVING COUNT(filmgenre.genreid) IN
(SELECT MAX(COUNT(filmgenre.genreID))
FROM filmgenre
GROUP BY filmgenre.filmid);



/* 19 herci ktorích mena zacinaju na H a usporiadat podla abecedy */
SELECT NAME, SURNAME
From ACTOR
WHERE NAME LIKE 'H%'
ORDER BY NAME;



/* 20 zoznam nazvov filmov(podla podmienky a ich postav, kazda do noveho riadku */





/* 21 vrati nazvy filmov podla zanrov horor a komedy*/
SELECT FILM.TITLE, GENRE.NAME FROM FILM
JOIN FILMGENRE ON fILMGENRE.FILMID = FILM.FILMID
JOIN GENRE ON GENRE.GENREID = FILMGENRE.GENREID
WHERE LOWER(GENRE.NAME) = 'horor' OR LOWER(GENRE.NAME) = 'komedy';


/* 22 vrati meno a prizvisko herca a jeho vek v rokoch ak rok ma 365 dni */
SELECT NAME AS MENO, SURNAME AS PRIEZVISKO, FLOOR((current_date-birthday)/365) AS VEK
FROM ACTOR;

/*neohlasili ziaden film v roku 2010- netusim ako to je dalej */





/*Vypise 4 ludi v poradi ktori sa narodili v oktobri a zoradit nie podla datumu ale podla dna kedy sa narodil- zle riesenie */
select actor.name, actor.surname from actor
where extract(month from actor.birthday) = 10
order by floor(current_date - birthday);