SQL REFERENCE - Concepts of Database Management - page 352

The following REVOKE statement revokes the SELECT privilege for the Rep table from the user named
Johnson:

352

REVOKE SELECT ON Rep FROM Johnson
;

SELECT

Use the SELECT command to retrieve data from a table or from multiple tables. Figure B-13 describes the
SELECT command.

Clause Description Required?

SELECT column or expression list Indicates the column(s) and/or expression(s) to

be retrieved.

Yes

FROM table list Indicates the table(s) required for the query. Yes

WHERE condition Indicates one or more conditions. Only the rows

for which the condition(s) are true will be retrieved.

No (If you omit the WHERE
clause, all rows will be retrieved.)

GROUP BY column list Indicates the column(s) on which rows are to

be grouped.

No (If you omit the GROUP BY
clause, no grouping will occur.)

Indicates a condition for groups. Only groups for
which the condition is true will be included in
query results. Use the HAVING clause only if
the query output is grouped.

No (If you omit the HAVING
clause, all groups will be included.)

HAVING condition involving groups

Indicates the column(s) on which the query
output is to be sorted.

No (If you omit the ORDER BY
clause, no sorting will occur.)

ORDER BY column or expression list

FIGURE B-13 SELECT command

The following SELECT command groups and orders rows by rep number. It displays the rep number, the
count of the number of customers having this rep, and the average balance of these customers. It renames the
count as NumCustomers and the average balance as AverageBalance. The HAVING clause restricts the reps to
be displayed to only those having fewer than four customers.

SELECT RepNum, COUNT(*) AS NumCustomers, AVG(Balance) AS AverageBalance
FROM Customer
GROUP BY RepNum
HAVING COUNT(*)<4
ORDER BY RepNum
;

SELECT INTO

Use the SELECT command with an INTO clause to insert the rows retrieved by a query into a table. As shown
in Figure B-14, you must indicate the name of the table into which the row(s) will be inserted and the query
whose results will be inserted into the named table.

Clause Description Required?

SELECT field list Indicates the list of fields to be selected. Yes

INTO table name Indicates the name of the table into which the row(s) will be inserted. Yes

remainder of query Indicates the remainder of the query (for example, FROM clause

and WHERE clause) whose results will be inserted into the table.

Yes

FIGURE B-14 SELECT command with INTO clause