use videos;

-- 3. feladat
select * FROM videos;

-- 4.feladat
select TITLE, DIRECTOR from videos;

select title, director
from videos;

-- 5.feladat
select TITLE, DIRECTOR, case TYPE when 'B' then ':)' when 'D' then ':(' end AS type from videos;
-- vagy
select TITLE, DIRECTOR, if(TYPE ='B',':)',':(') AS type from videos;

-- 6. feladat
select VIDEOID, TITLE, DIRECTOR, DURATION, FEE from videos where duration>90 and fee<500;


-- 7.feladat
select BORROWS.DATEOFCREATION as 'WHEN', MEMBERS.MEMBERID, MEMBERS.NAME
from borrows, members
where BORROWS.MEMBER = MEMBERS.MEMBERID
order by 'WHEN' asc;

-- 8. feladat
SELECT VIDEOID, TITLE, ifnull(dateofcreation,'SOHA') AS 'WHEN'
FROM videos left join borrows on VIDEOID=VIDEO
ORDER BY DATEOFCREATION DESC;


-- 9. feladat
SELECT VIDEOID, TITLE,  ifnull(dateofcreation,'SOHA') AS 'WHEN'
FROM videos left join borrows on VIDEOID=VIDEO
AND TYPE = 'D'
AND (DATEOFCREATION >= SUBDATE(curdate(), interval 1 year) OR DATEOFCREATION IS NULL)
ORDER BY DATEOFCREATION DESC;



-- 10. feladat
SELECT MEMBERID, NAME, SUM(DURATION)
FROM MEMBERS, VIDEOS, BORROWS
WHERE VIDEO = VIDEOID
AND MEMBER = MEMBERID
AND DURATION > 0
GROUP BY MEMBERID, NAME
ORDER BY SUM(DURATION) ASC; 

-- 11. feladat
SELECT MEMBERID, NAME, SUM(DURATION) as SUM_LENGTH
FROM  (VIDEOS JOIN BORROWS ON VIDEO  = VIDEOID) RIGHT OUTER JOIN MEMBERS ON MEMBER=MEMBERID
WHERE LEVELOFMEMBER = 'GOLD'
GROUP BY MEMBERID, NAME
HAVING SUM(DURATION) < 300 OR SUM(DURATION) IS NULL;


-- 12. feladat

SELECT videoid, title, memberid, name, dateofbirth
FROM 
(SELECT videoid, title, min(dateofbirth) oldest
 FROM  
 videos  LEFT OUTER JOIN borrows ON videoid =video LEFT OUTER JOIN members
 ON memberid=member
 GROUP BY videoid,title)  AS t
LEFT OUTER JOIN members ON oldest=dateofbirth 
WHERE 
(videoid, ifnull(memberid,0)) IN
    (SELECT videoid, ifnull(member,0)
     FROM videos LEFT OUTER JOIN borrows ON videoid=video)
ORDER BY videoid ASC, name DESC;

-- vagy
SELECT  DISTINCT VIDEOID, TITLE, MEMBERID, NAME
FROM VIDEOS LEFT JOIN (MEMBERS JOIN (BORROWS  JOIN
(SELECT VIDEO,  MIN(DATEOFBIRTH) AS OLDEST
FROM MEMBERS, BORROWS
WHERE  MEMBER = MEMBERID
GROUP BY VIDEO) AS A
ON BORROWS.VIDEO=A.VIDEO)
ON OLDEST = MEMBERS.DATEOFBIRTH)
ON VIDEOID=BORROWS.VIDEO
AND MEMBERID = MEMBER
ORDER BY VIDEOID ASC, NAME ASC;

