Are there cases where an Enchantment Aura effect remains on a creature when it is returned to owners hand?

Lorescale Coatl is never a frog in your hand, and Frogify is moved to the graveyard when Lorescale Coatl leaves the battlefield.

---

When does Frogify have an effect?

Frogify creates a continuous effect that changes the characteristics of the creature it enchants.

611.1. A continuous effect modifies characteristics of objects, modifies control of objects, or affects players or the rules of the game, for a fixed or indefinite period.

The continuous effects of static abilities apply as long as the static ability is active.

611.3a A continuous effect generated by a static ability isn’t “locked in”; it applies at any given moment to whatever its text indicates.

With few exceptions, static abilities function as long as their source is on the battlefield.^[1]

From this, we know that something is only a frog as long as Frogify is on the battlefield and enchanting it.

For example, if Frogify were to be destroyed, its effect on the creature it was enchanting would cease immediately.

---

Can Frogify affect a card in someone's hand?

So we know that Lorescale Coatl is only a frog as long as its enchanted by Frogify. So we ask ourselves if Frogify can enchant a card in someone's hand.

A Lorescale Coatl in your hand can't be a frog because Frogify can only enchant creatures. "Creature" specifically refers to creature cards and tokens on the battlefield.

303.4. Some enchantments have the subtype “Aura.” An Aura enters the battlefield attached to an object or player. What an Aura can be attached to is defined by its enchant keyword ability (see rule 702.5, “Enchant”). Other effects can limit what a permanent can be enchanted by.

109.2. If a spell or ability uses a description of an object that includes a card type or subtype, but doesn’t include the word “card,” “spell,” “source,” or “scheme,” it means a permanent of that card type or subtype on the battlefield.

110.1. A permanent is a card or token on the battlefield. [...]

---

But it doesn't even try to enchant a card in your hand

Even if Frogify could enchant a card in someone else's hand, it still wouldn't even try to do so in the OP's scenario.

In Magic, there's a concept of objects moving between zones. ("Enters the battle field", "leaves the battlefield", "put into the graveyard", etc) But that's a lie. Objects don't move between zones. What really happens when an object is moved is that the object in the first zone ceases to exist, and a new object is created in the new zone.

400.7. An object that moves from one zone to another becomes a new object with no memory of, or relation to, its previous existence. [...]

This means that when Lorescale Coatl is returned to your hand, the following two things really happen:

1. The Lorescale Coatl on the battlefield enchanted by Frogify ceases to exists.
2. An unrelated Lorescale Coatl card is created in your hand.

Since Frogify is no longer enchanting anything, it's sent to the graveyard.

704.5m If an Aura is attached to an illegal object or player, or is not attached to an object or player, that Aura is put into its owner’s graveyard.

There are nine situations in which one object can see a relationship between the object that ceased to exist and the new object. These are obvious in practice. (For example, Acrobatic Maneuver would never be able to return a card if it didn't recognize the link between the permanent it exiled and the card in exile that was created as a result.) None of those exceptions allow Frogify to enchant the Lorescale Coatl card in your hand.

---

1. The primary exception is that characteristic-defining abilities work in all zones, even outside of the game. The others are rare and obvious.

Comprehensive rules 400.7:

An object that moves from one zone to another becomes a new object with no memory of, or relation to, its previous existence. There are nine exceptions to this rule [...]

and none of the exceptions apply here. Therefore the object in your hand is the card Lorescale Coatl, and if you successfully cast it then it will be a "regular" Lorescale Coatl with its normal abilities.

Related to this, Frogify itself will go into its owner's graveyard.