You can use several functions to get the URL of a image of any of the intermediate sizes created by WordPress (thumbnail, medium, large, full, or any other custom size).
You can use get_the_post_thumbnail()/the_post_thumbnail(): use this function if you want to get a <img> element of the featured image of a post (aka “post thumbnail”). For example, to get the featured image, medium szie, of the post with ID 78:
$featured_image = get_the_post_thumbanil( 78, 'medium' );
echo $featured_image;
If you are within the loop, you can display the post thumbnail, medium size, of the current post as follow:
// Don't need echo
the_post_thumbnail( 'medium' );
If no image size is set, get_the_post_thumbanil()/the_post_thumbnail() use post-thumbnail, which is the size registered for the post thumbanil (featured image).
If you want to get a <img> element of any image, not the post thumbnail (featured image), use wp_get_attachment_image(). For example, if the attachement has ID of 7898 and you want to get medium size <img>:
echo wp_get_attachment_image( 7898, 'medium' );
You can also use wp_get_attachment_image_src() if you only want to get the URL of any image (but not a <img> element):
$image_url = wp_get_attachment_image_src( $id_of_the_attachment, $size );
For example, if the attachement has ID of 7898 and you want to get medium size URL:
$image_url = wp_get_attachment_image_src( 7898, 'medium' );
In you custom query, you could select the attachment ID instead of the guid and use this attachment ID with wp_get_attachment_image() and wp_get_attachment_image_src().
Anyway, you should avoid that custom SQL queries. You are missing important WordPress hooks and several functions, template tags, filters on content, embeds, etc, won’t wrok. If you want to work with WordPress posts within WordPress, the best choice is to use get_posts() o or a new instance of WP_Query.
Now, to directly answer your question: the attachment files, including the intermediate sizes of images, are stored in wp-content/uploads folder. The database keeps information of the attachement post type, but the files are not in database.