Please
UseCodeTags when posting code snippets.
You need to try to write this in HQL yourself first, if you get stuck people will be more than happy to help you, but we are
NotACodeMill
I would start the reference docs to help get a basic understanding, there are also lots of examples.
http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html_single/#queryhql
Remember while similar to SQL it is not the same. You have to think in terms of your domain objects. For example you would not select * you would select your object.