Geometric interpretation and computation of the Normal bundle
First of all, a warning (to myself first, because I use to get confused). The sheaf $I/I^2$ is a sheaf on $X$. This is not the conormal sheaf. Of course, it is supported on $Y$, but the conormal sheaf is the sheaf $i^\ast(I/I^2)$, and this is really on $Y$. That said, I will write $I/I^2$ instead of $i^\ast(I/I^2)$.
Example. Let us compute the normal bundle of a plane conic $C\subset\mathbb P^2$. The ideal of the conic is $I=\mathscr O_{\mathbb P^2}(-2)$, so $$I/I^2=I|_C=\mathscr O_{\mathbb P^2}(-2)|_C=\mathscr O_{C}(-2)\,\Rightarrow\,\mathcal N_{C/\mathbb P^2}=\mathscr O_C(2).$$ Note that this line bundle has actually a $5$-dimensional space of sections, as $$\mathscr O_C(2)\cong \mathscr O_{\mathbb P^1}(4).$$
This "$5$" is the dimension of the Hilbert scheme of plane conics, which is the smooth space $\mathbb P^5$. Actually, the vector space $H^0(C,\mathcal N_{C/\mathbb P^2})$ computes the tangent space of this $\mathbb P^5$ at $[C]$, and the latter is the space of deformations of the conic in the plane.
This is why I think it is useful to have in mind the following association: $$\textrm{(Sections of the) Normal bundle }\mathcal N_{Y/X}\,\,\longleftrightarrow\,\,\textrm{Deformations of } Y\textrm{ inside }X.$$ I will try to explain why, before coming to your examples.
In the case that interests you, namely that with $Y$ and $X$ both smooth, there is an exact sequence (called the conormal exact sequence): $$0\to I/I^2\to \Omega_X|_Y\to \Omega_Y\to 0.$$ Taking the dual, we find $$0 \to T_Y\to T_X|_Y\to \mathcal N_{Y/X}\to 0.$$ The normal bundle appears as cokernel of the map which identifies a tangent vector on $Y$ with a tangent vector on $X$, restricted to $Y$: those in the cokernel are tangent vectors restricted from $X$, up to those coming from $Y$. I cannot draw pictures here, but the idea is that a section of the normal bundle should be the datum of a family of vectors, orthogonal (normal!) to the tangent spaces, and these normal vectors draw for you a "nearby $Y$" inside $X$, i.e. a deformation of $Y$ inside $X$. The zeros of a section should then be the points $y\in Y$ where the vector was the zero vector: that particular $y$ did not contribute to the deformation. Formally, if $H$ is the Hilbert scheme of $X$, you have $$H^0(Y,\mathcal N_{Y/X})=T_{[Y]}H=\{\textrm{Deformations of }Y\textrm{ in }X\}.$$
Now for your examples.
- The fact that $\mathcal N_{S/\mathbb P^3}|_C=\mathscr O_C(2)$ is exactly as for the conic: $\mathcal N_{S/\mathbb P^3}=\mathscr O_S(2)$. The fact that $\mathcal N_{C/Q}=\mathscr O_C(1)$ is because any such curve is obtained as a hperplane section of $Q$, so the line bundle $\mathcal N_{C/Q}$ has to have degree $1$ on $C$.
- The fact that $\mathcal N_{X/\mathbb P^3}=\mathscr O_X(3)$ is again identical to the case of the conic, at the beginning. As for $\mathcal N_{C/X}=\mathscr O_C(C)$, it is something more general which happens (so you can forget about this special situation): if $Y\subset X$ is a smooth divisor, its ideal is $I=\mathscr O_X(-Y)$, so $$\mathcal N_{Y/X}=(I/I^2)^\vee=(\mathscr O_X(-Y)|_Y)^\vee=(\mathscr O_Y(-Y))^\vee=\mathscr O_Y(Y).$$
Normal bundle is O_C(C) see 2.9 here: http://math.mit.edu/~mckernan/Teaching/07-08/Autumn/18.735/l_2.pdf
In some cases, use the dual to the tangent bundle exact sequence to compute it (i.e. the exact sequence in Hartshorne chapter II.8)