How to Download and Install SQL Server and Sample Database: Step-by-Step
 Start Learning SQL by setting up a Professional Environment

Lianne & Justin

Lianne & Justin

Share on twitter
Share on linkedin
Share on facebook
Share on email
sql server database colorful storage
Source: Pixabay

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.

What is SQL?

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.

Is SQL a programming language?

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.

Why learn SQL? Is SQL required for data science?

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

What is the structured data that SQL can handle?

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.

What is SQL used for?

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.

Is SQL easy to learn? How long does it take to learn SQL?

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!

What does SQL server mean?

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.

Why installing Microsoft SQL Server? Is it free?

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.

Microsoft SQL Server Install developer edition

Once the file is successfully downloaded, click and open the .exe file to run it. You should see a screen like below.

Microsoft SQL Server Install developer

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.

Microsoft SQL Server Install developer file location

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.

sql server installation center

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.

sql server setup

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.

sql server setup install rules

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.

sql server install feature selection

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”.

sql server install instance configuration

For Server Configuration, we can just hit “Next” and move on.

sql server install server configuration

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”.

sql server install database engine configuration

The installation takes some time to complete. Once it is done, you will receive an Install successful message like below.

sql server install complete

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.

download sql server management studio

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.

install sql server management studio

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”.

connect to sql server database engine

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.

connect to sql server

Then we can hit “Connect”. The just_into_ds server that we had created should be on the Object Explorer panel (left side).

sql server connected

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.

path for sample database

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.

open file in sql server management studio

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.

set sqlcmd mode in ssms

Finally, we can click the “Execute” button with a little arrow sign to run the code.

execute sql code

You should see that in the Message box, it shows the Query executed successfully with no errors.

query executed successfully

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.

sample database adventureworks created

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!

Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on facebook
Facebook
Share on email
Email
Lianne & Justin

Lianne & Justin

Leave a Comment

Your email address will not be published. Required fields are marked *

More recent articles

Scroll to Top
We use cookies to ensure you get the best experience on our website.  Learn more.