How do I make an item to make a sound once it equipped in main hand or off-hand?

Try using the "Play sound" block in the "While item is in inventory ticks" procedure, where you monitor the presence of the item in your main hand and offhand using an NBT variable.

When you're holding the item and the variable is false, play the sound and set the variable to true. When you're no longer holding the item, set the variable to false.

NBT variables don't require extensive knowledge: their operation is fundamental to programming.

Tutorial from NorthWestTrees

MCreator explication

JAVA tutorial (easy)