Am I good enough?
What does it take to become an expert in writing SQL?
I used to spend countless hours asking myself if I have written a query the best way possible. Now, I believe there’s a better question to ask myself.
What is my purpose in learning SQL?
Like any tool that can be used for different purposes, we want to first know where we’re going to use it.
Let me tell you a quick story.
One of my most insightful projects was when I was a support agent. This job required that I talk to users and clients for almost any SQL-related question. Yes, I was that person who will talk to you after you click that "Live Chat" button. I answered SQL questions of people from around the globe, from business analysts, students, developers. The questions were varied – I'd see classroom questions, some open-ended business questions, and some very technical and complex queries. However, I notice that things get resolved the fastest if they already know what their problem is, or what they want to get.
From that project, it’s amazing how much perspective I gained about how people understand SQL, and what their biggest challenges are.
I first heard of SQL back in 2012, and I never looked back. I share with you how I managed to develop a strategy to become better in writing SQL.
Now, I just don’t ask myself “Am I already good enough?”
Rather, I ask myself “What do I need to do?“, and simply following it up with, “Was I able to do it?“. That’s objective, has bounds, and has a direction.
What are SQL Fundamentals?
Before we can start making complex queries, every analyst or programmer will have to start with the basics.
You have to understand a handful of keywords that you’ll be using.
Should you have a solid grasp of all the keywords such as CREATE, or WHERE, or GROUP BY? Not necessarily all of them (and that’s a relief!).
Only learn those that serve your purpose.
This is the time when things can branch out differently for each person.
Most importantly, your depth and width of learning depends on your role.
In my experience, I see three key personas that make use of SQL. These are Business Analysts, Students, and Developers.
What is a Basic SQL Query?
A basic SQL query contains parts that the database needs to give out information. Each part contains a keyword that is standard and straightforward.
Let’s look at this example:
SELECT first_name FROM employees WHERE age > 21 ORDER BY age ASC ;
In this query, each line tells us a story.
| English | SQL |
|---|---|
| I need the first names | SELECT first_name |
| of all employees | FROM employee |
| whose age is above 21 | WHERE age > 21 |
| (period, or end of sentence) | (in SQL, we use a semi-colon) |
It is important for anyone to start by learning the parts of a query, and how a basic query looks like.
SQL Fundamentals for a Business Analyst
If you are a business analyst, most of your SQL will involve projecting your business requirements into a query.
If you are looking at monthly sales per account team, then you know that you have to get Totals for each Group, which is a SUM and GROUP BY.
A lot of metrics and KPIs are easily converted into SQL.
Moving averages? LAG and LEAD functions.
Top 10 Performing Employees? The TOP / LIMIT function will give you that.
Some pre-defined Customer Segments? An in-depth GROUP BY is what you need.
As you might be dealing with more of calculated data, a business analyst’s best friend is aggregation, or grouping and summarizing data.
Your focus is more on getting insights and answers to your business questions, and this need is very different from a technical person.
As you might be dealing with more of calculated data, a business analyst’s best friend is aggregation, or grouping and summarizing data.
Your focus is more on getting insights and answers to your business questions, and this need is very different from a technical person.
SQL Fundamentals for a Student
What are the biggest challenges for a student? They are most likely expected to have a very solid foundation of the basics, plus they’re expected to know a little bit of everything.
However, oftentimes the best answer is also the things that most students hate. Practice.
But I get it 100%. Some students just want the answers (but you probably won’t be reading this if you are that kind of student).
A query can be too scary to even start. And it’s not helping that the fonts of most SQL software look like they were taken from 20 years ago.
However, it really is just that. Practice, practice, practice.
You will not learn how a DELETE statement works if you don’t actually prepare the tables and see what happens after you run the queries.
You don’t need to be afraid to make mistakes because you can always start again.
Best Tip for Students: Do not be afraid to try things out, especially if you have a backup of everything.
A student’s best friend is the entire database script (or what some say the Create Script). You will make mistakes, and you can simply drop everything, and then do it over and over again.
Students will learn the most because they also make the most mistakes.
SQL Fundamentals for a Developer
For me, a developer is someone who is actually making a living out of SQL. You may be the fresh graduate, or a cross-functional employee, or a senior developer.
I also started out as a full-time developer.
As a developer, I thought I was always expected to know more. Is this true?
However, it’s not knowing more, but knowing how to find out more information. As developers, we are in a constant challenge to consume as much innovation as we can.
Our value comes in when we need to write complex queries.
Undoubtedly, one of the biggest values where we come in is when we resolve errors.
Error resolution is an area that cannot be fully automated yet, at least at this point.
You will need to learn the ins and outs of SQL keywords. You will need to look out for query performance, and not just getting it done.
A developer’s best friend is Stack Overflow and the Database Manual.
Yup, you read it right. I’ve had some users ask me questions I know nothing of, and just sit with them as we read a closed thread in Stack Overflow. Works wonders.
I do think that almost all problems we are having is also encountered by someone else before. So, before you raise a support ticket, or ask people, try to see if someone has asked the same question before.
Lastly, your database manual is a gold mine. Some developers are not as good in documentation as they are in development, so this can happen.
Why You Need to Have a Testing Mindset
A testing mindset is focused around the idea of humility. What we think may not always be right.
Thus, the more unsure you are about your query, the more frequent you check each part for correctness.
In my early days, a perfectionist like me started writing queries from scratch by finishing the entire query, and then running it, hoping that I have done it 100% correctly.
When things are not 100% correct (which happens most of the time), it gets the best of me. It takes more time to find out where it went wrong. I remember myself to often need a time out. It’s frustrating.
Thus, as early as now, I encourage you to test your queries as often as possible.
Test, test, test. With a testing mindset, you work like a carpenter. You build a wall layer by layer, checking and correcting every step of the way.
Formatting Your Query Works Extremely Well
Following a specific format works well for me, because I can see exactly what’s happening in each line.
These two queries are exactly the same, except for the formatting.
SELECT first_name, last_name FROM employees e LEFT JOIN promotions p ON e.employee_id = p.promotion_id WHERE e.employee_id > 1200000 AND p.promotion_date < ‘2020-01-01’ ;
SELECT first_name, last_name FROM employees e LEFT JOIN promotions p ON e.employee_id = p.promotion_id WHERE e.employee_id > 1200000 AND p.promotion_date < ‘2020-01-01’;
The first one follows my preferred style for formatting. At a glance, this makes it easy for me to know the following information:
- How many tables are used?
- How many columns do we have in the output?
- How many conditions are in place?
As you become more comfortable with SQL, you can develop your own formatting that will save you a ton of time.
A format is similar to knowing where you always put your keys, so you don’t have to look for them all the time. Saves time and sanity, guaranteed.
