to study different patterns and trends from various available public databases [related to drug discovery] , which software serves most of the needs for visualizing using different methods.
I guess that very much depends on the type of analysis you want to carry out. To be able to handle a large number of compounds platforms like Pipeline Pilot or Knime are essential (see links).
From a visualisation point of view you might be interested in Scaffold Hunter (http://scaffoldhunter.sourceforge.net/).
And if you are into large chemical space you might find this also interesting: