The most common language used with cgi is perl (Pratical Extraction and Resource Language), but i think you can use any programming language,
as long as you have the right compiler on the server(don't quote me on that).
unless the server is really badly configured you shouldn't be able to see whats in the cgi-bin directory.

you call cgi scripts using a HTML eg;

href="cgi-bin/subdir/someperlscript.pl

If you are just looking for afew cool afects, you should try messing with javascript(its alot easier to set up than server-side scripting!)