SQL Environment

Setup Instructions

Downloading and Setting up SQL

• Download and install SQL Server Management Studio (Free)
• Once installed create a server (be careful of permission and passwords, easy to lose track of)
• Once a server is created, then you can create the database in which you wish to operate. We make a new database each year in order to stay organized.

Creating Tables

• Brainstorm which columns you will need to store. Think through what is important, and how the data will be collected. When you create the fields, keep an eye on your data types as this will be important down the road.
• Once you know what you want your data structure to look like. Create your query that will create the table.
• Execute this query to create the table.

Creating a View

• A view is similar to a table, but it is created through the use of a saved query. By building in things like compiled stats like averages, max, min, accuracy and grouping them by team across the event, we can see a holistic look of a team without having to run individual queries every time.
• Brainstorm which data points you would like to gather about teams
• Write a query to create the view and execute it.
• The new view is created

Helpful tips

• Be mindful of data types as you try and compute certain fields. Dividing integers by integers will not lead to a decimal result and will round to zero. Casting as a float or another data type will solve this problem.
• Make sure to name fields and columns in an easy to understand way.
• COMMENT, COMMENT, COMMENT your code and save your work. You will never know when you will need to go back to it at a later date.
• Try and use XRef tables whenever possible. For example, storing a value of 1/2/3 and knowing that 1=Low, 2=Mid, and 3=High will give you more flexibility when querying or compiling data. You can always add a description table with 2 columns with the value and description as well.

Header 2