In the question in your title, you start by saying
Suppose we have an open set $E$ such that $E \subset Y \subset X$ for some metric space $X$.
This is slightly ambiguous since it is not clear whether you mean that $E$ is given to be an open subset of $X$ or an open subset of $Y$. I assume you mean the former, so it is better to say
Suppose we have an open set $E \subset X$ such that $E \subset Y \subset X$ for some metric space $X$.
Now, since $E$ is an open subset of $X$, for every $p \in E$ there exists $r > 0$ such that $d(p,q) < r$ for $q \in X$ implies that $q \in E$. So, the same value of $r$ works to show that $E$ is relatively open in $Y$, because if $d(p,q) < r$ for $q \in Y \subset X$, then $q \in E$ by the previous statement, and $E \subset Y$ so $q \in Y$. Thus, there is no example of the kind that you seek.
This does not make Theorem 2.30 superfluous, however.
Some open subsets of $Y$ could indeed be nothing but open subsets of $X$ that happen to be contained in $Y$. I assume this is where your intuition is taking you when you said in the comments
Doesn't $E = Y \cap G \implies E \subset Y$. So wouldn't the proof of the theorem be completely trivial and superfluous if that were always true?
But the fact is there are other subsets of $Y$ that are not of this kind. For instance, let $X = \Bbb{R}$, $Y = [0,1]$ and $E = [0,1)$. Clearly $E$ is not an open set in $X$, but it is open relative to $Y$ because $E = Y \cap (-1,1)$.
So, the answer to your question
Perhaps the theorem does not assume that $E$ is an open set at all??
is yes, $E$ is not assumed to be an open set in $\mathbf{X}$ in the hypotheses of the theorem. Let me again emphasise that you must specify which is the ambient space when you say something is or isn't an open set.