Compared to how you use SQL in other DBMS, what you can do with ExecuteSQL will be very limited.
A ? is the Exedrine Headache #1 result that you get with a syntax error.
The obvious one that I can spot is that the qualified reference of Table::FieldName you used is correct syntax for a FileMaker calculation, but not a SQL query. It would be Table.Fieldname instead of Table::fieldName
But you could very easily get this value without any SQL at all.
Assuming this relationship:
Genres::GenreID = Movies::GenreID
You can open Manage | Database | Relationships, double click the relationship line and specify a sort order for Movies that sorts the related records by Rating in descending order.
Then you can go the Genres layout and add any fields from Movies that you want and you will see the Movie of that Genre that has the highest rating.
You could also leave your relationship unmodified and put a one row portal to Movies on your Genre layout and specify a sort order for the portal that lists the highest rated movie first and you'd get the same result.
You should use a "." not a "::". So your formula should be something like:
SELECT M.MovieName FROM Movies M INNER JOIN Genres G ON M.Genreid = G.GenreID ... etc
The "::" notation is only used in FileMakers calculation engine to identify specific fields. The format is TableName::FieldName. When you are working with the ExecuteSQL function anything in the first set of quotes is evaluated by the SQL engine and not the calculation engine. The main difference is it does not get any error checking. Items in the outer set of quotes are evaluated by the calculation engine so they need to follow Filemakers naming conventions.