I see some of the same problems as in your last query
Do not use single-letter aliases
c1... How about an alias that helps you write the query, instead of one that saves a few characters? That's what they are for, after all.
I think you would benefit from reading about explicit
JOIN syntax instead of using the pre-ANSI-92 syntax.
Vertical white space
I personally find queries much easier to read if you use line breaks between lists of columns/values/conditions, instead of writing them inline.
It's good practice to rename short/ambiguous/ugly column names to something more human-friendly while presenting the result set. To you it may not matter much, but if you were presenting this report to your boss, they may scratch their head at
lname. The syntax for column aliases is the same as table aliases.
BETWEEN is ambiguous. You should instead use logical operators.
This is about Microsoft T-SQL, but some problems apply pretty much across the board in SQL.
What do BETWEEN and the devil have in common?
I'll make no bones about it: BETWEEN is evil. For one, the meaning of the word in English does not always match the meaning of the operator in T-SQL. In T-SQL, BETWEEN is an inclusive range - not everyone gets that. Sure, in casual conversation when someone says "between 3 and 6" the answer really could be 3, 4, 5 or 6; but other times, they really mean to restrict the set to only 4 or 5 (an exclusive range).
The reviewed script
Actor.fname AS ActorFirstName,
Actor.lname AS ActorLastName,
Actor -- look no alias needed
INNER JOIN Cast AS OlderCast ON OlderCast.pid = Actor.id
INNER JOIN Movie AS OlderMovie ON OlderCast.mid = OlderMovie.id
INNER JOIN Cast AS NewerCast ON NewerCast.pid = Actor.id
INNER JOIN Movie AS NewerMovie ON NewerCast.mid = NewerMovie.id
AND OlderMovie.year >= 1850
AND OlderMovie.year <= 1900
AND NewerMovie.year >= 1901
AND NewerMovie.year <= 1950;