Kuva tabeli if16.efis_film_keyword sisu
SELECT * FROM efis_film_keyword

Kuva esimese saja filmi (id<=100) film_id, keyword_id
SELECT film_id, keyword_id FROM efis_film_keyword WHERE film_id<=100


Ühenda (join) efis_film_keyword omaenese koopiaga, nii et ühendavaks tulbaks on film_id
  Kuva tekkinud märksõnakoodide paarid esimese saja filmi juures

SELECT tabel1.keyword_id AS k1, tabel1.film_id, tabel2.keyword_id AS k2 
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  AND tabel1.film_id=1;

SELECT tabel1.keyword_id AS k1, tabel1.film_id, tabel2.keyword_id AS k2 
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  AND tabel1.film_id=1 AND tabel1.keyword_id<tabel2.keyword_id;


SELECT tabel1.keyword_id AS k1, tabel1.film_id, tabel2.keyword_id AS k2 
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  AND tabel1.film_id<=100 AND tabel1.keyword_id<tabel2.keyword_id;

SELECT tabel1.keyword_id AS k1, tabel2.keyword_id AS k2, COUNT(*) AS kogus
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  AND tabel1.film_id<=100 AND tabel1.keyword_id<tabel2.keyword_id
  GROUP BY tabel1.keyword_id, tabel2.keyword_id
  HAVING kogus>1
  ORDER BY kogus DESC;


SELECT distinct production_year FROM efis_film WHERE production_year<1940

SELECT tabel1.keyword_id AS k1, tabel1.film_id, tabel2.keyword_id AS k2 
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  JOIN efis_film ON tabel1.film_id=efis_film.id
  AND efis_film.production_year<1940 AND tabel1.keyword_id<tabel2.keyword_id;

SELECT tabel1.keyword_id AS k1, tabel2.keyword_id AS k2, 
   COUNT(*) as kogus, GROUP_CONCAT(production_year) AS aastad
  FROM efis_film_keyword AS tabel1
  JOIN efis_film_keyword AS tabel2 ON tabel1.film_id=tabel2.film_id
  JOIN efis_film ON tabel1.film_id=efis_film.id
  AND efis_film.production_year<1940 AND tabel1.keyword_id<tabel2.keyword_id
  GROUP BY k1, k2
  HAVING kogus>2
  ORDER BY kogus DESC;



* Tutvuge tabelitega efis_keyword ning efis_keyword_translation
* Harjutage tabelite sidumist ning kuvage filmi nr 1 võtmesõnad eestikeelsetena

* Kuvage sõjaeelsete filmide eestikeelsed võtmesõnade paarid sageduse järjekorras


SELECT table1.film_id, table3.value, table4.value,
COUNT(*) AS amount FROM efis_film_keyword AS table1
 
JOIN efis_film_keyword AS table2 ON table1.film_id = table2.film_id
JOIN efis_film ON table1.film_id = efis_film.id
JOIN efis_keyword_translation AS table3 ON table1.keyword_id=table3.keyword_id
JOIN efis_keyword_translation AS table4 ON table2.keyword_id=table4.keyword_id
AND table1.keyword_id < table2.keyword_id
AND efis_film.production_year < 1940
AND table3.language = 'et'
AND table4.language = 'et'
GROUP BY table3.value, table4.value
ORDER BY amount DESC


library(RMySQL)
yhendus<-dbConnect(dbDriver("MySQL"), user="root",
        password="root", host="localhost", dbname='film')
paring<-dbSendQuery(yhendus, 
          "SELECT id, production_year FROM efis_film")
filmiandmed=fetch(paring, n=-1)
head(filmiandmed)
filmiandmed %>% group_by(production_year) %>% 
    summarise(kogus=n()) %>% arrange(desc(kogus))