eesnimi pikkus mass synniaasta
juku    170    65   2002
kati    165    55   2003
mati    187    77   2001

Tabeli lai kuju:

eesnimi pikkus mass synniaasta  synnikoht huviala
juku    170    65   2002                  jalgpall
kati           55   2003        Paide
mati    187    77   2001


Tabeli pikk kuju:

eesnimi  tunnus     sisu
juku     pikkus     170
juku     mass       65
juku     synniaasta 2002
juku     huviala    jalgpall
kati     mass       55
kati     synniaasta 2001
kati     synnikoht  Paide
mati     pikkus     187
mati     mass       77
mati     synniaasta 2001


efis_film_physical_description
MariaDB [mart]> EXPLAIN efis_film_physical_description;

+-------------------------+------------------+------+-----+---------+----------------+
| Field                   | Type             | Null | Key | Default | Extra          |
+-------------------------+------------------+------+-----+---------+----------------+
| id                      | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| film_id                 | int(11) unsigned | NO   | MUL | NULL    |                |
| classificator_type_code | varchar(100)     | NO   |     | NULL    |                |
| classificator_code1     | varchar(100)     | NO   |     | NULL    |                |
| classificator_code2     | varchar(100)     | NO   |     | NULL    |                |
| classificator_code3     | varchar(100)     | YES  |     | NULL    |                |
| amount                  | varchar(255)     | YES  |     | NULL    |                |
| seq                     | int(11) unsigned | NO   |     | 1       |                |
| element_number          | int(11) unsigned | NO   |     | 1       |                |
| created                 | datetime         | NO   |     | NULL    |                |
| created_by              | int(11) unsigned | NO   |     | NULL    |                |
| updated                 | datetime         | NO   |     | NULL    |                |
| updated_by              | int(11) unsigned | NO   |     | NULL    |                |
+-------------------------+------------------+------+-----+---------+----------------+

SELECT DISTINCT classificator_code2 FROM efis_film_physical_description;

+---------------------------------------------------+
| classificator_code2                               |
+---------------------------------------------------+
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_COLOUR             |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDFORMATS             |
| FILM_PHYSICAL_TYPE_FILM_FILM_DURATION             |
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_WIDTH       |
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_MATERIAL    |
| FILM_PHYSICAL_TYPE_FILM_FILM_COLOUR               |
| FILM_PHYSICAL_TYPE_FILM_FILM_SOUND                |
| FILM_PHYSICAL_TYPE_FILM_FILM_ARCHIVE_TAG          |
| FILM_PHYSICAL_TYPE_FILM_FILM_METRAGE              |
| FILM_PHYSICAL_TYPE_FILM_FILM_NUM_PARTS_TOTAL      |
| FILM_PHYSICAL_TYPE_FILM_FILM_NOTES                |
| FILM_PHYSICAL_TYPE_FILM_FILM_LANGUAGE_VERSIONS    |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION           |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_SOUNDFORMATS       |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_FILEFORMATS |
| FILM_PHYSICAL_TYPE_FILE_FILE_COLOUR               |
| FILM_PHYSICAL_TYPE_FILE_FILE_DURATION             |
| FILM_PHYSICAL_TYPE_FILE_FILE_SOUNDFORMATS         |
| FILM_PHYSICAL_TYPE_FILM_FILM_PROJECTION_FORMAT    |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDDEVICES             |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDMATERIALS           |
| FILM_PHYSICAL_TYPE_SOUND_SOUND_ARCHIVE_TAG        |
| FILM_PHYSICAL_TYPE_SOUND_SOUND_NOTES              |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DIGITAL            |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_LANGUAGE_VERSIONS  |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_PROJECTION_FORMAT  |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ARCHIVE_TAG        |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_CODEC       |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_RESOLUTION  |
| FILM_PHYSICAL_TYPE_FILE_FILE_DEVICE_DISC          |
| FILM_PHYSICAL_TYPE_FILE_FILE_LANGUAGE_VERSIONS    |
| FILM_PHYSICAL_TYPE_FILE_FILE_ARCHIVE_TAG          |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_PROF        |
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_DEVICE      |
| FILM_PHYSICAL_TYPE_FILM_FILM_PROJECTION_TYPE      |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_STANDARD    |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_PROJECTION_TYPE    |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_NOTES              |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_SIMPLE      |
| FILM_PHYSICAL_TYPE_FILE_FILE_NOTES                |
+---------------------------------------------------+

SELECT classificator_code2, COUNT(*) 
  FROM efis_film_physical_description
  GROUP BY classificator_code2
  ORDER BY COUNT(*) DESC;
  

+---------------------------------------------------+----------+
| classificator_code2                               | COUNT(*) |
+---------------------------------------------------+----------+
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_WIDTH       |    11227 |
| FILM_PHYSICAL_TYPE_FILM_FILM_COLOUR               |    10845 |
| FILM_PHYSICAL_TYPE_FILM_FILM_SOUND                |    10390 |
| FILM_PHYSICAL_TYPE_FILM_FILM_ARCHIVE_TAG          |    10355 |
| FILM_PHYSICAL_TYPE_FILM_FILM_DURATION             |     9542 |
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_MATERIAL    |     2802 |
| FILM_PHYSICAL_TYPE_FILM_FILM_NUM_PARTS_TOTAL      |     2555 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION           |     1640 |
| FILM_PHYSICAL_TYPE_FILM_FILM_METRAGE              |     1613 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_COLOUR             |     1429 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_SOUNDFORMATS       |     1185 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DIGITAL            |      761 |
| FILM_PHYSICAL_TYPE_FILM_FILM_NOTES                |      660 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_PROJECTION_FORMAT  |      459 |
| FILM_PHYSICAL_TYPE_FILE_FILE_COLOUR               |      402 |
| FILM_PHYSICAL_TYPE_FILE_FILE_DURATION             |      396 |
| FILM_PHYSICAL_TYPE_FILE_FILE_SOUNDFORMATS         |      382 |
| FILM_PHYSICAL_TYPE_FILM_FILM_PHYSICAL_DEVICE      |      253 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_PROF        |      236 |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDFORMATS             |      216 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_NOTES              |      208 |
| FILM_PHYSICAL_TYPE_FILM_FILM_LANGUAGE_VERSIONS    |      208 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ARCHIVE_TAG        |      173 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_PROJECTION_TYPE    |       83 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_LANGUAGE_VERSIONS  |       55 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_SIMPLE      |       51 |
| FILM_PHYSICAL_TYPE_FILE_FILE_DEVICE_DISC          |       33 |
| FILM_PHYSICAL_TYPE_FILE_FILE_NOTES                |       32 |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_RESOLUTION  |       31 |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_FILEFORMATS |       31 |
| FILM_PHYSICAL_TYPE_FILM_FILM_PROJECTION_TYPE      |       16 |
| FILM_PHYSICAL_TYPE_FILM_FILM_PROJECTION_FORMAT    |       13 |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDDEVICES             |       11 |
| FILM_PHYSICAL_TYPE_SOUND_SOUND_ARCHIVE_TAG        |        9 |
| FILM_PHYSICAL_TYPE_FILE_FILE_LANGUAGE_VERSIONS    |        5 |
| FILM_PHYSICAL_TYPE_SOUND_SOUND_NOTES              |        4 |
| FILM_PHYSICAL_TYPE_FILE_FILE_PHYSICAL_CODEC       |        4 |
| FILM_PHYSICAL_TYPE_FILE_FILE_ARCHIVE_TAG          |        3 |
| FILM_PHYSICAL_TYPE_VIDEO_VIDEO_ANALOG_STANDARD    |        3 |
| FILM_PHYSICAL_TYPE_SOUND_SOUNDMATERIALS           |        2 |
+---------------------------------------------------+----------+

SELECT film_id, amount FROM efis_film_physical_description
 WHERE classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION' 
 ORDER BY film_id LIMIT 20;
 


+---------+-----------------------+
| film_id | amount                |
+---------+-----------------------+
|       8 | 64:14                 |
|       9 | 70:23                 |
|      13 | 15:36                 |
|      14 | 84:19                 |
|      15 | 89:48                 |
|      16 | 78:00                 |
|      17 | 24:02                 |
|      19 | 28:00                 |
|      24 | 95:00                 |
|      25 | 94:00                 |
|      26 | 23:59                 |
|      30 | 15:53                 |
|      31 | 72:37                 |
|      35 | 10:02                 |
|      38 | 10:20                 |
|      43 | 29:00                 |
|      49 | 120:31 (I ja II jagu) |
|      53 | 80:00                 |
|      57 | 53:49                 |
|      59 | 90:00                 |
+---------+-----------------------+
20 rows in set (0.00 sec)


SELECT film_id, amount FROM efis_film_physical_description
 WHERE classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION' 
 ORDER BY film_id LIMIT 20;

 
+---------+--------+
| film_id | amount |
+---------+--------+
|       1 | 08:00  |
|       2 | 9:59   |
|       7 | 15:00  |
|      12 | 15:33  |
|      23 | 21:36  |
|      30 | 18:00  |
|      39 | 90:00  |
|      45 | 16:34  |
|      58 | 05:52  |
|      64 | 03:59  |
|      70 | 00:25  |
|      71 | 19:22  |
|      88 | 02:43  |
|     103 | 23:00  |
|     111 | 17:00  |
|     114 | 20:00  |
|     156 | 15:27  |
|     157 | 85:00  |
|     170 | 86:00  |
|     179 | 09:00  |
+---------+--------+
20 rows in set (0.03 sec)


SELECT efis_film_translation.title_proper, efis_film.id, efis_film_physical_description.amount 
 FROM efis_film_physical_description
  INNER JOIN efis_film ON efis_film_physical_description.film_id=efis_film.id
  INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
 WHERE classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION' 
   AND language='et'
 ORDER BY efis_film.id LIMIT 20;
 
  -- muutke lauset nõnda, et näidatakse pikkusi nii filmi- kui videolindilt
  
+-----------------------------+-----+--------+
| title_proper                | id  | amount |
+-----------------------------+-----+--------+
| 03:07                       |   1 | 08:00  |
| Vanapagan                   |   2 | 9:59   |
| 30 minutit vaikust          |   7 | 15:00  |
| Aaria                       |  12 | 15:33  |
| Amatöörid                   |  23 | 21:36  |
| Armas tuss 50 aastat hiljem |  30 | 18:00  |
| August 1991                 |  39 | 90:00  |
| Bibendum                    |  45 | 16:34  |
| Kuritegu maksab kätte       |  58 | 05:52  |
| Jumalik tragöödia           |  64 | 03:59  |
| E2-E4                       |  70 | 00:25  |
| Ebaõnne talisman            |  71 | 19:22  |
| Fiktsioon                   |  88 | 02:43  |
| Helmut                      | 103 | 23:00  |
| Indigo tuba                 | 111 | 17:00  |
| Inimkaamera                 | 114 | 20:00  |
| Kingapuu                    | 156 | 15:27  |
| Kinnunen                    | 157 | 85:00  |
| Kohtumine tundmatuga        | 170 | 86:00  |
| Kontrolör                   | 179 | 09:00  |
+-----------------------------+-----+--------+


SELECT efis_film_translation.title_proper, efis_film.id, efis_film_physical_description.amount,
  efis_film_physical_description.classificator_code2
 FROM efis_film_physical_description
  INNER JOIN efis_film ON efis_film_physical_description.film_id=efis_film.id
  INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
 WHERE (classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION' 
   OR classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
   OR classificator_code2='FILM_PHYSICAL_TYPE_FILE_FILE_DURATION')
   AND language='et'
 ORDER BY efis_film.id LIMIT 20;
 

+-----------------------------+----+--------+
| title_proper                | id | amount |
+-----------------------------+----+--------+
| 03:07                       |  1 | 08:00  |
| Vanapagan                   |  2 | 9:59   |
| 30 minutit vaikust          |  7 | 15:00  |
| 31. osakonna hukk  I jagu   |  8 | 64:14  |
| 31. osakonna hukk  II jagu  |  9 | 70:23  |
| Aaria                       | 12 | 15:33  |
| Aednik                      | 13 | 15:36  |
| Aeg elada, aeg armastada    | 14 | 84:19  |
| Aeg maha!                   | 15 | 89:48  |
| Agent Sinikael              | 16 | 78:00  |
| Agu Sihvka annab aru        | 17 | 24:02  |
| Sellised kolm lugu: Aida    | 19 | 28:00  |
| Amatöörid                   | 23 | 21:36  |
| Ameerika mäed               | 24 | 95:00  |
| Andruse õnn                 | 25 | 94:00  |
| Ankur                       | 26 | 23:59  |
| Armas tuss 50 aastat hiljem | 30 | 18:00  |
| Armas tuss 50 aastat hiljem | 30 | 15:53  |
| Armastuse lahinguväljad     | 31 | 72:37  |
| Armuavaldus                 | 35 | 10:02  |
+-----------------------------+----+--------+


SELECT efis_film_translation.title_proper, efis_film.id, efis_film_physical_description.amount,
  efis_film_physical_description.classificator_code2
 FROM efis_film_physical_description
  INNER JOIN efis_film ON efis_film_physical_description.film_id=efis_film.id
  INNER JOIN efis_film_translation ON efis_film_translation.film_id=efis_film.id
 WHERE (classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION' 
   OR classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
   OR classificator_code2='FILM_PHYSICAL_TYPE_FILE_FILE_DURATION')
   AND language='et'
 ORDER BY efis_film.id LIMIT 20;

+-----------------------------+----+--------+-----------------------------------------+
| title_proper                | id | amount | classificator_code2                     |
+-----------------------------+----+--------+-----------------------------------------+
| 03:07                       |  1 | 08:00  | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| Vanapagan                   |  2 | 9:59   | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| 30 minutit vaikust          |  7 | 15:00  | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| 31. osakonna hukk  I jagu   |  8 | 64:14  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| 31. osakonna hukk  II jagu  |  9 | 70:23  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Aaria                       | 12 | 15:33  | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| Aednik                      | 13 | 15:36  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Aeg elada, aeg armastada    | 14 | 84:19  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Aeg maha!                   | 15 | 89:48  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Agent Sinikael              | 16 | 78:00  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Agu Sihvka annab aru        | 17 | 24:02  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Sellised kolm lugu: Aida    | 19 | 28:00  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Amatöörid                   | 23 | 21:36  | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| Ameerika mäed               | 24 | 95:00  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Andruse õnn                 | 25 | 94:00  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Ankur                       | 26 | 23:59  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Armas tuss 50 aastat hiljem | 30 | 18:00  | FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION |
| Armas tuss 50 aastat hiljem | 30 | 15:53  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Armastuse lahinguväljad     | 31 | 72:37  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
| Armuavaldus                 | 35 | 10:02  | FILM_PHYSICAL_TYPE_FILM_FILM_DURATION   |
+-----------------------------+----+--------+-----------------------------------------+

SELECT efis_film.id, andmed1.amount AS filmikestus FROM efis_film
  INNER JOIN efis_film_physical_description AS andmed1
    ON andmed1.film_id=efis_film.id 
     AND classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
  ORDER BY efis_film.id	  
  LIMIT 20;
  

+----+-----------------------+
| id | filmikestus           |
+----+-----------------------+
|  8 | 64:14                 |
|  9 | 70:23                 |
| 13 | 15:36                 |
| 14 | 84:19                 |
| 15 | 89:48                 |
| 16 | 78:00                 |
| 17 | 24:02                 |
| 19 | 28:00                 |
| 24 | 95:00                 |
| 25 | 94:00                 |
| 26 | 23:59                 |
| 30 | 15:53                 |
| 31 | 72:37                 |
| 35 | 10:02                 |
| 38 | 10:20                 |
| 43 | 29:00                 |
| 49 | 120:31 (I ja II jagu) |
| 53 | 80:00                 |
| 57 | 53:49                 |
| 59 | 90:00                 |
+----+-----------------------+
20 rows in set (0.00 sec)

SELECT efis_film.id, andmed1.amount AS filmikestus, andmed2.amount AS videokestus FROM efis_film
  INNER JOIN efis_film_physical_description AS andmed1
    ON andmed1.film_id=efis_film.id 
     AND andmed1.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
  INNER JOIN efis_film_physical_description AS andmed2
    ON andmed2.film_id=efis_film.id 
     AND andmed2.classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION'
  ORDER BY efis_film.id	  
  LIMIT 20;	  
  
-- filmid, kus on olemas nii video- kui filmipikkus
+------+-------------+-------------+
| id   | filmikestus | videokestus |
+------+-------------+-------------+
|   30 | 15:53       | 18:00       |
|  256 | 12:00       | 12:00       |
|  264 | 21:04       | 21:04       |
|  283 | 72:31       | 73:50       |
|  305 | 00:08:32    | 8:32        |
|  511 | 14:00       | 14:00       |
|  888 | 28:07       | 28:00       |
| 2878 | 15:46       | 16:00       |
| 3014 | 39:28       | 42:00       |
| 3027 | 19:00       | 19:00       |
| 3738 | 7:49        | 7:49        |
| 3754 | 30:00       | 31:00       |
| 3775 | 51:41       | 55:00       |
| 4169 | 53:00       | 53:00       |
| 5392 | 11:00       | 11:00       |
| 5490 | 27:00       | 27:00       |
| 6129 | 59:01       | 59:01       |
| 6425 | 48:00       | 46:50       |
| 6720 | 59:04       | 62:00       |
| 7110 | 52:00       | 55:00       |
+------+-------------+-------------+

SELECT efis_film.id, andmed1.amount AS filmikestus, andmed2.amount AS videokestus FROM efis_film
  LEFT JOIN efis_film_physical_description AS andmed1
    ON andmed1.film_id=efis_film.id 
     AND andmed1.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
  LEFT JOIN efis_film_physical_description AS andmed2
    ON andmed2.film_id=efis_film.id 
     AND andmed2.classificator_code2='FILM_PHYSICAL_TYPE_VIDEO_VIDEO_DURATION'
  ORDER BY efis_film.id	  
  LIMIT 20;	
  

+----+-------------+-------------+
| id | filmikestus | videokestus |
+----+-------------+-------------+
|  1 | NULL        | 08:00       |
|  2 | NULL        | 9:59        |
|  5 | NULL        | NULL        |
|  7 | NULL        | 15:00       |
|  8 | 64:14       | NULL        |
|  9 | 70:23       | NULL        |
| 12 | NULL        | 15:33       |
| 13 | 15:36       | NULL        |
| 14 | 84:19       | NULL        |
| 15 | 89:48       | NULL        |
| 16 | 78:00       | NULL        |
| 17 | 24:02       | NULL        |
| 19 | 28:00       | NULL        |
| 23 | NULL        | 21:36       |
| 24 | 95:00       | NULL        |
| 25 | 94:00       | NULL        |
| 26 | 23:59       | NULL        |
| 30 | 15:53       | 18:00       |
| 31 | 72:37       | NULL        |
| 32 | NULL        | NULL        |
+----+-------------+-------------+


SELECT STR_TO_DATE("11:36", "%i:%s");

+-------------------------------+
| STR_TO_DATE("11:36", "%i:%s") |
+-------------------------------+
| 00:11:36                      |
+-------------------------------+


SELECT MINUTE(STR_TO_DATE("11:36", "%i:%s"));

+---------------------------------------+
| MINUTE(STR_TO_DATE("11:36", "%i:%s")) |
+---------------------------------------+
|                                    11 |
+---------------------------------------+

SELECT TIME_TO_SEC(STR_TO_DATE("11:36", "%i:%s"));

-- Leidke, mitu sekundit on kokku 3:15 ja 4:55

SELECT TIME_TO_SEC(STR_TO_DATE("3:15", "%i:%s"))+TIME_TO_SEC(STR_TO_DATE("04:55", "%i:%s")) AS tulemus;

+---------+
| tulemus |
+---------+
|     490 |
+---------+

SELECT SEC_TO_TIME(TIME_TO_SEC(STR_TO_DATE(" 3:15", "%i:%s"))+
                   TIME_TO_SEC(STR_TO_DATE("04:55", "%i:%s"))) AS tulemus;
+----------+
| tulemus  |
+----------+
| 00:08:10 |
+----------+

SELECT amount FROM efis_film_physical_description
  WHERE classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION";
  
| 01:28                      |
| 00:58                      |
| 01:11                      |
| 00:51                      |
+----------------------------+
9542 rows in set (0.25 sec)

SELECT film_id, amount, TIME_TO_SEC(STR_TO_DATE(amount, "%i:%s")) AS sekundeid 
   FROM efis_film_physical_description
  WHERE classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION";
  
|    7947 | 01:00                      |        60 |
|    7946 | 01:09                      |        69 |
|    7950 | 01:28                      |        88 |
|   12972 | 00:58                      |        58 |
|   12973 | 01:11                      |        71 |
|   12974 | 00:51                      |        51 |
+---------+----------------------------+-----------+
9542 rows in set, 389 warnings (0.21 sec)

SELECT SUM(TIME_TO_SEC(STR_TO_DATE(amount, "%i:%s"))) AS sekundeidkokku
   FROM efis_film_physical_description
  WHERE classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION";

+----------------+
| sekundeidkokku |
+----------------+
|        2440491 |
+----------------+

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(STR_TO_DATE(amount, "%i:%s")))) AS sekundeidkokku
   FROM efis_film_physical_description
  WHERE classificator_code2="FILM_PHYSICAL_TYPE_FILM_FILM_DURATION";
+----------------+
| sekundeidkokku |
+----------------+
| 677:54:51      |
+----------------+

-- Leidke, milline on kokku mängufilmide ajaline pikkus

Soovitavad alamülesanded

Tabelist efis_film_type_translation - title=mängufilm
Tabelist efis_film_type vastav kood
efis_film_type_rel abil leiab vastavad filmid
Võib kuvada pealkirjad (efis_film_translation)
Kontrolli, kas andmed on usutavad
Arvuta efis_film_physical_description abil kokku pikkused
 (võimalusel video+filmi+failikandjal)
 

 
Annika lahendus:

Mängufilmide andmete loetelu:

SELECT efis_film.id, efis_film_type_translation.title, efis_film_type.id, efis_film_physical_description.amount
   FROM efis_film_type_translation
   INNER JOIN efis_film_type 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_type_rel.film_id=efis_film.id
   INNER JOIN efis_film_physical_description ON efis_film.id=efis_film_physical_description.film_id
   WHERE efis_film_type_translation.title='Mängufilm'
   AND efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
   ORDER BY efis_film.id;
   

Lühemate filmide kogupikkus: 
 SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(STR_TO_DATE(efis_film_physical_description.amount, "%i:%s")))) AS kogupikkus
   FROM efis_film_type_translation
   INNER JOIN efis_film_type 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_type_rel.film_id=efis_film.id
   INNER JOIN efis_film_physical_description ON efis_film.id=efis_film_physical_description.film_id
   WHERE efis_film_type_translation.title='Mängufilm'
   AND efis_film_physical_description.classificator_code2='FILM_PHYSICAL_TYPE_FILM_FILM_DURATION'
   ORDER BY efis_film.id;
   
 Üle 60-minutilisi ei saa veel kätte, nendega tuleb eraldi tegelda.