How are SQL queries properly handled within a module? I would appreciate a little guidance on where to begin so I don't waste a ton of time looking in the wrong places.

I have a content type that has a number of associated entities appended to it; these all have date ranges on them. I need to create a module that upon hook_node_save() and hook_node_insert() checks other associated entities for various date conditions (do dates completely overlap, or partially overlap) and either adjusts the dates on those entities, or deletes the entity.

I know how I'd do this if it was purely SQL, but I want to do it the Drupal way.  

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.