.svg)
13 SQL skills that might make you a better engineer. Or at least better at SQL.
Welcome to another entry in “Shortcut talks about engineering stuff”, a series whose title is fairly self explanatory. We might talk about data, we might look into fixing bugs, we might talk about how we approached building a new project internally, and we might write up a quick guide to a programming language, which is exactly what we’ve done here in this wide ranging overview of how to go from knowing nothing about SQL to knowing something about SQL to knowing a lot about SQL.
Ah, SQL. We love SQL, don’t we folks? Is there a cooler set of initials found anywhere in tech?
Wait, sorry, I’ve confused SQL’s reputation for another language's haven’t I? Maybe Go? Wait, is Go still cool?
How many questions can we open this post with? We’re at right around seven right now, yeah? Is there a Guiness Book of World Records entry for most questions asked at the beginning of a blog post?
OK. SQL is not necessarily all that cool, but it is important if you want to do any work involving data. SQL skills are in high demand everywhere, especially at companies like Netflix and Spotify whose appeal and growth is highly driven by their understanding of how users engage with their services.
Even if you aren’t planning to move over to the data team any time soon, it’s worth taking the time to learn a bit about SQL. You’ll gain a better understanding of building and interacting with databases, which will make you more well rounded. And that’s cool in its own way.
So where should you focus your efforts? Don’t worry, this step-by-step guide — we guess this is a listicle, or should we say, liSQL — has you covered.
Beginner level SQL
If you’re only passingly familiar with SQL (alias Structured Query Language), focus on learning some of the key concepts and ideas behind it. Understanding of SQL databases, tables, and indexes, as well as being able to write a basic query, will put you in a great position to understand SQL’s purpose and major applications.
If you find the notion that you may not already be able to write a basic SQL query a little insulting, we recommend jumping ahead to the next section.
1. Fundamentals of databases
To understand the point of SQL, you first need to wrap your head around the concept of databases. What are they for? How can these electronic filing systems help companies organize and use data in a practical way?
A better appreciation of the role of databases and their uses will set you up to understand SQL’s functionality and the role it plays in your own organization.
Learn More: Fundamentals of Database System
2. The purpose of tables and indexes
Tables and indexes are the major building blocks of SQL. It can be helpful to think of the database as the library, the tables as individual books, and indexes as one of those old-fashioned Dewey Decimal file-card cabinets that helps you find what you’re looking for.
While knowing how they actually operate is a more advanced skill, it’s important to understand what they are and what they do so that you can effectively navigate the data in your database.
Learn more: Database Indexes Explained
3. Basic SQL syntax
To understand any programming language, you must first master its syntax. In SQL, this means understanding how to construct a query to find relevant information.
Learn basic SELECT and FROM commands to create a functional query that pulls data out of a table. SQL is incredibly precise, and small errors can lead to major problems down the road, so also spend some time learning how to avoid common syntax mistakes, such as keyword errors and incorrect quotation marks.
Learn More: Learn Beginner SQL in 5 steps in 5 minutes
4. Conditional filters
You don’t always want to pull every single record from a specific column. Usually, your needs will be a bit more specific. Once you understand how to structure a query, learn how to write a slightly more advanced one so you can extract the information you need from a table.
WHERE clauses help you filter your table, along with OR and AND. GROUP BY and ORDER BY help you organize the data you’ve pulled. Once you’ve learned these you will know how to write most basic queries.
Learn More: How to Filter Query Results
Intermediate level SQL skills
<!-- Code for CTA component starts here-->
<div class="post_body_cta"><div class="post_body_cta-heading">Enjoy your work</div><div class="post_body_cta-text">Project management software should be helpful, not a hassle.</div><div class="common_buttons_wrap"><a href="/signup" class="button primary text-color-white w-button">Get Started - free forever</a><a href="https://api.app.shortcut.com/oauth/signup" class="button is-google w-button">Sign up with Google</a></div></div>
<!-- Code for CTA component ends here-->
If you already know the basics of what SQL does and how to query a database, you can level up your skills with some more advanced functions and a deeper knowledge of theory. These skills are a touch more complicated and, as with anything that’s more complicated, may take longer to fully grasp.
5. Joins
Not all of the data you need to work with will always be in one table; in fact, most often it won’t be. By mastering joins, you can merge the data from multiple tables together. This makes it much easier to analyze different datasets.
There are four types of joins: inner, left, right, and full. You’ll need to learn which to use when, and the code needed to execute them.
Learn More: Basic SQL Join Types
6. Subqueries
A subquery, sometimes known as a nested query, is a query nested inside another statement. Like joins, they are used to link data between different tables, but when done right, they are faster and more efficient than joins because they eliminate extra data steps in data extraction.
Learn when and where you can use subqueries instead of joins, and learn how to write readable and maintainable SQL using common table expressions even as your subqueries become more complex.
Learn More: Introduction to a Subquery in SQL
7. How Indexes work
Database indexes make queries quicker. By imposing order onto tables of data, indexes make it easier for queries to target desired information.
Knowing how (and why) indexes are created, and the different types, will give you a greater understanding of how to effectively use them. Learn the difference between clustered and nonclustered indexes, the ways indexing adds data structure, and the rules for creating efficient indexes.
Learn More: How Does Indexing Work
8. Gain a working knowledge of PHP
Lol. You’re already working hard to learn SQL, and now we’re telling you to start learning PHP too?
It’s asking a lot, but gaining some mastery of PHP will help you interact with many popular SQL database programs, like MySQL. You can build almost any kind of website using the two together. So if you’re going all-in, you should really go ALL in.
Learn More: PHP & MySQL Tutorial
9. How to work with popular database systems
SQL on its own is just a programming language. To actually store data, you’ll need to work with a system like MySQL or PostgreSQL. Both are open source and freely available and use similar syntax, so it’s a good idea to try to master both.
Learn More: PostgreSQL vs MySQL
Advanced SQL skills
<!-- Code for CTA component starts here-->
<div class="post_body_cta"><div class="post_body_cta-heading">Give us a whirl</div><div class="post_body_cta-text">Because you shouldn't have to project manage your project management.</div><div class="common_buttons_wrap"><a href="/signup" class="button primary text-color-white w-button">Get Started - free forever</a><a href="https://api.app.shortcut.com/oauth/signup" class="button is-google w-button">Sign up with Google</a></div></div>
<!-- Code for CTA component ends here-->
If you already know your way around a database and can work with popular database management systems, you’re probably feeling pretty good right now. At least if working with databases is the sort of thing that makes you feel good. But there’s always more to learn.
10. Execution plans
Execution plans are a visual representation of how a database engine executes a query. They basically let you peek under the hood and see how the information sausage is made.
Execution plans can tell you a lot about the efficiency of a query and are the main tool for troubleshooting a slow or underperforming query. Reading and understanding them can help you tune queries without messing up performance. Learn how to pull an execution plan, read one, and tune it to increase query performance.
Learn More: Execution plan in SQL Server
11. Backup databases
Creating a backup database is crucial in case your first one is corrupted or damaged in some way. There are different types of backups, and knowing which to use and how to institute (and restore) each is an important part of database management.
Learn More: Understand SQL Server Backup Types
12. Using indexes to speed up SQL queries
Indexes can speed up performance by making data quicker to find, but poor indexing is also one of the biggest performance killers. Learning how to to identify good candidates for indexes, as well as how to craft and maintain them, will help you run a quick and orderly database.
Learn More: SQL query performance killers — understanding poor database indexing
13. OLAP
Online Analytical Processing (OLAP) describes a class of database applications that allow you to analyze data faster and in more innovative ways than you can with just a simple two-dimensional spreadsheet. It used to be done primarily in the form of OLAP cubes but has evolved in recent years to include running OLAP workloads directly on columnar databases.
It takes a lot of advanced skill to implement, but OLAP can help you collate numbers in interesting ways that help to understand a business.
Learn More: OLAP Tutorials
Further advancing your SQL skills
What SQL lacks in coolness, its community makes up for in enthusiasm. The internet is bursting with incredible free resources to help you learn more about the wide world of SQL. From dedicated blogs to online courses to active communities of SQL developers, there are nearly endless options for learning more.
Start with the basics, work your way up, and soon you’ll be a database master. And that’s pretty cool.
You know what else is cool, but doesn't require any SQL or database knowledge? Using Shortcut to manage your projects. Signup and try us out for free to see just how cool it is.
If you have questions about Shortcut, visit our help center. We're here to help. And also to project manage. But mostly to help.
