execute() is a generic method which can be called for retrieving data from the database as well updating the database. However it is never going to return you the ResultSet in case of retrieval or the number of rows updated in case of update/delete/insert. You might want to use it in places where you just want to test if the database access is a success or not.
On the other hand, executeQuery is very specific to retrieval of data from the database. It returns you a ResultSet which can be used to pull the data you need.
Also, The executeQuery() method takes a precompiled SQL statement as parameter and executes it.
Whereas, the execute() method simply executes an SQL statement without prior knowledge of the type of statement.
Actually the execute() method does return a ResultSet, if the SQL produces one. And it returns an update count, too, if the SQL produces one. From the documentation:
The execute method executes an SQL statement and indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
Incidentally, the documentation is the answer to the original question.