SQL for Performance Analysis – What’s it all about?

SQL isn’t new but it it is finding it’s way into more and more job applications. If you want to separate yourself from the 1,000’s of other applying for positions having programming and SQL skills can be a big help. In fact Ben Alamar (who knows a lot about sports analytics), said in a recent piece titled So you want to work in sports analytics… stated that ‘SQL is must these days ‘.

Q & A with Kris Wenzel

So with that in mind I wanted to give you an idea of how to get started. Below is a Q&A I did with Kris Wenzel, he runs a site called Essential SQL and it’s full of great advice and even a training programme to get started. Enjoy and check out Kris’s site and training programme, which is free.

Rob: A lot of sports teams in Europe still manage all their data in Excel/Numbers what are the main advantages of a Database?

database-searchKris: Excel is a great tool to for analyzing facts and figures; however, it is best used for reporting and not necessarily storing and managing the data. There are a couple of reasons why it is better to use a database to store your information:

  1. Definition – A database provides structure for your data. The database can be set up to enforce that only certain data is allowed in fields. This can be done by restricting input to certain data types, such as a date, or limited to set of values using foreign key constraints. These constraints simply mean you can define that a field only can use values found in another table.
  2. Concurrency – A database allows multiple people to modify and access the database at the same time.
  3. Consistency – A database can use transactions to ensure that only whole sets of data are entered. For instance, we can set it so that only games statistics are saved only if both the home and away team entries are completed.
  4. Scalability – As your data grows, a database makes it easier to manage the data.

Rob: Can you explain to a complete beginner what SQL is?

KAWTwitterSQL is short for Structured Query Language. It a language specialized for querying and managing databases. The language has its roots within IBM. In general SQL is split into two major pieces: 1) DML – Data Manipulation Language, and 2) DDL – Data Definition Language. DML is used to query and modify data. DDL is used to define the database.

Familiar statements, such as SELECT, INSERT, UPDATE, and DELETE are considered DML Statements; whereas, CREATE and ALTER are considered DDL. This is a general standard for SQL, and for the most part it is consistent across database vendors, such as Microsoft SQL Server, and Oracle; however, each vendor has introduced their own flavor into the language.

Rob: For someone starting from scratch where should they start to learn SQL?

It makes sense to first start to learn how to query data. Just by focusing on the SELECT statement you can touch upon so many database concepts. This only helps you learn other new concepts.

Start out with a simple select statement and build on to it by incorporating the WHERE clause, ORDER BY, GROUP BY, HAVING, and then ultimately JOINS.

Rob: Do you have any good SQL based resources you would like to share?

BI-2012-Book-Cover

For MS SQL Server administrative topics I really like Brent Ozar’s site. The topics are spot on and they really have a great sense of humor, which I appreciate. Regarding the SQL language, I frequently refer to the Books Online for SQL Server. They are somewhat dense, but, if you need to figure out a statement’s syntax, they are spot on. For general questions I also like StackOverflow.com

Rob: Is database a good field (good skill) to be entering today?

I think the database fields is a really great field to enter. There are so many areas opening up that require database skills. In particular, Business Intelligence is a hot topic. SQL skills are a good foundation for this. I recently saw an article on mashable.com where DBA’s are one of the top eight tech jobs of 2014.

But SQL isn’t just for DBA’s!

I generally feel that developers and system administrator alike can benefit from learning SQL. Obviously, the more developers understand databases, the better queries they’ll write, and the more efficient their apps will be. If you get good at SQL you can get some really cool data that isn’t available in canned reports. I done this with tools such as Solar Winds as well as Microsoft SCCM.

Rob: Can you tell me a bit about your site?

I want essentialsql.com to be the “go to” site for beginners. I’m hoping to attract anyone who has a desire to learn SQL. My goal is to tackle explaining some of the fundamental concepts in simple English.

I enjoy explaining things, and have always had a knack for breaking down complicated subjects into simple parts. I’m trying to steer clear of jargon, for the sake of jargon. Of course you have to talk in DB terms, but jargon, for jargon sake, has no value. I want to steer clear of that.

The site’s current focus is on SQL, I’m trying to keep it vendor neutral and just focus on the basics such as the SQL SELECT statement, normalization, and joins.

When visiting the site, I recommend checking the Getting Started page first.

Rob: And your training course?

I currently have a free training course I’m offering that covers the basics of SQL. The course contains written materials, instructional videos, and exercises.

The course is still being developed, but so far it covers the basic elements of the SELECT statement. I recently published a module on the GROUP BY clause, which is used to summarize data. I’ve received a lot of feedback from readers, and it seems most people get hung up on sub queries and joins. Those will be the next two topics I cover. I’ve also put together some modules on database normalization. I feel it is important to understand the why a database is split up into many tables in the first place. If you understand the gist of that, then it makes more sense when you try to join, or piece it back together.

Over time I’ll cover other statements, such as INSERT and UPDATE. My goal is to provide my readers with a free resource learn the fundamentals of database queries.

If you’re interested in the training, you can find all the modules here.

No Responses

  1. Pingback: MOOC & Sabermetrica101 November 5, 2014

Add Comment