SQL Pocket Guide, 4th Edition

Chapter 4. Querying Basics

A query is a nickname for a SELECT statement, which consists of six main clauses. Each section of this chapter covers a clause in detail:

1. SELECT

2. FROM

3. WHERE

4. GROUP BY

5. HAVING

6. ORDER BY

The last section of this chapter covers the LIMIT clause, which is supported by MySQL, PostgreSQL, and SQLite.

The code examples in this chapter reference four tables:

waterfall

waterfalls in Michigan’s Upper Peninsula

owner

owners of the waterfalls

county

counties where the waterfalls are located

tour

tours that consist of multiple waterfall stops

Here is a sample query that uses the six main clauses. It is followed by the query results, which are also known as the result set.

-- Tours with 2 or more public waterfalls
SELECT   t.name AS tour_name,
         COUNT(*) AS num_waterfalls
FROM     tour t LEFT JOIN waterfall w
         ON t.stop = w.id
WHERE    w.open_to_public = 'y'
GROUP BY t.name
HAVING   COUNT(*) >= 2
ORDER BY tour_name;

tour_name  num_waterfalls
---------- ---------------
M-28                     6
Munising                 6
US-2                     4

To query a database means to retrieve data from a database, typically from a table or multiple tables.

Note

It is also possible to query a view instead of a table. Views look like tables and are derived from tables, but they themselves do not hold any data. More on views can be found in “Views” in Chapter 5.

The SELECT Clause

The SELECT clause specifies the columns that you want a statement to return.

In the SELECT clause, the SELECT keyword is followed by a list of ...