wpdb is a simple wrapper around the mysql_* functions, but you can’t mix and match them. $wpdb->query does not return the database reference that mysql_fetch_row needs. It …
… returns an integer corresponding to the number of rows
affected/selected. If there is a MySQL error, the function will return
FALSE. (Note: since both 0 and FALSE can be returned, make sure you
use the correct comparison operator: equality == vs. identicality
===).
You have but to read the Codex.
So, $wpdb->query is really for queries that don’t return data, like a delete.
What you want is:
$new = $wpdb->get_results("select * from wp_posts where post_title="Auto Draft"");
You want prepare as well if you were using user supplied data.
$new = $wpdb->get_results($wpdb->prepare("select * from wp_posts where post_title=%s",$str));
That will return an object, but you can pass a second parameter to get an array. You can iterate over that object or array with ordinary PHP object/array operations.
There is also $wpdb->get_var and $wpdb->get_col.
The database holds, as with any other PHP/MySQL applications, your dynamic content– posts, pages, configurable options, category tables, etc. It is not an easily enumerable list.
As for the question in the comment, “which case we need to use most probably” No idea. Depends on what you are doing. That isn’t really answerable, but I would say that in most cases you should not need, nor should you anyway, write SQL to manipulate the database. There are many core functions for that– WP_Query, WP_User_Query, set_option, update_post_meta, wp_update_post, the continues for about twenty more lines.