You will always get Magikarps when fishing with the Old Rod. Upgrade to a Good Rod in Falonia City.
Is there any special/rare Pokémon you can get with the super rod only?
Do you talk to the same guy to get the super rod?
No you buy at a place called the trainer tower in runko islands far later in the game