The six clauses of the SELECT statement
There are six clauses that can be used in an SQL statement. These six clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. Clauses must be coded in a specific sequence. Let's briefly discuss each here. You will learn more about them as you encounter them later in the tutorial.
|1. SELECT||column name(s)*|
|2. FROM||table or views|
|3. WHERE||conditions or predicates are met|
|4. GROUP BY||subsets of rows|
|5. HAVING||a common condition as a group|
|6. ORDER BY||a sorting method|
Note: column name(s) are more correctly referred to as elements, because the SELECT statement displays both columns that exist in the table and columns that may be generated by SQL as a result of a query.
SELECT perkey, sum(dollars) FROM aroma.sales WHERE perkey < 50 GROUP BY perkey HAVING sum(dollars) > 8000 ORDER BY perkey;
The SELECT clause is where you list the columns you're interested in. The SELECT displays what you put here. There are other items you can put in a SELECT that will be explained later. In the example, the perkey column, as well as the sum of the dollar column, are selected.
The FROM clause indicates the table you're getting your information from. You can list more than one table. The number of tables you could list is specific to your operating system. In the example, both columns are selected from the Sales table.
SELECT and FROM are required; the rest of these clauses are optional and serve to filter or limit, aggregate or combine, and control the sort.
The WHERE clause is where you indicate a condition. This helps you filter unwanted data from the results. WHERE gives you a subset of the rows in a table. In the example, only rows with a perkey value of less than 50 are selected.
GROUP BY allows you to group your data to achieve more meaningful results. Instead of getting a total sum of the dollar sales for all the rows selected, you can break down sales by perkey to get the daily sales total. This is done in the example by indicating GROUP BY perkey.
HAVING puts a condition on your groups. In the example, only those days that have a total dollar amount greater than 8,000 are returned.
ORDER BY orders your result rows. You can choose to order results by ASC (ascending) or DESC (descending) order. The default is ASC.
The SELECT statement is the most common usage of data manipulation language (DML). Other DML statements (UPDATE, INSERT, and DELETE) and the other two components of SQL (data definition language and control language) are discussed in Part 6 of this series.