SQL REFERENCE - Concepts of Database Management - page 354

REVOKE

Use the REVOKE statement to revoke privileges from a user. Figure B-12 describes the REVOKE statement.

354

Clause Description Required?

REVOKE privilege Indicates the type of privilege(s) to be revoked. Yes

ON database object Indicates the database object(s) to which the privilege pertains. Yes

FROM user name Indicates the user name(s) from whom the privilege(s) are to be revoked. Yes

FIGURE B-12 REVOKE statement

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

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.)

HAVING condition involving groups 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.)

ORDER BY column or expression list 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.)

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
;