SQL Query Exercise Oracle 18c

I’m trying to resolve this exercises:

Visualize the actors who have had more than twice the compensation of the media of their compatriots.

• FILM (CODFILM,TITLE,YEAR,GENRE) –Year is NUMBER, it contains only the Year
• ACTOR (CODACTOR,NAME,SURNAME,NAZIONALITY)
• CAST (CODFILM,CODACTOR, COMPENSATION).

I’ve tried this solution, but it doesn’t works

SELECT CODACTOR
FROM ACTOR NATURAL JOIN CAST T
WHERE COMPENSATION >(
SELECT AVG(COMPENSATION) * 2
FROM ACTOR NATURAL JOIN CAST
WHERE NAZIONALITY = T.NAZIONALITY)

Select, for each actor, the year in which he / she played the first and last films by viewing the results in descending order based on the latter value.

SELECT CODACTOR, MIN(YEAR), MAX(YEAR)
FROM FILM NATURAL JOIN CAST T
GROUP BY CODFILM, CODACTOR
HAVING CODACTOR IN (
SELECT CODACTOR
FROM CAST
WHERE CODFILM = T.CODFILM)

This last exercise I don’t know how to resolve because I don’t have Date Of Birth column.

Visualize the roles in which French actors born before 1990 have never played. (Suppose that exist the column BIRTH, type DATE.)

Answer

Please try the below for your first query

    SELECT A.codactor
    FROM   actor A
    inner join cast C
    ON A.codactor = C.codactor
    WHERE  compensation > (SELECT trunc(Avg(compensation)) * 2
                   FROM   actor A1
                          inner join cast C1
                                  ON A1.codactor = C1.codactor
                   WHERE  A1.nazionality = A.nazionality

                              )

For your second query please try the below

  WITH films
 AS (SELECT c.codfilm,
            c.codactor,
            F.title,
            F.year,
            F.genre
     FROM   film F
            inner join cast c
                    ON c.codfilm = F.codfilm)
   SELECT A.codactor,
   Min(F.year) FIRST_YEAR,
   Max(F.year) LAST_YEAR
          FROM   actor A
          inner join films F
           ON A.codactor = F.codactor
           GROUP  BY A.codactor