For sites that are running in CKAN using the datastore plugin on CSV/tabular resources, there is essentially a sql endpoint to make queries against that data. There are also automated conversions of that data to csv/json/xml. In my experience though, there's not a lot of activity around those apis -- people tend to just download the data.
(CKAN is an open source, traditional web app style (flask/jinja/postgresql) open data portal that powers a bunch of open data portals, including data.gov.ie)
I do think that SQLite is a really interesting format for publishing data, and I'd love to see more places publish raw SQLite files. It's much better at preserving things like column type information and relationships between tables than CSV is.