I'll highlight a conceptual information on unit tests first --- skip to last paragraph for the answer
Unit tests are written to confirm the behaviour of an atomic logical task within its service boundary. Any external supporting systems that comes outside the boundary is usally mocked. Hence we mock the database, third party IO services because they're external systems.
The above statement doesn't mean we should stay cool because all unit tests worked. It's like saying my car engine, wheels, steering and headlights work fine independently so I'm sure car should run. No. You need to "assemble" them and then run the assembled car to know if it actually runs. You need to wire unit tests together along with necessary external systems which is what people call Integration Tests.
Now enough of concepts.
Coming to one of your usecases creating a user; you may call it as a logic task but I would call it as persisting in an "external" datastore so I wouldn't write a unit test for that case unless there is a logic involved along with db persist, instead I would an integration test without mocking database. Sometimes there will be a validation logic involved like you said "O'bama" for which I would fit it in integration test otherwise it would be an overkill to write unit test only to test validation logic with mocked DB.
This is how I follow the testing approach and there can be better approaches too.