--CREATE OR REPLACE VIEW feladat_1_12 AS 
SELECT DISTINCT c.country_name 
FROM   mb16_mitlev.country c 
       inner join mb16_mitlev.country_language cl 
               ON c.country_code = cl.country_code 
       inner join mb16_mitlev.LANGUAGE l 
               ON cl.language_code = l.language_code 
WHERE  l.language_code IN (SELECT language_code 
                           FROM   mb16_mitlev.country_language 
                           WHERE  country_code IN (SELECT country_code 
                                                   FROM   mb16_mitlev.country 
                                                   WHERE 
                                  country_name = 'Hungary')); 


--CREATE OR REPLACE VIEW feladat_2_12 AS 
SELECT country_name 
FROM   (SELECT c.country_name, 
               Rank() 
                 over( 
                   PARTITION BY c.country_code 
                   ORDER BY l.language_code) rank 
        FROM   mb16_mitlev.country c 
               inner join mb16_mitlev.country_language cl 
                       ON c.country_code = cl.country_code 
               inner join mb16_mitlev.LANGUAGE l 
                       ON cl.language_code = l.language_code) 
WHERE  rank = 5; 


--CREATE OR REPLACE VIEW feladat_3_12 AS 
-- :( 


--CREATE OR REPLACE VIEW feladat_4_12 AS 
SELECT country_name, 
       area nemnormalis, 
       100 * ( area - ( Min(area)  over() ) ) / ( Max(area) over() - Min(area) over() ) normalis 
FROM   mb16_mitlev.country 
ORDER  BY area DESC; 


--CREATE OR REPLACE VIEW feladat_5_12 AS 
SELECT j.country_name, 
       Listagg(j.language_name, ';') 
         within GROUP (ORDER BY j.country_name) nyelvek 
FROM   (SELECT UNIQUE c.country_name, 
                      l.language_name 
        FROM   mb16_mitlev.country c 
               inner join mb16_mitlev.country_language cl 
                       ON c.country_code = cl.country_code 
               inner join mb16_mitlev.LANGUAGE l 
                       ON cl.language_code = l.language_code 
        WHERE  c.median_age > 30) j 
GROUP  BY j.country_name; 


--CREATE OR REPLACE VIEW feladat_6_12 AS 
SELECT country_name, 
       rnk 
FROM   (SELECT country_name, 
               population, 
               Row_number() 
                 over ( 
                   ORDER BY population DESC) AS rnk 
        FROM   (SELECT country_name, 
                       population 
                FROM   mb16_mitlev.country)) 
WHERE  rnk <= 10; 