What I'm asking is, would it be possible to make something like this in Inkscape
Possible yes. Probable no. And I have done photorealistic (simple) objects with Inkscape.
You can see what the Inkscape stuff looks like here. As you can see, the more intricate, the more time, the less realistic.
And if not, what type of programs were used to make a piece of art like that?
You seem to be under the very large misunderstanding any software "makes" illustrations like that. What it looks like to me, and I have worked in everything from watercolor to oil, is that might have been done with an arcrylic on some type of board or canvas. That would be a sketch, then physical paint ...applied with a brush. ...by hand. ...by an illustrator with artistic talent and training.
I've done something not unlike Norman Rockwell, drawing, scanning, then finishing it in PhotoShop.
You can see that by
clicking this link. So could I do something like that? Yes. I would not use Inkscape however.
Would I do that? I think that is best left unexplored, based on what I can just smell as this conversation going. And no, there is no "Draw Like Norman Rockwell" in the dropdowns of PhotoShop.