What is the purpose of having SpriteNode class when it basically has the same source code as all other classes?
As far as I can tell the class is used to draw some simple sprites at a fixed location. What else would you want to use?
Why does it seem like there is numerous unnecessary explicits and virtual functions that could be avoided?
explicit keywords are used for clean code/good code design, since instantiating classes implicitly will often lead to hard to understand code.
Virtual functions are part of the polymorphic entity system. How would you "avoid" them?
It may have been explained in the book, why is there numerous "Holder" classes that do what appears to be the same thing?
Because you have to manage different resources. The simplest approach is to instantiate a class for each resource. Other design are possible, but it would complicate the code too much and distract from teaching the concepts to the reader.