This is a complete tutorial to download and install a FREE SQL server environment, which is the best way to learn SQL.
SQL is an essential skill for data science/machine learning. Most companies still have their data stored on SQL databases.
Following this guide with step-by-step instructions, you can easily and quickly:
- get your FAQs of SQL answered, such as what is SQL (server), why SQL.
- download and install Microsoft SQL Server, one of the most popular relational databases on Windows OS.
- install sample database AdventureWorks for practice with real professional simulation.
Whether you want to land those data science jobs successfully or just want to learn SQL, setup the SQL environment and start practicing!
After this, you can follow SQL Tutorial for Beginners: Learn SQL for Data Analysis.
All you need is your computer.
Let’s jump in!
FAQ: SQL and SQL Server
We’ll kick start with the frequently asked questions related to SQL. If you are already familiar with SQL, please skip to the steps below.
According to Wikipedia, SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
It is particularly useful in handling structured data, i.e., data incorporating relations among entities and variables.
SQL is a query language.
SQL itself is NOT a programming language since it can’t perform tasks like loops or control structures, which are basics for a programming language.
IBM initially developed SQL in the early 1970s. SQL has a long history of being an efficient tool/language for deploying and maintaining databases.
Nowadays, many companies in various industries are still using SQL-driven databases to store information, which makes it always a critical skill for data science.
Related article: What are the In-Demand Skills for Data Scientists in 2020
Structured data is highly organized with a clear definition of the data, such as names, dates, numbers, and strings. It can be easily entered, stored, queried, and analyzed. Most data science/machine learning problems still involve structured data.
Unstructured data is all those “data” that can’t be easily classified and fit into a box such as photos, videos, emails.
We can query, manipulate (insert, update, delete), define, access the data with SQL. Besides these, SQL can also help in maintaining and even optimizing the databases.
SQL is more straight-forward to learn compared to many other programming languages.
If the job only requires basic SQL queries, you can learn as quickly as a few hours.
Yet, the more complicated queries and tasks will take at least a few weeks to master.
That’s why it’s important to set up the SQL environment on your computer to practice!
SQL servers are equivalent to the concept of relational databases. While people also use it to refer to Microsoft SQL Server, one of the most popular relational databases.
There are many different SQL servers/relational databases. Our purpose is to learn and practice SQL through a real server. So we would want a free and popular one.
We are going to recommend Microsoft SQL Server since it has free versions and is fast and reliable.
Yet, the employers usually trust somebody as long as one has experience with one of the servers. The SQL syntax/code is somewhat similar among servers.
Step #1: Download Microsoft SQL Server
To start the installation, let’s go to Microsoft’s website below.
Under the Developer, click the button “Download now” to save the file. The developer edition is a full-featured free edition in a non-production environment, which is perfect for us to practice.
Once the file is successfully downloaded, click and open the .exe file to run it. You should see a screen like below.
Next, we choose the “Custom” installation type out of the above three options.
Then, we also need to select the file location for installation before hitting the “Install” button.
Once this installation is complete, we should see a Downloaded Success message.
Now we are ready to install the SQL server.
Step #2: Install New SQL Server
Following the previous step, a new window with the SQL Server Installation Center will pop up.
We need to click “Installation” (left panel) and then select “New SQL Server stand-alone installation or add features to existing installation” (right panel).
Then the SQL Server Setup wizard will come up.
Under Specify a free edition, the Developer should already be selected. All you need to do is click “Next”, accept the license terms, and again click on “Next”.
The setup files will be installed. We might get a warning of the Windows Firewall, which is normal and can be ignored.
Then we can click “Next” to move to the next stage, which is Install Rules.
Within the Feature Selection, there’re many different options to choose from. We need to check the “Database Engine Services” as well as the “Full-Text and Semantic Extractions for Search” options and click “Next”.
The full-text option is necessary when creating the sample database in the later step, so please don’t skip it.
Within the Instance Configuration, we can specify an instance name that will be used later to connect to the SQL server.
For example, you can put just_into_ds for Named instance and hit “Next”.
For Server Configuration, we can just hit “Next” and move on.
In the Database Engine Configuration, we need to select the Mixed Mode as the Authentication Mode, specify the password, and click on “Add Current User”. Then, click “Next” and “Install”.
The installation takes some time to complete. Once it is done, you will receive an Install successful message like below.
Step #3: Install SQL Server Management Studio (SSMS)
Before we can start using SQL, let’s also install the SQL Server Management Studio (SSMS), which provides a Graphical User Interface (GUI) to manage the database.
Let’s go back to the SQL Server Installation Center. We can click the “Install SQL Server Management Tools” this time on the right side.
The center will redirect us to the Microsoft website, from where we can download the SSMS.
Once the installation is complete, you should see a file named SSMS-Setup-ENU in your Downloads folder. Simply open that file and Install it.
You’ll see a success message after it’s completed.
Now we have installed the SQL Server tools on the computer!
Step #4: Connect to the SQL Server
How do we access the server?
As mentioned earlier, let’s search for Microsoft SQL Server Management Studio App in the “Type here to search box” on the computer and open it.
Each time we launch a new session, the studio will ask us to Connect to Server. In the server name option, click the dropdown menu and select “Browse for more”. Expand the Database Engine by clicking the small “+” sign. It should show the instance name created earlier, select and click “OK”.
For the Authentication option, select “SQL Server Authentication”. Then we can put the default Login name (which is ‘sa’), and the password we created earlier.
Then we can hit “Connect”. The just_into_ds server that we had created should be on the Object Explorer panel (left side).
We have successfully connected with our SQL server!
Step #5: Create the Sample Database: AdventureWorks
Now before we can practice, we need a database that resembles the real world situation. In this last step, we’ll download a sample database from the Microsoft SQL Server Samples Repository.
There are three main samples, and we’ll be using the AdventureWorks OLTP (Online Transactional Processing). The AdventureWorks Database supports a fictitious, multinational manufacturing company called Adventure Works Cycles.
First, let’s set up a folder directory of “C:\Samples\AdventureWorks” on the computer.
Please make sure you have the exact directories of folders (names). Later this directory will be used to execute SQL code.
Then, click to download AdventureWorks-oltp-install-script.zip and extract the content to the C:\Samples\AdventureWorks folder.
Next, within the SQL Server Management Studio, select “File” from the top menu, then “Open”, then “File”. And choose to open the file C:\Samples\AdventureWorks\instawdb.sql.
You’ll see a lot of SQL codes within the file. Don’t worry about understanding them. Simply follow the below procedures to run them to create the AdventureWorks sample database.
If you have followed the previous steps, we should have enabled the Full-Text Search when installing SQL Server.
If not, you may run the SQL2019-SSEI-Dev file (from Downloads folder) again. And follow the same instructions to go through the setup process (to select the full-text option).
Within the SQL Server Management Studio, select “Query” from the top menu and click “SQLCMD mode” to enable it.
Finally, we can click the “Execute” button with a little arrow sign to run the code.
You should see that in the Message box, it shows the Query executed successfully with no errors.
You should have the sample database AdventureWorks on your SQL Server.
To check it, from the Object Explorer, click the little “+” sign next to your server name to expand it. Then expand the Databases too. You should be able to see AdventureWorks under the directory. There are many tables you can explore.
If you don’t see it there, try to click the blue circle to Refresh it.
Congratulations! Now you should have the environment for learning SQL, a critical skill for data science. You are one step closer!
Start practicing by following SQL Tutorial for Beginners: Learn SQL for Data Analysis and Quick SQL Database Tutorial for Beginners.
Leave a comment for any questions you may have or anything else.
Related “Break into Data Science” articles:
How to Learn Data Science Online: ALL You Need to Know
Check out this for a detailed review of resources online, including courses, books, free tutorials, portfolios building, and more.
How to Install/Setup Python and Prep for Data Science NOW
If you haven’t, get the other critical tool for data science: Python, which is also free and easy to install.
What are the In-Demand Skills for Data Scientists in 2020
In this article, we extracted information from Indeed job postings. You’ll see why we recommend setting up the data science environment with SQL and Python.
Before you leave, don’t forget to sign up for the Just into Data newsletter below! Or connect with us on Twitter, Facebook.
So you won’t miss any new data science articles from us!
