Beginning Learning SQL – Post 2 – User Creation and Sample Database Installation

Well, having finally found a sample script for a database (I started writing my own, but between my job and my life, it is a little too cumbersome – I wrote about 200 lines and I was like… someone else has to have written this already) we are ready to begin learning SQL – this is only provided that you followed post 1, and that you have a server (either virtualized or a real server with either SQL Server 2008, 2008 R2, or 2012 on it). See my earlier post here if you need help getting set up.

To install this sample database, you’ll need a System Admin account as we are creating schemas, databases, tables, constraints, functions, etc… We’ll get in to these at a later time, but for the time being, installing this database will suffice. I am going to assume that you are competent enough in computers to have opened a firewall port, installed SQL Server Management Studio on a second computer (or else that you are comfortable remoting in to you SQL Server to use SSMS). You can do all of this through SSMS itself on the actual SQL Server with the local administrator account. Follow the how to here on how to do that:

Log in to your local SQL Server box (either a VM, whatever as a user with Administrative rights). In my case, all I did was just use the standard Administrator Account that is default in Windows Server 2008 R2. Open up SQL Server Management Studio and log in using Windows Credentials.

Once you are logged in, open the Security Node and then the Logins node on the left. Right click on Logins and the first tab that comes up is “Create New Login”.  

Create a new login, save and you’re done.

You can now log on remotely to your box with some SQL Server Authentication credentials (provided you’ve opened port 1433 on your servers firewall). Some people prefer using solely Windows Authentication, that’s fine. If you want to do it that way, you can read up on that here.

Once you log in, you’re going to be looking at  a program that (depending on your installation options) looks something like this:

Pretty nifty. To install our sample database, download THIS – this is a bunch of SQL Code stuffed in to a zip file. I would list it as code on this site, but alas, its almost 7000 lines long and its much easier to zip it.

Unzip it and you’ll have a file called LearningSQL.sql. Double click on it and it should open in to your SSMS Query Editor.

To execute it, you can either press the “Execute” button at the top left side of the SSMS application, or hit F5. I would strongly suggest using F5 – you’ll want to get used to that if you are looking at becoming a DBA. This script may take a few moments to run (depending on how much power your server has, network speed, etc…) though it shouldn’t take longer than a minute unless you are using a toaster as your SQL Server.

Within the next few days we will learn some pretty basic queries on how to recall data from these databases you created – but for now you can explore the data we uploaded, learn your way around SSMS, and get comfortable with some shortcuts, because they will be extremely handy.