SQL Commands Every Data Scientist Should Master

SQL (Structured Query Language) is an indispensable skill for data scientists. A recent survey by Burtch Works found that SQL was the most commonly required skill for data science and analytics positions, with 70% of job postings listing it as a prerequisite.[^1]

Being able to efficiently retrieve, filter, join, aggregate, and manipulate data using SQL allows data scientists to extract valuable insights from relational databases. While the rise of big data and NoSQL databases has changed the data landscape, SQL remains a core competency.

In this in-depth guide, we‘ll cover the essential SQL commands and concepts that every data scientist should know, with clear explanations and practical examples. We‘ll also explore how SQL integrates with other data science tools and consider performance best practices.

1. The SELECT Statement

At the heart of SQL is the SELECT statement, which retrieves data from one or more tables. The basic syntax is:

SELECT column1, column2, ...
FROM table_name;

To select all columns, you can use the * wildcard:

SELECT * 
FROM table_name;

Column aliasing allows you to assign a different name in the result set:

SELECT column_name AS alias_name
FROM table_name;

Avoid using SELECT * in production code as it can return unnecessary data and make queries harder to maintain. Be explicit in the columns you choose.

2. The WHERE Clause

The WHERE clause filters rows based on a condition:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Comparison operators in the WHERE clause include =, <>, <, >, <=, and >=. Logical operators like AND, OR, IN, BETWEEN, and LIKE allow you to combine conditions.

SELECT * 
FROM employees
WHERE department = ‘Sales‘ AND salary > 50000;

Use parentheses to make the logical order of operations clear when combining multiple conditions.

3. Joining Tables

Joins combine rows from two or more tables based on a related column. The main types are:

• INNER JOIN: Returns records with matching values in both tables
• LEFT JOIN: Returns all records from left table and matched from right
• RIGHT JOIN: Returns all records from right table and matched from left
• FULL OUTER JOIN: Returns all records when there is a match in either table

SELECT *
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

The ON keyword specifies the join condition. Table aliasing keeps queries concise.

Joining is an expensive operation that can slow queries down as data size grows. Be judicious in the tables you join and use filters to limit the data processed.

4. Grouping and Aggregating Data

The GROUP BY clause groups rows by common values in specified columns. It‘s frequently used with aggregate functions:

Function	Description
COUNT()	Number of values in the group
SUM()	Total of numeric values in the group
AVG()	Average of numeric values in the group
MIN()	Minimum value in the group
MAX()	Maximum value in the group

SELECT department, COUNT(*) as num_employees
FROM employees
GROUP BY department;

The HAVING clause filters groups based on a condition, like WHERE does for rows.

Aggregations can be powerful for understanding data distributions and computing summary statistics. But they can also hide insights in individual rows, so explore both granular and aggregated views.

5. Sorting Results

The ORDER BY clause sorts result rows by one or more columns:

SELECT *
FROM products
ORDER BY price DESC, name;

DESC sorts descending, while the default ASC sorts ascending. Columns are ordered left to right.

Only include ORDER BY if row order matters for your analysis. Sorting large result sets can be costly.

6. Subqueries

A subquery is a SELECT nested inside another statement:

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);  

This returns employees earning more than the company average by using a subquery to calculate the average inside the WHERE clause.

Subqueries can go in SELECT, FROM, and WHERE but be cautious about overusing them as they can impede readability. CTEs or temp tables are sometimes clearer.

7. Set Operations

Set operations combine result sets of multiple queries:

• UNION: Concatenates results and removes duplicates
• UNION ALL: Concatenates results (keeping duplicates)
• INTERSECT: Returns distinct rows in both result sets
• MINUS: Returns distinct rows in first result set but not second

SELECT first_name, last_name 
FROM employees
UNION
SELECT first_name, last_name
FROM contractors;

Queries in a set operation must return the same number of columns with compatible types.

Set operations provide a concise way to compare distinct result sets. But they do require materializing each full result in memory or disk.

8. Window Functions

Window functions compute values across a set of rows relative to the current row. Some key functions include:

• Ranking: ROW_NUMBER(), RANK(), DENSE_RANK()
• Analytics: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
• Aggregates: SUM(), AVG(), COUNT() etc. with OVER()

SELECT name, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_salary_rank
FROM employees;

This ranks employees by salary within each department using RANK() with PARTITION BY.

Window functions offer a powerful way to compare rows or compute running totals and averages. But they can tax system memory for large windows.

9. Integrating SQL with Python and R

For data scientists working in languages like Python or R, integrating with SQL is crucial. Libraries like pandas and dplyr can connect to databases and issue SQL queries:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(‘sqlite:///employees.db‘)
query = ‘‘‘
    SELECT name, salary, department 
    FROM employees
    WHERE salary > 50000
‘‘‘
df = pd.read_sql(query, engine)

This Python snippet uses SQLAlchemy to connect to a SQLite database and pandas to read the SQL query result into a DataFrame.

The ability to incorporate SQL querying into a data science workflow involving Python or R code for machine learning unlocks huge potential for analyzing relational data at scale.

10. The Future of SQL in Data Science

As the fields of data science and machine learning evolve, SQL endures as a key skill for wrangling and exploring data. SQL is adapting to the world of big data through innovations like:

• Distributed SQL engines (Presto, Hive, Impala, BigQuery)
• Cloud data warehouses (Snowflake, Redshift, Azure Synapse)
• Support for semi-structured data types (JSON, arrays, structs)
• Integration with data lakes and lakehouses (Databricks, Presto)
• SQL-based feature stores for machine learning (Hopsworks, Feast)

Modern SQL engines can query petabytes of data across clusters. Cloud data warehouses separate storage and compute to scale. Support for complex data types handles diverse data.

And technologies like Spark and Dask allow large-scale, distributed data manipulation in Python and R with a DataFrame abstraction backed by optimized SQL execution.

Conclusion

SQL remains an essential language for data scientists to extract, transform, and analyze data. The commands and concepts we‘ve covered empower you to tackle a wide range of data challenges:

• Retrieving and filtering data with SELECT and WHERE
• Combining data with JOINs
• Summarizing data with GROUP BY and aggregations
• Manipulating result sets with ORDER BY, subqueries, and set operations
• Computing relative values with window functions
• Integrating SQL with Python and R for end-to-end data science

To deepen your SQL expertise, continue practicing on real-world datasets. Explore query optimization techniques, database design principles, and the SQL documentation for your database system.

Consider how you can incorporate SQL into a machine learning workflow, perhaps using a notebook environment like Jupyter or Databricks to prototype queries before productionizing as ETL jobs or dataset creation scripts.

The more fluent you become with SQL, the more efficient you can be in exploring data, validating assumptions, and generating valuable features to feed into statistical analysis or predictive models.

SQL has been a staple of working with data for decades, and its future in data science looks bright as tools evolve to handle the scale and complexity of modern machine learning applications. Embrace it as a powerful addition to your data science toolbox.

[^1]: Burtch Works. (May 2022). The Burtch Works Study: Salaries of Data Scientists. https://www.burtchworks.com/wp-content/uploads/2022/05/Burtch-Works-Study_DS-PAP-2022.pdf