A step-by-step tutorial on getting started with Microsoft Visual Studio, MySQL, and the NCES Public School Characteristics dataset.
A friend of mine asked me about the best way to start practicing SQL at home for free. My first instinct was to direct them to codeacademy, datacamp, and other similar sites. However, my friend wanted a close to authentic experience, so I started thinking about how I interact with SQL at work.
I realized that I’ve been taking my ability to log into our database server and query some rich datasets for granted. Unfortunately, my friend did not have the access to our work development environment, so I decided to do a little research…
Today, we’ll download and install MySQL, its prerequisites and get our hands on some data from the public domain. Beware! This is not for the faint of heart; there are a lot of steps involved:
- Download MySQL
- Download Microsoft Visual Studio
- Continue Installing MySQL
- Download Dataset
- Create a Database
- Import Data
- Query Away!
This tutorial was done on a Windows 11 machine, but the steps outlined should be similar and applicable to other operating systems.
Let’s head on to https://dev.mysql.com/downloads/mysql/ and download the latest version of MySQL Community Server.
You will be asked to log in or create an account, but this is optional.
Let’s start the download.
Double-click on the installer to begin our installation…
MySQL is telling us that we need to install Microsoft Visual Studio first.
Let’s close the installer for now.
Download Microsoft Visual Studio
Head on to https://visualstudio.microsoft.com/downloads/ and scroll down the page until you see “Older Downloads” and click on the link. (Let’s get the 2019 version because that’s the latest version that MySQL requires as of this writing.)
The website will ask you to “Sign In.” Type in your Microsoft account credentials or “Create one.”
After logging in, accept the terms and conditions by clicking on the “Confirm” button.
Find the “Downloads” tab on the next page and click on it.
Find the version that you want and follow the link.
Then, find the community edition and click on its corresponding download button.
Double-click on the installer.
When asked which workload to install, find “.NET desktop development” and select it.
Click on “Install.”
Once it’s done installing, we can resume installing MySQL.
Continue Installing MySQL
Let’s continue where we first left off until you come across the screen below:
Click on the “Execute” button and keep on clicking “Next” until we get to the Accounts and Roles page. At this point, we can safely accept the default preselected values.
Choose a strong password (for good measure) and click on “Next” again.
On the “Connect To Server” page, type in the password you set earlier and “Check” it until a green checkmark appears. Then, click on the “Next” button.
Continue clicking on the “Execute” and “Finish” buttons until a terminal appears and the “Welcome to MySQL Workbench” appears on the screen.
Click on the Local instance of MYSQL80…
Go ahead and type in the password you had set earlier and click on the “OK” button.
You should now see the MySQL Workbench application like the one below.
Look in the left side of the window for the “Navigator” panel. Next, click on the “Schemas” tab and look for the “Create a new schema…” icon above the panel.
Type in a name for your schema.
On the subsequent windows, click on the “Apply” and “Finish” buttons.
And now, let’s get a dataset from the public domain.
Head to Kaggle and download the NCES Public School Characteristics 2018–2019 dataset.
Extract the zip file.
Create a Database
Navigate back to MySQL, find “Tables” on the “Schemas” tab, and right-click on it. Next, select “Table Data Import Wizard” on the context menu that appears.
Import the Data
Click on the “Browse…” button and navigate to the file from the previous step. Click “Next.”
On the next popup window, select the “public_schools” schema from the drop-down menu and rename the table and click “Next.”
Click “Next” two more times and prepare to wait for a few minutes while the program is importing the data.
When done, click on “Next” and “Finish.”
Back on the main screen of Workbench, navigate to the “Query 1” tab.
And that’s it!
You are now ready to practice and hone your ninja SQL skills.