DiscoverDataScience.org

The Data Scientist’s Toolkit: SQL

By Kat Campise, Data Scientist, Ph.D.

When it comes to all things machine-oriented, we need specific languages for communicating what we want the machine to do: calculate, extract data, store data, produce an image, search for a word or a sentence, etc. Some languages have broad capabilities (e.g., Python, C++, Java, R), whereas others have a narrower function, such as SQL.

Pronounced like the word “sequel,” the structured query language known as SQL is a particular method for interacting with a relational database (RDMS). If you peruse data science job postings, you’ll notice there is also NoSQL, which is used for non-relational databases and for the same purposes as SQL: storage, retrieval, and updating/writing data. While it is important for data scientists to understand the differences between relational and non-relational databases, especially since many companies may use MongoDB, CouchDB, Cassandra (or other non-relational database management systems), SQL continues to be one of the most popular programming languages within data science. So, having a solid foundation — if not expertise — in the language is still an in-demand skill for data science.

SQL: It’s Probably Older than Most Data Scientists

At the very least, SQL predates the title of data scientist by roughly 40 years when, at the dawn of the 1970’s, two Ph.D. students devised SQL based on the relational database model initially conceived by IBM researcher E.F. Cobb (for those of you who are information addicts, Cobb’s paper can be found here).

By the close of the decade, Oracle jumped on the bandwagon and created their own SQL which it began to offer to its customer base, and IBM quickly followed suit. After roughly 6 to 7 years of use, two standardization organizations, ANSI and ISO, issued an official “Database Language SQL” definition. If we view the continued use of SQL from the perspective that not all languages survive the test of time (Fortran, BASIC, Cobol, Lisp — while they may not be completely dead, you’ll be hard-pressed to find these in data science job descriptions), then SQL is still going strong. Of course, the hurricane of data that was triggered by our infamous internet of things may have been what saved SQL from its death march.

Why SQL Instead of Excel?

On the surface, the SQL vs. Excel question may seem harmless enough. After all, we want clean datasets with the data nestled comfortably in columns and rows (without NULL or missing values, of course). Prettified datasets are easier to understand and analyze. Excel and CSVs are still widely used in data science, but when the data exceeds one million rows and tens of thousands of columns, our nifty Excel workbook begins to malfunction.

The other consideration is that Excel is not a database management system. When you’re collecting terabytes, petabytes, and exabytes of gnarly data that may be structured, unstructured or semi-structured, then it needs to be prepared and stored in a database that can handle massive data collections. We do use Excel and CSV files in data science — R and Python both have an export to CSV/Excel function — but these are for generally used for smaller datasets, e.g., a sample population.

How Easy is SQL to Learn?

Most of the verbiage used in SQL should be familiar: SELECT, FROM, WHERE, AND, OR, NOT. Analogous to everyday language of speaking and writing, there are a set of rules — syntax — for how to connect the aforementioned query declarations. Also, the queries can become complex depending on the amount of data you’re querying and what you are trying to achieve through the query.

If you have no programming background, it may require more effort to understand when, where, and how to use the statements and operators (e.g., equal to, greater than or equal to, less than, etc.) In comparison to Python or R, SQL can be easier to learn since it’s a declarative language where you’re telling the system what to do without having to go through the tedium of listing the logical steps towards the end goal.

SQL Learning Resources

We’re in an open source world where SQL learning resources can be found through a simple Google search. However, for the more structured learners out there who don’t necessarily want to return to a university just to pick up a targeted skill, there are several options:

  • Coursera is an excellent resource for SQL courses and certifications: Excel to MySQL, Managing Big Data with MySQL, SQL for Data Science, and Introduction to SQL can be audited for free (no certificate), or you can earn a certificate for a nominal fee. If you’re curious about NoSQL, Coursera has an introductory course for the non-relational database query language as well.
  • edX is another solid avenue for increasing your SQL knowledge base. UC Berkeley and Microsoft (among others) have partnered with edX to offer SQL Server Database Administration, Using Non-Relational Data in SQL, and Managing SQL Database Transactions and Concurrency. Granted, many of these are geared towards the data engineering side of SQL, but having a data engineering background as a data scientist is a bonus — the more you know about building data pipelines, constructing and maintaining database infrastructures, and the ETL (extraction, transformation, and loading) process, the more marketable you will be.
  • DataCamp offers an Introduction to SQL for data science which you can start for free. If you’re always on the go, they provide an app that you can download onto your phone for snippets of practice time in between your usual daily activities.
  • Codecademy provides a limited amount of lessons free of charge, but for more in-depth practice they will prompt you to upgrade to their pro-level
  • When you need a refresher on SQL, w3schools.com has a selection of mini-tutorials that will take you through the basic and not-so-basic portions of the SQL syntax. It’s completely cost-free (except for your self-tutoring time investment).

Becoming a data scientist requires a commitment to learning (and mastering) several skillsets, and as technology swiftly moves forward there will be more to learn. But, once you have the foundational skills (including SQL) you’ll be able to quickly scale your expertise in all things data science.