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;

Using Excel 2016 to connect to a SQL Server Database

To connect to a SQL SERVER database in Excel:

1) Follow instructions to make sure the database engine is running here:
http://edgallagher.blogspot.com/2015/10/start-sql-server-2012.html
2) Open Excel 2016, select Data > From Other Sources > From SQL Server

3) This will prompt you to enter a Server Name.
4) Start up SQL Server Management Studio.  Copy the Server Name from the connection, and Paste into the excel prompt.

5) If The authentication uses Windows Authentication, select the same for excel, otherwise enter the login and password from SQL Server.

6) Select 'Next'.

7) Select the database you are interested in from the dropdown, and select the tables you would like to use.  This portion can be modified later.

8) Select 'Next'

9) This process will create a connection settings file, Fill out as needed.

10) Finally, select what your final output should be.  Whether to view the data via Table, or to input into a pivot table Report.

Start SQL Server 2012

This starts the database engine so when you open SQL Server Management Studio it can connect to the database.

Go to Start > Control Panel > Administrative Tools > Services
Scroll down to SQL Server(SQLEXPRESS), Right click, and select 'Start'

download, install, and query an oracle database in minutes

1. Determine what bit version you want to download.  In my case, I use SQL Server 64-bit, and excel 64-bit, so I want oracle 64-bit
2. google "oracle database 11g free 64-bit"
3. I first am experimenting with the oracle express edition.  I'll only be using my home database for testing connection that I otherwise would not feel comfortable with on work databases.  Plus I have control over settings and configurations that might otherwise not be possible.  
4. Download and install.  Installation says is 70mb
5. It will prompt for a database password, and installation will complete.

SQL Developer

The oracle database installation does not come with an IDE client.  SQL Developer is a client provided free from oracle that is easiest to install and set up.

1. Google for oracle sql developer download.  Once again, download the 64-bit version.  I am downloading sqldeveloper-4.1.1.19.59-x64
2. Unzip.  

Start the database

1. In oracle, start the database.  Oracle 11g Express Edition -> Start Database
2. Start Sqldeveloper in the unzipped directory
3. Go to connections, add new connection.  The username is SYSTEM, and the password is the one created above.  The defaults worked fine, but were:

• connection type: basic
• role: default
• hostname: localhost
• port: 1521
• SID: xe

     4.  Select "Test" to test the connection.  Enter a connection Name ("oraclepc") Then "save password" and "save".

     5.  Connect to the database.  See if everything works by creating, inserting, and querying the data.

CREATE TABLE test_db( 

fld01 VARCHAR2(5),

fld02 VARCHAR2(5),

fld03 DATE

)

insert into test_db (fld01, fld02, fld03) values ('A','1','20-AUG-2015');

insert into test_db (fld01, fld02, fld03) values ('B','2','20-AUG-2015');

insert into test_db (fld01, fld02, fld03) values ('C','3','20-AUG-2015');

SELECT  *

FROM    test_db

WHERE   fld03 > '01-AUG-2015';