Set up the Lahman Baseball database in sqlserver

The Lahman baseball database contains multiple tables on MLB players and their batting / pitching / fielding statistics for as far back as the data is known.  It's a fun and easy dataset to learn how to query. Best of all, I found a dataset already created for SQL Server that will have you up and running in minutes.  There's tons of information on querying the Lahman data, and there's courses on edx specifically on sabermetrics and querying the lahman data via sql or in R.


1) Goto https://www.sqlskills.com/sql-server-resources/sql-server-demos/
2) Download the baseball stats sample database, baseball_db.zip, and unzip.
3) Start SQL Server management studio, right click 'Databases' and select 'Restore Database'
4) On the General tab, select 'Device' and '...'
5) Add, and navigate to the downloaded BaseballData.bak
6) Select 'ok'
7) The data will be imported into your dataset.


Verification

1) Run the query:

use BaseballData;
select b.*
from   dbo.players p
inner join dbo.batting b on p.playerid = b.playerID
where p.namelast = 'Utley' and p.namefirst = 'Chase'
Order by yearID asc

2) Results should have batting information.
3) Search google for: chase utley baseball statistics
4) Search for specific numbers in the fields to validate that the data aligns with standard sources Always be skeptical of your data and independently validate when possible.

Download and Install SQL Server for Windows 10

Decide what version of SQL Server you want to install. 

SQL Server 2012

• Still frequently used in large corporations.  Use this if you are looking to learn SQL Server for immediate use in the work force

SQL Server 2014

• Mostly used in smaller companies.  Download this if you are familiar already with SQL and want to immediately get caught up on the latest SQL Server technologies.

Since my job uses SQL Server 2012, I want to download that.

Note: This youtube video walks through everything described below:

https://www.youtube.com/watch?v=vng0P8Gfx2g

1) Google: SQL Server 2012 Express Download, go to the Microsoft link

2) Decide what Bit to download.  I have a new computer, running windows 10, so try to do everything 64-bit.  For the casual learner, you want to make sure that the bit is the same as excel, in case you want to learn to connect to other data sources.

3) Download the following files (assuming 64-bit downloads):

• ENU\x64\SQLEXPR_x64_ENU.exe: the database engine
• ENU\x64\SQLManagementStudio_x64_ENU.exe: the development tools

4) Check to see if you need to download and install Microsoft .NET Framework 3.5 Service Pack 1

a) Press “Windows Logo” + “R” keys on the keyboard.
b) Type “appwiz.cpl” in the “Run” command box and press “ENTER”.
c) In the “Programs and Features” window, click on the link “Turn Windows features on or off”.
d) Check if the “.NET Framework 3.5 (includes .NET 2.0 and 3.0)” option is available in it.
e) If yes, then enable it and then click on “OK”.

If it doesn't exist, the install page provides information for download. I recently upgraded my computer to Windows 10, and this is one of the first programs I'm installing, so it appears that this comes default with computers now.

5) Run the database engine (SQLEXPR_x64_ENU.exe)
* Click yes as needed.
* Select New SQL Server standalone, as opposed to upgrade existing.
* Go through the Standard yes and update settings.
* When you get to the setup, select the defaults for Feature selection
* For Instance configuration, select 'Default Instance', since we can select the default because this is the only sql server database installed on this computer.  If there were more, you would have to select a named instance.  Multiple databases would be used to mimic corporate databases where different departments might have their own server.  You might want to install more than one if you wanted to test this out.
Default Instance ID name: MSSQLSERVER
* The rest uses default settings for installation.

* Note: if your install becomes corrupted, restart and rerun the setup, selecting the repair button in the Maintenance tab.


Configure
1) In the run bar, enter 'sql server configuration manager'
2) In the left panel, select 'SQL Server Services' and in the right, right click on SQL Server, and select start.  The state should say 'Running'

Install SQL Server Management Studio
 1) Run ENU\x64\SQLManagementStudio_x64_ENU.exe.  The setup will seem familiar to the previous.  It's different.
2) On installation type, Keep the 'Perform a new installation of SQL Server 2012'.
3) In the features, select 'Management Tools - Basic' and 'SQL Connectivity SDK'


Verification
1) Open 'SQL Server Management Studio' in the apps, or searching in the run bar
2) The information should be autopopulated if you selected windows authentication.  Select connect.
3) Select New Query from the ribbon at the top of the screen.
4) Type in the query below, highlight it all at once, and select 'Run' or F5

CREATE TABLE testmydb
(
mycounter int,
myname varchar(100)
);
insert into testmydb(mycounter, myname) values (1,'BOB');
insert into testmydb(mycounter, myname) values (2,'JOE');
insert into testmydb(mycounter, myname) values (2,'ED');

select * from testmydb


5) Results should appear in the test window.  Save your query (or not) and exit Sql Server Management Studio.
6) Follow steps 1-3 in verification above
7) Type in the query below:

SELECT *


FROM testmydb;

8) Verify the database table exists.  It does, feel free to drop.  SQL Server 2012 was successfully installed!

DROP TABLE testmydb;