Queries enable you to pull information from your database using SQL. A good place to practice writing queries is control panel/admin/utilities/sql manager/database query form. If the query works there, you can use it on your pages with the Query Module, which enables you to invoke a query with an EE tag.
For example, here is a query to display all comments from all weblogs:
<ul>
{exp:query sql="SELECT * FROM exp_comments "}
<li>{name}-{comment}</li>
{/exp:query}
</ul>
You could restrict comments by author, for example, only showing comments from authors with id numbers 1 and 4:
<ul>
{exp:query sql="SELECT * FROM exp_comments WHERE author_id IN (1, 4)"}
<li>{name}-{comment}</li>
{/exp:query}
</ul>
Or you could show comments only from a certain weblog:
<ul>
{exp:query sql="SELECT * FROM exp_comments WHERE weblog_id='3'"}
<li>{name}-{comment}</li>
{/exp:query}
</ul>
Finally, here’s a query to show the 10 most prevalent posters, except the site owner, for the current month only:
{exp:query sql="SELECT COUNT(entry_id) AS count, name, email, url, author_id, weblog_id, comment_date FROM `exp_comments` WHERE FROM_UNIXTIME(comment_date,'%m') = DATE_FORMAT(CURRENT_DATE(), '%m') AND FROM_UNIXTIME(comment_date,'%y') = DATE_FORMAT(CURRENT_DATE(), '%y') AND status='o' GROUP BY email ORDER BY count DESC LIMIT 10"}
{count} comments by {name}<br />
{/exp:query}
You can also restrict what you are seeing based on a particular field such as comment_id and restrict it to show only the lastest five entries:
<ul>
{exp:query sql="SELECT * FROM exp_comments order by comment_id desc limit 5"}
<li>{name}-{comment}</li>
{/exp:query}
</ul>
You can also use an EE tag to make the search depend on the contents of the segment variables. For example, if the current user’s id is in {segment_3}, to show them all their comments you could use:
<ul>
{exp:query sql="SELECT * FROM exp_comments WHERE author_id='{segment_3}'"}
<li>{name}-{comment}</li>
{/exp:query}
</ul>
Unfortunately, not all variables can be included in the query in this manner—for example, in EE 1.2,
... WHERE author_id='{member_id}' ...
does not work.
More complicated queries will require using SQL JOINs. For an example, try to Show only categories with other entries (also linked under “S” below).