Dynamical tides in exoplanetary systems containing Hot Jupiters: confronting theory and observations