What DOESN'T it do?
How much you can fit depends on the Memory Stick Pro Duo you put in it. I bought a 512 MB stick for $65 from B&H. That's how I was able to fit Star Wars on it. There are 1GB sticks out there but VERY hard to find for roughly $99-$129 (and higher).
It will play MP3s and ATRAC files. Haven't tried WAV yet.
It will play MP4 movies that are encoded in a specific way. You can use ffmpegX (Mac) or 3GP Converter (Windows) to convert them.
It will play JPEGs in the Photo directory. You can organize them with folders.
Now, under the Game menu, you do have the option of running a game off the memory stick because the options are:
So the memory stick is a medium that you're able to run a game from.
You can apparently play games over the internet wirelessly. Gamespot.com lists 5 games having this capability.
THe two games I have - Lumiens and Ridge Racer - have wireless Ad Hoc head-to-head play. I haven't tried it yet since nobody else I know has a PSP.
The top of the unit has a USB mini port like you have on digital cameras. You can connect it to your Mac or PC and mount the memory stick directly that way.