As the most fully-featured non-commercial chess program, SCID is an essential download for anyone with more than a passing interest in chess software. It is primarily a very good and very fast database program, with similar functionality to Chessbase, but it can also be used for play and training. The original program hasn't been developed by Shane Hudson since 2004; however, Pascal Georges continues development.http://scid.sourceforge.net/
A good place to start exploring, once you've opened your database, is the Tournament Finder. This is an extremely powerful piece of kit, and better than the equivalent in Chessbase or Chess Assistant! You can filter the tournaments by Player (e.g. Topalov), Date (e.g. 2006), Average ELO rating (e.g. 2600), Country (e.g. ENG), Site (e.g. Linares), and Event, giving you an almost instantaneous overview of any information you could desire.
Right-click on a tournament to display the tournament Crosstable. There, click on a result to load or browse a game. Click on a player's name to display Player Information, from which you can display a Rating Graph or create a Player Report. Another way to reach Player Information is via the Player Finder.
Open the Tree Window for database statistics on the board position, with button access to the Best Tree Games List and a graphical representation in the Tree Graph. The Statistics Window gives a breakdown by rating and year. Or navigate using the ECO Browser and then create an Opening Report.
And that's just for starters! There's also a Piece Tracker, a Repertoire Editor; Endgame Tablebase, Opening Book and Correspondence Chess windows; play on the internet or against an engine; and three training modes. Not to mention comprehensive database maintenance tools. There's probably other stuff, too!
Perhaps the best large free quality database is Million Base 1.74, which comprises the formerly commercial Rebel database brought up to date with The Week in Chess. For a more selective database (of about 575,000 games), Norm Pollock's PGNs are a good choice. For recent games, as well as TWIC, ChessOK provide a free weekly database for download. Another place to look for databases is on Lars Balzer's Chess Games Links.
Hints and Tips
Timeseal, which allows the clocks to be adjusted for lag when playing on FICS, can be downloaded here.
You can give the developers suggestions and report bugs on the scid users forum.