BUG: `read_sql_table` can't find views
closes #10750 

Add ability to 'read_sql_table' to read views and implement an unit test to check its behaviour

Looks good!
- Can you add a small note in the whatsnew file (in the 'other enhancements' section)
- You included the test in `_TestSQLApi` which only tests the API with sqlite. I think this is OK (I don't see potential differences between database flavours), but in that case the sql code for the different flavors is not needed.
 @IamGianluca for future reference, it is not needed to open a new PR if something went wrong. You can always rebase the current branch and force push again.
