4 steps to start practicing SQL at home | by Skyler Dale | Nov, 2020


Photo by olia danilevich from Pexels

Download MySQL and do it yourself

If you’re learning SQL for the first time, you’ve probably dabbled with at least one of the following resources: codeacademy, datacamp, and khan academy. Each of these websites provides an interactive, hands-on method for beginners to learn SQL.

These are great, free resources and you absolutely should use them.

But what comes next? How do you bridge the gap from “I know a little SQL” to “I’m comfortable querying and analyzing large datasets in SQL”?

Practice makes perfect — obviously — but how does a beginner get practice?

In this post, I’ll walk through a simple set of steps you can take to build up your skills at home, for free.

Your first task is to download database software.

Sure, there are options for practicing SQL online without the software. But there are some serious benefits to doing it yourself:

  • You get the experience of downloading and setting up software.
  • You’ll start to get comfortable with a SQL editor, which is what you’ll use if you employ these skills at a company.
  • You get control over your own data and databases that you set up.

You have several options to choose from, but MySQL is the most popular, so I’ll leave a few notes below on how to set it up.

There are actually two things you need to download:

MySQL Community Server: This is the database itself. Download here: MySQL Community Server.

MySQL Workbench: This is the SQL editor or “GUI” which you can use to interact with the database. Download here: MySQL Workbench.

Below are a couple tips as you go through the installation process:

  • No need to login or sign up, just click “No thanks, start my download.”
  • If prompted, select “Use Strong Password Encrpytion”
  • You will be asked to enter a password for the “root” user. That’s you — make sure to choose something that you’ll remember.
  • When you’re done with the installation, you should have a “MySQL Workbench” application. This is what you’ll use to start coding!

Great — we now have the software we need to get started.

The next step is to open the MySQL Workbench, click the local instance, and type in our root password:

Screenshot of MySQL software. Image created by author.

Now that we’re in the workbench, we can create our first database and then our first table.

In order to create a database, we simply type the following command in a query window and press the lightning bolt icon to run:

create database testdb;
Screenshot of MySQL software. Image created by author.

Next, we’ll want to create our first table within our database. Again, we’ll open up a query tab and then use the “create table” command:

Screenshot of MySQL software. Image created by author.

Note that we include the database name (“testdb”) before the table name.

Now that we know how to create a table, it’s time to get some data to analyze. There are a few ways to do this.

First, we can create the data ourselves using the “insert” command:

Screenshot of MySQL software. Image created by author.

Then we can run our first query:

Screenshot of MySQL software. Image created by author.

Notice that the result grid returns the records we just created.

This method is useful because it allows us to create reference tables. But it will be very manual to create enough data to practice on.

A more scalable option is to find a csv file on the web that we can download and import into a table. For example, we can pull COVID19 data from kaggle.com for free.

Then we can right click on “Tables” under the database we want to use and select “Table Data Import Wizard”:

Screenshot of MySQL software. Image created by author.

This brings us to a screen where we can import a csv into a new a table. Then we can click “Browse” and find the file we downloaded from the web. Finally, we define the table name and confirm the data types:

Screenshot of MySQL software. Image created by author.

The last way we can import data is by finding free SQL databases online. For example, the website “MySQL Tutorial” has a free sample database here.

All we need to do is download, double click to open it in MySQL, and then run the script to create the database.

This is great because it gives us a database with multiple tables — a perfect opportunity to practice joining data together.

You’ve come a long way. You downloaded SQL software, learned how to create databases and tables, and imported data from various sources.

The last step is the most rewarding, time to practice!

In order to be a good data analyst or data scientist, you need to be curious about your data. You need to ask the right questions and try to understand what’s going on in the domain you are addressing.

Practicing should be no different.

Download datasets from various sources and practice joining them together into new tables. Work with your newly created tables to answer questions that are complicated and interesting. Research advanced SQL techniques and figure out how they might be relevant with the data you have. Google solutions when you get stuck.

Let your practice be defined by your own curiosity and persistence.

In this post, we walked through 4 steps to get set up and ready to practice SQL at home. The great thing about learning data analysis is that you can learn by doing. And doing means: sourcing data, understanding it, asking interesting questions, and deriving meaningful insights.

And that’s the fun part. Enjoy.

P.S. → if you want to learn more about SQL, Python, and data science, subscribe to my free email list.


Source link

Write a comment