Well, I use Adobe Premiere. It runs about $550-$600 or so retail, but you can often find it packaged with video cards or whatnot for cheaper (or if you're a student). Pinnacle Systems sells a DV card that comes with Premiere 6.0 for right at $300 last I checked. It's a full-on program that has many pro-level features, and it merges easily with other Adobe products such as Photoshop, After Effects, etc. Speaking of Pinnacle Systems, I've used their entry-level video editing software. I think it's called StudioDV 7, and it's pretty good for basic capture and edit. You can also add sound effects or put on a music bed and add titles and simple things like that. And it's relatively cheap (under $100). Those are the only two I've used. I also know some people who have had good things to say about Vegas Video (I think that's what it's called).
The only ones I've used, that have any type of quality what so ever are Adobe Premier @$500 coupled with After-Effects @ $600 new. That's about as nice as I've seen short of dedicated hardware-intigrated solutions. They have the same econony of design and scalibility of power as you'd find in Photoshop. Needless to say, your much better off locating a student version, if you qualify, or a used 'qualifying product' and the upgrades.
I edited a home movie with studio dv from pinnacle. Was about $99 with the video capture card and firewire. I was very satisfied with it. I have not used any others, though.
For a little less, Apple makes Final Cut Pro, which, I've heard, is very good. Premier is the cream of the crop though for most.
When I bought my Pyro Firewire card, I got Ulead VideoStudio 4.0 thrown in. It's basic and a bit clumsy but does the job great. I have used it to make home videos (MiniDV) into VCDs that play on my DVD player. I would love to more video editing, but its all time baby...
when i was in high school, we had two kind of editors. the linear ones, and some mac kind. i forgot which kind. we also have some casio avio blanco thing, whatever that is, but i hear it's good and stuff. maybe somebody know what it is and can say it right.
Figure out what the earlest version of Premier qualifies for an upgrade. For instance, perhaps if you have a copy of Premier 3.0 you can use the upgrade, but if you have 2.9 you can't use the upgrade. Once you figure this out, go to a used software store, or perhaps get a copy from EBay with the license, for perhaps $35 dollars. You can then buy the upgrade for $149. Total cost of ownership: $184, vs. the $550 they charge for a new copy, and it's perfectly legal, as opposed to the 'borrow from Morpheus' method mentioned earlier. DaD - I thought all of Avid's solutions were designed to work with integrated Avid cards? If we're talking about integrated solutions, I saw some really cool $5000 boxes as ComDex 3 years ago designed for real-time broadcast TV that were great, but, again, the rub is the hardware integration.
I know that cheapest Avid DV program (that's like $1500) works with other manufacturer's cards but requires a hardware dongle to work (though I've seen work-arounds).