#HINTS:https://rua.ua.es/dspace/bitstream/10045/28357/11/T11_Conjuntos.pdf
#Esbrinar el títol i ladescripció d’aquelles pel·lícules que es van rodar en l’idioma original que té per nom (name) “English”.(FK -> filme.language_id)
#@
SELECT title,description
    FROM film as f
    INNER JOIN
    (select * from language where name = "English") as lang
    ON f.language_id = lang.language_id;

#$

# Esbrinar títol i descripció d’aquelles pel·lícules que pertanyen a la categoria “Sci-Fi”.
#@
SELECT
    title, description
FROM
    film
        INNER JOIN
    film_category AS fca ON fca.film_id = film.film_id
        INNER JOIN
    category AS ca ON ca.category_id = fca.category_id
WHERE
    ca.name = 'Sci-fi';

#$


# Esbrinar el ID, el nom i el cognom (last_name i first_name) dels actors que no han participat a cap pel·lícula,
# més aquells que han participat a menys de 20 pel·lícules.
#@
SELECT DISTINCT
    a.actor_id, first_name, last_name, COUNT(film_id)
FROM
    actor AS a
        LEFT JOIN
    film_actor AS fa ON fa.actor_id = a.actor_id
GROUP BY actor_id
HAVING COUNT(film_id) = 0
UNION SELECT
    a.actor_id, first_name, last_name, COUNT(film_id)
FROM
    actor AS a
        INNER JOIN
    film_actor AS fa ON fa.actor_id = a.actor_id
GROUP BY actor_id
HAVING COUNT(film_id) < 20;
#$
# Obtenir un llistat on aparegui els títols de les pel·lícules i el número de vegades que han estat llogades (rental) durant tot l’any 2005.
#@
SELECT DISTINCT
    f.film_id, f.title, rl.rental_id, COUNT(rental_id)
FROM
    film AS f
        INNER JOIN
    inventory AS inv ON inv.film_id = f.film_id
        INNER JOIN
    rental AS rl ON inv.inventory_id = rl.inventory_id
WHERE
    rental_date BETWEEN '2005-01-01' AND '2005-12-31'
GROUP BY film_id;
#$

#Obtenir un llistat on aparegui el id de la pel·lícula, el títol, el id del lloguer i els dies que s'ha llogat le pel·lícula (retrun-date - rental_date )
# de totes les pel·lícules que no estan llogades actualment.
#@
SELECT
    f.film_id,
    f.title,
    rental_id,
    DATEDIFF(return_date, rental_date)
FROM
    film AS f
        INNER JOIN
    inventory AS inv ON inv.film_id = f.film_id
        INNER JOIN
    rental AS rt ON inv.inventory_id = rt.inventory_id
WHERE
    rt.return_date IS NOT NULL;
#$

#Obtenir un llistat on aparegui almenys, el nom de l’actor (first_name) i la mitjana del temps de lloguer (retrun-date - rental_date )
# de totes les pel·lícules en las que han participat. ALERTA! Pot esser que dos actors diferents tenguin el mateix nom.
#@
SELECT
    a.first_name, AVG(DATEDIFF(rt.return_date, rt.rental_date))
FROM
    actor AS a
        INNER JOIN
    film_actor AS fa ON fa.actor_id = a.actor_id
        INNER JOIN
    film AS f ON f.film_id = fa.film_id
        INNER JOIN
    inventory AS inv ON inv.film_id = f.film_id
        INNER JOIN
    rental AS rt ON rt.inventory_id = inv.inventory_id
GROUP BY a.actor_id;
#$
#Esbrinar el títol i la descripció d’aquelles pel·lícules que estan únicament a l’estoc de les tendes que es troben a les adreces amb ID 3 i 4.
#https://www.techonthenet.com/mysql/intersect.php

#@
SELECT
    address_id, f.film_id, f.title, f.description
FROM
    film AS f
        INNER JOIN
    inventory AS inv ON f.film_id = inv.film_id
        INNER JOIN
    store AS st ON inv.store_id = st.store_id
WHERE
    address_id = 3
        AND f.film_id IN (SELECT
            f.film_id
        FROM
            film AS f
                INNER JOIN
            inventory AS inv ON f.film_id = inv.film_id
                INNER JOIN
            store AS st ON inv.store_id = st.store_id
        WHERE
            address_id = 4);
#$

#De totes les tendes que es troben al codi postal 07009 treure una llistat que contengui el ID de la tenda,el nom del client, el cognom del client,
#el correu electrònic del client i la quantitat total que han pagat en concepte de lloguer de pel·lícules,
#però només d’aquelles pel·lícules que durin 120 minuts o més (camp lenght).
#@
SELECT
    st.store_id,
    cu.first_name,
    cu.last_name,
    cu.email,
    SUM(pa.amount)
FROM
    store AS st
        INNER JOIN
    address AS ad ON ad.address_id = st.address_id
        AND ad.postal_code = 07009
        INNER JOIN
    inventory AS inv ON inv.store_id = st.store_id
        INNER JOIN
    film AS f ON f.film_id = inv.film_id
        AND f.length >= 120
        INNER JOIN
    rental AS rt ON rt.inventory_id = inv.inventory_id
        INNER JOIN
    payment AS pa ON pa.rental_id = rt.rental_id
        INNER JOIN
    customer AS cu ON cu.customer_id = rt.customer_id
GROUP BY cu.customer_id;
#$
#Mostrar els camps address i address2 de les tendes que almenys tenguin al seu estoc les pel·lícules amb títol:
# “DETECTIVE VISION” i “DEVIL DESIRE”. Si les tendes tenen més pel·lícules al seu estoc no hi ha problema, podran aparèixer al resultat.

#@
SELECT DISTINCT
    f.title, ad.address, ad.address2
FROM
    address AS ad
        INNER JOIN
    store AS st ON st.address_id = ad.address_id
        INNER JOIN
    inventory AS inv ON st.store_id = inv.store_id
        INNER JOIN
    film AS f ON f.film_id = inv.film_id
WHERE
    f.title = 'DETECTIVE VISION'
        AND st.store_id IN (SELECT
            st.store_id
        FROM
            address AS ad
                INNER JOIN
            store AS st ON st.address_id = ad.address_id
                INNER JOIN
            inventory AS inv ON st.store_id = inv.store_id
                INNER JOIN
            film AS f ON f.film_id = inv.film_id
        WHERE
            f.title = 'DEVIL DESIRE');
#$
/* RUBRICA:
+---------------------------------------------------------+-----+
|                        Concepte                         | Pes |
+---------------------------------------------------------+-----+
| El resultat que dóna l'execució de la query és correcte | 50% |
| El codi es homogeni (estandard)                         | 20% |
| S'ha fet la query de la forma més òpitma                | 30% |
+---------------------------------------------------------+-----+
*/