Using SQL for Data Science

Why SQL?

How is SQL (pronounced see–kwell) relevant to you as an aspiring data scientist? And what is SQL anyway?

SQL, or Structured Query Language, is a special-purpose programming language for managing data held in relational database management systems. Almost all structured data is stored in such databases, so, if you want to play with data, chances are you’ll want to know some SQL.

Some of what you can do with SQL—data insertion, queries, updating and deleting, schema creation and modification, and data access control—you can also accomplish with R, Python, or even Excel, but writing your own SQL code is more efficient and can yield more easily reproducible scripts. Learning SQL can also give you a leg up on folks transitioning, often with limited database experience, into the data science industry from academia.

Six Handy SQL Features for Data Scientists

Here, adapted from a list on the Yhat blog, are “some awesome things you can do with SQL.”

• Generate queries from a query: Basic string concatenation makes it easy to generate en masse queries that use data in a database to fetch data found in another system.
• Handle dates: “Fantastic date functions” exist to meet all your formatting and type conversion needs.
• Text mining: Yhat recommends going as far as you can with SQL’s built-in string functions before turning to a scripting language.
• Find the median: Since there’s no built-in aggregate function for median, Yhat provides the code.
• Load data into your database with the \COPY command. (Note: this is specific to PostgreSQL's psql client rather than standard SQL — other database systems, like MySQL or SQL Server, use their own bulk-load commands.)
• Generate sequences: Use the generate_series function to create ranges of dates and times and to handle time series and funnels.

Two additional features are worth knowing well beyond this list, since they come up constantly in real-world data science work:

• Window functions let you perform calculations across a set of rows related to the current row — running totals, rankings, and period-over-period comparisons — without collapsing the result into a single row the way a standard GROUP BY does.
• Common table expressions (CTEs), written with the WITH keyword, let you define a temporary named result set to reference elsewhere in a query. They make complex, multi-step queries far easier to read and debug than deeply nested subqueries.

It's also worth knowing that most data scientists today write SQL against cloud data warehouses like Snowflake, BigQuery or Redshift as often as traditional databases like PostgreSQL or MySQL. The core language is largely the same across all of them, though each has its own dialect-specific functions worth learning once you know which platform you're working with.

Interested in a different career? Check out our other bootcamp guides below:

• Data Science Bootcamp Guide
• Data Analytics Bootcamp Guide
• Coding Bootcamp Guide
• FinTech Bootcamp Guide

Learn SQL Online

Resources for learning SQL abound, ranging from text-based to interactive and providing entry points for learners with varying levels of programming and database experience. Here’s a sampling of the online offerings:

• Intro to SQL: Querying and managing data from Khan Academy covers SQL basics, more advanced SQL queries, relational queries in SQL, and modifying databases with SQL.
• SQLBolt is a free, interactive tutorial geared toward beginners, walking through SQL basics with exercises you run directly in the browser.
• SQLCourse.com allows you to practice what you learn in its tutorial using an online SQL interpreter.
• SQL Tutorial from Tutorials Point gives beginners a quick start with SQL.
• Learn SQL the Hard Way is written for people with no prior database, programming, or SQL knowledge, and promises to teach you “80% of SQL you probably need to use it effectively.”

Happy querying!