The art of SQL algorithms

SQL is the standard language for querying and manipulating relational databases. Although SQL is ubiquitous for simple analytical queries, it is rarely used for more complex computations, such as those in AI and machine learning. However, utilizing SQL for these computations offers significant advantages over non-SQL methods. Specifically, SQL keeps computations close to the data, requires minimal user permissions, abstracts the underlying hardware away, and increases software portability. Indeed, these benefits are achievable because SQL provides constructs that enable all kinds of computations. In the first part of the thesis, we show how to translate imperative code to SQL — enabling complex SQL-only algorithms. The performance of the resulting SQL algorithms depends heavily on the underlying DBMS. Notably, query engines like HyPer can achieve very high performance — in some cases even outperforming state-of-the-art linear algebra libraries like NumPy. In the second part of the thesis, we focus on large tensor expressions, crucial in fields such as AI and physics. Although external libraries are usually used for their evaluation, we demonstrate that SQL constructs can be effectively employed for this purpose. We show how these tensor expressions can be translated into efficient and portable SQL code, opening up new data processing possibilities within SQL. We illustrate these new possibilities with four examples: querying triplestore data, solving Boolean satisfiability problems, performing inference in graphical models, and simulating quantum circuits.