do you want to learn about creating database applications with c# or with sql server compact edition? the second is related to mobile applications. So dont use that compact keyword there not to cause confusion. there are very good video tutorials in www.asp.net/learn.
be patient and watch as many videos as you can, you will understand what you need to learn for further once you get an idea of the whole picture. also go and watch the videos in www.windowsclient.net afterwards.

Well, first off, my understanding of SQL compact is that it's like sqlite.. embeddable into software so I don't have to have a server running on whatever machine I wanna run the software. Is that what it is, or am I mistaken?

sql compact is the sql server for devices like pocket pcs, you are mistaken. do what i suggest go and watch some video tutorials.
and also you can download sql server express edition to you pc for free. you can not use sql server without a server. you can use ms access for that reason.

No, Serkan it also used as they said in desktop application SQL Server Compact 3.5 is a free, easy-to-use embedded database engine that lets developers build robust Windows Desktop and mobile applications that run on all Windows platforms including Windows XP, Vista, Pocket PC, and Smartphone. read more

You're both right everyone has another piece of information.

actually i havent updated my skills to framework 3.5, as of 2.0 i dont remember such a tool. but thanks, i didnt know that.
geniouses also make mistakes