Writing SQL statements is easy ….
When students first learn how to write SQL, the tendency is to get too focused on getting the syntax right, and making sure the query executes. Don’t get me wrong. That’s really important especially when you’re just beginning your database journey. I have been guilty of this, and still am sometimes guilty of this.
However, blame it on age or experience, my focus has shifted from worrying about the syntax to understanding the problem at hand. I no longer jump to “coding” right away. I still worry about the syntax, but I leave the worrying for later.
This shift in focus may be frustrating to some of my students who are eager to start writing SQL code. Maybe I caution them too early in the game, but I want them to realize it’s not just about writing SQL, it’s really about solving a problem. I always caution them to slow down, and try to first understand what the question is. What question are you answering? What problem are you trying to solve?
In addition to understanding the question, we also need to understand our data. It is important to get to know the tables and the records that live in them. What data do we have? Can the data we have help us answer the question?
The queries we write should provide us answers. It should provide us good answers, right answers (of course this is constrained by the quality of data you have).
You may write a query that has no syntax errors, join on columns that have the same names, and hit that Execute (or F5) without any red messages, but you may still not be answering the question or solving the problem.
Writing that SQL statement should be the easy part. Worry about that later. Take the time first to understand what it is that you need to do, and how you can get there. (Sounds like life, doesn’t it?)
Some of the common stumbling blocks I see students encounter:
joining tables based on column names alone. This is a dangerous assumption. You have to assume that some columns in different tables can have the same name, but different meanings.
assuming tables can be be joined only based on PK (primary key) and FK (foreign key). Most of the time, yes, that’s how you would join tables. However there will be cases where you might want or need to join on non-ID columns.
joining tables based on ID columns (regardless or the name or meaning)
mixing up COUNT and SUM, and not knowing (or noticing) the difference
not validating the results
stopping work as soon as the errors go away