# SQL Techniques for Performing Operations on Matrices  atrices are very useful mathematical objects that science and technology professionals use to describe real-life scenarios and build abstract models for those scenarios. You will find matrices used in economics and statistics, in cryptography and genetics, and in computer graphics. Unfortunately for database developers, SQL and SQL Server do not support direct operations on matrices. However, because tables and matrices share the same structures, SQL allows easy manipulations with matrices. This article demonstrates a few SQL techniques for performing some basic operations on matrices.

Some Matrix Operation Definitions
A matrix is a rectangular array of elements, where the elements can be symbols, symbolic expressions, or numbers (see Figure 1 below): Figure 1. Matrix Representation: A matrix is a rectangular array of elements, which can be symbols, symbolic expressions, or numbers.

In general, matrix A in Figure 1 can be denoted by the following: In this denotation, m is the number of rows and n is the number of columns in the matrix A. Notation m x n represents the size of the matrix, so matrix A can also be denoted by Am x n or by [A]m x n.

The first matrix in Figure 1 has three rows and four columns. Therefore, the size of that matrix is 3 x 4. The size of the second matrix in Figure 1 is 3 x 3. This matrix is also called a square matrix, because the number of rows is equal to the number of columns.

Each element of the matrix Am x n is denoted by aij, where the row number is 1 ? i ? m and the column number is 1 ? j ? n. The element a23 of the first matrix is located in the third column of the second row. Similarly, you can find the location of element a23 in the second matrix. The value of that element is a23 = 20.

Simple Operations on Matrices
Here are some simple operations you can implement on matrices:

Matrices A and B can be added, if they are the same size (e.g., m x n). The matrix sum C = A + B also will be a m x n matrix, where for each element cij = aij + bij and 1 ? i ? m and 1 ? j ? n. You would use this solution: Matrix addition is commutative (e.g., A + B = B + A) and associative (e.g., C + (D + E) = (C + D) + E).

2) Matrix Subtraction
Two matrices (A and B) can be subtracted if they are the same size (e.g., k x l). The matrix difference D = A ? B will also be a k x l matrix with the element dij = aij ? bij, where 1 ? i ? k and 1 ? j ? l.

To subtract matrix B from matrix A: You would use this solution: Matrix subtraction is neither commutative nor associative.

3) Matrix Scalar Multiplication
If A is an m x n matrix and the constant k is a number, then the scalar product of k and A is a new matrix B = kA, where bij = kaij and 1 ? i ? m and 1 ? j ? n.

To find B = 5?A, where: You would use this solution: Scalar multiplication is commutative and associative:

``k(AB) = (kA)B = A(kB) = (AB)k``

4) Matrices Multiplication
Two matrices (A and B) can be multiplied if they are compatible, meaning that the first (left) matrix has as many columns as the second (right) matrix has rows. If matrices A and B meet this condition, you can multiply them. The result of such a multiplication will be matrix C, which has as many rows as matrix A and as many columns as matrix B:

``Amxn ?Bnxp  = Cmxp``

The rules for matrices multiplication are not as trivial as for scalar matrix multiplication. To calculate the ij-element of the product matrix, you need to multiply the ith row vector of the first matrix by the jth column vector of the second matrix. The multiplication of a row vector by a column vector goes as follows:

1. Multiply the first elements of the row and column vectors to get product 1.
2. Multiply the second elements of the row and column vectors to get product 2.
. . . . . . . . . . . . .
N. Multiply the Ns elements of the row and column vectors to get product N.
Lastly, sum up all the products.

The result of such a calculation will be a scalar.

Figure 2 shows the algorithm in math notation for Amxn ?Bnxp = Cmxp (where i = 1, 2, . . m, and j =1, 2, . . p.). Figure 2. Calculating ij-element of Product Matrix C: Here is the algorithm in math notation for Amxn ?Bnxp = Cmxp.

To find product C = A ? B, where: You would use this solution: Matrices multiplication is not commutative (i.e., A?B ? B?A). Indeed, in Example 4, the multiplication A3×2 ? B2×4 is allowed, because matrices A3×2 and B2×4 are compatible. However, the multiplication B2×4 ? A3×2 would be illegal, because the number of columns in the left matrix is not equal to the number of rows in the right matrix. But even if both A?B and B?A are compatible, that doesn?t guarantee the same result.

Consider two square matrices: However, matrices multiplication is associative and distributive:

• associative – A?(B?C) = (A?B)?C
• distributive – A?(B + C) = A?B + A?C
• distributive – (A + B)C = A?C + B?C

5) Matrix Transpose
The transposing of a matrix simply means an exchange between the rows and the columns. Suppose you have an m x n matrix, Amxn. If you transpose that matrix, you will get an n x m matrix, Bnxm, with the following correspondence between the elements of A and B:

``aixj   = bjxi, for all i and j.``

The most common notation for the transposing of matrix A is AT.

Find the transpose of a matrix A as follows: To demonstrate the implementation of matrices addition and subtraction in SQL, create tables A and B, which will represent two sample matrices A5×5 and B5×5 (see Listing 1).

Since matrices A and B are the same size, you can add and subtract them. The implementation in SQL is very straightforward (see Listing 2):

Matrices Multiplication Implementation in SQL
Since matrix scalar multiplication in SQL is obvious?simply multiply all columns in the table by a constant?it won’t be discussed here. Instead, let’s explore a more complicated operation: matrices multiplication. The example in this section will implement matrices multiplication in three steps:

1. Transform matrices A and B into two-dimensional arrays.
2. Calculate the product.
3. Transform calculated product back into matrix form.

Before you start, rebuild tables A and B. This time, they will represent two sample matrices, A7×8 and B8×9 (see Listing 3).

Matrices A7×8 and B8×9 are compatible because the number of columns in matrix A7×8 is equal to the number of rows in matrix B8×9. The result of multiplication is that matrix-product C7×9 will have seven rows and nine columns: A7×8 ? B8×9 = C7×9.

The code in the Listing 4 demonstrates how to perform matrices multiplication. All three steps described earlier in this section are implemented in the one query in Listing 4.

The first and second CTE queries transform matrices A and B into two-dimensional arrays. The third CTE query calculates matrices product according to the formula in Figure 2. The last SQL statement transforms a two-dimensional array into the matrix result.

Using the same approach with two-dimensional arrays, you can easily perform addition and subtraction.

Matrix Transpose Implementation in SQL
To transpose matrix A, just transform that matrix into a two-dimensional array and then transform it back into the matrix form. For reverse transformation, however, you replace the rows with the columns and the columns with the rows (see Listing 5).

Now you know a few SQL techniques for performing addition, subtraction, multiplication, and transpose on matrices. Share the Post: ### GlobalFoundries: Semiconductor Industry Titan

GlobalFoundries, a company that might not be a household name but has managed to make enormous strides in its relatively short 14-year history. As the third-largest semiconductor foundry in the ### Conquering Pandemic Supply Chain Struggles

The worldwide coronavirus pandemic has underscored supply chain challenges that resulted in billions of dollars in losses for automakers in 2021. Consequently, several firms are now contemplating constructing domestic manufacturing ### Cybersecurity Battles: Lapsus\$ Era Unfolds

In 2023, the cybersecurity field faces significant challenges due to the continuous transformation of threats and the increasing abilities of hackers. A prime example of this is the group of ### Web Application Supply Chain Security

Today’s web applications depend on a wide array of third-party components and open-source tools to function effectively. This reliance on external resources poses significant security risks, as malicious actors can ### Thrilling Battle: Germany Versus Huawei

The German interior ministry has put forward suggestions that would oblige telecommunications operators to decrease their reliance on equipment manufactured by Chinese firms Huawei and ZTE. This development comes after ### The iPhone 15’s Secrets and Surprises

As we dive into the most frequently asked questions and intriguing features, let us reiterate that the iPhone 15 brings substantial advancements in technology and design compared to its predecessors. ### iPhone 15: Performance, Camera, Battery

Apple’s highly anticipated iPhone 15 has finally hit the market, sending ripples of excitement across the tech industry. For those considering upgrading to this new model, three essential features come ### Virginia’s Clean Economy Act Soars Ahead

Virginia has made significant strides towards achieving its short-term carbon-free objectives as outlined in the Clean Economy Act of 2020. Currently, about 44,000 megawatts (MW) of wind, solar, and energy ### Innovative Energy Storage Solutions

The Department of Energy recently revealed a significant investment of \$325 million in advanced battery technologies to store excess renewable energy produced by solar and wind sources. This funding will ### Revolutionizing India’s Tech Sector with Renesas

Tushar Sharma, a semiconductor engineer at Renesas Electronics, met with Indian Prime Minister Narendra Modi to discuss the company’s support for India’s “Make in India” initiative. This initiative focuses on ### Thrilling East Windsor Mixed-Use Development

Real estate developer James Cormier, in collaboration with a partnership, has purchased 137 acres of land in Connecticut for \$1.15 million with the intention of constructing residential and commercial buildings. ### Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting ### European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its