SQL/ORACLE query excercise 18c

user_13165850 0
Viewed 43

Display, in alphabetical order, the actors who have played at least one film per year between 1980 and 1985

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

I'm not sure about this solution, can someone check them?

SELECT *
FROM ACTOR
WHERE CODACTOR IN (
SELECT CODACTOR
FROM FILM NATURAL JOIN CAST
WHERE YEAR = 1980 AND YEAR = 1981 AND YEAR = 1982 AND YEAR = 1983 AND YEAR = 1984 AND YEAR = 1985)
ORDER BY COGNOME;

Another solution that I thought was (changing WHERE in the subquery):

WHERE YEAR BETWEEN 1980 AND 1985
oracle sql
1 Answers

You must join all the tables, group by actor and set the condition in the having clause:

SELECT a.CODACTOR, a.SURNAME, a.NAME
FROM ACTOR a 
INNER JOIN CAST c ON c.CODACTOR = a.CODACTOR
INNER JOIN FILM f ON f.CODFILM = c.CODFILM
WHERE f.YEAR BETWEEN 1980 AND 1985
GROUP BY a.CODACTOR, a.NAME, a.SURNAME
HAVING COUNT(DISTINCT f.YEAR) = 1985 - 1980 + 1
ORDER BY a.SURNAME, a.NAME
user_104988280
Related