I see some of the same problems as in your last query
Do not use single-letter aliases
a, m1, 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.
Old style JOIN
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.
Column aliases
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 fname and lname. The syntax for column aliases is the same as table aliases.
That Devil BETWEEN
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#
The reviewed script
SELECT DISTINCT
Actor.fname AS ActorFirstName,
Actor.lname AS ActorLastName,
FROM
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
WHERE
AND OlderMovie.year >= 1850
AND OlderMovie.year <= 1900
AND NewerMovie.year >= 1901
AND NewerMovie.year <= 1950;