- create tables, update the schema, insert rows, add an index
- select, filters, joins, order by, limit, inner queries
It takes forever to be comfortable with:
- anything that involves summarizing, grouping, having, min, max, windows
The other tip is to sketch the problem in excel/google sheets when it gets hairy. Not the actual code (I don't have a clue how to do that, others have), just the values in the different steps. In the end it is only about rows and columns.
But that said, these days a lot of it happens intuitively for me, I pretty much know the solution before I can spell it out. It certainly was not like that when I started.
When you begin, "programming without for loops" feels like programming with your right hand tied behind your back. But in hindsight you get a lot of exercise in the immutable paradigms of functional programming, working with comprehensions, sets, maps folds comes very natural.
I've used SQL enough to have to write nested queries, haven't dove further than that.
https://learnsql.com/blog/sql-subquery-cte-difference/
You can think of CTEs as a way to save an intermediate query as a sort of temp variable/table that can be used in the final statement following `WITH something AS (...)`. They are great for flattening your queries and giving descriptive names to subqueries, and you can chain multiple WITHs together as well.
What so you mean by this?
For starters, i can never actually tests parts of those queries without rewriting the query up to the part that i want to test, for example:
WITH
query_one AS (SELECT ...),
query_two AS (SELECT ...),
query_three AS (SELECT ...)
SELECT ... /* main query */
Then, working with SQL and CTEs feels like going back from a language where functions are first class citizens to one where no such thing exists, just in regards to querying data. It would be nice if i could store parts of queries under packages, to be able to write dynamic SQL more easily, instead of having to use tools like myBatis for this purpose: https://mybatis.org/mybatis-3/sqlmap-xml.html (see the bit about SQL fragments)
So i'd like to do the following:
PACKAGE my_snippets BODY IS
SNIPPET query_one
SELECT ... /* probably 500 lines long but often used snippet */
END query_one;
END my_snippets;
/* and then, somewhere in code */
WITH
query_one AS my_snippets.query_one,
query_two AS (SELECT ...),
query_three AS (SELECT ...)
SELECT ... /* main query */
Next up, debugging in databases is just really bad. How am i supposed to put logging in the queries, without mixing the logging code with the other triggers and tables? What about debugging long running processes? What about adding breakpoints that i can trigger when a particular view or table is accessed? What about doing this on the server while i have a local app instance connected to the DB, or maybe even another app server? Why can't i step through the query execution and see how the filtered record count changes with each "step"?
Apart from that, my problems are largely with the tooling around databases. There are relatively few universal (cross language) DB migration solutions out there, for example dbmate, every framework seems to have its own approach. There seems to be this odd division between procedural SQL and regular SQL statements, where what you can do differs based on context, which is inconsistent. Procedural languages as a whole vary wildly in what they can do - you won't be doing complex logic with custom types on MySQL/MariaDB anytime soon, whereas Oracle or PostgreSQL will suffice. But even those two have different dialects, it's never "just SQL". There are oddities with selecting certain kinds of data, only pgAdmin seems to work nicely with geospatial data, but apart from that i've also seen problems with using lower level JDBC logic which you can't really test outside of the app, in something like SQL Developer. But even apart from that, as much as we like ER diagrams, MySQL Workbench is the only tool that i've seen which allows you to actually do model driven development properly and synchronize schemas and do forward/backward engineering - even pgAdmin fails at doing this. Oh, and the tools themselves are really inconsistent - you'll see a world of difference between MySQL Workbench, pgAdmin, SQL Developer, JetBrains DataGrip and others.
And now those DBMSes are attempting to add more functionality, such as exposing REST interfaces, instead of fixing the underlying and dated problems, because people out there are relying on those and therefore the logic is set into stone. It's no wonder that every year there's a new product or two that attempt to improve upon these, even if most of the time those products die out.
Perhaps the above is a stream of consciousness with some annoying things that i've dealt with over the years, but personally, relational databases are something that i use because they're often the least horrible tool for the job, even if they are not pleasant or easy to use, at least as easy as they should be. That's where i think the main problem lies - tools should be good for solving the problems on which they'll be used, these ones aren't.
Someone with 20 years of experience might have a different outlook, but personally i'd suggest that you utilize DBMSes for what they're good for - storing, retrieving and manipulating data and don't get too carried away with in database processing otherwise, since doing certain things within the app code seems to scale horizontally far more easier in some situations, has better auditability, debugging etc.
What you do here is to also wrap the main query into a CTE and then end everything with SELECT * from main_query.
Then you can easily change that last clause to do SELECT FROM query_two while you keep everything as is, even the CTE you called main_query.
For example, selecting the CTEs up to a certain point and using a particular button or keyboard shortcut within a development tool, much like we can already do with executing selection?
Of course, adding "clever" functionality like that might as well create some risks and inconsistencies, so i'm not entirely sure about that.
This is an entirely different subject and deserves a long discussion, and one which SQL is not ideal for.
As you said, breaking CTEs into separate views is the start, and then you can use a tool like getdbt.com to make your references into parameters. And then you also need to create the mock data (which you can do for example by writing it into csv:s)
From the role of an analyst I must say though, that once you have done all that work, the risk is that you forgot to worry about to the biggest risk here. What is actually inside that data you are querying? Maybe your biggest problem is not the logic of the query, but rather how dirty your input data is? Or for that matter, that you made completely incorrect assumptions on your input data, like that column X contains distinct values when it contains duplicates. That type of error wreaks havoc on your end result, with a big chance you'll never notice.
In regards to the correctness of the data and the quality, there are at least constraints that you can put into the DB, but i've personally seen plenty of cases where that isn't even considered and is forgotten about, without even getting into the OTLT and EAV anti-patterns and the implications that they may have: https://tonyandrews.blogspot.com/2004/10/otlt-and-eav-two-bi...
In every case where i've seen someone attempt to introduce polymorphic foreign keys, a lot of those consistency guarantees and control mechanisms have gone out the window, since you can't really have conditional constraints or complex logic in there either. Though thankfully not everyone has to deal with things like that in their projects.
One cannot overstate how important modelling your data is, to the point where schema-first is a strong and reliable approach most of the time.
Oh and lest I forget you can't just re-use a CTE in another query. But you can of course re-use a view. Also given what another user here remarked, Postgres might internally treat (and optimize) CTEs like views, so to me that makes views superior to CTEs in more or less all respects.
To make a non-trivial SQL query scale to non-trivial amounts of data, you have to understand the physical data organization and how query optimizer is likely to use it, which is kind of contradictory to the idea of the SQL as a "declarative" language where you just say what you want, and let the query optimizer figure out how to get it.
Instead, you have to design your indexes carefully to coax the optimizer into choosing a reasonable access path for your particular query. And do the same for all queries where performance is important.
Indexes are fundamentally not about data, but about access patterns. Which is what the developers are responsible for. That's why physical database design is a development task, not database administration task.
And I always forget which join does what.
I’d say they are more important to know than right joins.
However WINDOW queries definitely have a learning curve. Not the least because useful examples almost always require you to use a nested query.
Another one that caught me by surprise was NULL vs unknown[1]. That bit me in a couple of queries.
One instance was to turn a column containing comma separated values into rows[1], so I could join on them. Wasn't for a query that needed performance of course.
If only the DB we're using had one of those :)
I found that the most important success factors in learning SQL is the analytical thinking of the trainee and the way the trainer is explaining the concepts, in what order and what examples are used (the best examples are the ones the trainees meet in their regular work).
The functions are simple, the only difficulty is to remember the ones that are not used often enough (ex: some window functions). Even in that case, a quick check in the documentation is enough to get up to speed. The major difficulty with SQL is to write efficient queries on large data volumes, covered by the right indexes. This is very specific to each RDBMS, especially because of the tools helping with the work are specific (ex: SSMS, SQL Sentry Plan Explorer, statistics parser etc).
There is no point having developers write sql at all if someone else has to come in and redo it after.
On the occasion that there have been DBAs at the company I worked for, they always refused to help with any SQL, on the grounds that all SQL is "application level", and insisting that they were only responsible for configuring / deploying / monitoring the DB infrastructure.
No need of DBAs if your database is up to a few GB, you cannot live without DBAs if you exceed 100GB. I have several hundred SQL servers with databases exceeding 1 TB, on average several hundreds of GB each. This is where performance tuning is essential.
select c.cid, c2.cid
from customer as c
inner join customer as c2 on c.street = c2.street
where c.city <> c2.city
For example normalisation (join with a groupby/sum of yourself) or rank (join each row with all rows that have lower value than yourself and count those rows).
But as I mentioned above. A good start is to sketch that out in excel. You will realize that what you need is another column (e.g. total sum for this id). And from that you can work yourself backwards to figure out what is the table you need to join with to create that column.
the question was: "List all pairs of customer IDs who live on a street with the same name but in a different city." listed under self-join
that said i haven't wrangled with raw sql in a spell so the reading on window functions is interesting.
That said, I have a feeling your duplicates can be fixed by adding the requirement that c.cid < c2.cid
Not sure a window function would help in this particular situation, but they are there to help in more mundane examples.