I wouldn't put Hadoop in the same camp as NoSQL technologies - for the most part NoSQL technologies tend to be real-time, versus Hadoop, which is batch-based, and excels at ETL, DW type use cases. In terms of which NoSQL solution to pick that's a touch choice as there doesn't seem to be a clear winner in the marketplace at the moment. Having said that Cassandra, MongoDB and HBase have distinctive traits which will likely push you to one of them based on how you intend to access your data. I'm not an expert on these systems so I won't attempt to push one over the other, but after you do some research I think it'll become apparent which one will work best for you.
Relational systems are quite different from Hadoop, and not only from an the real-time/batch perspective. Hadoop isn't a transactional system, but it was architected from the ground-up to scale, so you can typically work with much larger data sets than you can with monolithic database systems. Hadoop is also great at joining structured and unstructured data together, and for data aggregations and summarizations. You can also use tools like Mahout for predictive analytics.
Hope this helps some of your questions.