To help you better understand the basics of presentation architecture, take a look at a Very Simple Web Site documents on the Sitecore Developer Network. Also review the Fundamental Concepts document.
Essentially, you can have your style sheets wherever you want (i.e. CSS folder), but you'll usually find reference to style sheets in a Sitecore Layout. Often, developers will use a presentation component to retrieve the file reference from a field that stores the CSS file path in a Sitecore Item. A Sitecore Layout is an item of type layout in the content tree and an .ASPX file.
Make sure you know how Sitecore references media stored in the media library for CSS. Also look for forum posts on how to handle CSS references for Rich Text Fields.
Depending on your deployment systems architecture, there are other considerations for working with style sheets. One is publishing. If you publish from an authoring environment to a live website environment, you’ll want to make sure you copy your CSS files and any other file system references (like media NOT in the media library). This can be accomplished through the staging module. If your using Sitecore CMS 6.3, there are newer approaches.