Example of a concrete irrationality test
First, there are some nice examples like $$ e=\sum_{n\ge0}\frac1{n!} $$ or Liouville-like numbers, mentioned in the answers by Wilem2, that can be easily proved to be irrational using the theorem, but for which typically there are simpler irrationality proofs: For $e$, we quickly get that $$0<n!\bigl(e-\sum_{k\le n}\frac1{k!}\bigr)<1$$ for all large enough $n$, so $e$ cannot be rational, because otherwise for $n$ large enough the number in between 0 and 1 would be an integer. For Liouville-like numbers, the many 0s in between consecutive 1s readily imply the number does not have a periodic decimal expansion, so it should be irrational.
Now, one can prove that given any irrational $r\in\mathbb R$ there is a sequence as in the theorem converging to $r$. This is not quite obvious. Instead, I'll refer you to the theory of continued fractions. The two points to make are that if $|r-p/q|<1/q^2$ then $|qr-p|<1/q$ (so, if $q\to\infty$, then $|qr-p|\to0$) and that for any two consecutive convergents to $r$, in fact one satisfies $0<r-p/q<1/q^2$. The convergents to $r$ are the rational numbers that are obtained by iterating the following recursive procedure:
Start with $r$ irrational, let $r_0=\lfloor r\rfloor$. Note that $0<r-r_0<1$, so it is $1/x$ for some $x>1$, and we can repeat the same procedure with $x$ instead of $r$ and to obtain $r_1\in\mathbb N$ and $y>1$ such that $x=r_1+1/y$. For instance, if $r=\sqrt2$, you get $r=1+(\sqrt2-1)=1+\frac1x$, where $x=\frac1{\sqrt2-1}=\sqrt2+1=2+\frac1x$, so $$ \sqrt2=1+\frac1{2+\frac1{2+\frac1\ddots}}, $$ and the convergents to $\sqrt2$ are the rationals that appear along the way, by stopping the procedure after finitely many times, that is, the sequence $$ 1, 1+\frac12=\frac32,1+\frac1{2+\frac12}=\frac75,1+\frac1{2+\frac1{2+\frac12}}=\frac{17}{12},\dots$$
In general, if $p_0/q_0,p_1/q_1,\dots$ are the convergents to $r$, we have that $(p_n,q_n)=1$ for all $n$ and $$ p_0/q_0<p_2/q_2<\dots<r<\dots<p_3/q_3<p_1/q_1, $$ so $p_0/q_0,p_2/q_2,\dots$ provides a sequence as desired (one can easily check just from the inequalities that $q_n\to\infty$).
That said, it is perhaps a bit unsatisfactory to use continued fractions to illustrate the theorem, because of course if $r$ has an infinite sequence of convergents, then it is irrational (by the Euclidean algorithm!), so we already know $r$ is irrational before we even exhibit the relevant sequence. That said, in certain cases, there are nice recursive relations that allow us to find $p_{n+2},q_{n+2}$ in terms of $p_n,p_{n+1},q_n,q_{n+1}$, so we can easily exhibit sequences as in the theorem, thus witnessing the irrationality of many $r$. (Of course, for some $r$, there is no nice recursive way of obtaining such sequences unless we have access to $r$ to begin with.)
Indeed, for instance for $\sqrt2$, we have $p_0=1=q_0$, $p_1=3,q_1=2$ and, in general $p_{n+2}=2p_{n+1}+p_n$ and $q_{n+2}=2q_{n+1}+q_n$.
In general, for a given $r$, rather than always using $2$, we use $r_{n+2}$, where the integers $r_0,r_1,r_2,\dots$ are the integer parts obtained through the procedure as described above. For many irrationals $r$ there does not seem to be a nice formula for these numbers $r_n$. But there are nice formulas for $e$ and for any quadratic irrational.
(It is actually an interesting open problem whether there are nice patterns for, say $\root3\of2$ or $\pi$.)