One of the key differences between the factory intake and any aftermarket mods is the removal of the sound baffling system. It keeps internal engine noises from coming out the intakes, because most everyday consumers find them objectionable or worrying. So with e.g. the Gen3 or whatever, you will hear more ticking and tapping and whatnot than you did before, and if you're the paranoid type you might think you have a sticking lifter or something like that. It's just fuel injector noise and general engine noise, louder than before. In any case all of that's mostly at idle.
At speed, they just sound, uh, meaner and louder. Youtube could help you out, although probably most people who install them also have aftermarket exhausts.
Nissan 370Z Stillen Gen 3 Intake - YouTube
Nissan 370Z Stillen Gen3 Intake@Young-Motors - YouTube
370Z Fast Intentions LTH/CBE/GEN3 Dyno - YouTube
(Note that in a dyno video like the 3rd link above, the very high-pitched whine is from the dyno itself).
As for in-cabin, that's harder. I can offer you some track vids I took, but it's hard to separate which parts of what you're hearing are the headers+exhaust vs the intakes, and they're all with the windows down too. Here's a jump into the middle of one just before a ~4K->redline run in 3rd:
MSR Houston CCW - TDE 20120421 - 370Z - YouTube