Difference between various contributor roles available in Azure and how to use it effectively?

There are different type of contributor roles in Azure. If I'm giving "contributor" role at resource group level, and that resouce group has SQL Server, Storage and other resources as well.

Is it that, someone having "contributor" role to the resource group level can perform activities same as "storage account contributor"( for the given perticuler storage) and "SQL Server contributor"( for the given perticuler SQL server ) in that perticuler resource group? So that, I don't have to give any other contributor roles? My intention is here not to duplicate the roles and I can just give "contributor" role at resouce group level once and people can perform other available contributor tasks ( on storage or on SQL server) using main "contributor" role given.

Thanks.

Yes, you are on the right path. Assigning Contributor role at the resource group level can perform activities same as "Storage account Contributor"( for the given particular storage) and "SQL Server contributor"( for the given particular SQL server ) in that particular resource group.

Please note that, Contributor role at resource group can give access to manage any resource whereas Storage Account Contributor can give access to manage the specific resource (storage accounts).

I tested the same scenario in my environment and got the below results:

I assigned only Contributor role to a user at resource group level like below:

The user is able to create storage account successfully like below without having Storage Account Contributor role (having only Contributor role).

For more information, please refer below links:

Can Contributor role in Azure see the data of resources like DB, DWH & blobs? - Stack Overflow

What is the difference between a Contributor role and Storage Account Contributor role in Azure AD? - Stack Overflow

Classic subscription administrator roles, Azure roles, and Azure AD roles | Microsoft Docs