Who is Kamek?
A high ranking Magikoopa and advisor to Bowser who first appeared in Yoshi's Island. He looks like this:

When did Kamek first appear?
His first named appearance was in Yoshi's Island for the SNES, where it was he who attacked the stork sending Baby Mario and Luigi to their house and causing the events of the game to occur. However, his attacks may have took basis from the unnamed Magikoopas, sometimes referred to as Kamek found in Super Mario World which transformed blocks to enemies and objects.
What's with his name?
Kamek's name in Japan is the name for the Magikoopa species as a whole, so Kamek in Japan could refer to any magikoopa rather than him specifically, possibly explaining why one of his possible appearances was called 'Giant Kamek' and referred to as a 'Kamek made giant by magic'.
What his rank in Bowser's army?
Unknown, but from the events of Yoshi's Island and various other games, high and a likely advisor to Bowser. This is also backed by the Bowser's Tail novel found at the DS Ultimate forums, and his appearances in various Mario spinoff media.
What enemies does he transform into bosses?
In Yoshi's Island:
In Yoshi's Island DS
He can also obviously do other things, like give enemies and monsters various powers or even activate various robots via magic, which is interesting.
Is Psycho Kamek that Kamek?
Likely not, because after said game, Kamek was still helping Bowser cause complete mayhem, so the idea of him being old and somewhat senile kinda got discredited.
What age is Kamek?
No idea, although I hear from a reliable source that turtles and such like do not show visible signs of aging, so his age is always going to be a complete guess.
Any other skills?
His appearances in Mario Party Advance dictate he has some decent expertise at game playing, as he's the Game Mage in said game and the master of games. He also, in various spinoffs and Yoshi's Island DS is shown to have some knowledge of technology and mechanics.
Random other information
Kamek has a likely academic background as shown in Mario Party DS.
He is at this moment in time the longest serving lead Magikoopa to Bowser, and likely one of the longest serving characters in the Koopa Troop altogether.
Bowser's lead magikoopa minion in Super Mario Galaxy, Kamella is a Magikoopa with a purple robe and a star wand which uses the power of a star to fire green shells and fire at the heroes. She is found in the Space Junk and Deep Dark Galaxy.
Bowser's other koopa assistant, this time from the Paper Mario series, she has appeared in Paper Mario and Paper Mario the Thousand Year Door. In these games though, while she seems to perform the same duties as Kamek, she is also a boss character which Mario has actually battled, and one who actually seized the Star Rod in the beginning. She does not appear in Super Paper Mario for some reason.
An Egypt pharoah type Magikoopa boss found in Paper Mario, he is the guardian of Dry Dry Ruins and the master of Chain Chomps. He never appeared after this, but he does resemble a very similar boss from Mario Pinball Land.
An evil Magikoopa who claimed to own the Forest of Illusion in the Super Mario World show, he captured Mario, Luigi and Princess Peach in his basement and was based in a strange haunted house in the forest. He was eventually defeated and as with most one time villains, never seen again.
Found in Ghosts 'R' Us episode.
What console is it for?
Yoshi's Island was released for the SNES in 1995, and it was then re released quite recently for the GBA as Super Mario Advance 3 in 2002.
What is the story?
Basically, the stork is taking Baby Mario and Luigi to their parents, Kamek attacks the stork and captures Baby Luigi, Baby Mario ends up on Yoshi's Island and with the help of the Yoshis goes on an adventure to rescue his brother. And all this time, the Toadies sent by Kamek are trying to capture him.
General Gameplay?
Yoshi's Island is a 2D platform game with gameplay generally similar to the Super Mario Bros/World series. However, Yoshi can eat enemies and make them into eggs to use as projectiles, and you unlock certain things by collecting all the red coins, flowers and stars in levels. Oh, and there's a timer which you mustn't let get to zero which decreases when you hit an enemy and lose Baby Mario; if it gets to zero as Baby Mario is floating around, he gets captured by the Toadies and you lose a life.
Any sequels?
Yoshi's Story and Yoshi's Island DS are both sequels to this game, and generally have similar gameplay.