SQL for Analytics—Expanding Analytics in Relational Databases
Published byO'Reilly Media, Inc.
The SQL language has been a mainstay in database environments since its inception in the early 1970s. But even with such a long and storied history, many people mistakenly view SQL as nothing more than a means for accessing and writing data stored in a database and performing rudimentary analysis. However, today’s modern database platforms offer powerful analytics capabilities beyond the more fundamental table aggregations and simple joins with which most people associate SQL. Indeed, common table expressions, windowing functions, recursive queries, and cross joins make SQL a powerful platform for data analytics. In addition, subqueries and exotic join patterns can transform data in less obvious ways, and windowing functions can flexibly aggregate on contexts not possible with GROUP BY. And there are yet more benefits: performing intensive analysis with SQL also allows you to put the burden of churning data on the database engine, which is optimized for that purpose.
In this learning path, which is intended for those who are comfortable with basic SQL operations and analysis and familiar with INNER JOIN, LEFT JOIN, and GROUP BY operators, your host, Thomas Nield, shows you how you can use SQL to handle complex data analysis tasks. You’ll also learn how to invoke SQL from Java, Python, and R so that your applications and data science models can work with data sources directly. In addition, Thomas explains database vulnerabilities such as SQL Injection attacks and how you can defend against them. When you’ve completed this learning path, you’ll know how SQL can answer most common analytics questions—and do it more efficiently.
What you’ll learn—and how you can apply it
Through this learning path, you will learn:
- How you can use SQL for deeper and more complex analytical tasks, including windowing functions, common table expressions, recursive queries, and cross joins
- When to use SQL versus another technology (like R or Python) for a given task
- Common database vulnerabilities via SQL injection and how to prevent them
And you’ll be able to:
- Flexibly derive sets of data and query off of queries
- Create more flexible and legible SQL using tools like common table expressions
- Use SQL joins, including recursive and cross joins, in less obvious and powerful ways
- Take advantage of windowing functions to create contextual aggregations
- Use SQL with Python, R, or Java
This learning path is for you because…
- You’re an analyst or data science professional with fundamental SQL proficiency who wants to use SQL for more advanced analysis
- You’re a programmer or developer who needs to process large amounts of analytical data and wants to take advantage of more advanced SQL capabilities to put that work back on the database
- You’re a database administrator who wants to better understand advanced analysis features like windowing functions
- You should be proficient in basic SQL operators, including WHERE, GROUP BY, INNER JOIN, and LEFT JOIN
- If you are not familiar with any of the basic SQL operators, you should view the SQL Fundamentals learning path, also hosted by Thomas Nield, as a primer
Materials or downloads needed in advance:
- Code repository for the course: https://resources.oreilly.com/learning-paths/sql-for-analytics.git
- A SQLite client, either SQLiteStudio (https://sqlitestudio.pl/) or SQLite Online (https://sqliteonline.com/) is recommended