-- Filmy
+------+--------------------------+------+
| ID   | Nazev                    | Rok  |
+------+--------------------------+------+
|    1 | Pulp Fiction             | 1994 |
|    3 | Inception                | 2010 |
|    4 | XXX (no Heath, no Bobby) | 1977 |
+------+--------------------------+------+

-- Herci
+------+--------------------+--------------+
| ID   | Jmeno              | Rok_narozeni |
+------+--------------------+--------------+
|    1 | John Travolta      |         1954 |
|    2 | Heath Ledger       |         1979 |
|    3 | Leonardo DiCaprio  |         1974 |
|    4 | Bobbie Milie Brown |         2012 |
+------+--------------------+--------------+


SELECT Filmy.ID, Filmy.Nazev, Filmy.Rok, Herci.Jmeno, Herci.Rok_narozeni
    FROM Filmy
    JOIN Herci ON Filmy.Rok < Herci.Rok_narozeni
    ORDER BY filmy.nazev, herci.Jmeno;
+------+--------------------------+------+--------------------+--------------+
| ID   | Nazev                    | Rok  | Jmeno              | Rok_narozeni |
+------+--------------------------+------+--------------------+--------------+
|    3 | Inception                | 2010 | Bobbie Milie Brown |         2012 |
|    1 | Pulp Fiction             | 1994 | Bobbie Milie Brown |         2012 |
|    2 | The Dark Knight          | 2008 | Bobbie Milie Brown |         2012 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Bobbie Milie Brown |         2012 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Heath Ledger       |         1979 |
+------+--------------------------+------+--------------------+--------------+

SELECT Filmy.ID, Filmy.Nazev, Filmy.Rok, Herci.Jmeno, Herci.Rok_narozeni
    FROM Filmy
    JOIN Herci ON Filmy.Rok > Herci.Rok_narozeni
    ORDER BY filmy.nazev, herci.Jmeno;
+------+--------------------------+------+-------------------+--------------+
| ID   | Nazev                    | Rok  | Jmeno             | Rok_narozeni |
+------+--------------------------+------+-------------------+--------------+
|    3 | Inception                | 2010 | Heath Ledger      |         1979 |
|    3 | Inception                | 2010 | John Travolta     |         1954 |
|    3 | Inception                | 2010 | Leonardo DiCaprio |         1974 |
|    1 | Pulp Fiction             | 1994 | Heath Ledger      |         1979 |
|    1 | Pulp Fiction             | 1994 | John Travolta     |         1954 |
|    1 | Pulp Fiction             | 1994 | Leonardo DiCaprio |         1974 |
|    2 | The Dark Knight          | 2008 | Heath Ledger      |         1979 |
|    2 | The Dark Knight          | 2008 | John Travolta     |         1954 |
|    2 | The Dark Knight          | 2008 | Leonardo DiCaprio |         1974 |
|    4 | XXX (no Heath, no Bobby) | 1977 | John Travolta     |         1954 |
|    4 | XXX (no Heath, no Bobby) | 1977 | Leonardo DiCaprio |         1974 |
+------+--------------------------+------+-------------------+--------------+


-- SELF JOIN
-- kolik filmu je dle roku pred nim 
 SELECT b.nazev, COUNT(A.ID)
    -> FROM filmy a right JOIN filmy b
    -> on a.ID <> b.ID
    -> AND a.Rok > b.Rok
    -> GROUP BY b.ID;
+--------------------------+-------------+
| nazev                    | COUNT(A.ID) |
+--------------------------+-------------+
| Pulp Fiction             |           2 |
| The Dark Knight          |           1 |
| Inception                |           0 |
| XXX (no Heath, no Bobby) |           3 |
+--------------------------+-------------+

-- nebo korelovany, vede ke stejnemu vysledku
SELECT *, (SELECT COUNT(*) FROM filmy WHERE rok > x.rok ) FROM filmy x