SQL REFERENCE - Concepts of Database Management - page 353

The following INSERT command inserts the values shown in parentheses as a new row in the Rep table:

INSERT INTO Rep VALUES
( ' 16 ' , ' Rands ' , ' Sharon ' , ' 826 Raymond ' , ' Altonville ' , ' FL ' , ' 32543 ' ,0.00,0.05)
;

353

INTEGRITY

You can use the ALTER TABLE command with an appropriate CHECK, PRIMARY KEY, or FOREIGN KEY
clause to specify integrity. Figure B-11 describes the ALTER TABLE command for specifying integrity.

Clause Description Required?

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

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
column 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

primary 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
customer 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
;