SQL Clauses

An SQL SELECT statement is used to retrieve table data and can be used with the following clauses:

WHERE
You can use a WHERE clause to limit the effect of a SELECT or UPDATE statement to a subset of rows in the table.

The WHERE keyword is followed by one or more logical expressions or predicates that evaluate to TRUE or FALSE for each row in the table.

Only the rows where the predicates evaluate to TRUE are retrieved by a SELECT statement or modified by an UPDATE statement.

For example, the SELECT statement below retrieves all rows where the ZONE ID column contains a value of "T4".
```
SELECT company_name, ten_id
```
```
FROM tenements
```
```
WHERE zone_id = "T4"
```
Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator.
```
SELECT company_name, ten_id
```
```
FROM tenements
```
```
WHERE (zone_id = "T4") OR (zone_id = "T5")
```
ORDER BY
Use ORDER BY to sort the rows retrieved by a SELECT statement based on the values of one or more fields.

ORDER BY <field_name> [, <field_name> ...] [ASC|DESC]

The ORDER BY keyword is a comma-separated list of field names. The field names in the list must also be in the SELECT clause of the query statement.

Use ASC (ASCENDING) to force the sort to be in ascending order, or use DESC (DESCENDING) for a descending sort order. When not specified, an ASCENDING sort order is used by default.
```
SELECT company_name, ten_id
```
```
FROM tenements
```
```
WHERE (zone_id = "T4") OR (zone_id = "T5")
```
```
ORDER BY company_name ASC, ten_id DESC
```
GROUP BY
Use GROUP BY to combine rows with the same field values into a single row. The criteria for combining rows is based on the values in the fields specified in the GROUP BY clause.

Use GROUP BY to aggregate records based upon the unique values of one or more fields. You can only use a GROUP BY clause when one or more fields have an aggregate function applied to them.

GROUP BY <field_name> [, <field_name> ...]

The GROUP BY keyword is followed by a comma-separated list of fields. Each field must meet the following criteria:
- be present in one of the tables specified in the FROM clause
- appear in the SELECT clause of the query.
- must not have an aggregate function applied to it.
Fields that are the subject of an aggregate function have their values across all rows in the group combined.

Fields that are not the subject of an aggregate function retain their value and serve to distinctly identify the group.

For example, in the SELECT statement below, the values in the AREA field are aggregated (totalled) based on distinct values in the COMPANY_NAME field. This produces a total area for each company.
```
SELECT company_name, SUM(area) AS TOTALAREA
```
```
FROM tenements
```
```
GROUP BY company_name
```
```
ORDER BY company_name
```
HAVING
You can use a HAVING clause to limit the rows retrieved by a SELECT statement to a subset of rows where the aggregated field values meet the specified criteria.

A HAVING clause can only be used in a SELECT statement when the statement also has a GROUP BY clause and one or more fields are the subject of an aggregate function.

The value for a HAVING clause is one or more logical expressions, or predicates, that evaluate to true or false for each aggregate row retrieved from the table. Only those rows where the predicates evaluate to true are retrieved by a SELECT statement. For example, the SELECT statement below retrieves all rows where the total area for the tenements of an individual companies exceed 5000.
```
SELECT company_name, SUM(area) AS TOTALAREA
```
```
FROM tenements
```
```
GROUP BY company_name
```
```
HAVING (SUM(area) >= 5000)
```