There are three 50-turn multiplayer scenarios in the base game -- Ancient Rivals (land-grab/culture game set in ancient era), Hallowed Ground (religious warfare in the medieval era), and Global Thermonuclear War (what you think it would be).
Although accessed through the Multiplayer menu, if you want to play them in SP mode, you should be able to set them up as hotseat MP games where there is only a single human player (you) and the rest are AI players.