When you think about handling and processing the huge amount of data, what comes to mind? For many, thoughts of utilizing Python coupled with machine learning algorithms arise. What may not initially come to thought is the notion of using SQL instead. You might be scratching your head at this prospect of using SQL in Data Science or Bioinformatics when there are other alternatives. Or, perhaps, you’re not familiar enough with this particular language to jump to a conclusion. The fact of the matter is that SQL is a programming language you should familiarize yourself with if you’re looking to jump into the world of big data.
So why would somebody use SQL instead of the many alternatives? Simply put, SQL provides simplicity and robustness that you can seldom find anywhere else. Add to the equation that Data Science careers sometimes require more than handling big data. A big skill set that one can have is the ability to conduct database management on web applications; a feat for SQL and an RDMS (we’ll discuss this a little bit later.)
Database Introduction (Introduction to SQL)
For a thorough understanding of SQL and its potential role in Data Science, some basics are needed including an introduction to databases. First off, what exactly is a database? For simplicity, a database is just an organized collection of data. Within this collection, we have even more organization in the form of tables. Tables have specific bits of information stored inside them, and within these tables, there are individual columns that have even more specificity to them.
All of this may seem a little confusing, so we’ll go ahead and see a table, called “table1,” inside a database to clear things up.
(Word of caution: The database table and associated column names were created with simplicity in mind. You’ll probably never run across a table titled “table1,” or a column titled “address,” especially when dealing with large databases.)
This sample table contains some information about a fabricated client base. In our actual database, there will be other tables that contain more relevant information, but let’s pretend this is all we need for the moment. As we have mentioned, each column contains a particular characteristic and here we can see the values of clientID, address, city and state.
Every single row in our table contains specific data (in this case, a particular client), whereas the columns include universal values or traits.
SQL is the language, while a Database Management System (DBMS) is the software that contains and manages the data.
Something that a lot of people get confused with is when they hear things about MySQL, SQLite or NoSQL and don’t quite understand its relation to SQL. In our example, we showcased our database table inside a simple Excel file. In a real world example, your data will more than likely be stored in some other software dedicated to database management. This idea is what we refer to as Database Management Software, or DBMS for short. MySQL, SQLite, and NoSQL are all examples of DBMS. You should not worry about mastering DBMS’ until you get the hang of SQL itself. Most DBMS for SQL follow the same protocols, with some minor changes that you can learn later.
A particular kind of DBMS named a Relational Database Management System (RDMS) and uses a specific type of modeling called a relational model. The RDMS, in particular, is called MySQL and is a popular database choice for websites. In fact, it’s what our site uses for database management.
If you are lost with some of the technical jargon, just remember: SQL is the programming language, and a DBMS is the database system we will be using to manage our data.
Where Can I Practice My Code?
Since we aren’t going to go in-depth with the DBMS, you’re probably wondering how you’ll be playing around with SQL code. There are some programs you can download to do so, or you can utilize this free online SQL interpreter. This website allows you to test out some basic SQL code without having to download more complicated DBMS’.
The Almighty Select Statement
In any SQL or SQL in Data Science course, the first statement you’ll learn is select. It’s quite simple as to why this is the first statement you hear. In SQL, the objective is to alter and configure the database to suit your needs. To do this, you must select certain attributes and do something with them. However, just selecting something won’t do much. You’ll need to pick something, tell the system where you are selecting it from and then what you want to do with it. This concept of actually doing something with the data leads us to the from statement. Whenever you utilize select, you’ll almost always use from as well. Take a look at the following:
Say, for example; you want to choose all of the client’s addresses from the table example from above. You would utilize the following SQL statement (given that the table is named table1):
Select address From table1;
The results would yield you all of the addresses for every single row. You could use the same structure if you wanted to pull all of the ClientID’s or any of the other columns from the table.
Don’t Forget The Semi-Colon
The semi-colon in SQL denotes termination, so you’ll need to place it at the end of your SQL statement.
The Where Clause Makes Things Happen
While the select and from statements allow you to pick which specific data you want to handle, the where statement allows you to conduct the altering itself. There are numerous operators that the where clause can use to help you make things happen.
- Using the where clause for equality, non-equality, and showing greater than or less than. (=, <>, >, <.)
- Using the where clause to join other clauses.
- Using the where clause for mathematical calculations.
- Using the where clause to group data.
The list goes on and on for how you can incorporate where into SQL, but just remember that it allows you to make things happen within SQL.
Taking the Basics and Applying it to Data
Our introduction to SQL was overly simplified and was aimed at providing a very brief introduction to those who have never used SQL. If you’re eager to learn more, we recommend a free MOOC provided by Stanford Lagunita. I’ve personally taken this course, and it’s an excellent introduction to SQL!
We touched at the beginning of this article about some of the ways you might be using SQL in Data Science or Bioinformatics careers. Ultimately, it does depend on the particular job. You may never touch SQL once you are in your future career. Or, on the contrary, you may find that your position uses SQL extensively alongside other programming languages and software.
Due to the unexpected nature of whether or not you’re going to need SQL, it’s worth a shot to know it at some level. At the minimum, I would recommend you at least have a basic understanding of SQL and how to do simple database analysis and alterations. Again, you never know if this can come in handy.
Some careers may also not care about the specific programming languages, as long as you can conduct the data analysis that they need. Whether you’re using Python, Perl or SQL doesn’t matter nearly as much as whether or not you can perform the tasks.
If you have used any other SQL MOOC’s or have any useful materials for beginners to grasp this programming language, feel free to post the resources below! Be sure also to join us over at the YDSOA Community Forums as we discuss a wide variety of related topics!