The world’s leading publication for data science, AI, and ML professionals.

How to Practice SQL on AWS for Free

Improve your SQL skills by practicing on a real database.

Photo by Florian Krumm on Unsplash
Photo by Florian Krumm on Unsplash

One of the required skills of a data scientist is working on databases using SQL. You might argue that it is the job of a data engineer but the data scientist roles are inclined to being full-stack. Besides, as a data scientist, you wouldn’t want to depend on a data engineer to retrieve data from a database.

There are lots of tutorials that explain SQL syntax to do certain operations. You can even practice and test your queries on sample databases. However, it is not a very efficient way to learn.

Working on a database of your own and running SQL queries with an RDBMS (e.g. MySQL) is far more efficient in improving your skills. In this post, we will create a MySQL database on AWS and connect to it using MySQL Workbench.

The outline is as follows:

  1. Create a MySQL database using Amazon RDS
  2. Connect to the database using MySQL Workbench
  3. Practice section

You first need to create a free tier account on AWS. It is very simple to create and you won’t be charged a dime if you stay within the limits of the free tier. The scope of the free tier is more than enough for learning purposes.

If you already have an AWS account or just created a free tier one, we are ready to start the following section.

1. Create a MySQL database using Amazon RDS

We will use Amazon RDS (Relational Database Service) which can be accessed by typing RDS on the console.

(image by author)
(image by author)

Scroll down a little and click on the "Create database" box.

(image by author)
(image by author)

You need to select the standard option and MySQL as the engine option.

(image by author)
(image by author)

You don’t need to change the edition. Make sure to choose "free tier" under the templates.

(image by author)
(image by author)

In the settings section right under the templates, you type a name for your database instance, login ID for the master user, and create a password.

(image by author)
(image by author)

For database instance size, there is only one option for the free tier which is db.t2.micro.

For storage, just select the general purpose and the default 20 GB. Make sure to unclick the "enable storage autoscaling" option.

(image by author)
(image by author)

Multi-AZ deployment is not available for the free tier so we will just past that.

The next section is "Connectivity". Select the default VPC. Make sure to select "yes" for public access and create a new VPC group.

(image by author)
(image by author)

Creating a new VPC group is important because this will create a security group that will allow connection from the IP address of the device that you are currently using to the database created.

For database port, leave it as the default value 3306.

In the additional configuration section, type an initial database name and leave the other two options with the default settings.

(image by author)
(image by author)

For the remaining configuration sections, the default choices are fine. Make sure the "Monitoring" option is not selected.

(image by author)
(image by author)

You can now scroll down to the end of the page and click on the "Create Database" box.

(image by author)
(image by author)

After you click on the "Create database", you will be directed to your databases in the Amazon RDS dashboard. The status will be seen as "creating" for a while and then become "available".

(image by author)
(image by author)

2. Connect to the database using MySQL Workbench

The next step is to download MySQL Workbench which is also free.

(image by author)
(image by author)

Select the appropriate version for your computer, download it, and then install it.

On the MySQL Workbench console, click on "connect to a database" under the "database" section on top.

(image by author)
(image by author)

The following screen will pop up.

(image by author)
(image by author)
  • Hostname: The Endpoint of your database. You can see the Endpoint when you click on the database instance.
(image by author)
(image by author)
  • Port: 3306
  • Username: The username you types when creating the database (I typed MasterUser1)
  • Password: Click on "store in vault" and type the password you created

Then, click on "OK".

Congratulations! You are now connected to your database on AWS. MySQL Workbench will open up an SQL editor so you can start practicing and running queries.

There is one last thing to do before running queries. Click on "schemas" and double click on the database you want to use. There will be only one for now.

(image by author)
(image by author)

3. Practice Section

We are ready to go now. Let’s run a few simple SQL queries.

Create a table

Type the following SQL code in the editor and click on run.

(image by author)
(image by author)

You now have a table in your database. The name of the table is "sample" and it has 4 columns.

To get an overview of the table, type "DESC SAMPLE" in the editor, and run it.

(image by author)
(image by author)

Insert a row

The "sample" table is empty now. We can add entries using the insert function.

(image by author)
(image by author)

I’ve added two entries using the insert query above.

Select from table

We can now see the entries in the table. The following select query will all rows in the table.

(image by author)
(image by author)

Conclusion

We have created a MySQL database on AWS using the Amazon RDS service and connected to it using MySQL Workbench.

We can now improve our SQL skills by practicing on a real database. It kind of simulates a real-life work scenario which I think will motivate you and make it easier to learn SQL.

Thank you for reading. Please let me know if you have any feedback.


Related Articles