How are SQL queries properly handled within a module?
Drupal essentially has three groups of functions to query the database:


Functions to make static queries
Functions to make dynamic queries
Functions to make queries about entity fields


db_query() and db_query_range() are part of the first group, while db_select() is part of the second group; finally the EntityFieldQuery class is what you use for queries involving the values of entity fields.

To make clear which functions you should use, keep in mind that dynamic queries are those queries that are dynamically built at run-time, such as in the following code.

$table = $load_current ? _field_sql_storage_tablename($field) : _field_sql_storage_revision_tablename($field);

$query = db_select($table, 't')
  -&gt;fields('t')
  -&gt;condition('entity_type', $entity_type)
  -&gt;condition($load_current ? 'entity_id' : 'revision_id', $ids, 'IN')
  -&gt;condition('language', field_available_languages($entity_type, $field), 'IN')
  -&gt;orderBy('delta');


Since the table name changes at runtime, using that code is preferable to concatenate strings to build the query.

Dynamic queries are also those that needs to be altered by other modules, such as the following one.

if ($nids) {
  $query = db_select('book', 'b', array('fetch' =&gt; PDO::FETCH_ASSOC));
  $query-&gt;join('node', 'n', 'b.nid = n.nid');
  $query-&gt;join('menu_links', 'ml', 'b.mlid = ml.mlid');
  $query-&gt;addField('n', 'type', 'type');
  $query-&gt;addField('n', 'title', 'title');
  $query-&gt;fields('b');
  $query-&gt;fields('ml');
  $query-&gt;condition('n.nid', $nids, 'IN');
  $query-&gt;condition('n.status', 1);
  $query-&gt;orderBy('ml.weight');
  $query-&gt;orderBy('ml.link_title');
  $query-&gt;addTag('node_access');
  $result2 = $query-&gt;execute();
  foreach ($result2 as $link) {
    $link['href'] = $link['link_path'];
    $link['options'] = unserialize($link['options']);
    $all_books[$link['bid']] = $link;
  }
}


$query-&gt;addTag('node_access') allows other modules to alter the query to return only the nodes to which the currently logged-in user has access.

In the other cases, it is preferable to use a static query, which are faster. The only case you should not use static queries is when the query involves the value of entity fields; in which case, you should use the EntityFieldQuery class, which avoids you need to know any details about how entity fields are implemented (e.g. which database classes contain the field values).

$query = new EntityFieldQuery();

$query-&gt;entityCondition('entity_type', 'node')
  -&gt;entityCondition('bundle', 'article')
  -&gt;propertyCondition('status', NODE_PUBLISHED)
  -&gt;fieldCondition('field_news_types', 'value', 'spotlight', '=')
  -&gt;fieldCondition('field_photo', 'fid', 'NULL', '!=')
  -&gt;fieldCondition('field_faculty_tag', 'tid', $value)
  -&gt;fieldCondition('field_news_publishdate', 'value', $year . '%', 'like')
  -&gt;fieldOrderBy('field_photo', 'fid', 'DESC')
  -&gt;range(0, 10)
  -&gt;addMetaData('account', user_load(1)); // Run the query as user 1.