SELECT efis_film.id, title_proper, amount, production_year FROM efis_film_type
        INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id
        INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id
        INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id
        INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
        LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id
        AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION")
        WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm" LIMIT 20;
		
+-----+-----------------------------+--------+-----------------+
| id  | title_proper                | amount | production_year |
+-----+-----------------------------+--------+-----------------+
| 466 | Valgus Koordis              | 93:08  | 1951            |
| 880 | Jäljed                      | 95:00  | 1963            |
| 393 | Suvi                        | 80:34  | 1976            |
| 125 | Juunikuu päevad             | 87:02  | 1957            |
| 748 | Põrgupõhja uus Vanapagan    | 94:40  | 1964            |
| 406 | Tagahoovis                  | 75:31  | 1956            |
| 882 | Jääminek                    | 88:00  | 1962            |
| 719 | Kutsumata külalised         | 92:45  | 1959            |
| 908 | Vihmas ja päikeses          | 91:58  | 1960            |
| 452 | Tütarlaps mustas            | NULL   | 1966            |
|  81 | Esimese järgu kapten        | 90:40  | 1958            |
| 513 | Väike reekviem suupillile   | 87:00  | 1972            |
| 881 | Jalgrattataltsutajad        | 69:26  | 1963            |
| 480 | Varastatud kohtumine        | NULL   | 1988            |
| 766 | Jahid merel                 | 79:48  | 1955            |
| 471 | Vallatud kurvid             | 88:52  | 1959            |
| 443 | Tulivesi                    | 92:50  | 1994            |
|  79 | Elu tsitadellis             | 85:45  | 1947            |
| 902 | Ühe küla mehed              | 88:04  | 1961            |
| 417 | Tavatu lugu                 | 75:03  | 1973            |
+-----+-----------------------------+--------+-----------------+
20 rows in set (0.01 sec)


SELECT * FROM 
(SELECT efis_film.id, title_proper, amount, production_year FROM efis_film_type
        INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id
        INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id
        INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id
        INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
        LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id
        AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION")
        WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm") AS vahetabel;
		

| 18954 | Ohtlik ema                                     | NULL                       |                 |
| 18968 | Elu hammasratastel (tootmises)                 | NULL                       | 2018            |
| 18969 | Johannes Pääsukese tegelik elu (tootmises)     | NULL                       | 2019            |
| 19058 | Briljandid proletariaadi diktatuurile, II jagu | 120:31 (I ja II jagu)      | 1975            |
| 19059 | Pihtimus                                       | 88:00                      | 2017            |
+-------+------------------------------------------------+----------------------------+-----------------+
244 rows in set (0.02 sec)

SELECT COUNT(*) FROM 
(SELECT efis_film.id, title_proper, amount, production_year FROM efis_film_type
        INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id
        INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id
        INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id
        INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
        LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id
        AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION")
        WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm") AS vahetabel;
		

+----------+
| COUNT(*) |
+----------+
|      244 |
+----------+
1 row in set (0.04 sec)

SELECT COUNT(*), production_year FROM 
(SELECT efis_film.id, title_proper, amount, production_year FROM efis_film_type
        INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id
        INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id
        INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id
        INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
        LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id
        AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION")
        WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm") AS vahetabel
	GROUP BY production_year ORDER BY COUNT(*) DESC;
	
+----------+-----------------+
| COUNT(*) | production_year |
+----------+-----------------+
|       11 | 2011            |
|       10 | 2016            |
|       10 | 2007            |
|       10 | 2012            |
|        7 | 2015            |
|        7 | 2013            |
|        7 | 2006            |
|        6 | 2008            |
|        6 | 2009            |


CREATE TABLE dh_jaagup_mangufilmid AS
SELECT efis_film.id, title_proper, amount, production_year FROM efis_film_type
        INNER JOIN efis_film_type_translation ON efis_film_type_translation.film_type_id=efis_film_type.id
        INNER JOIN efis_film_type_rel ON efis_film_type.id=efis_film_type_rel.film_type_id
        INNER JOIN efis_film ON efis_film.id=efis_film_type_rel.film_id
        INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
        LEFT JOIN efis_film_physical_description ON efis_film_physical_description.film_id=efis_film.id
        AND (classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION"
        OR classificator_code2="FILM_PHYSICAL_TYPE_FILE_FILE_DURATION")
        WHERE efis_film_translation.language='et' AND title="Täispikk mängufilm";
		


MariaDB [mart]> SELECT * FROM dh_jaagup_mangufilmid LIMIT 10;
+-----+----------------------------+--------+-----------------+
| id  | title_proper               | amount | production_year |
+-----+----------------------------+--------+-----------------+
| 466 | Valgus Koordis             | 93:08  | 1951            |
| 880 | Jäljed                     | 95:00  | 1963            |
| 393 | Suvi                       | 80:34  | 1976            |
| 125 | Juunikuu päevad            | 87:02  | 1957            |
| 748 | Põrgupõhja uus Vanapagan   | 94:40  | 1964            |
| 406 | Tagahoovis                 | 75:31  | 1956            |
| 882 | Jääminek                   | 88:00  | 1962            |
| 719 | Kutsumata külalised        | 92:45  | 1959            |
| 908 | Vihmas ja päikeses         | 91:58  | 1960            |
| 452 | Tütarlaps mustas           | NULL   | 1966            |
+-----+----------------------------+--------+-----------------+
10 rows in set (0.01 sec)

SELECT amount FROM dh_jaagup_mangufilmid;
 
ALTER TABLE dh_jaagup_mangufilmid ADD sekundid INT; 

MariaDB [mart]> SELECT * FROM dh_jaagup_mangufilmid LIMIT 10;
+-----+----------------------------+--------+-----------------+----------+
| id  | title_proper               | amount | production_year | sekundid |
+-----+----------------------------+--------+-----------------+----------+
| 466 | Valgus Koordis             | 93:08  | 1951            |     NULL |
| 880 | Jäljed                     | 95:00  | 1963            |     NULL |
| 393 | Suvi                       | 80:34  | 1976            |     NULL |
| 125 | Juunikuu päevad            | 87:02  | 1957            |     NULL |

SELECT * FROM dh_jaagup_mangufilmid WHERE amount REGEXP '^[0-9]{2}:[0-9]{2}$';

| 16861 | Luuraja ja luuletaja                           | 95:00  | 2016            |     NULL |
| 17485 | Polaarpoiss                                    | 97:00  | 2016            |     NULL |
| 18684 | Bodom                                          | 85:00  | 2016            |     NULL |
| 18726 | Sangarid                                       | 90:00  | 2017            |     NULL |
| 19059 | Pihtimus                                       | 88:00  | 2017            |     NULL |
+-------+------------------------------------------------+--------+-----------------+----------+
170 rows in set (0.00 sec)

SELECT * FROM dh_jaagup_mangufilmid WHERE amount REGEXP '^[0-9]{2}:[0-9]{2}$';
SELECT amount, SUBSTRING(amount, 1, 2), SUBSTRING(amount, 4, 2)
  FROM dh_jaagup_mangufilmid WHERE amount REGEXP '^[0-9]{2}:[0-9]{2}$';


| 83:41  | 83                      | 41                      |
| 98:00  | 98                      | 00                      |
| 90:00  | 90                      | 00                      |
| 87:00  | 87                      | 00                      |
| 99:00  | 99                      | 00                      |
| 85:00  | 85                      | 00                      |
| 95:00  | 95                      | 00                      |
| 97:00  | 97                      | 00                      |
| 85:00  | 85                      | 00                      |
| 90:00  | 90                      | 00                      |
| 88:00  | 88                      | 00                      |
+--------+-------------------------+-------------------------+
170 rows in set (0.00 sec)

UPDATE  dh_jaagup_mangufilmid 
  SET sekundid=SUBSTRING(amount, 1, 2)*60+SUBSTRING(amount, 4, 2)
  WHERE amount REGEXP '^[0-9]{2}:[0-9]{2}$';
  
SELECT * FROM dh_jaagup_mangufilmid;


| 18969 | Johannes Pääsukese tegelik elu (tootmises)     | NULL                       | 2019            |     NULL |
| 19058 | Briljandid proletariaadi diktatuurile, II jagu | 120:31 (I ja II jagu)      | 1975            |     NULL |
| 19059 | Pihtimus                                       | 88:00                      | 2017            |     5280 |
+-------+------------------------------------------------+----------------------------+-----------------+----------+

-- lisage UPDATE laused MMM:SS ning HH:MM:SS juhule

UPDATE  dh_jaagup_mangufilmid 
  SET sekundid=SUBSTRING(amount, 1, 3)*60+SUBSTRING(amount, 5, 2)
  WHERE amount REGEXP '^[0-9]{3}:[0-9]{2}$';
  
UPDATE  dh_jaagup_mangufilmid 
  SET sekundid=SUBSTRING(amount, 1, 2)*3600+SUBSTRING(amount, 4, 2)*60+SUBSTRING(amount, 7, 2)
  WHERE amount REGEXP '^[0-9]{2}:[0-9]{2}:[0-9]{2}$';

SELECT * FROM dh_jaagup_mangufilmid WHERE sekundid IS NULL;

SELECT SUM(sekundid) FROM dh_jaagup_mangufilmid;

+---------------+
| SUM(sekundid) |
+---------------+
|       1150185 |
+---------------+

MariaDB [mart]> EXPLAIN dh_jaagup_mangufilmid;
+-----------------+------------------+------+-----+---------+-------+
| Field           | Type             | Null | Key | Default | Extra |
+-----------------+------------------+------+-----+---------+-------+
| id              | int(11) unsigned | NO   |     | 0       |       |
| title_proper    | varchar(255)     | YES  |     | NULL    |       |
| amount          | varchar(255)     | YES  |     | NULL    |       |
| production_year | varchar(50)      | NO   |     | NULL    |       |
| sekundid        | int(11)          | YES  |     | NULL    |       |
+-----------------+------------------+------+-----+---------+-------+

SELECT title_proper, sekundid/1150185 FROM dh_jaagup_mangufilmid;

| Igitee                                         |           0.0052 |
| Eia jõulud Tondikakul (tootmises)              |             NULL |
| Ohtlik ema                                     |             NULL |
| Elu hammasratastel (tootmises)                 |             NULL |
| Johannes Pääsukese tegelik elu (tootmises)     |             NULL |
| Briljandid proletariaadi diktatuurile, II jagu |             NULL |
| Pihtimus                                       |           0.0046 |
+------------------------------------------------+------------------+

-- Leidke 2011. aasta filmide kogupikkus sekundites
-- Kuvage iga 2011. aasta filmi kohta, mitu protsenti moodustab see selle aasta filmide kogupikkusest

SELECT SUM(sekundid) FROM dh_jaagup_mangufilmid WHERE production_year=2011;

+---------------+
| SUM(sekundid) |
+---------------+
|         54418 |
+---------------+

SELECT title_proper, sekundid, sekundid/54418 FROM dh_jaagup_mangufilmid WHERE production_year=2011;


+-------------------------------------+----------+----------------+
| title_proper                        | sekundid | sekundid/54418 |
+-------------------------------------+----------+----------------+
| Üks mu sõber                        |     6120 |         0.1125 |
| Idioot                              |     7620 |         0.1400 |
| Idioot                              |     NULL |           NULL |
| Kirjad Inglile                      |     7080 |         0.1301 |
| Kormoranid ehk nahkpükse ei pesta   |     6900 |         0.1268 |
| Rotilõks                            |     6300 |         0.1158 |
| Surnuaiavahi tütar                  |     5899 |         0.1084 |
| Täitsa lõpp                         |     NULL |           NULL |
| Hella W                             |     4860 |         0.0893 |
| Õhuta balloon                       |     4299 |         0.0790 |
| Hella W.                            |     5340 |         0.0981 |
+-------------------------------------+----------+----------------+

SET @aasta=2011;
SELECT @aasta;

+--------+
| @aasta |
+--------+
|   2011 |
+--------+

SET @aastasumma=(SELECT SUM(sekundid) FROM dh_jaagup_mangufilmid WHERE production_year=@aasta);
SELECT @aastasumma;

+-------------+
| @aastasumma |
+-------------+
|       54418 |
+-------------+
SELECT title_proper, sekundid, sekundid/@aastasumma 
  FROM dh_jaagup_mangufilmid WHERE production_year=@aasta;
  
-- Tehke samad tehted aasta 2012 kohta

SET @aasta=2012;
SET @aastasumma=(SELECT SUM(sekundid) FROM dh_jaagup_mangufilmid WHERE production_year=@aasta);
SELECT title_proper, sekundid, sekundid/@aastasumma 
  FROM dh_jaagup_mangufilmid WHERE production_year=@aasta;

+----------------------------+----------+----------------------+
| title_proper               | sekundid | sekundid/@aastasumma |
+----------------------------+----------+----------------------+
| Vasaku jala reede          |     6034 |               0.1204 |
| Eestlanna Pariisis         |     5700 |               0.1137 |
| Üksik saar                 |     5940 |               0.1185 |
| Deemonid                   |     7080 |               0.1413 |
| Deemonid                   |     7080 |               0.1413 |
| Rat King                   |     5625 |               0.1122 |
| Seenelkäik                 |     NULL |                 NULL |
| Kõik muusikud on kaabakad  |     5160 |               0.1030 |
| Puhastus                   |     7500 |               0.1496 |
| Pitsad                     |     NULL |                 NULL |
+----------------------------+----------+----------------------+


SELECT title_proper, sekundid, 
   sekundid/(SELECT SUM(sekundid) FROM dh_jaagup_mangufilmid) AS suhe
 FROM dh_jaagup_mangufilmid;
 

+------------------------------------------------+----------+--------+
| title_proper                                   | sekundid | suhe   |
+------------------------------------------------+----------+--------+
| Valgus Koordis                                 |     5588 | 0.0049 |
| Jäljed                                         |     5700 | 0.0050 |
| Suvi                                           |     4834 | 0.0042 |
| Juunikuu päevad                                |     5222 | 0.0045 |
| Põrgupõhja uus Vanapagan                       |     5680 | 0.0049 |
| Tagahoovis                                     |     4531 | 0.0039 |
| Jääminek                                       |     5280 | 0.0046 |

-- Kuva iga filmi kohta, mitu korda ta on pikem mängufilmide keskmisest pikkusest

SELECT title_proper, sekundid, 
   sekundid/(SELECT AVG(sekundid) FROM dh_jaagup_mangufilmid) AS suhe
 FROM dh_jaagup_mangufilmid;

 Bodom                                          |     5100 | 0.9445 |
| Sangarid                                       |     5400 | 1.0000 |
| Tõde ja õigus (tootmises)                      |     9000 | 1.6667 |
| Kallis õeke                                    |     6000 | 1.1111 |
| Igitee                                         |     6000 | 1.1111 |

-- Leidke iga aasta kohta filmide pikkuste summa ning keskmine pikkus
-- Järjestage aastad filmide keskmise pikkuse järgi

SELECT SUM(sekundid), production_year FROM dh_jaagup_mangufilmid
 GROUP BY production_year ORDER BY SUM(sekundid);
 

|         36118 | 2013            |
|         36884 | 2015            |
|         37528 | 2006            |
|         44438 | 2007            |
|         50119 | 2012            |
|         50713 | 2016            |
|         54418 | 2011            |
+---------------+-----------------+


SELECT AVG(sekundid), production_year FROM dh_jaagup_mangufilmid
 GROUP BY production_year ORDER BY AVG(sekundid);
 
|     6264.8750 | 2012            |
|     6328.3333 | 1991            |
|     6420.0000 | 2001            |
|     7272.0000 | 1988            |
|     9000.0000 | 2019            |
+---------------+-----------------+

SELECT AVG(sekundid), ROUND(production_year, -1) FROM dh_jaagup_mangufilmid
 GROUP BY ROUND(production_year, -1) ORDER BY AVG(sekundid);
 
+---------------+----------------------------+
| AVG(sekundid) | ROUND(production_year, -1) |
+---------------+----------------------------+
|          NULL |                          0 |
|     3810.2000 |                       1930 |
|     4680.0000 |                       1920 |
|     4804.7500 |                       1980 |
|     4810.8696 |                       1970 |
|     5318.5417 |                       1960 |
|     5340.8966 |                       1990 |
|     5366.5000 |                       1950 |
|     5667.1818 |                       2000 |
|     5816.1356 |                       2010 |
|     6008.8500 |                       2020 |
+---------------+----------------------------+

SELECT AVG(sekundid), 10*FLOOR(production_year/10) AS kymnend FROM dh_jaagup_mangufilmid
 GROUP BY kymnend ORDER BY AVG(sekundid);
 

+---------------+---------+
| AVG(sekundid) | kymnend |
+---------------+---------+
|          NULL |       0 |
|     3616.3333 |    1920 |
|     4294.0000 |    1930 |
|     4768.1739 |    1970 |
|     5070.2500 |    1980 |
|     5141.4000 |    1950 |
|     5145.0000 |    1940 |
|     5275.6400 |    1960 |
|     5295.1923 |    1990 |
|     5608.9048 |    2000 |
|     6021.5385 |    2010 |
+---------------+---------+

-- kuva mängufilmid, mille pikkus ületab mängufilmide keskmist pikkust

SELECT title_proper, sekundid FROM dh_jaagup_mangufilmid
  WHERE sekundid>(SELECT AVG(sekundid) FROM dh_jaagup_mangufilmid);
  

| Polaarpoiss                         |     5820 |
| Päevad, mis ajasid segadusse        |     6300 |
| Sangarid                            |     5400 |
| Tõde ja õigus (tootmises)           |     9000 |
| Kallis õeke                         |     6000 |
| Igitee                              |     6000 |
+-------------------------------------+----------+
107 rows in set (0.00 sec)

Filmid, mille pikkus sekundites ületab vastava aasta filmide keskmist pikkust sekundites

SELECT title_proper, sekundid, production_year FROM dh_jaagup_mangufilmid
  WHERE sekundid>
    (SELECT AVG(sekundid) FROM dh_jaagup_mangufilmid AS koopia
	  WHERE koopia.production_year=dh_jaagup_mangufilmid.production_year);

| Luuraja ja luuletaja                |     5700 | 2016            |
| Koit                                |     6600 | 2015            |
| Polaarpoiss                         |     5820 | 2016            |
| Päevad, mis ajasid segadusse        |     6300 | 2016            |
| Kallis õeke                         |     6000 | 2016            |
| Igitee                              |     6000 | 2017            |
+-------------------------------------+----------+-----------------+