PostgreSQL

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


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


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


— find the parts assigned to employee 24 SELECT part.name 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(> );


CREATE


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


test=> SELECT statename.name 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»