The repository is not a gateway to access Database. It is an abstraction that allow you to store and load domain objects from some form of persistence store. (Database, Cache or even plain Collection). It take or return the domain objects instead of its internal field, hence it is an object oriented interface.
It is not recommended to add some methods like GetAllProductsByCategoryId or GetProductByName to the repository, because you will add more and more methods the repository as your use case/ object field count increase. Instead it is better to have a query method on the repository which takes a Specification. You can pass different implementations of the Specification to retrieve the products.
Overall, the goal of repository pattern is to create a storage abstraction that does not require changes when the use cases changes. This article talks about the Repository pattern in domain modelling in great detail. You may be interested.
For the second question: If I see a ProductRepository in the code, I’d expect that it returns me a list of Product. I also expect that each of the Product instance is complete. For example, if Product has a reference to ProductDetail object, I’d expect that Product.getDetail() returns me a ProductDetail instance rather than null. Maybe the implementation of the repository load ProductDetail together with Product, maybe the getDetail() method invoke ProductDetailRepository on the fly. I don’t really care as a user of the repository. It is also possible that the Product only returns a ProductDetail id when I call getDetail(). It is perfect fine from the repository’s contract point of view. However it complicates my client code and forces me to call ProductDetailRepository myself.
By the way, I’ve seen many service classes that solely wrap the repository classes in my past. I think it is an anti-pattern. It is better to have the callers of the services to use the repositories directly.