Database playground

Preparations

To get our own SQL up and running we are gonna need few things:

  1. SQL Server
  2. SQL Management studio (SSMS)
  3. Database filled with data

If you’re not preparing for a job in SQL and just want to try some SQL code, then perhaps this might be enough.

 

https://data.stackexchange.com/stackoverflow/query/new

 

It’s a web portal where you can write queries directly agains the sample Stack Overflow database.

 

Everyone else, read on.

 

 

1. SQL Server

This is the server we will connect to. Normally companies have it on separate machine, but we will install it locally.

 

We have a choice between Express and Developer edition. Both are free and since Developer edition has a bit more functionality, we’re gonna go with that.

 

Download link https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Installation

For speed we’re gonna go with the Basics installation. You can always change the details later.

1/3 Select the basic settings
1/3 Select the basic settings
2/3 Choose your instal location
2/3 Choose your instal location
3/3 Installation complete!
3/3 Installation complete!

2. SQL Management Studio (SSMS)

With this program we will connect to our newly installed SQL server. This is the tool you will use the most when writing SQL code. Always download and use the latest stable version, it doesn’t matter which version of SQL Server you’re using.

 

Download link https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017

Installation

This installation is straightforward – no screenshots needed.

3. Database

We need some data to run our queries on.

Lucky for us, Microsoft provides us with that as well. We are gonna download Adventure Works database.

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

 

I’d recommend AdventureWorks2016_EXT.bak. You can choose any version, that is same or lower year than that of your SQL Server (ours is 2017).

Also be careful not to download the files that containt DW in their name – that means DataWarehouse and it has data stored differently than we need to.

Installation

This will require us to fire up SQL Management Studio (SSMS) and connect to our SQL Server.

I will write out the text, but there is a screenshot guide below.

  1. Open SSMS studio
  2. Connect to our SQL Server
    1. If you followed basic install it will be the name of your machine
  3. Open Object Explorer
    1. It might be opened as default
    2. If not use hotkey F8
  4. Expand the server, right click on the Database folder
  5. Choose option “Restore Database“
  6. In the popup on the “General” page select radio button “Device”
    1. Click on the ellipses …
    2. Click on “Add“
    3. Navigate to folder where you downloaded AdventureWorks2016_EXT.bak
  7. Back in the popup you should see the table populated.
  8. Click OK and it’s done, we have our database.
1/5 Connect to your SQL Server
1/5 Connect to your SQL Server
2/5 In Object Explorer right click on the Database, select Restore Database
2/5 In Object Explorer right click on the Database, select Restore Database
3/5 Select Device and click the ellipses
3/5 Select Device and click the ellipses
4/5 Click Add and navigate to folder with AdventureWorks database
4/5 Click Add and navigate to folder with AdventureWorks database
5/5 You should see the table populated
5/5 You should see the table populated

Looking forward

Next time I’ll write about basic navigation around the SSMS with some useful tips and tricks.