Yes it can. Currently there are no real "DSi only" games so the games are interchangeable between systems. The difference between the two systems is that the DSi can download dsiware from the internet so that you can store games on the console.
Another difference is that the Ds lite has a slot for Game Boy Advance games, which, if you're new to the system probably won't concern you since you probably don't have a collection of those older games.
But yes, to answer your question in a nutshell, all "ds" cartridges work in the DSi system.
You cannot use the Guitar Hero or Band Hero games. I have a DSi as well, and since there is no GBA slot there is no place to fit the attachment. They may yet develop one for the DSi, but for now that's all you can't play and I'd say it's worth getting even though you miss out on those games.
No, all DS games absolutely do NOT work with the DSi. They must be DSi compatible. I only know this because I have several games I've purchased that DO NOT work on our DSi, but DO work on our DS lite. That is definitely NOT what consumers are led to believe. I just got off the phone with Nintendo support and was told that you need to ask the store clerk if the game is compatible or they have the games listed on their (Nintendo) site. I'm so frustrated!!