Learning SQL can be one of the greatest career decisions you can make. It’s a highly sought-after skill that will open doors for you. But more importantly, when you learn SQL you also change how you think about information. You learn how to separate data into its constituent parts and how to think about the relationships between these parts.
Doing this is one of the things that helped me boost myself into working as a software developer. I didn’t go to night class or spend any money on books or a Lynda subscription or any of that.1 There are countless highly intelligent people on the internet willing to share their knowledge and experience for free! So why not take advantage of some of these resources?
Below are the first steps to installing the tools you need to start learning, and links to free resources from best-in-the-biz websites and experts. Time to start learning.
0. Install SQL Server
Before you can query or work with databases, first you need to install an RDBMS (Relational Database Management System). Because I work with Microsoft products every day, I am fairly biased, but I would recommend SQL Server Express Edition. It’s free, it has all the features you’ll ever need in your education, and it’s basically the full package. What you see in the Express Edition is most of what you’ll see in any production version of SQL Server, so the skills you pick up while learning are directly transferable to the real world.
Installing SQL Server installs a bunch of services and a program called SQL Server Management Studio, or SSMS for short. SSMS is what you will use to interface with the databases.
1. Installing AdventureWorks
Microsoft has released a sample database called AventureWorks for a fictitious company called Adventure Works Cycles, a large manufacturing company. Many exercises online use AdventureWorks in their examples, so it’s important and useful to have this database accessible.
First, you’ll need to download the AdventureWorks database itself.
- Download a database backup for the appropriate SQL Server version you’re running
- AdventureWorks sample databases github link
- If you’re unsure which version you have, open up SQL Server Management Studio. In the Start Menu, it will be under Microsoft SQL Server 20xx – this should be the version you have
- On your file system, navigate to the MSSQL Backup folder on your C: drive.
- For SQL Server 2016, this is C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup by default. Whichever version you’re using, the path will be similar – change MSSQL13 to MSSQL10_50 for 2008 R2, for instance
- Copy or move the AdventureWorks20xx.bak file to this Backup folder
Restoring the Database Backup
- Open up SSMS and connect to your SQL Server instance. In most installations, by default, you should be able to just click “Connect” on the first dialog that appears
- In the Object Explorer on the left-hand side of the screen, right-click on “Databases” and select “Restore Database”
- On the Restore Database dialog, select the “Device” radio button, then the “…” button
- On the Select Backup Devices dialog, make sure “Backup media type” is set to File, then click “Add”
- Another dialog will pop up, and you should immediately see your AdventureWorks20xx.bak file here. If not, navigate to where ever it is in your file system and select it, then click OK
- Click OK again to confirm on the Select Backup Devices dialog
- Now, your Restore Database dialog should look something like this:
- Click “OK” and watch the database be restored!
- Now, you should see the “AdventureWorks20xx” database in your object explorer when you expand the “Databases” list.
2. Read Stairway to T-SQL DML
Now you have a sample database ready to be used. But what to use it for?
SQL Server Central is an online community centered around (surprise) SQL Server. They have what are called “Stairways,” each of which is a series of articles concerning specific aspects of SQL and SQL Server. This Stairway is centered on the basics of Microsoft’s SQL dialect, called T-SQL. Each “Level” of the Stairway ends with a handful of exercises which use the AdventureWorks database. Certain Levels will teach you the basic syntax of SQL and how it works each step of the way.
This series also includes a few Levels on set theory and the math behind SQL—specifically, what JOINs are and how they work. Many people struggle with visualizing or thinking about JOINs. If you can master JOINs and thinking in sets, then you will find yourself yet another step higher on that ladder. Or stairway. You get it.
Working through this Stairway will probably help you more than any other free introductory resource out there today.
Exactly what it says on the tin: once you’ve got the first Stairway down, this one runs you through more advanced exercises on SQL functions and the interesting things you can do with them.
4. Exercises and Further Reading
Reading through the Stairways above will be helpful, but as with most knowledge, if you don’t use it, you lose it. So here is a list of more resources to further cement what you’ve learned.
- Stairway to Data: Data types and their representations in SQL Server
- Database Administrators (DBA) Stack Exchange Newsletter: A weekly email newsletter with hot & unanswered questions from the past week. Good real-world exercises in testing your knowledge. Try to answer the questions on your own, then review the provided answers, and consider how they may differ
- Brent Ozar’s Blog: A big name in the DBA community. Brent Ozar and co. are typically focused on SQL Server performance, but they run through just about every topic you could ask about in the blog. On top of this, they run a weekly Office Hours podcast with Q&A from listeners, again, with real-world scenarios
- The Redgate Blog: Read blog posts from experts in the industry. When you find terms or expressions you don’t understand, Google them, and make notes, then return and reread with new understanding.
- SQL Zoo: Quizzes that steadily ramp up in difficulty, from the basics to more advanced business cases
- AdventureWorks Business Scenarios: Microsoft’s own series of scenarios for the AdventureWorks database. Use as a reference for decent database documentation.
- Make Your Own Database! Think of an app you’d like to see in the world. Then model and build a database to power that app. If you watch a lot of movies and would like to keep a log of the movies you watch on a regular basis, then start thinking of what the data behind such an app might look like. What would you need to store? How would you best access and modify data? What relationships are there between movies, actors, crew, and people who do the watching? If nothing else, you could find an app you already use, and try to reverse engineer what its database might look like.
- Download and Query Existing Databases: Out there on the Internet you can find countless awesome databases filled with real and fake data. Google around and find something in a field that interests you, then start poking around the data contained within. You’d be amazed at what you can find and you might even come up with some great ideas of your own for something to build.