http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc b/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc new file mode 100644 index 0000000..a9fbe86 --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/performance_troubleshooting.adoc @@ -0,0 +1,416 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[performance-and-troubleshooting]] += Performance and Troubleshooting + +This chapter describes how to improve and monitor the performance of +SPJs on the Trafodion platform and provides guidelines for +troubleshooting common problems: + +* link:#_bookmark209["Troubleshooting Common Problems"] +* link:#_bookmark212["Performance Tip"] +* link:#_bookmark214["Displaying an Execution Plan of a CALL Statement" +(page 59)] + +[[troubleshooting-common-problems]] +== Troubleshooting Common Problems + +To resolve problems that occur when you register or execute an SPJ, +follow these guidelines: + +* Note the SQLCODE or SQLSTATE value of the error messages and locate +the information in the __Neoview Messages Manual__, which provides +cause, effect, and recovery information for all SQL errors. +* Check that the user has the appropriate permissions to create or call +the SPJ. See these sections: +** link:#_bookmark114["Required Privileges for Creating or Dropping an +SPJ" (page 37)] +** link:#_bookmark156["Required Privileges for Calling an SPJ" (page +49)] +* Check the code of the SPJ method. See link:#bookmark36[Chapter 3 (page +18)]. Fix any problems. +* If you successfully compiled, deployed, and registered the SPJ but are +receiving errors when calling the SPJ, check that the output parameters +in the Java method are specified as arrays. See +link:#_bookmark48["Returning Output Values From the Java Method" (page +19)]. +* Verify that someone did not alter the library by selecting a JAR file +that contains a different class name, method name, or method signature +than the original JAR file, without dropping and re-creating the SPJ. + +In HPDM, compare the library's class name, method name, and method +signature with the procedure's external file (class name), external name +(method name), and signature. To see the library's class name, method +name, and method signature, launch the Library Browser dialog box in +HPDM by right-clicking the name of the library, and selecting Browse +Library. For more information, see link:#_bookmark109["Use the Library +Browser" (page 35)]. To see the procedure's external file (class name), +external name (method name), and signature in HPDM, see +link:#_bookmark131["Using HPDM] link:#_bookmark131[to Display a +Procedure in a Schema" (page 42)]. If you notice a discrepancy, see +link:#_bookmark136["Altering] link:#_bookmark136[an SPJ and Its Java +Bytecode" (page 44)]. + +* Check the characteristics of the stored procedure in HPDM and compare +them with the SPJ method that resides on the Trafodion platform. Fix any +discrepancies. +* Check the syntax of the CALL statement in the application. See +link:#bookmark151[Chapter 7 (page 49)]. Fix any problems. +* If the SPJ is supposed to return result sets, but the result sets are +not being returned to the calling application, check that the SPJ method +does not explicitly close a java.sql.Connection object. See +link:#_bookmark66["Use of java.sql.Connection Objects" (page 23)]. +* If a java.lang.ArrayIndexOutOfBoundsException occurs, check that the +SPJ method is not trying to insert more than one array element into a +java.sql.ResultSets[] array. For more information, see +link:#_bookmark54["Returning Stored Procedure Result Sets" (page 20)]. +* To identify Java-related errors, execute the SPJ method outside the +Trafodion database by invoking the Java method directly in a Java +application that you run on a client workstation, using the HP JDBC Type +4 driver to connect to the Trafodion platform. +* If you are using JDBC tracing and logging, follow these guidelines: +** Execute the SPJ method outside the database by invoking the Java +method directly from a Java application that you run on a client +workstation, using the HP JDBC Type 4 driver to connect to the Trafodion +platform. +** Verify that the file directory specified in the T4LogFile property +exists on the client workstation and that you have write access to it. + +For more information about JDBC tracing and logging, see the __Neoview +JDBC Type 4 Driver Programmer's Reference__. + + +[[performance-tip]] +== Performance Tip + +To ensure the optimal performance of SPJs on the Trafodion platform, +avoid nesting CALL statements in an SPJ method, which wastes resources +and might diminish performance. For more information, see +link:#_bookmark62["Nested Java Method Invocations" (page 22)]. + +[[displaying-an-execution-plan-of-a-call-statement]] +== Displaying an Execution Plan of a CALL Statement + +An execution plan reveals how a CALL statement was optimized. You can +display all or part of the execution plan for a CALL statement by using +the EXPLAIN statement or function. + +[[using-the-explain-statement]] +=== Using the EXPLAIN Statement + +Suppose that you want to display the execution plan for this CALL +statement: + +``` +CALL demo.persnl.adjustsalary(202,5.5,?); +``` + +Enter this EXPLAIN statement in an trafci session: + +``` +SQL>**prepare spj1 from call demo.persnl.adjustsalary(202,5.5,?);** + +--- SQL command prepared. SQL>**explain spj1;** + +------------------------------------------------------------------ PLAN +SUMMARY MODULE_NAME .............. DYNAMICALLY COMPILED + +STATEMENT_NAME ........... SPJ1 + +PLAN_ID .................. 212206487012085509 + +ROWS_OUT 1 + +EST_TOTAL_COST 0 + +STATEMENT ................ call demo.persnl.adjustsalary(202,5.5,?) + +------------------------------------------------------------------ NODE +LISTING ROOT ====================================== SEQ_NO 2 ONLY CHILD +1 REQUESTS_IN 1 + +ROWS_OUT 1 + +EST_OPER_COST 0 + +EST_TOTAL_COST 0 + +DESCRIPTION + +max_card_est 1 + +fragment_id 0 + +parent_frag ............ (none) + +fragment_type .......... master + +statement_index 0 + +affinity_value 3,466,211,324 + +max_max_cardinality 1 + +total_overflow_size .... 0.00 KB xn_autoabort_interval -1 + +plan_version ....... 2,500 + +LDAP_USERNAME .......... sqluser_admin NVCI_PROCESS ........... ON +SHOWCONTROL_UNEXTERNALI OFF + +SCHEMA ................. DEMO.INVENT + +CATALOG ................ NEO + +PRIORITY ............... 9 (for table +HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY ............... 9 (for +table HP_SYSTEM_CATALOG.MXCS_SCHEMA.DATASOURC + +ES) + +PRIORITY ............... 9 (for table +HP_SYSTEM_CATALOG.MXCS_SCHEMA.ENVIRONME + +NTVALUES) + +PRIORITY ............... 9 (for table +HP_SYSTEM_CATALOG.MXCS_SCHEMA.NAME2ID) PRIORITY ............... 9 (for +table HP_SYSTEM_CATALOG.MXCS_SCHEMA.RESOURCEP + +OLICIES) select_list ............ NUMERIC(8,2) SIGNED + +input_variables ........ ? + +CALL ====================================== SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... DEMO.PERSNL.ADJUSTSALARY + +REQUESTS_IN 1 + +ROWS_OUT 1 + +EST_OPER_COST 0 + +EST_TOTAL_COST 0 + +DESCRIPTION + +max_card_est ........... -1 + +fragment_id 0 + +parent_frag ............ (none) + +fragment_type .......... master + +routine_name ........... DEMO.PERSNL.ADJUSTSALARY + +parameter_modes ........ I I O sql_access_mode ........ MODIFIES SQL +DATA external_name .......... adjustSalary library ................ +DEMO.PERSNL.PAYROLL + +external_file .......... Payroll + +signature .............. +(Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language +............... JAVA + +parameter_style ........ JAVA external_security ...... INVOKER +max_result_sets 0 + +parameters ............. cast(202), cast(cast((cast(5.5) / cast(10)))), + +NUMERIC(8,2) SIGNED + +--- SQL operation complete. SQL> +``` + +The EXPLAIN statement generates and displays all the columns of the +result table of the EXPLAIN function. For the syntax of the EXPLAIN +statement, see the __Trafodion SQL Reference Manual__. For more +information about optimizing query execution plans, see the __Trafodion +Query Guide__. + +[[using-the-explain-function]] +=== Using the EXPLAIN Function + +You can also prepare the CALL statement and select specific columns from +the result table of the EXPLAIN function, as shown below: + +``` +SQL>**prepare spj1 from call demo.persnl.adjustsalary(202,5.5,?);** + +--- SQL command prepared. + +SQL>**select substring(operator,1,8) as "OPERATOR", operator_cost,** + ++>**substring(description,1,500) as "DESCRIPTION"** + ++>**from table (explain(null, 'SPJ1'));** + +OPERATOR OPERATOR_COST DESCRIPTION +-------- -------------- -------------------------------------------------------------------------------------- + +CALL 0.0 max_card_est: -1 fragment_id: 0 parent_frag: (none) +fragment_type: master routine_name: DEMO.PERSNL.ADJUSTSALARY +parameter_modes: I I O sql_access_mode: MODIFIES SQL DATA external_name: +adjustSalary library: DEMO.PERSNL.PAYROLL external_file: Payroll +signature: (Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language: +JAVA parameter_style: JAVA external_security: INVOKER max_result_sets: 0 +parameters: cast(202), cast(cast((cast(5.5) / cast(10)))), NUMERIC(8,2) +SIGNED + +ROOT 0.0 max_card_est: 1 fragment_id: 0 parent_frag: (none) +fragment_type: master statement_index: + +0 affinity_value: 3466211324 max_max_cardinality: 1 total_overflow_size: +0.00 KB statement: call demo.persnl.adjustsalary(202,5.5,?) +xn_autoabort_interval: -1 plan_version: 2500 LDAP_USERNAME: +sqluser_admin NVCI_PROCESS: ON SHOWCONTROL_UNEXTERNALIZED_ATTRS: OFF +SCHEMA: DEMO.INVENT CATALOG: NEO PRIORITY: 9 (for table +HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS) PRIORITY: 9 (for table +HP_SYSTEM_CATALOG.MXCS_SCHEMA.D + +--- 2 row(s) selected. SQL> +``` + +For a CALL statement, the OPERATOR column of the result table contains a +row named CALL. The DESCRIPTION column contains special token pairs for +the CALL operator. For descriptions of the token pairs, see this table: + +Token + +Token Description + +Data Type + +max_card_est + +The upper limit for the operator cardinality in the query tree. + +integer + +fragment_id + +A sequential number assigned to the fragment. 0 is always the master +executor, and 1 is reserved for the Explain plan. Numbers 2 to _n_ will +be ESP or TSE fragments. + +integer + +parent_frag + +The fragment_id for the parent fragment of the current fragment. The +value is (none) for the master executor. + +integer + +fragment_type + +Type of fragment, which can be either master, ESP, or TSE. + +text + +routine_name + +ANSI name of the procedure. + +text + +parameter_modes + +A sequence of characters that specifies SQL parameter modes for the +procedure. I is used for an IN parameter, O for an OUT parameter, and N +for an INOUT parameter. Characters are separated by a single space. The +value none is returned if the procedure has no SQL parameters. + +text + +sql_access_mode + +SQL access mode of the procedure. + +text + +external_name + +Java method name. + +text + +library + +ANSI name of the library object that maps to the procedure's JAR file. + +text + +external_file + +Java class name, possibly prefixed by a package name, that contains the +SPJ method. + +text + +signature + +Java signature of the SPJ method in internal Java Virtual Machine (JVM) +format. + +text + +language + +Language in which the SPJ method is written, which is always Java. + +text + +parameter_style + +Convention of passing parameter arguments to the stored procedure, which +conforms to the Java language for SPJs. + +text + +external_security + +External security of the stored procedure, indicating the privileges or +rights that users have when executing (or calling) the procedure. The +value is either INVOKER or DEFINER. For more information, see +link:#_bookmark124["Understand External Security" (page 41)]. + +text + +max_result_sets + +The maximum number of result sets that this procedure can return. + +integer + +parameters + +The parameter arguments that are passed to or from the procedure. + +text + +For the syntax of the EXPLAIN function, see the __Trafodion SQL Reference +Manual__. +
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/c86d2385/docs/spj_guide/src/asciidoc/_chapters/sample_database.adoc ---------------------------------------------------------------------- diff --git a/docs/spj_guide/src/asciidoc/_chapters/sample_database.adoc b/docs/spj_guide/src/asciidoc/_chapters/sample_database.adoc new file mode 100644 index 0000000..6ee790c --- /dev/null +++ b/docs/spj_guide/src/asciidoc/_chapters/sample_database.adoc @@ -0,0 +1,816 @@ +//// +/** + *@@@ START COPYRIGHT @@@ + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * @@@ END COPYRIGHT @@@ + */ +//// + +[[b-sample-database]] += B Sample Database + +This appendix presents the Sample Database schemas and tables on which +the SPJs in this manual operate: + +* link:#_bookmark338["PERSNL Schema" (page 103)] +** link:#_bookmark340["JOB Table" (page 103)] +** link:#_bookmark342["EMPLOYEE Table" (page 104)] +** link:#_bookmark344["DEPT Table" (page 106)] +** link:#_bookmark346["PROJECT Table" (page 107)] +* link:#_bookmark348["SALES Schema" (page 110)] +** link:#_bookmark350["CUSTOMER Table" (page 110)] +** link:#_bookmark352["ORDERS Table" (page 111)] +** link:#_bookmark354["ODETAIL Table" (page 112)] +** link:#_bookmark356["PARTS Table" (page 114)] +* link:#_bookmark358["INVENT Schema" (page 115)] +** link:#_bookmark360["SUPPLIER Table" (page 115)] +** link:#_bookmark362["PARTSUPP Table" (page 116)] +** link:#_bookmark364["PARTLOC Table" (page 119)] + +[[persnl-schema]] +== PERSNL Schema + +The PERSNL schema stores employee data. + +``` +CREATE SCHEMA DEMO.PERSNL ; +SET SCHEMA DEMO.PERSNL ; +``` + +[[job-table]] +=== JOB Table + +``` +CREATE TABLE demo.persnl.job +( jobcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, jobdesc VARCHAR (18) DEFAULT ' ' NOT NULL +, PRIMARY KEY (jobcode) +) ; + +INSERT INTO demo.persnl.job VALUES + ( 100, 'MANAGER' ) +, ( 200, 'PRODUCTION SUPV' ) +, ( 250, 'ASSEMBLER' ) +, ( 300, 'SALESREP' ) +, ( 400, 'SYSTEM ANALYST' ) +, ( 420, 'ENGINEER' ) +, ( 450, 'PROGRAMMER' ) +, ( 500, 'ACCOUNTANT' ) +, ( 600, 'ADMINISTRATOR' ) +, ( 900, 'SECRETARY' ) +; + +UPDATE STATISTICS FOR TABLE demo.persnl.job ON EVERY COLUMN; +``` + +[[employee-table]] +=== EMPLOYEE Table + +``` +CREATE TABLE demo.persnl.employee +( empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, first_name CHARACTER (15) DEFAULT ' ' NOT NULL +, last_name CHARACTER (20) DEFAULT ' ' NOT NULL +, deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, jobcode NUMERIC (4) UNSIGNED DEFAULT NULL +, salary NUMERIC (8, 2) UNSIGNED DEFAULT NULL +, PRIMARY KEY (empnum) +); + +ALTER TABLE demo.persnl.employee + ADD CONSTRAINT empnum_constrnt CHECK (empnum BETWEEN 0001 AND 9999) + ; + +CREATE INDEX xempname ON employee +( last_name +, first_name +) ; + +CREATE INDEX xempdept ON employee +( deptnum +) ; + +CREATE VIEW demo.persnl.emplist AS +SELECT + empnum +, first_name +, last_name +, deptnum +, jobcode +FROM employee +; + +INSERT INTO demo.persnl.employee VALUES + ( 1, 'ROGER', 'GREEN', 9000, 100, 175500.00 ) +, ( 23, 'JERRY', 'HOWARD', 1000, 100, 137000.10 ) +, ( 29, 'JANE', 'RAYMOND', 3000, 100, 136000.00 ) +, ( 32, 'THOMAS', 'RUDLOFF', 2000, 100, 138000.40 ) +, ( 39, 'KLAUS ', 'SAFFERT', 3200, 100, 75000.00 ) +, ( 43, 'PAUL', 'WINTER', 3100, 100, 90000.00 ) +, ( 65, 'RACHEL', 'MCKAY', 4000, 100, 118000.00 ) +, ( 72, 'GLENN', 'THOMAS', 3300, 100, 80000.00 ) +, ( 75, 'TIM', 'WALKER', 3000, 300, 32000.00 ) +, ( 87, 'ERIC', 'BROWN', 4000, 400, 89000.00 ) +, ( 89, 'PETER', 'SMITH', 3300, 300, 37000.40 ) +, ( 93, 'DONALD', 'TAYLOR', 3100, 300, 33000.00 ) +, ( 104, 'DAVID', 'STRAND', 4000, 400, 69000.00 ) +, ( 109, 'STEVE', 'COOK', 4000, 400, 68000.00 ) +, ( 111, 'SHERRIE', 'WONG', 3500, 100, 70000.00 ) +, ( 178, 'JOHN', 'CHOU', 3500, 900, 28000.00 ) +, ( 180, 'MANFRED', 'CONRAD', 4000, 450, 32000.00 ) +, ( 201, 'JIM', 'HERMAN', 3000, 300, 19000.00 ) +, ( 202, 'LARRY', 'CLARK', 1000, 500, 25000.75 ) +, ( 203, 'KATHRYN', 'HALL', 4000, 400, 96000.00 ) +, ( 205, 'GINNY', 'FOSTER', 3300, 900, 30000.00 ) +, ( 206, 'DAVE', 'FISHER', 3200, 900, 25000.00 ) +, ( 207, 'MARK', 'FOLEY', 4000, 420, 33000.00 ) +, ( 208, 'SUE', 'CRAMER', 1000, 900, 19000.00 ) +, ( 209, 'SUSAN', 'CHAPMAN', 1500, 900, 17000.00 ) +, ( 210, 'RICHARD', 'BARTON', 1000, 500, 29000.00 ) +, ( 211, 'JIMMY', 'SCHNEIDER', 1500, 600, 26000.00 ) +, ( 212, 'JONATHAN', 'MITCHELL', 1500, 600, 32000.00 ) +, ( 213, 'ROBERT', 'WHITE', 1500, 100, 90000.00 ) +, ( 214, 'JULIA', 'KELLY', 1000, 500, 50000.00 ) +, ( 215, 'WALTER', 'LANCASTER', 4000, 450, 33000.50 ) +, ( 216, 'JOHN', 'JONES', 4000, 450, 40000.00 ) +, ( 217, 'MARLENE', 'BONNY', 4000, 900, 24000.90 ) +, ( 218, 'GEORGE', 'FRENCHMAN', 4000, 420, 36000.00 ) +, ( 219, 'DAVID', 'TERRY', 2000, 250, 27000.12 ) +, ( 220, 'JOHN', 'HUGHES', 3200, 300, 33000.10 ) +, ( 221, 'OTTO', 'SCHNABL', 3200, 300, 33000.00 ) +, ( 222, 'MARTIN', 'SCHAEFFER', 3200, 300, 31000.00 ) +, ( 223, 'HERBERT', 'KARAJAN', 3200, 300, 29000.00 ) +, ( 224, 'MARIA', 'JOSEF', 4000, 420, 18000.10 ) +, ( 225, 'KARL', 'HELMSTED', 4000, 450, 32000.00 ) +, ( 226, 'HEIDI', 'WEIGL', 3200, 300, 22000.00 ) +, ( 227, 'XAVIER', 'SEDLEMEYER', 3300, 300, 30000.00 ) +, ( 228, 'PETE', 'WELLINGTON', 3100, 300, 32000.20 ) +, ( 229, 'GEORGE', 'STRICKER', 3100, 300, 32222.00 ) +, ( 230, 'ROCKY', 'LEWIS', 2000, 200, 24000.00 ) +, ( 231, 'HERB', 'ALBERT', 3300, 300, 33000.00 ) +, ( 232, 'THOMAS', 'SPINNER', 4000, 450, 45000.00 ) +, ( 233, 'TED', 'MCDONALD', 2000, 250, 29000.00 ) +, ( 234, 'MARY', 'MILLER', 2500, 100, 56000.00 ) +, ( 235, 'MIRIAM', 'KING', 2500, 900, 18000.00 ) +, ( 321, 'BILL', 'WINN', 2000, 900, 32000.00 ) +, ( 337, 'DINAH', 'CLARK', 9000, 900, 37000.00 ) +, ( 343, 'ALAN', 'TERRY', 3000, 900, 39500.00 ) +, ( 557, 'BEN', 'HENDERSON', 4000, 400, 65000.00 ) +, ( 568, 'JESSICA', 'CRINER', 3500, 300, 39500.00 ) +, ( 990, 'THOMAS', 'STIBBS', 3500, NULL, NULL ) +, ( 991, 'WAYNE', 'O''NEIL', 3500, NULL, NULL ) +, ( 992, 'BARRY', 'KINNEY', 3500, NULL, NULL ) +, ( 993, 'PAUL', 'BUSKETT', 3100, NULL, NULL ) +, ( 994, 'EMMY', 'BUSKETT', 3100, NULL, NULL ) +, ( 995, 'WALT', 'FARLEY', 3100, NULL, NULL ) +; + +UPDATE STATISTICS FOR TABLE demo.persnl.employee ON EVERY COLUMN ; +``` + +[[dept-table]] +=== DEPT Table + +``` +CREATE TABLE demo.persnl.dept +( deptnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, deptname CHARACTER (12) NO DEFAULT NOT NULL +, manager NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, rptdept NUMERIC (4) UNSIGNED DEFAULT 0 NOT NULL +, location VARCHAR (18) DEFAULT ' ' NOT NULL +, PRIMARY KEY (deptnum) +) ; + +CREATE INDEX xdeptmgr ON dept +( manager +) ; + +CREATE INDEX xdeptrpt ON dept +( rptdept +) ; + +ALTER TABLE demo.persnl.dept + ADD CONSTRAINT mgrnum_constrnt + CHECK (manager BETWEEN 0000 AND 9999) + ; + +ALTER TABLE demo.persnl.dept + +ADD CONSTRAINT deptnum_constrnt + CHECK ( deptnum IN + ( 1000 + , 1500 + , 2000 + , 2500 + , 3000 + , 3100 + , 3200 + , 3300 + , 3500 + , 4000 + , 4100 + , 9000 + ) + ) ; + +CREATE VIEW demo.persnl.mgrlist +( first_name +, last_name +, department +) +AS SELECT + first_name +, last_name +, deptname +FROM dept, employee +WHERE dept.manager = employee.empnum +; + +INSERT INTO demo.persnl.dept VALUES + ( 1000, 'FINANCE', 23, 9000, 'CHICAGO' ) +, ( 1500, 'PERSONNEL', 213, 1000, 'CHICAGO' ) +, ( 2000, 'INVENTORY', 32, 9000, 'LOS ANGELES' ) +, ( 2500, 'SHIPPING', 234, 2000, 'PHOENIX' ) +, ( 3000, 'MARKETING', 29, 9000, 'NEW YORK' ) +, ( 3100, 'CANADA SALES', 43, 3000, 'TORONTO' ) +, ( 3200, 'GERMNY SALES', 39, 3000, 'FRANKFURT' ) +, ( 3300, 'ENGLND SALES', 72, 3000, 'LONDON' ) +, ( 3500, 'ASIA SALES', 111, 3000, 'HONG KONG' ) +, ( 4000, 'RESEARCH', 65, 9000, 'NEW YORK' ) +, ( 4100, 'PLANNING', 87, 4000, 'NEW YORK' ) +, ( 9000, 'xxCORPORATE', 1, 9000, 'CHICAGO' ) +; + +UPDATE STATISTICS FOR TABLE demo.persnl.dept ON EVERY COLUMN ; +``` + +[[project-table]] +=== PROJECT Table + +``` +CREATE TABLE demo.persnl.project +( projcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, empnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, projdesc VARCHAR (18) DEFAULT ' ' NOT NULL +, start_date DATE DEFAULT DATE '2011-07-01' NOT NULL +, ship_timestamp TIMESTAMP DEFAULT TIMESTAMP '2011-08-01:12:00:00.000000' NOT NULL +, est_complete INTERVAL DAY DEFAULT INTERVAL '30' DAY NOT NULL +, PRIMARY KEY (projcode, empnum) +) ; + +INSERT INTO demo.persnl.project +VALUES + ( 1000, 213, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) +, ( 1000, 211, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) +, ( 1000, 23, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) +, ( 1000, 1, 'SALT LAKE CITY', DATE '2011-04-10', TIMESTAMP '2011-04-21:08:15:00.00', INTERVAL '15' DAY ) +, ( 2000, 227, 'ROSS PRODUCTS', DATE '2011-06-10', TIMESTAMP '2011-07-21:08:30:00.0000', INTERVAL '30' DAY ) +, ( 2000, 109, 'ROSS PRODUCTS', DATE '2011-06-10', TIMESTAMP '2011-07-21:08:30:00.0000', INTERVAL '30' DAY ) +, ( 2000, 215, 'ROSS PRODUCTS', DATE '2011-06-10', TIMESTAMP '2011-07-21:08:30:00.0000', INTERVAL '30' DAY ) +, ( 2000, 65, 'ROSS PRODUCTS', DATE '2011-06-10', TIMESTAMP '2011-07-21:08:30:00.0000', INTERVAL '30' DAY ) +, ( 2500, 65, 'MONTANA TOOLS', DATE '2011-10-10', TIMESTAMP '2011-12-21:09:00:00.0000', INTERVAL '60' DAY ) +, ( 2500, 207, 'MONTANA TOOLS', DATE '2011-10-10', TIMESTAMP '2011-12-21:09:00:00.0000', INTERVAL '60' DAY ) +, ( 2500, 232, 'MONTANA TOOLS', DATE '2011-10-10', TIMESTAMP '2011-12-21:09:00:00.0000', INTERVAL '60' DAY ) +, ( 2500, 180, 'MONTANA TOOLS', DATE '2011-10-10', TIMESTAMP '2011-12-21:09:00:00.0000', INTERVAL '60' DAY ) +, ( 2500, 93, 'MONTANA TOOLS', DATE '2011-10-10', TIMESTAMP '2011-12-21:09:00:00.0000', INTERVAL '60' DAY ) +, ( 3000, 65, 'AHAUS TOOL/SUPPLY', DATE '2011-08-21', TIMESTAMP '2011-10-21:08:10:00.0000', INTERVAL '60' DAY ) +, ( 3000, 221, 'AHAUS TOOL/SUPPLY', DATE '2011-08-21', TIMESTAMP '2011-10-21:08:10:00.0000', INTERVAL '60' DAY ) +, ( 3000, 226, 'AHAUS TOOL/SUPPLY', DATE '2011-08-21', TIMESTAMP '2011-10-21:08:10:00.0000', INTERVAL '60' DAY ) +, ( 3000, 224, 'AHAUS TOOL/SUPPLY', DATE '2011-08-21', TIMESTAMP '2011-10-21:08:10:00.0000', INTERVAL '60' DAY ) +, ( 3000, 225, 'AHAUS TOOL/SUPPLY', DATE '2011-08-21', TIMESTAMP '2011-10-21:08:10:00.0000', INTERVAL '60' DAY ) +, ( 4000, 75, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 4000, 29, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 4000, 231, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 4000, 228, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 4000, 223, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 4000, 568, 'THE WORKS', DATE '2011-09-21', TIMESTAMP '2011-10-21:10:15:00.0000', INTERVAL '30' DAY ) +, ( 5000, 65, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +, ( 5000, 568, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +, ( 5000, 557, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +, ( 5000, 216, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +, ( 5000, 203, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +, ( 5000, 218, 'ASIA PROJECT', DATE '2011-09-28', TIMESTAMP '2011-10-28:09:25:01.1111', INTERVAL '30' DAY ) +; + +UPDATE STATISTICS FOR TABLE demo.persnl.project ON EVERY COLUMN ; +``` + +[[sales-schema]] +== SALES Schema + +The SALES schema stores customer and sales data. + +``` +CREATE SCHEMA DEMO.SALES ; +SET SCHEMA DEMO.SALES ; +``` + +[[customer-table]] +=== CUSTOMER Table + +``` +CREATE TABLE demo.sales.customer +( custnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, custname CHARACTER (18) NO DEFAULT NOT NULL +, street CHARACTER (22) NO DEFAULT NOT NULL +, city CHARACTER (14) NO DEFAULT NOT NULL +, state CHARACTER (12) DEFAULT ' ' NOT NULL +, postcode CHARACTER (10) NO DEFAULT NOT NULL +, credit CHARACTER (2) DEFAULT 'C1' NOT NULL +, PRIMARY KEY (custnum) +) ; + +INSERT INTO demo.sales.customer VALUES + ( 21, 'CENTRAL UNIVERSITY', 'UNIVERSITY WAY', 'PHILADELPHIA', 'PENNSYLVANIA', '19104', 'A1' ) +, ( 123, 'BROWN MEDICAL CO', '100 CALIFORNIA STREET', 'SAN FRANCISCO', 'CALIFORNIA', '94944', 'C2' ) +, ( 143, 'STEVENS SUPPLY', '2020 HARRIS STREET', 'DENVER', 'COLORADO', '80734', 'A2' ) +, ( 324, 'PREMIER INSURANCE', '3300 WARBASH', 'LUBBOCK', 'TEXAS', '76308', 'A1' ) +, ( 543, 'FRESNO STATE BANK', '2300 BROWN BLVD', 'FRESNO', 'CALIFORNIA', '93921', 'B3' ) +, ( 926, 'METALL-AG.', '12 WAGNERRING', 'FRANKFURT', 'WEST GERMANY', '34', 'D4' ) +, ( 1234, 'DATASPEED', '300 SAN GABRIEL WAY', 'NEW YORK', 'NEW YORK', '10014', 'C1' ) +, ( 3210, 'BESTFOOD MARKETS', '3333 PHELPS STREET', 'LINCOLN', 'NEBRASKA', '68134', 'A4' ) +, ( 3333, 'NATIONAL UTILITIES', '6500 TRANS-CANADIENNE', 'QUEBEC', 'CANADA', 'H4T 1X4', 'A1' ) +, ( 5635, 'ROYAL CHEMICALS', '45 NEW BROAD STREET', 'LONDON', 'ENGLAND', 'EC2M 1NH', 'B2' ) +, ( 7654, 'MOTOR DISTRIBUTING', '2345 FIRST STREET', 'CHICAGO', 'ILLINOIS', '60610', 'E4' ) +, ( 7777, 'SLEEPWELL HOTELS', '9000 PETERS AVENUE', 'DALLAS', 'TEXAS', '75244', 'B1' ) +, ( 9000, 'BUNKNOUGHT INN', '4738 RALPH STREET', 'BAYONNE', 'NEW JERSEY', '09520', 'C1' ) +, ( 9010, 'HOTEL OREGON', '333 PORTLAND AVE.', 'MEDFORD', 'OREGON', '97444', 'C2' ) +, ( 9033, 'ART SUPPLIES, INC.', '22 SWEET ST.', 'PITTSBURGH', 'PENNA.', '08333', 'C3' ) +; + +UPDATE STATISTICS FOR TABLE demo.sales.customer ON EVERY COLUMN; +``` + +[[orders-table]] +=== ORDERS Table + +``` +CREATE TABLE demo.sales.orders +( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL +, order_date DATE DEFAULT DATE '2011-07-01' NOT NULL +, deliv_date DATE DEFAULT DATE '2011-08-01' NOT NULL +, salesrep NUMERIC (4) UNSIGNED DEFAULT 0 NOT NULL +, custnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, PRIMARY KEY (ordernum) +) ; + +ALTER TABLE demo.sales.orders + ADD CONSTRAINT demo.sales.date_constrnt CHECK (deliv_date >= order_date) + ; + +CREATE INDEX xordrep ON orders +( salesrep +) ; + +CREATE INDEX xordcus ON orders +( custnum +) ; + +CREATE VIEW demo.sales.ordrep AS SELECT + empnum +, last_name +, ordernum +, o.custnum +FROM + demo.persnl.employee e +, demo.sales.orders o +, demo.sales.customer c +WHERE e.empnum = o.salesrep + AND o.custnum = C.custnum +; + +CREATE INDEX xcustnam ON customer +( +custname +) ; + +CREATE VIEW demo.sales.custlist AS SELECT + custnum +, custname +, street +, city +, state +, postcode +FROM demo.sales.customer +; + +INSERT INTO demo.sales.orders VALUES + ( 100210, DATE '2011-04-10', DATE '2011-04-10', 220, 1234 ) +, ( 100250, DATE '2011-01-23', DATE '2011-06-15', 220, 7777 ) +, ( 101220, DATE '2011-07-21', DATE '2011-12-15', 221, 5635 ) +, ( 200300, DATE '2011-02-06', DATE '2011-07-01', 222, 926 ) +, ( 200320, DATE '2011-02-17', DATE '2011-07-20', 223, 21 ) +, ( 200490, DATE '2011-03-19', DATE '2011-11-01', 226, 123 ) +, ( 300350, DATE '2011-03-03', DATE '2011-08-10', 231, 543 ) +, ( 300380, DATE '2011-03-19', DATE '2011-08-20', 226, 123 ) +, ( 400410, DATE '2011-03-27', DATE '2011-09-01', 227, 7654 ) +, ( 500450, DATE '2011-04-20', DATE '2011-09-15', 220, 324 ) +, ( 600480, DATE '2011-05-12', DATE '2011-10-10', 226, 3333 ) +, ( 700510, DATE '2011-06-01', DATE '2011-10-20', 229, 143 ) +, ( 800660, DATE '2011-10-09', DATE '2011-11-01', 568, 3210 ) +; + +UPDATE STATISTICS FOR TABLE demo.sales.orders ON EVERY COLUMN; +``` + +[[odetail-table]] +=== ODETAIL Table + +``` +CREATE TABLE demo.sales.odetail +( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL +, partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, unit_price NUMERIC (8, 2) NO DEFAULT NOT NULL +, qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL +, PRIMARY KEY (ordernum,partnum) +) ; + +INSERT INTO demo.sales.odetail VALUES + ( 100210, 244, 3500.00, 3 ) +, ( 100210,2001, 1100.00, 3 ) +, ( 100210,2403, 620.00, 6 ) +, ( 100210,5100, 150.00, 10 ) +, ( 100250, 244, 3500.00, 4 ) +, ( 100250,5103, 400.00, 10 ) +, ( 100250,6301, 245.00, 15 ) +, ( 100250,6500, 95.00, 10 ) +, ( 101220, 255, 3900.00, 10 ) +, ( 101220,5103, 400.00, 3 ) +, ( 101220,7102, 275.00, 7 ) +, ( 101220,7301, 425.00, 8 ) +, ( 200300, 244, 3500.00, 8 ) +, ( 200300,2001, 1000.00, 10 ) +, ( 200300,2002, 1400.00, 10 ) +, ( 200320,5504, 165.00, 5 ) +, ( 200320,6201, 195.00, 16 ) +, ( 200320,6301, 245.00, 6 ) +, ( 200320,6400, 540.00, 7 ) +, ( 200490,3210, 715.00, 1 ) +, ( 200490,5505, 350.00, 1 ) +, ( 300350, 244, 2800.00, 20 ) +, ( 300350,5100, 150.00, 5 ) +, ( 300350,5110, 525.00, 12 ) +, ( 300350,6301, 245.00, 5 ) +, ( 300350,6400, 550.00, 5 ) +, ( 300380, 244, 3000.00, 6 ) +, ( 300380,2402, 320.00, 12 ) +, ( 300380,2405, 760.00, 8 ) +, ( 400410, 212, 2450.00, 12 ) +, ( 400410, 255, 3800.00, 12 ) +, ( 400410,2001, 1000.00, 36 ) +, ( 400410,6301, 240.00, 48 ) +, ( 400410,6400, 500.00, 70 ) +, ( 400410,7301, 415.00, 36 ) +, ( 500450, 212, 2500.00, 8 ) +, ( 500450, 255, 3900.00, 12 ) +, ( 500450,2001, 1100.00, 16 ) +, ( 500450,2002, 1500.00, 16 ) +, ( 500450,2402, 330.00, 48 ) +, ( 600480,2001, 1000.00, 60 ) +, ( 600480,2002, 1450.00, 20 ) +, ( 600480,2003, 1900.00, 40 ) +, ( 600480,3103, 4000.00, 40 ) +, ( 600480,3205, 625.00, 20 ) +, ( 600480,5100, 135.00, 60 ) +, ( 600480,5103, 390.00, 20 ) +, ( 600480,7301, 425.00, 40 ) +, ( 700410,2003, 1900.00, 65 ) +, ( 700410,2403, 650.00, 10 ) +, ( 700510, 255, 4000.00, 4 ) +, ( 700510,6500, 95.00, 8 ) +, ( 700510,7102, 275.00, 5 ) +, ( 800660, 244, 3000.00, 6 ) +, ( 800660,2001, 1000.00, 30 ) +, ( 800660,2403, 600.00, 48 ) +, ( 800660,2405, 795.00, 10 ) +, ( 800660,3201, 525.00, 6 ) +, ( 800660,3205, 600.00, 18 ) +, ( 800660,3210, 715.00, 6 ) +, ( 800660,4102, 26.00, 130 ) +, ( 800660,5100, 150.00, 12 ) +, ( 800660,5101, 200.00, 6 ) +, ( 800660,5110, 490.00, 48 ) +, ( 800660,5504, 165.00, 18 ) +, ( 800660,6201, 195.00, 6 ) +, ( 800660,6301, 235.00, 24 ) +, ( 800660,6400, 525.00, 30 ) +, ( 800660,6401, 700.00, 36 ) +, ( 800660,6500, 95.00, 22 ) +, ( 800660,7102, 275.00, 6 ) +, ( 800660,7301, 425.00, 12 ) +; + +UPDATE STATISTICS FOR TABLE demo.sales.odetail ON EVERY COLUMN; +``` + +[[parts-table]] +=== PARTS Table + +``` +CREATE TABLE demo.sales.parts +( partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, partdesc CHARACTER (18) NO DEFAULT NOT NULL +, price NUMERIC (8, 2) NO DEFAULT NOT NULL +, qty_available NUMERIC (5) DEFAULT 0 NOT NULL +, PRIMARY KEY (partnum) +) ; + +CREATE INDEX xpartdes ON parts +( partdesc +) ; + +INSERT INTO demo.sales.parts VALUES + ( 186, '186 MegaByte Disk', 186186.86, 186 ) +, ( 212, 'PC SILVER, 20 MB', 2500.00, 3525 ) +, ( 244, 'PC GOLD, 30 MB', 3000.00, 4426 ) +, ( 255, 'PC DIAMOND, 60 MB', 4000.00, 3321 ) +, ( 2001, 'GRAPHIC PRINTER,M1', 1100.00, 2100 ) +, ( 2002, 'GRAPHIC PRINTER,M2', 1500.00, 3220 ) +, ( 2003, 'GRAPHIC PRINTER,M3', 2000.00, 2200 ) +, ( 2402, 'DAISY PRINTER,T1', 350.00, 4425 ) +, ( 2403, 'DAISY PRINTER,T2', 650.00, 3312 ) +, ( 2405, 'DAISY PRINTER, T3', 795.00, 2712 ) +, ( 3103, 'LASER PRINTER, X1', 4200.00, 3300 ) +, ( 3201, 'HARD DISK 20 MB', 525.00, 4436 ) +, ( 3205, 'HARD DISK 30 MB', 625.00, 2209 ) +, ( 3210, 'HARD DISK 40 MB', 715.00, 3314 ) +, ( 4102, 'DISKETTE HD, BOX', 28.00, 6540 ) +, ( 5100, 'MONITOR BW, TYPE 1', 150.00, 3237 ) +, ( 5101, 'MONITOR BW, TYPE 2', 200.00, 2400 ) +, ( 5103, 'MONITOR COLOR, M1', 400.00, 3328 ) +, ( 5110, 'MONITOR COLOR, M2', 525.00, 3236 ) +, ( 5504, 'MEMORY CARD, 512KB', 165.00, 2630 ) +, ( 5505, 'MEMORY CARD, 1 MB', 315.00, 3830 ) +, ( 6201, 'GRAPHIC CARD, LR', 195.00, 2306 ) +, ( 6301, 'GRAPHIC CARD, HR', 245.00, 2331 ) +, ( 6400, 'STREAMING TAPE,M20', 550.00, 1268 ) +, ( 6401, 'STREAMING TAPE,M60', 725.00, 1308 ) +, ( 6500, 'DISK CONTROLLER', 95.00, 2532 ) +, ( 6603, 'PRINTER CONTROLLER', 45.00, 430 ) +, ( 7102, 'SMART MODEM, 1200', 275.00, 2200 ) +, ( 7301, 'SMART MODEM, 2400', 425.00, 2332 ) +; + +UPDATE STATISTICS FOR TABLE demo.sales.parts ON EVERY COLUMN ; +``` + +[[invent-schema]] +=== INVENT Schema + +The INVENT schema stored inventory data. + +``` +CREATE SCHEMA DEMO.INVENT ; +SET SCHEMA DEMO.INVENT ; +``` + +[[supplier-table]] +=== SUPPLIER Table + +``` +CREATE TABLE demo.invent.supplier +( suppnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, suppname CHARACTER (18) NO DEFAULT NOT NULL +, street CHARACTER (22) NO DEFAULT NOT NULL +, city CHARACTER (14) NO DEFAULT NOT NULL +, state CHARACTER (12) NO DEFAULT NOT NULL +, postcode CHARACTER (10) NO DEFAULT NOT NULL +, PRIMARY KEY (suppnum) +) ; + +CREATE INDEX xsuppnam ON supplier +( suppname +) ; + +INSERT INTO demo.invent.supplier VALUES + ( 1, 'NEW COMPUTERS INC', '1800 KING ST.', 'SAN FRANCISCO', 'CALIFORNIA', '94112' ) +, ( 2, 'DATA TERMINAL INC', '2000 BAKER STREET', 'LAS VEGAS', 'NEVADA', '66134' ) +, ( 3, 'HIGH DENSITY INC', '7600 EMERSON', 'NEW YORK', 'NEW YORK', '10230' ) +, ( 6, 'MAGNETICS INC', '1000 INDUSTRY DRIVE', 'LEXINGTON', 'MASS', '02159' ) +, ( 8, 'ATTRACTIVE CORP', '7777 FOUNTAIN WAY', 'CHICAGO', 'ILLINOIS', '60610' ) +, ( 10, 'LEVERAGE INC', '6000 LINCOLN LANE', 'DENVER', 'COLORADO', '80712' ) +, ( 15, 'DATADRIVE CORP', '100 MAC ARTHUR', 'DALLAS', 'TEXAS', '75244' ) +, ( 20, 'Macadam''S PC''s', '106 River Road', 'New Orleans', 'Louisiana', '67890' ) +, ( 25, 'Schroeder''s Ltd', '212 Strasse Blvd West', 'Hamburg', 'Rhode Island', '22222' ) +, ( 30, 'O''Donnell''s Drives', '729 West Palm Beach ', 'San Antonio', 'Texas', '78344' ) +, ( 35, 'Mac''Murphys PC''s', '93323 Alemeda', 'Menlo Park', 'California', '94025' ) +, ( 36, 'MAC''MURPHYS PCB''s', '93323 Alemeda Suite B', 'Menlo Park', 'California', '94025' ) +, ( 90, 'laser jets inc', '284 blue ridge way', 'levittown', 'penna.', '09520' ) +, ( 92, 'watercolors', '84 north grand avenue', 'menlo park', 'california', '94025' ) +, ( 95, 'application do''ers', '2846 yellowwood drive', 'wayland', 'mass', '02158' ) +, ( 99, 'terminals, inc.', '2 longfellow way', 'heightstown', 'nj', '08520' ) +, ( 186, '186 Disk Makers', '186 Dis Way', 'Dat Way', 'Wisconsin', '00186' ) +; + +UPDATE STATISTICS FOR TABLE demo.invent.supplier ON EVERY COLUMN; +``` + +[[partsupp-table]] +=== PARTSUPP Table + +``` +CREATE TABLE demo.invent.partsupp +( partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, suppnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, partcost NUMERIC (8, 2) NO DEFAULT NOT NULL +, qty_received NUMERIC (5) UNSIGNED DEFAULT 0 NOT NULL +, PRIMARY KEY (partnum,suppnum) +) ; + +CREATE INDEX XSUPORD ON partsupp +( suppnum +) ; + +CREATE VIEW demo.invent.view207 +( partnumber +, partdescrpt +, suppnumber +, supplrname +, partprice +, qtyreceived +) +AS SELECT + x.partnum +, partdesc +, x.suppnum +, suppname +, partcost +, qty_received +FROM + demo.invent.partsupp x +, demo.sales.parts p +, demo.invent.supplier s +WHERE x.partnum = p.partnum + AND x.suppnum = s.suppnum +; + +CREATE VIEW demo.invent.view207n +( partnumber +, partdescrpt +, suppnumber +, supplrname +, partprice +, qtyreceived +) +AS SELECT + x.partnum +, p.partdesc +, s.suppnum +, s.suppname +, x.partcost +, x.qty_received +FROM demo.invent.supplier s +LEFT JOIN demo.invent.partsupp x ON s.suppnum = x.suppnum +LEFT JOIN demo.sales.parts p ON x.partnum = p.partnum +; + +CREATE VIEW demo.invent.viewcust +( custnumber +, cusname +, ordernum +) +AS SELECT + c.custnum +, c.custname +, o.ordernum +FROM demo.sales.customer c +LEFT JOIN demo.sales.orders o ON c.custnum = o.custnum +; + +CREATE VIEW demo.invent.viewcs AS SELECT + custname +FROM demo.sales.customer +UNION SELECT + suppname +FROM demo.invent.supplier ; + +INSERT INTO demo.invent.partsupp VALUES + ( 212, 1, 2000.00, 20 ) +, ( 212, 3, 1900.00, 35 ) +, ( 244, 1, 2400.00, 50 ) +, ( 244, 2, 2200.00, 66 ) +, ( 255, 1, 3300.00, 35 ) +, ( 255, 3, 3000.00, 46 ) +, ( 2001, 1, 700.00, 100 ) +, ( 2001, 2, 750.00, 55 ) +, ( 2002, 1, 1000.00, 120 ) +, ( 2002, 6, 1100.00, 20 ) +, ( 2003, 1, 1300.00, 100 ) +, ( 2003, 2, 1400.00, 50 ) +, ( 2003, 10, 1450.00, 50 ) +, ( 2402, 1, 200.00, 35 ) +, ( 2403, 1, 300.00, 200 ) +, ( 2405, 1, 500.00, 40 ) +, ( 2405, 6, 450.00, 50 ) +, ( 3103, 1, 3200.00, 200 ) +, ( 3103, 15, 3300.00, 100 ) +, ( 3201, 1, 380.00, 36 ) +, ( 3205, 1, 425.00, 150 ) +, ( 3210, 6, 470.00, 10 ) +, ( 3210, 15, 450.00, 25 ) +, ( 4102, 6, 20.00, 115 ) +, ( 4102, 8, 19.00, 140 ) +, ( 4102, 15, 21.00, 30 ) +, ( 5100, 6, 100.00, 50 ) +, ( 5100, 8, 105.00, 40 ) +, ( 5100, 15, 95.00, 60 ) +, ( 5101, 8, 135.00, 33 ) +, ( 5101, 15, 125.00, 43 ) +, ( 5103, 8, 265.00, 20 ) +, ( 5103, 15, 250.00, 58 ) +, ( 5110, 1, 335.00, 100 ) +, ( 5110, 2, 350.00, 36 ) +, ( 5504, 2, 85.00, 10 ) +, ( 5504, 6, 75.00, 10 ) +, ( 5504, 15, 78.00, 10 ) +, ( 5505, 15, 200.00, 100 ) +, ( 6201, 1, 100.00, 110 ) +, ( 6301, 1, 150.00, 230 ) +, ( 6400, 1, 390.00, 50 ) +, ( 6401, 2, 500.00, 20 ) +, ( 6401, 3, 480.00, 38 ) +, ( 6500, 2, 60.00, 140 ) +, ( 6500, 3, 65.00, 32 ) +, ( 6603, 2, 25.00, 150 ) +, ( 7102, 10, 165.00, 100 ) +, ( 7301, 1, 300.00, 32 ) +; + +UPDATE STATISTICS FOR TABLE demo.invent.partsupp ON EVERY COLUMN; +``` + +[[partloc-table]] +=== PARTLOC Table + +``` +CREATE TABLE demo.invent.partloc +( loc_code CHARACTER (3) NO DEFAULT NOT NULL +, partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, qty_on_hand NUMERIC (7) DEFAULT 0 NOT NULL +, PRIMARY KEY (loc_code,partnum) +) ; + +INSERT INTO demo.invent.partloc VALUES + ('A10', 2001, 800 ) +, ('A21', 255, 21 ) +, ('A34', 6201, 0 ) +, ('A35', 6301, 0 ) +, ('A36', 6400, 34 ) +, ('A66', 6603, 300 ) +, ('A67', 6401, 454 ) +, ('A78', 244, 43 ) +, ('A78', 5505, 100 ) +, ('A87', 212, 18 ) +, ('A88', 2403, 735 ) +, ('A88', 5504, 30 ) +, ('A94', 3205, 200 ) +, ('A98', 5110, 510 ) +, ('G11', 2002, 20 ) +, ('G34', 6201, 106 ) +, ('G35', 6301, 331 ) +, ('G36', 6400, 1034 ) +, ('G43', 5100, 77 ) +, ('G45', 4102, 69 ) +, ('G65', 3201, 36 ) +, ('G68', 6500, 1132 ) +, ('G76', 2405, 42 ) +, ('G76', 7301, 32 ) +, ('G78', 5505, 0 ) +, ('G87', 212, 20 ) +, ('G87', 3103, 0 ) +, ('G87', 3210, 44 ) +, ('G87', 2402, 0 ) +, ('G88', 2403, 32 ) +, ('G88', 5504, 0 ) +, ('G89', 5101, 86 ) +, ('G94', 3205, 59 ) +, ('G98', 5103, 28 ) +, ('G98', 5110, 136 ) +, ('P10', 2001, 0 ) +, ('P12', 2002, 200 ) +, ('P12', 2003, 0 ) +, ('P15', 2003, 200 ) +, ('P66', 6603, 40 ) +, ('P67', 6401, 54 ) +, ('P68', 6500, 0 ) +, ('P76', 7102, 200 ) +, ('P78', 244, 23 ) +, ('P87', 3103, 300 ) +; + +UPDATE STATISTICS FOR TABLE demo.invent.partloc ON EVERY COLUMN ; +```
