Devices connected to different SSIDs on the same network are going to be able to talk to each other.It's been a while...
Indeed.
I have ethernet running to most places in the house.
Right now, I've got an old WRT access point (DD-WRT) in the living room. It's just good enough, but only 2.4GHz and only one channel - set to mirror the guest network on the router. The problem is, if you connect to that, it will happily let you connect to my server (assume the server likes your credentials).
I'm not an IT pro, but know my way around linux and such. I just have no idea what's available today, product-wise.
Now we are back to how much do you want to spend?
To do what you want with the guest network you will need something that can support VLANs and has some firewall abilities.
Another option could be to create two networks with devices that both NAT traffic going to your router.
Either option is going to be more complicated than you are used to and more expensive.