SQL REFERENCE - Concepts of Database Management - page 351

Clause Description Required?

ALTER TABLE table name Indicates the table for which integrity is being specified. Yes

351

integrity clause CHECK, PRIMARY KEY, or FOREIGN KEY Yes

FIGURE B-11 Integrity options

The following ALTER TABLE command changes the Part table so that the only legal values for the Class col-
umn are AP, HW, and SG:

ALTER TABLE Part
CHECK (Class IN ('AP','HW','SG') )
;

The following ALTER TABLE command changes the Rep table so that the RepNum column is the table's pri-
mary key:

ALTER TABLE Rep
ADD PRIMARY KEY(RepNum)
;

The following ALTER TABLE command changes the Customer table so that the RepNum column in the
Customer table is a foreign key referencing the primary key of the Rep table:

ALTER TABLE Customer
ADD FOREIGN KEY (RepNum) REFERENCES Rep
;

JOIN

To join tables, use a SELECT command in which both tables appear in the FROM clause and the WHERE
clause contains a condition to relate the rows in the two tables. The following SELECT statement lists the cus-
tomer number, customer name, rep number, first name, and last name by joining the Rep and Customer tables
using the RepNum fields in both tables:

SELECT CustomerNum, CustomerName, Customer.RepNum, FirstName, LastName
FROM Rep, Customer
WHERE Rep.RepNum = Customer.RepNum
;

Note: Many implementations of SQL also allow a special JOIN operator to join tables. The following com-
mand uses the JOIN operator to produce the same result as the previous query:

SELECT CustomerNum, CustomerName, Customer.RepNum, FirstName, LastName
FROM Rep
INNER JOIN Customer
ON Rep.RepNum = Customer.RepNum
;

REVOKE

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

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