Chapter 5
|
Supported standards 29
FROM clause
The FROM clause indicates the tables that are used in the SELECT statement. The format is:
FROM table_names [table_alias]
table_names can be one or more simple table names in the current working directory or complete pathnames.
table_alias can be used to give the table a more descriptive name, or to abbreviate a longer table name.
Field names can be prefixed with the table name or the table alias. For example, given the table specification
FROM employee E, you can refer to the LAST_NAME field as E.LAST_NAME. Table aliases must be
used if the SELECT statement joins a table to itself. For example:
SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id
The equal sign (=) includes only matching rows in the results.
If you are joining more than one table, and you want to discard all rows that don’t have corresponding rows
in both source tables, you can use INNER JOIN. For example:
SELECT *
FROM Salespeople INNER JOIN Sales_Data
ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID
Note OUTER JOIN is not currently supported.
WHERE clause
The WHERE clause specifies the conditions that records must meet to be retrieved. The WHERE clause
contains conditions in the form:
WHERE expr1 rel_operator expr2
expr1 and expr2 can be field names, constant values, or expressions.
rel_operator is the relational operator that links the two expressions. For example, the following
SELECT statement retrieves the names of employees who make $20,000 or more.
SELECT last_name,first_name FROM emp WHERE salary >= 20000
Note If you use fully qualified names in the SELECT (projection) list, you must also use fully qualified
names in the related WHERE clause.
GROUP BY clause
The GROUP BY clause specifies the names of one or more fields by which the returned values should be
grouped. This clause is used to return a set of aggregate values. It has the following format:
GROUP BY column_expressions
column_expressions must match the column expression used in the SELECT clause. A column
expression can be one or more field names of the database table separated by commas, or one or more
expressions separated by commas.
The following example sums the salaries in each department.
SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id
This statement returns one row for each distinct department ID. Each row contains the department ID and
the sum of the salaries of the employees in the department.
