Notes

11.pocet postav ktore maju v mene aspon dve slova
SELECT COUNT(character.characterid)
FROM character
WHERE character.name LIKE '% %';

12.vrati meno priezvisko a narodeniny ktory do 1.5.2017 nedovrsili 40 rokov
select actor.name as MENO, actor.surnamen as PRIEZVISKO, actor.birthday as NARODENINY
from actor
where floor((current_date -birthday)/365)<40
AND DATE<'01.05.2018';

13.vrati nazvy filmoch a rating na ktorych sa podielaju spojene staty
select film.title, film.rating
from film
where country = 'USA'
order by film.rating;

14.vrati nazvy zanrov a pocty filmov
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;


14.vrati nazvy zanrov ktore nemaju ziaden film
select UPPER(genre.name) as ZANER
from genre
left join filmgenre ON genre.genreid= filmgenre.genreid
where filmgenre.filmID is NULL;

SELECT UPPER(NAME) FROM GENRE
MINUS
SELECT DISTINCT UPPER(NAME) FROM GENRE
JOIN FILMGENRE ON FILMGENRE.GENREID = GENRE.GENREID;

12.cele zaznamy actorov pre hercov ktory sa narodili v parnom roku
select *
from actor
where birthday(mood(year from birthday.actor),2)=0
order by birthday.actor;

13.ktory vrati do jedneho stlca zjednotene roky vydani filmov a hercov
select releaseYear
from film
union all
select(extract(year from actor.birthday))
from actor;

13.vrati nazvy filmov a mena postav
select title
from film
union
select name
from character
order by title DESC;

13 vrati v jednom stlpci mena hercov(meno+prieyvisko) a mena postav nech sa vola NAME bey duplikatov
select name||' '||surnamen as MENO
from actor
union
select name
from character;

15 vrati nayov a rok vzdania filmu ku ktoremu je priradenych najviac zanrov
select title, releaseYear
from (
select title, releaseYear,
from film
join filmgenre ON film.filmID=filmgenre
group by title, releaseYear
)where rownum =1;
ASIK NEVIEM

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);


16.vrat mena a priezvisko hercov ktore maju najviac filmov
select name, surnamen
from(select name, surname
from actor
join character ON actor.actorid=character.filmID
group by name, surname
)where rownum=1;

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;

15. vrati mena a preieyviska hercov ktory od roku 2010 nehrali v komedii
select name, surname
from actor
MINUS
select name, surname
from actor
join character ON actor.actorid=character.actorid
join film ON character.filmID=filmID
join filmgenre ON film.filmID=filmgenre.filmID
join genre ON filmgenre.genreid=genre.genre
where releaseYear = '2010' and genre.name = 'comedi';
ASIK NEVIEM

14.zoznam nazvov filmov, rok vydania, mena hercov(zretazenim mena medyea priyvisko ) ktory v danom filme hrali postavu
vysledok usporiadajte podla nazvu filmu a mena herca
select title, releaseYear, actor.name||' '||actor.surname



13.vrati nazov filmu a jeho dlzku v hodinach pre filmy ktore maju v nazve pink pomenujte nazov
selec title AS NAZOV, (rountime/60)as HODINA
from film
where title LIKE '%Pink%';


11.nazvy zanrov ktore zacinaju na h
select name
from genre
where genre.name LIKE 'H%';

/* 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;


/* 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

/*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;


/* 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 extract(day from birthday);







SELECT * FROM SYSTEM_USER;
SELECT * FROM COMMENTS;
SELECT * FROM BUG;
SELECT * FROM BUG_FEATURE;
SELECT * FROM FEATURE;
commit;
SELECT LOWER(TITLE) as title FROM BUG
WHERE LOWER(TITLE) LIKE '%LEVEL%' OR TITLE LIKE '%LEVEL%';
SELECT REPORTED_DATE "DATE"
FROM BUG
UNION
SELECT PUBLISHED_DATE
FROM COMMENTS;
SELECT * FROM COMMENTS
WHERE EXTRACT(MONTH FROM PUBLISHED_DATE) != 4
ORDER BY PUBLISHED_DATE;
SELECT NAME, SURNAME FROM(
SELECT NAME,SURNAME, COUNT(*) FROM SYSTEM_USER U
JOIN BUG B ON B.REPORTER_LOGIN = U.LOGIN
GROUP BY NAME, SURNAME
ORDER BY COUNT(*) DESC
)WHERE ROWNUM = 1;
SELECT PUBLISHED_DATE FROM COMMENTS
UNION ALL
SELECT REPORTED_DATE FROM BUG;
SELECT NAME || ' ' ||SURNAME AS MENO FROM (
SELECT * FROM SYSTEM_USER U
JOIN COMMENTS C ON C.LOGIN = U.LOGIN
ORDER BY PUBLISHED_DATE DESC
) WHERE ROWNUM = 1;
SELECT COUNT(NAME) AS POCET FROM SYSTEM_USER
WHERE NAME LIKE 'S%' OR NAME LIKE 'R%';
SELECT DESCRIPTION FROM BUG
WHERE EXTRACT(YEAR FROM REPORTED_DATE) = 2016 AND EXTRACT(MONTH FROM
REPORTED_DATE) = 4
ORDER BY REPORTED_DATE;
SELECT NAME || ' ' || SURNAME AS MENO, ROLE FROM(
SELECT * FROM SYSTEM_USER
ORDER BY SURNAME
) WHERE ROWNUM = 1;
SELECT COUNT(*) AS BUGY FROM BUG
WHERE EXTRACT(MONTH FROM REPORTED_DATE) = 4;
SELECT NAME, SURNAME FROM SYSTEM_USER U
LEFT JOIN BUG B ON U.LOGIN = B.REPORTER_LOGIN
WHERE B.REPORTER_LOGIN IS NULL;
SELECT NAME FROM FEATURE
WHERE ID IN(SELECT ID FROM FEATURE
MINUS
SELECT BUG_FEATURE.FEATURE_ID
FROM COMMENTS, BUG_FEATURE
WHERE COMMENTS.BUG_ID = BUG_FEATURE.BUG_ID AND UPPER(COMMENTS.TEXT)
LIKE '%FIX%');
SELECT NAME, SURNAME, POCET2016
FROM(
SELECT U.NAME, U.SURNAME, COUNT(C.LOGIN) AS POCET2016, C.LOGIN
FROM SYSTEM_USER Ux
LEFT JOIN COMMENTS C ON U.LOGIN = C.LOGIN
AND EXTRACT(YEAR FROM C.PUBLISHED_DATE) = 2016
GROUP BY C.LOGIN, U.NAME, U.SURNAME)
WHERE POCET2016<=1;
SELECT NAME, SURNAME
FROM(
SELECT NAME, SURNAME FROM SYSTEM_USER
ORDER BY SURNAME
) WHERE ROWNUM = 1;
SELECT NAME FROM SYSTEM_USER
UNION ALL
SELECT SURNAME FROM SYSTEM_USER;
SELECT NAME || ' ' || SURNAME as DEVELOPER, POCET
FROM (
SELECT NAME, SURNAME, COUNT(C.LOGIN)"POCET" FROM SYSTEM_USER U
LEFT JOIN COMMENTS C ON C.LOGIN = U.LOGIN
WHERE U.ROLE = 'Developer'
GROUP BY C.LOGIN,NAME, SURNAME
ORDER BY POCET DESC
)where POCET<=1;
SELECT NAME, SURNAME FROM SYSTEM_USER U
LEFT JOIN BUG B ON B.REPORTER_LOGIN = U.LOGIN
WHERE B.REPORTER_LOGIN IS NULL;
SELECT TITLE AS BUG, DESCRIPTION AS OPIS, TRUNC((CURRENT_DATE -
REPORTED_DATE))AS VEK FROM BUG;
SELECT NAME || ' ' || SURNAME AS MENO FROM SYSTEM_USER
WHERE NAME LIKE 'J%';
SELECT NAME AS MENO, SURNAME AS PRIEZVISKO FROM SYSTEM_USER
WHERE LOGIN LIKE '%c%'
ORDER BY SURNAME;
SELECT F.NAME AS FEATURE, COUNT(BF.FEATURE_ID) AS POCET FROM FEATURE F
LEFT JOIN BUG_FEATURE BF ON F.ID = BF.FEATURE_ID
GROUP BY F.NAME
ORDER BY POCET;
SELECT NAME, SURNAME, POCET2016
FROM(
SELECT U.NAME, U.SURNAME, COUNT(C.LOGIN) AS POCET2016, C.LOGIN
FROM SYSTEM_USER U
LEFT JOIN COMMENTS C ON C.LOGIN = U.LOGIN
AND EXTRACT (YEAR FROM C.PUBLISHED_DATE) = 2016
GROUP BY C.LOGIN, U.NAME, U.SURNAME
ORDER BY POCET2016
)WHERE POCET2016<=1;
SELECT C.TEXT, FLOOR((CURRENT_DATE-PUBLISHED_DATE)/365) AS VEK FROM
COMMENTS C;
SELECT NAME FROM SYSTEM_USER
UNION
SELECT SURNAME FROM SYSTEM_USER;
SELECT * FROM COMMENTS
WHERE EXTRACT(YEAR FROM PUBLISHED_DATE) = 2016 AND EXTRACT(MONTH FROM
PUBLISHED_DATE) = 5;
SELECT COUNT(*) AS BUGGED_FEAT
FROM(
SELECT COUNT(FEATURE_ID)
FROM BUG_FEATURE
GROUP BY FEATURE_ID
);
SELECT PUBLISHED_DATE"DATE" FROM COMMENTS
UNION ALL
SELECT REPORTED_DATE FROM BUG;
SELECT NAME, SURNAME FROM(
SELECT NAME, SURNAME, COUNT(*) FROM SYSTEM_USER
JOIN BUG ON BUG.REPORTER_LOGIN = SYSTEM_USER.LOGIN
GROUP BY NAME, SURNAME
ORDER BY COUNT(*) DESC
)WHERE ROWNUM = 1;
SELECT NAME || ' ' || SURNAME AS MENO FROM(
SELECT U.NAME, U.SURNAME, C.PUBLISHED_DATE FROM SYSTEM_USER U
JOIN COMMENTS C ON U.LOGIN = C.LOGIN
ORDER BY PUBLISHED_DATE DESC
)WHERE ROWNUM = 1;