According to ESRB, this game contains: Animated Blood & Gore, Animated Violence
Parents (and perverts) should definitely turn their radars on for this one because not only is this typically-violent fare, but this is probably one of the more racy games (containing much sexual innuendo and many provocative images of women) to ever grace a Nintendo system. The game feels like it was delayed way too long and as a result seems out of date in every way possible.
People looking for a Zelda or Shadow Man-type adventure with Duke Nukem at the helm will be sorely disappointed as Zero Hour plays more like an FPS.
Groups may find some value in the multiplayer fragfests that uses the first-person view all-around, but none of the modes particularly stand out and older games like GoldenEye still play about a hundred times better.