https://online.stanford.edu/courses/soe-ydatabases-databases
Otherwise SQL is best learned by solving problems and googling when you’re stuck.
Most developers only need a few SQL constructs like SELECT, WHERE, WITH, JOIN, INSERT, CREATE and GROUP BY. Most devs learn and forget because they don’t use SQL very often. It’s too bad because the relational data structure (or more generally the table) is a super useful abstraction and often much more powerful than lists and dicts.
Data analysts need a few more like OVER (PARTITION BY) and maybe PIVOT/UNPIVOT.
Data engineers of course need many more specifically those related to database object manipulation, and stuff around performance like INDEX.
Really advanced SQL experts do stuff like this.
Just more generally, do you have some mental model when you move from using something like a dataframe to using SQL?
For instance people working in R rarely talk about using an SQL database - even when they have huge datasets. And I guess I don't really see when you should be thinking about making the switch over.
The only thing I see distinct about databases is it's always hand in hand with web-tech - and it more focused on mutability (which if you are doing some stats your data is usually immutable)
It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that subset becomes a local data frame that an R user can work off of.
Data frames and relational databases are very similar. When you’re using dplyr many of the operations conceptually map 1-to-1 to SQL. SQL is just a data frame manipulation DSL for databases. It’s all about filtering, aggregation and projections.
Also not true about mutability and web tech. Most SQL operations do not mutate. Also SQL is more associate with data analytics, data engineering and report creation from data warehouses than web tech. The web tech use case came about because of PHP/MySQL and Django etc. but there SQL is usually used as an API language for the underlying data store rather than the full query engine that it is.
The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end of the day, it sounds like the people using tables/dataframes are just too lazy to setup and use a SQL? I don't really see any distinction btwn the two domains then
For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra.
That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations).
Using SQL via sqlite to manipulate local tables confer similar benefits.
[1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.
It probably says that every day. I've seen that in a lot of self-paced courses
Other reasons: every language can use a SQLite db, you can add indexes, you can add multiple tables in one file, you can store large amounts of data on disk, it has built in json queries, there’s a great ecosystem of SQLite libraries, support for it is built into python.
Dataframe abstractions are really easy to understand so I have worked with journalists and biologists who are able to work productively with pandas or R. Transforming and analyzing data is often easier to write with pyspark while spark sql makes it easy to access and extract data from some data sources.
It all really depends on who is your customer, who are your coworkers, how much volume of data do you have, how much time do you have to accomplish your task etc.
You might frequently find the No Starch and O’Reilly books on Humble Bundle, (https://www.humblebundle.com/books?hmb_source=navbar) if that is available in your location.There’s often loads of overlap between bundles. I’m sure I’ve bought the python book about 5 times so far but I don’t mind as it’s great value.
If you want to learn about database theory, however, as well as the practicalities of SQL, then I found that most of the resources I used when I did this at uni were online. The book we used was the Connolly/Begg book (https://www.pearson.com/us/higher-education/program/Connolly...)
I don’t think this effectively answered the “why” of your question, however. My guess is that SQL is a very well established domain language and when it comes to data normalised across many tuples it’s the standard for manipulation.
I don’t think that dataset size is the primary reason to manipulate data via SQL; I think that the moment your programme starts to need more than a single flat file data source, you naturally start to think about normalisation, indexes etc for performance and sanity.
If I may, however: I am coming at this from the other way in that I am far more often manipulating CSV and excel data and would like some good resources in how to quickly load that in to a dataframe in pandas or similar vs using SQL. (If I’m responding to a thread hijack I may as well go all in and totally derail it)
(I think most/all of the comics are entries on her blog if you desperately don't want to pay).
Maybe out of ignorance, but in all the years I've programmed I've never had a situation when I said to myself "I need a database"! I either have data in maps/vectors in memory or if I need tables I'll use some table datastructure (like in R/Matlab). So reading a CSV file into a SQL table seems a bit weird - though maybe some people have multi-gig CSV files? I guess at a certain size (when it can't fit in RAM?) you gotta turn to SQL - or maybe I should be using SQL for smaller/simpler problems as well - but I don't really know where to start.