Basically, instead of thinking in terms of how to get to the result, think instead of the result and figure out how to get there. For example, let's say I need to get a mapping of students to their classes. One way would be to get the students, then loop through that to get classes for each one. Another way to do it would be to ask the database to combine those sets of data for us instead and return the result (join students and class tables on the student ID).
Basically, find the data you want from each table, tell the database about the relationship between them (join on <field>), and set up your constraints. I guess you could think about the query as a function, and you're just writing the unit tests for it (when I call X with parameters Y, I expect Z).
I have the reverse problem: every time I motivate myself to learn Prolog I feel like I could just input the constraints in a db and use SQL to get my results.
(1) Think of a database as a "big pile of stuff in a room". Some of it's ordered in a sane way, some of it's not. There is "a person at the door" to the room preventing you from entering - this is the Database Engine, not to be confused with the Database itself. You need to get things out of the room, and you need to put things in the room. You're not allowed to enter. If you want something out of the room, you must ask for it (query) by specifying what the thing looks like and which part of the pile you think it's in. If you want to put something in the room, you must provide explicit instructions for where it must go. When you write SQL, you are either asking a question of or giving instructions to the database engine. The DB Engine is distinct from the Database (the big pile of stuff...which you never have direct access to).
(2) As others have pointed out, SQL is very much the mathematics of Set Theory put into a programming language. Stanford has a free class on Databases. I took it a few years ago (it may have changed), and most of it had no SQL at all. It was fairly very straight forward - things you can do with pencil and paper. It's free, so no pressure. Go sign up and do some of the homework assignments. Do them until you get them right (you can re-take wrongly answered problems immediately). It'll help break your procedural mindset. https://online.stanford.edu/courses/soe-ydatabases-databases
Of course you can always screw up and write suboptimal SQL that will take forever to finish, but it's just harder to do unless you're really trying to be dumb about it.
So I do think there's still a lot of merit in trying to learn and use SQL more.
There are certain situations where an SQL query is all that I can work with.
I find myself having the opposite problem I probably use (abuse?) SQL more than should.
My world (industrial plant) is very DB/historian heavy everything speaks SQL it's pretty much the common tongue connecting everything. I think this is slowly changing some PLC/historians now offer a webservice which returns JSON objects via an ajax query - personally I vastly prefer SQL to ajax.
I'd estimate for a typical problem I'm working with maybe 90% is done in SQL vs 10% in R/SAS code.
When I need Regression, Principal Component Analysis, Time Series manipulation, Plots etc I have to break into dedicated language.
For most other things - extract, merge, filtering, high level aggregation (Count sum etc) type of operations using SQL feels more natural and expressive to me.
But over time, and very heavy SQL reporting, I realized the procedural mindset still applies, it's just not expressed as such.
You need to think through the steps of how you want to retrieve, transform, aggregate etc. the data and build up the query to accomplish those steps. The resulting query doesn't look procedural, but the thought process was. Of course you need to know the sometimes opaque constructs/tricks/syntax to express the steps, which again, look like magic at face value or just reading docs.
I think this is why people struggle with understanding SQL. The thought process is still procedural, but the query isn't. You need to translate the query back into procedural terms for your own understanding, which is a PITA.
Just the basics, very clear and easy to understand.
It also helps to change the language you use in your inner monologue. Instead of thinking, "For each row in table A...", you should think, "For all the rows in table A that match on...".
EDIT: A CTE (Common Table Expression) might be what you're thinking you need. You can do some fun recursive queries with them.
It's like all my fears concentrated, like the power of a thousand suns, onto a single sentence.
Some problems can to be assembled from CTEs / subqueries when you might have reached for a procedural solution to 'finish' the problem. Grasping CTEs was a big thing for me and now I'm wrestling with issues of style mixing CTEs with tiny subqueries and wondering if the increase in readability (for me) is correct.
I'd love to see suggestions for digging deeper on this too.
1) Think in terms of excel spreadsheets. At each step in your SQL you are creating a set of data. Doesn't usually matter how many rites it is, you just want the right columns. 2) Once you have a recordset, decide how the next recordset goes together. I would mentally visualize putting one worksheet next to the other and think through what columns I had to join together to get the rows I wanted. 3) Make sure you know what your first recordset should be. This is the base data that everything can be joined into. I refer to this as the domain of records.
Using another tool for processing data often results in recreating SQL mechanics at application level. E.g. select this data, retrieve it, loop and if this, then set that, etc. SQL does it way better, guaranteed.
Of course that's often required for technical reasons (scalability etc.) or processing that's too complex to implement at data layer, or just for cleaner design.
But SQL is amazing at processing data!
I think it influences the mindset of the developer. As you say, “retrieve ... if this, then ... loop”. If you're in a “data processing” mindset, then you'll think of a problem like “Get the total number of car widgets in the warehouse” as fetch a widget row; if it's of type car, add number to total; loop until you've processed every row; there you have your total. If, OTOH, you're in an “asking questions” mindset, you'll go: What was the question again, exactly? Oh yes, get the sum of the number for all the widgets which are of type car widgets. Which is almost exactly the same as SELECT SUM(NUMBER) FROM WIDGET WHERE TYPE = 'CAR';.
Processing data is when you do it (in code); answering questions is when the RDBMS (i.e, its code) does it for you. :-)
(At least that's what I think the difference is _in terms of vvkumar's original question._)
We do >95% of our transformations with pure SQL and the queries are primarily in Looker.
- Step 1 - Step 2 - Loop through results in Step 2 - Curate and finish output.
I try very hard to transform the above steps into an SQL statement spanning multiple tables, but always fail and I usually fallback to python for manually extracting and processing the data.
Does anyone else face this problem?
Any suggested guides / books to make me think more SQL'ley ?