|
|
Программирование >> Sql: полное руководство
REFERENCES PRODUCTS ON DELETE RESTRICT) CREATE TABLE PRODUCTS (MFR ID CHAR(3) NOT NULL, PRODUCT ID CHAR(5) NOT NULL, DESCRIPTION VARCHAR(20) NOT NULL, PRICE MONEY NOT NULL, QTY ON HAND INTEGER NOT NULL, PRIMARY KEY (MFR ID, PRODUCT ID)) Ha рисунках с A 2 по A 6 приведено содержимое каждой из пяти таблиц учебной базы данных Таблицы результатов почти всех запросов, встречающихся в книге, основаны на данных, представленных на этих рисунках сивт мии | СОИРАМУ | CUST ReP | CREDIT LIM1T | 2111 | JCP Inc | | $50,000 00 | 2102 | First Corp | | $65 ООО 00 | 2103 | Acme Hfg | | $50,000 00 | 2123 | Carter & Sons | | $40 ООО 00 | 2107 | Ace International | | $35,000 00 | 2115 | Smithson Corp | | $20,000 00 | 2101 | Jones Mfg | | $65 ООО 00 | 2112 | Zetacorp | | $50,000 00 | 2121 | QUA Assoc | | $45,000 00 | 2114 | Orion Corp | | $20,000 00 | 2124 | Peter Brothers | | $40 ООО 00 | 2108 | Holm & Landis | | $55,000 00 | 2117 | J P Sinclair | | $35,000 00 | 2122 | Three-Way Lines | | $30,000 00 | 2120 | Rico Enterprises | | $50 ООО 00 | 2106 | Fred Lewis Corp | | $65 ООО 00 | 2119 | Solomon Inc | | $25 ООО 00 | 2118 | Midwest Systems | | $60 ООО 00 | 2113 | Ian & Schmidt | | $20 ООО 00 | 2109 | Chen Associates | | $25 ООО 00 | 2105 | AAA Investments | | $45,000 00 |
Рис А 2 Таблица CUSTOMERS EMPL NUM | NAME | | REP OFF(CE | TITLE | HlfiE.DATE | MANAGER | QUOTA | SALES | | Bill Adams | | | Sales Яер | 12-FEB-88 | | $350,000 00 | $367,911 00 | | Hary Jones | | | Sales Rep | 12-0CT-89 | | $300,000 00 | $392 725 00 | | Sue Smith | | | Sales Rep | 10-DEC-86 | | $350 ООО 00 | $474,050 00 | | Sam Clark | | | VP Sales | 14-JUN 88 | NULL | $275 ООО 00 | $299,912 00 | | Bob Smith | | | Sales Mgr | 19-HAY-87 | | $200,000 00 | $142,594 00 | | Dan Roberts | | | Sales Rep | 20-0CT-86 | | $300,000 00 | $305,673 00 | | Tom Snyder | | NULL | Sales Rep | 13-JAN-90 | | NULL | $75,985 00 | | Larry Fitch | | | Sales Hgr | 12-0CT-89 | | $350,000 00 | $361 865 00 | | Paul Cruz | | | Sales Rep | 01-HAR-87 | | $275 ООО 00 | $286 775 00 | | Marcy Angel 11 | | | Sales Rep | 14-M0V-88 | | $300 ООО 00 | $186 042 00 |
Рис A 3 Таблица SALESREPS
| | OFFICE | CITY | REGION | | TARGET | SALES | | 22 11 12 13 21 | Denver New York Chicago Atlanta Los Angeles | Western Eastern Eastern Eastern Western | 106 106 104 105 108 | $300,000 00 $575,000 00 $800,000 00 $350,000 00 $725,000 00 | $186,042 00 $692,637 00 $735,042 00 $367,911 00 $835,915 00 | Рис. А 4 Таблица OFFICES |
ORDER NUM | ORDER DATE | CUST | | | PRODUCT | | AMOUNT | 112961 | 17-DEC-89 | 2117 | | | 2A44L | | $31,500 00 | 113012 | 11-JAN-90 | 2111 | | | 41003 | | $3,745 00 | 112989 | 03-JAN-90 | 2101 | | | | | $1,458 00 | 113051 | 10-FEB-90 | 2118 | | | XK47 | | $1,420 00 | 112968 | 12-0CT-89 | 2102 | | | 41004 | | $3,978 00 | 113036 | 30-JAN-90 | 2107 | | | 4100Z | | $22,500 00 | 113045 | O2-FEB-90 | 2112 | | | 2A44R | | $45,000 00 | 112963 | 17-DEC-89 | 2103 | | | 41004 | | $3,276 00 | 113013 | U-JAN-90 | 2118 | | | 41003 | | $652 00 | 113058 | 23-FEB-90 | 2108 | | | | | $1,480 00 | 112997 | 08-JAN-90 | 2124 | | | 41003 | | $652 00 | 112983 | 27-DEC-89 | 2103 | | | 41004 | | $702 00 | 113024 | 20-JAN-90 | 2114 | | | XK47 | | $7,100 00 | 113062 | 24-FEB-90 | 2124 | | | | | $2 430 00 | 112979 | 12-0CT-89 | 2114 | | | 4100Z | | $15,000 00 | 113027 | 22-JAN-90 | 2103 | | | 41002 | | $4,104 00 | 113007 | 08-JAN-90 | 2112 | | | 773C | | $2 925 00 | 113069 | 02-HAR-90 | 2109 | | | 775C | | $31,350 00 | 113034 | 29-JAN-90 | 2107 | | | 2A45C | | $632 00 | 112992 | 04-N0V-89 | 2118 | | | 41002 | | $760 00 | 112975 | 12-0CT-89 | 2111 | | | 2A44G | | $2,100 00 | 113055 | 15-FEB-90 | 2108 | | | 4100X | | $150 00 | 113048 | 10-FEB-90 | 2120 | | | 779C | | $3,750 00 | 112993 | 04-JAN-89 | 2106 | | | 2A45C | | $1,896 00 | 113065 | 27-FEB-90 | 2106 | | | XK47 | | $2,130 00 | 113003 | 25-JAN~90 | 2108 | | | 779C | | $5,625 00 | 113049 | 10-FEB-90 | 2118 | | | XK47 | | $710 00 | 112987 | 31-DEC-89 | 2103 | | | 4100Y | | $27,500 00 | 113057 | 18-FEB-90 | 2111 | | | 4100X | | $600 00 | 113042 | 02-FEB-90 | 2113 | | | 2ааап | | $22,500 00 |
Рис A 5 Таблица ORDERS
MFR ID | PRODUCT ID | DESCRIPTION | PRICE | QrY ON HAND | | 2A45C | Ratchet Link | $79 00 | | | 4100Y | Widget Remover | $2,750 00 | | | XK47 | Reducer | $355 00 | | | 41672 | Plate | $180 00 | | | 779C | 900-lb Brace | $1,875 00 | | | 41003 | Size 3 Widget | $107 00 | | | 41004 | Size 4 Widget | $117 00 | | | 41003 | Handle | $652 00 | | | 887P | Brace Pin | $250 00 | | | XK48 | Reducer | $134 00 | | | 2A44L | Left Hinge | $4,500 00 | | | | Housing | $148 00 | | | 887H | Brace Holder | $54 00 | | | 41089 | Reta1ner | $225 00 | | | 41001 | Size 1 Widget | $55 00 | | | 775C | 500-lb Brace | $1,425 00 | | | 4100Z | Widget Installer | $2,500 00 | | | XK48A | Reducer | $1/7 00 | | | 41002 | Size 2 Widget | $76 00 | | | 2A44R | Right Hinge | $4,500 00 | | | 773 С | 300-lb Brace | $975 00 | | | 4100X | Widget Adjuster | $25 00 | | | | Motor Mount | $243 00 | | | 887X | Brace Retainer | $475 00 | | | 2A44G | Hinge Pin | $350 00 | |
1* TaiUalpROWCTS t*
|
