When is a matrix congruent to a diagonal matrix and how to find the congruent transformation? What matrix can be congruent to a diagonal matrix and how can we find the congruent transform and the diagonal matrix?
One special case is when the congruence is also similarity. 
For example, for a normal matrix, we can use a transform which is both similar and congruent to convert it into a diagonal matrix. But is it the only case where congruence to a diagonal matrix is of interest?
Thanks and regards!

As you might know, if a matrix $A$ is real symmetric, then it is always diagonalisable. In fact, it is orthogonally diagonalisable. So we can find an orthogonal matrix $P$ such that $P^TAP=D$ is a diagonal matrix having entries equal to the eigenvalues of $A$. This is nothing but the consequence of the Spectral decomposition theorem. As you rightly said, we can also say in this case that $A$ is congruent to $D$ (even $A$ is similar to $D$). In other words, by performing appropriate row operations and the same column operations on $A$, we can diagonalise $A$ to the matrix $D$.
However, we can diagonalise $A$ (if it is diagonalisable) even without the Spectral decomposition, provided $A$ is symmetric. We do this by performing congruent operations on $A$.
An important result on congruence operations is

Theorem. An $n\times n$ real symmetric matrix $A$ of rank $r$ is congruent to an $n\times n$ real diagonal matrix $D$ with non-zero elements in the first $r$ diagonal positions and zero elsewhere.

This result can be extended by stating:

An $n\times n$ real symmetric matrix $A$ of rank $r$ is congruent to the $n\times n$ real diagonal matrix $G$ whose first $m$ diagonal elements are $1$, the next $r-m$ diagonal elements are $-1$ and the remaining diagonal elements, if any, are all zero.

(I had asked a similar question regarding the proof of the result here.)
Thus $G$ takes the form $$\begin{pmatrix} I_m &  O &  O \\ O & -I_{r-m} &  O \\ O &  O &  O \\\end{pmatrix}\,,$$
which is said to be the normal form of $A$ under congruence.
Finally, we can say with relative ease that

Theorem. Two real symmetric matrices of the same order are congruent iff they have the same rank and same signature.


An example:
Suppose we have to diagonalise the matrix $$A=\begin{pmatrix}2 & 4 & 3 \\4 & 6 & 3 \\3 & 3 &1 \\\end{pmatrix}.$$
Or equivalently, diagonalise the quadratic form $$Q(x,y,z)=2x^2+6y^2+z^2+8xy+6xz+6yz\,.$$
Indeed, we can find the eigenvalues of $A$ and the corresponding normalised eigenvectors to perform the eigendecomposition. But we don't need an orthogonal transformation for the diagonalisation; any nonsingular transformation would suffice. (We don't even need to convert $A$ to the normal form).
We rewrite $A$ as $I_3AI_3=A \tag{$\star$}$
We apply the operations $R_2'=R_2-2R_1,R_3'=R_3-\frac{3}{2}R_1$ in the first step, followed by the same column operations in the next step. Then we perform $R_3'=R_3-\frac{3}{2}R_2$ and its corresponding column operation to reduce $A$ to the matrix $D=\mathrm{diag}(2,-2,1)$.
We find that $(\star)$ has become $$P^TAP=D\,,$$ where $P=\begin{pmatrix}1 & -2 & 3/2 \\0 & 1 & -3/2 \\0 & 0 &1 \\\end{pmatrix}$ is a nonsingular matrix.
Equivalently, the quadratic form $Q(x,y,z)$ has been transformed to some $$Q(u,v,w)=2u^2-2v^2+w^2$$ by means of the transformation $(x,y,z)^T=P(u,v,w)^T$.
Then the transformation matrix is given by $P^{-1}$ and the nonsingular transformation (congruent transformation) that diagonalises the given quadratic form is $(u,v,w)^T=P^{-1}(x,y,z)^T$.
This procedure is especially helpful for $3\times 3$ matrices, instead of finding the eigenvalues/eigenvectors or applying Lagrange's method of diagonalisation of a quadratic form.
