Скачать в pdf «PostgreSQL»

— find the employee assigned to part number 153 SELECT FROM part, employee

WHERE part.employee_id = employee.employee_id AND part.part_id = 153;

— find the parts assigned to employee 24 SELECT FROM part, employee

WHERE part.employee_id = employee.employee_id AND employee.employee_id = 24 ORDER BY name;

Figure 6.12: Joining part and employee

test=> CREATE TABLE statename (code CHAR(2), test(>    name CHAR(30)

test(> );


test=> INSERT INTO statename VALUES (‘AL’, ‘Alabama’); INSERT 20629 1

test=> SELECT AS customer_statename test-> FROM customer, statename test-> WHERE customer.customer_id = 648 AND test->    customer.state = statename.code;

•    Cheek codes against a list of valid values—that is, allow only valid state codes

•    Store code descriptions—that is, state code and state name

6.7 Choosing a Join Key

The join key is the value used to link rows between tables. For example, in Figure
6.4, в48 is the customer key, appearing in the customer table to uniquely identify the row, and in the salesorder table to refer to that specific customer row.

Some people might question whether an identification number is needed. Should the customer name be used as a join key? Using it as the join key is not a good idea for several reasons:

•    Numbers are less likely to be entered incorrectly.

•    Two customers with the same name would be impossible to distinguish in a join.

•    If the customer name changes, all references to that name would have to change.

•    Numeric joins are more efficient than joins of long character strings.

Скачать в pdf «PostgreSQL»