http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/lowerPrice.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/lowerPrice.java b/docs/2.0.0/spj_guide/resources/source/lowerPrice.java new file mode 100644 index 0000000..b49ff2f --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/lowerPrice.java @@ -0,0 +1,44 @@ +// The LOWERPRICE procedure determines which items are selling poorly (that +// is, have less than 50 orders) and lowers the price of these items in the +// database by 10 percent. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#lowerprice-procedure +// for more documentation. +public static void lowerPrice() throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getParts = + conn.prepareStatement( "SELECT p.partnum, " + + "SUM(qty_ordered) AS qtyOrdered " + + "FROM trafodion.sales.parts p " + + "LEFT JOIN trafodion.sales.odetail o " + + "ON p.partnum = o.partnum " + + "GROUP BY p.partnum" + ) ; + + PreparedStatement updateParts = + conn.prepareStatement( "UPDATE trafodion.sales.parts " + + "SET price = price * 0.9 " + + "WHERE partnum = ?" + ) ; + + ResultSet rs = getParts.executeQuery() ; + while ( rs.next() ) + { + BigDecimal qtyOrdered = rs.getBigDecimal( 2 ) ; + + if (( qtyOrdered == null ) || ( qtyOrdered.intValue() < 50 ) ) + { + BigDecimal partnum = rs.getBigDecimal( 1 ) ; + updateParts.setBigDecimal( 1, partnum ) ; + updateParts.executeUpdate() ; + } + } + + rs.close() ; + conn.close() ; + +} +
http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/numDailyOrders.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/numDailyOrders.java b/docs/2.0.0/spj_guide/resources/source/numDailyOrders.java new file mode 100644 index 0000000..568d71f --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/numDailyOrders.java @@ -0,0 +1,29 @@ +// The DAILYORDERS procedure accepts a date and returns the number of +//orders on that date to an output parameter. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#dailyorders-procedure +// for additional documenation. +public static void numDailyOrders( Date date + , int[] numOrders + ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getNumOrders = + conn.prepareStatement( "SELECT COUNT(order_date) " + + "FROM trafodion.sales.orders " + + "WHERE order_date = ?" + ) ; + + getNumOrders.setDate( 1, date ) ; + + ResultSet rs = getNumOrders.executeQuery() ; + rs.next() ; + + numOrders[0] = rs.getInt( 1 ) ; + + rs.close() ; + conn.close() ; + +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/numMonthlyOrders.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/numMonthlyOrders.java b/docs/2.0.0/spj_guide/resources/source/numMonthlyOrders.java new file mode 100644 index 0000000..942ea01 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/numMonthlyOrders.java @@ -0,0 +1,41 @@ +// The MONTHLYORDERS procedure accepts an integer representing the month +// and returns the number of orders during that month to an output parameter. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#monthlyorders-procedure +// for more documentation. +public static void numMonthlyOrders( int month + , int[] numOrders + ) throws SQLException + +{ + if ( month < 1 || month > 12 ) + { + throw new SQLException( "Invalid value for month. " + + "Retry the CALL statement " + + "using a number from 1 to 12 " + + "to represent the month." + , "38001" + ) ; + } + + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getNumOrders = + conn.prepareStatement( "SELECT COUNT( month( order_date ) ) " + + "FROM trafodion.sales.orders " + + "WHERE month( order_date ) = ?" + ) ; + + getNumOrders.setInt( 1, month ) ; + + ResultSet rs = getNumOrders.executeQuery() ; + rs.next() ; + + numOrders[0] = rs.getInt(1) ; + + rs.close() ; + conn.close(); + +} + http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/orderSummary.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/orderSummary.java b/docs/2.0.0/spj_guide/resources/source/orderSummary.java new file mode 100644 index 0000000..8b8e5bb --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/orderSummary.java @@ -0,0 +1,76 @@ +// The ORDERSUMMARY procedure accepts a date, which is formatted as a +// string, and returns this information about the orders on or after that +// date: +// +// * The number of orders as an output parameter +// * A result set that contains one row for each order. Each row contains +// fields for the order number, the number of parts ordered, total dollar +// amount, order date, and the name of the sales representative. +// * A result set that contains details about each order. Each order has +// one or more rows that provide details about the ordered parts. Each row +// contains fields for the order number, part number, unit price, quantity +// ordered, and part description. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#ordersummary-procedure +// for more documentation. +public static void orderSummary( java.lang.String onOrAfter + , long[] numOrders + , java.sql.ResultSet[] orders + , java.sql.ResultSet[] detail + ) throws SQLException +{ + java.lang.String s ; + + java.sql.Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + // Get the number of orders on or after this date + s = "SELECT COUNT(ordernum) FROM trafodion.sales.orders " + + "WHERE order_date >= CAST(? AS DATE) " + ; + + java.sql.PreparedStatement ps1 = conn.prepareStatement( s ) ; + ps1.setString( 1, onOrAfter ) ; + + java.sql.ResultSet rs = ps1.executeQuery() ; + rs.next() ; + + numOrders[0] = rs.getLong( 1 ) ; + rs.close() ; + + // Open a result set for order num, order info rows + s = "SELECT amounts.*, orders.order_date, emps.last_name " + + "FROM ( SELECT " + + " o.ordernum " + + " , COUNT(d.partnum) AS num_parts " + + " , SUM(d.unit_price * d.qty_ordered) AS amount " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail d " + + " WHERE o.ordernum = d.ordernum " + + " AND o.order_date >= CAST(? AS DATE) " + + " GROUP BY o.ordernum " + + " ) amounts " + + " , trafodion.sales.orders orders " + + " , trafodion.persnl.employee emps " + + "WHERE amounts.ordernum = orders.ordernum " + + " AND orders.salesrep = emps.empnum " + + "ORDER BY orders.ordernum " + ; + + java.sql.PreparedStatement ps2 = conn.prepareStatement( s ) ; + ps2.setString( 1, onOrAfter ) ; + orders[0] = ps2.executeQuery() ; + + // Open a result set for order detail rows + s = "SELECT d.*, p.partdesc " + + "FROM trafodion.sales.odetail d, trafodion.sales.parts p, trafodion.sales.orders O " + + "WHERE d.partnum = p.partnum AND d.ordernum = o.ordernum " + + " AND o.order_date >= CAST(? AS DATE) " + + "ORDER BY d.ordernum " + ; + + java.sql.PreparedStatement ps3 = conn.prepareStatement( s ) ; + ps3.setString( 1, onOrAfter ) ; + detail[0] = ps3.executeQuery() ; + +} + http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/partData.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/partData.java b/docs/2.0.0/spj_guide/resources/source/partData.java new file mode 100644 index 0000000..6d7b54b --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/partData.java @@ -0,0 +1,103 @@ +// The PARTDATA procedure accepts a part number and returns this +// information about the part: +// +// * Part description, price, and quantity available as output parameters. +// * A result set that contains rows from the ORDERS table about when this part was ordered. +// * A result set that contains rows from the PARTLOC table, listing locations that have this +// part in stock and the quantity they have on hand. +// * A result set that contains rows from the PARTSUPP table for suppliers who carry this part. +// * A result set that contains rows from the EMPLOYEE table for sales reps who have sold this part. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#partdata-procedure +// for more documentation. +public static void partData( int partNum + , String[] partDescription + , BigDecimal[] unitPrice + , int[] qtyAvailable + , ResultSet[] orders + , ResultSet[] locations + , ResultSet[] suppliers + , ResultSet[] reps + ) throws SQLException +{ + + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + // Retrieve detail about this part into the output parameters + PreparedStatement getPartInfo = + conn.prepareStatement( "SELECT P.partdesc, P.price, P.qty_available " + + "FROM trafodion.sales.parts P " + + "WHERE partnum = ? " + ) ; + + getPartInfo.setInt( 1, partNum ) ; + + ResultSet rs = getPartInfo.executeQuery() ; + rs.next() ; + + partDescription[0] = rs.getString( 1 ) ; + unitPrice[0] = rs.getBigDecimal( 2 ) ; + qtyAvailable[0] = rs.getInt( 3 ) ; + + rs.close(); + + // Return a result set of rows from the ORDERS table listing orders + // that included this part. Each ORDERS row is augmented with the + // quantity of this part that was ordered. + PreparedStatement getOrders = + conn.prepareStatement( "SELECT O.*, QTY.QTY_ORDERED " + + "FROM trafodion.sales.orders O " + + " , ( select ordernum, sum(qty_ordered) as QTY_ORDERED " + + " from trafodion.sales.odetail " + + " where partnum = ? " + + " group by ordernum ) QTY " + + "WHERE O.ordernum = QTY.ordernum " + + "ORDER BY O.ordernum " + ) ; + + + getOrders.setInt( 1, partNum ) ; + orders[0] = getOrders.executeQuery() ; + + // Return a result set of rows from the PARTLOC table listing + // locations that have this part in stock and the quantity they + // have on hand. + PreparedStatement getLocations = + conn.prepareStatement( "SELECT * " + + "FROM trafodion.invent.partloc " + + "WHERE partnum = ? " + ) ; + + getLocations.setInt( 1, partNum ) ; + locations[0] = getLocations.executeQuery() ; + + // Return a result set of rows from the PARTSUPP table listing + // suppliers who supply this part. + PreparedStatement getSuppliers = + conn.prepareStatement( "SELECT * " + + "FROM trafodion.invent.partsupp " + + "WHERE partnum = ? " + ) ; + + getSuppliers.setInt( 1, partNum ) ; + suppliers[0] = getSuppliers.executeQuery() ; + + // Return a result set of rows from the EMPLOYEE table listing + // sales reps that have sold this part. + PreparedStatement getReps = + conn.prepareStatement( "SELECT * " + + "FROM trafodion.persnl.employee " + + "WHERE empnum in ( SELECT O.salesrep " + + " FROM trafodion.sales.orders O, " + + " trafodion.sales.odetail D " + + " WHERE D.partnum = ? " + + " AND O.ordernum = D.ordernum ) " + + "ORDER BY empnum " + ) ; + + getReps.setInt( 1, partNum ) ; + reps[0] = getReps.executeQuery() ; + +} + http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/partlocations.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/partlocations.java b/docs/2.0.0/spj_guide/resources/source/partlocations.java new file mode 100644 index 0000000..88cdaae --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/partlocations.java @@ -0,0 +1,42 @@ +// The PARTLOCATIONS procedure accepts a part number and quantity and returns a +// set of location codes that have the exact quantity and a set of location +// codes that have more than that quantity. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#partlocations-procedure +// for more documentation. +public static void partLocations( int partNum + , int quantity + , ResultSet exactly[] + , ResultSet moreThan[] + ) throws SQLException + +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getLocationsExact = + conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " + + "FROM trafodion.invent.partloc L " + + "WHERE L.partnum = ? " + + " AND L.qty_on_hand = ? " + + " ORDER BY L.partnum " + ) ; + + getLocationsExact.setInt( 1, partNum ) ; + getLocationsExact.setInt( 2, quantity) ; + + PreparedStatement getLocationsMoreThan = + conn.prepareStatement( "SELECT L.loc_code, L.partnum, L.qty_on_hand " + + "FROM trafodion.invent.partloc L " + + "WHERE L.partnum = ? " + + " AND L.qty_on_hand > ? " + + "ORDER BY L.partnum " + ) ; + + getLocationsMoreThan.setInt( 1, partNum ) ; + getLocationsMoreThan.setInt( 2, quantity) ; + + exactly[0] = getLocationsExact.executeQuery() ; + moreThan[0] = getLocationsMoreThan.executeQuery() ; + +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/persnl_dept_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/persnl_dept_table.sql b/docs/2.0.0/spj_guide/resources/source/persnl_dept_table.sql new file mode 100644 index 0000000..91b35f3 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/persnl_dept_table.sql @@ -0,0 +1,70 @@ +CREATE TABLE trafodion.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 trafodion.persnl.dept + ADD CONSTRAINT mgrnum_constrnt + CHECK (manager BETWEEN 0000 AND 9999) + ; + +ALTER TABLE trafodion.persnl.dept + ADD CONSTRAINT deptnum_constrnt + CHECK ( deptnum IN + ( 1000 + , 1500 + , 2000 + , 2500 + , 3000 + , 3100 + , 3200 + , 3300 + , 3500 + , 4000 + , 4100 + , 9000 + ) + ) + ; +3 +CREATE VIEW trafodion.persnl.mgrlist +( first_name +, last_name +, department +) +AS SELECT + first_name +, last_name +, deptname +FROM dept, employee +WHERE dept.manager = employee.empnum +; + +INSERT INTO trafodion.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 trafodion.persnl.dept ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/persnl_employee_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/persnl_employee_table.sql b/docs/2.0.0/spj_guide/resources/source/persnl_employee_table.sql new file mode 100644 index 0000000..58bac1f --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/persnl_employee_table.sql @@ -0,0 +1,99 @@ +CREATE TABLE trafodion.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 trafodion.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 trafodion.persnl.emplist AS +SELECT + empnum +, first_name +, last_name +, deptnum +, jobcode +FROM employee +; + +INSERT INTO trafodion.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 trafodion.persnl.employee ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/persnl_job_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/persnl_job_table.sql b/docs/2.0.0/spj_guide/resources/source/persnl_job_table.sql new file mode 100644 index 0000000..29d59f1 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/persnl_job_table.sql @@ -0,0 +1,20 @@ +CREATE TABLE trafodion.persnl.job +( jobcode NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL +, jobdesc VARCHAR (18) DEFAULT ' ' NOT NULL +, PRIMARY KEY ( jobcode ) +) ; + +INSERT INTO trafodion.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 trafodion.persnl.job ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/persnl_project_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/persnl_project_table.sql b/docs/2.0.0/spj_guide/resources/source/persnl_project_table.sql new file mode 100644 index 0000000..21526ec --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/persnl_project_table.sql @@ -0,0 +1,45 @@ +CREATE TABLE trafodion.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 trafodion.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 trafodion.persnl.project ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/persnl_schema.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/persnl_schema.sql b/docs/2.0.0/spj_guide/resources/source/persnl_schema.sql new file mode 100644 index 0000000..140ab97 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/persnl_schema.sql @@ -0,0 +1,2 @@ +CREATE SCHEMA trafodion.persnl ; +SET SCHEMA trafodion.persnl ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/projectTeam.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/projectTeam.java b/docs/2.0.0/spj_guide/resources/source/projectTeam.java new file mode 100644 index 0000000..0d150c4 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/projectTeam.java @@ -0,0 +1,25 @@ +// The PROJECTTEAM procedure accepts a project code and returns the +// employee number, first name, last name, and location of the employees +// assigned to that project. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#projectteam-procedure +// for more documentation. +public static void projectTeam( int projectCode + , ResultSet[] members + ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getMembers = + conn.prepareStatement( "SELECT E.empnum, E.first_name, E.last_name, D.location " + + "FROM trafodion.persnl.employee E, trafodion.persnl.dept D, trafodion.persnl.project P " + + "WHERE P.projcode = ? " + + " AND P.empnum = E.empnum " + + " AND E.deptnum = D.deptnum " + ) ; + + getMembers.setInt( 1, projectCode ) ; + members[0] = getMembers.executeQuery() ; + +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/sales_customer_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/sales_customer_table.sql b/docs/2.0.0/spj_guide/resources/source/sales_customer_table.sql new file mode 100644 index 0000000..31efef0 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/sales_customer_table.sql @@ -0,0 +1,30 @@ +CREATE TABLE trafodion.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 trafodion.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 trafodion.sales.customer ON EVERY COLUMN; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/sales_odetail_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/sales_odetail_table.sql b/docs/2.0.0/spj_guide/resources/source/sales_odetail_table.sql new file mode 100644 index 0000000..da6fa37 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/sales_odetail_table.sql @@ -0,0 +1,84 @@ +CREATE TABLE trafodion.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 trafodion.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 trafodion.sales.odetail ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/sales_orders_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/sales_orders_table.sql b/docs/2.0.0/spj_guide/resources/source/sales_orders_table.sql new file mode 100644 index 0000000..b418082 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/sales_orders_table.sql @@ -0,0 +1,66 @@ +CREATE TABLE trafodion.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 trafodion.sales.orders + ADD CONSTRAINT trafodion.sales.date_constrnt CHECK ( deliv_date >= order_date ) + ; + +CREATE INDEX xordrep ON orders +( salesrep +) ; + +CREATE INDEX xordcus ON orders +( custnum +) ; + +CREATE VIEW trafodion.sales.ordrep AS SELECT + empnum +, last_name +, ordernum +, o.custnum +FROM + trafodion.persnl.employee e +, trafodion.sales.orders o +, trafodion.sales.customer c +WHERE e.empnum = o.salesrep + AND o.custnum = C.custnum +; + +CREATE INDEX xcustnam ON customer +( +custname +) ; + +CREATE VIEW trafodion.sales.custlist AS SELECT + custnum +, custname +, street +, city +, state +, postcode +FROM trafodion.sales.customer +; + +INSERT INTO trafodion.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 trafodion.sales.orders ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/sales_parts_table.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/sales_parts_table.sql b/docs/2.0.0/spj_guide/resources/source/sales_parts_table.sql new file mode 100644 index 0000000..267a814 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/sales_parts_table.sql @@ -0,0 +1,45 @@ +CREATE TABLE trafodion.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 trafodion.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 trafodion.sales.parts ON EVERY COLUMN ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/sales_schema.sql ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/sales_schema.sql b/docs/2.0.0/spj_guide/resources/source/sales_schema.sql new file mode 100644 index 0000000..f3867bd --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/sales_schema.sql @@ -0,0 +1,2 @@ +CREATE SCHEMA trafodion.sales ; +SET SCHEMA trafodion.sales ; http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/supplierinfo.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/supplierinfo.java b/docs/2.0.0/spj_guide/resources/source/supplierinfo.java new file mode 100644 index 0000000..c98a392 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/supplierinfo.java @@ -0,0 +1,38 @@ +// The SUPPLIERINFO procedure accepts a supplier number and returns the +// supplier's name, street, city, state, and post code to separate output +// parameters. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#supplierinfo-procedure +// for more documentation. +public static void supplierInfo( BigDecimal suppNum + , String[] suppName + , String[] streetAddr + , String[] cityName + , String[] stateName + , String[] postCode + ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getSupplier = + conn.prepareStatement( "SELECT suppname, street, city, " + + " state, postcode " + + "FROM trafodion.invent.supplier " + + "WHERE suppnum = ?" + ) ; + + getSupplier.setBigDecimal( 1, suppNum ) ; + ResultSet rs = getSupplier.executeQuery() ; + rs.next() ; + + suppName[0] = rs.getString( 1 ) ; + streetAddr[0] = rs.getString( 2 ) ; + cityName[0] = rs.getString( 3 ) ; + stateName[0] = rs.getString( 4 ) ; + postCode[0] = rs.getString( 5 ) ; + + rs.close() ; + conn.close() ; + +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/supplyquantities.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/supplyquantities.java b/docs/2.0.0/spj_guide/resources/source/supplyquantities.java new file mode 100644 index 0000000..59a6911 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/supplyquantities.java @@ -0,0 +1,32 @@ +// The SUPPLYQUANTITIES procedure returns the average, minimum, and maximum +// quantities of available parts in inventory to separate output +// parameters. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#supplyquantities-procedure +// for more documentation. +public static void supplyQuantities( int[] avgQty + , int[] minQty + , int[] maxQty + ) throws SQLException +{ + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getQty = + conn.prepareStatement( "SELECT AVG(qty_on_hand), " + + " MIN(qty_on_hand), " + + " MAX(qty_on_hand) " + + "FROM trafodion.invent.partloc" + ) ; + + ResultSet rs = getQty.executeQuery() ; + rs.next() ; + + avgQty[0] = rs.getInt( 1 ) ; + minQty[0] = rs.getInt( 2 ) ; + maxQty[0] = rs.getInt( 3 ) ; + + rs.close() ; + conn.close() ; + +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/topSalesReps.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/topSalesReps.java b/docs/2.0.0/spj_guide/resources/source/topSalesReps.java new file mode 100644 index 0000000..beee8ca --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/topSalesReps.java @@ -0,0 +1,43 @@ +// The TOPSALESREPS procedure accepts a number representing the fiscal +// quarter (1, 2, 3, and 4, with each number representing a range of +// months) and returns the employee number, first name, last name, and sale +// figures of the top five sales representatives who had the highest sales +// (unit_price * qty_ordered) that quarter. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#topsalesreps-procedure +// for more documentation. +public static void topSalesReps( int whichQuarter + , ResultSet[] topReps + ) throws SQLException +{ + if ( whichQuarter < 1 || whichQuarter > 4 ) + { + throw new SQLException ( "Invalid value for quarter. " + + "Retry the CALL statement " + + "using a number from 1 to 4 " + + "to represent the quarter." + , "38001" + ) ; + } + + Connection conn = + DriverManager.getConnection( "jdbc:default:connection" ) ; + + PreparedStatement getTopReps = + conn.prepareStatement( "SELECT [first 5] e.empnum, e.first_name, " + + "e.last_name, totals.total " + + "FROM trafodion.persnl.employee e, " + + " ( SELECT o.salesrep, " + + " SUM( od.unit_price * od.qty_ordered ) as total " + + " FROM trafodion.sales.orders o, trafodion.sales.odetail od " + + " WHERE o.ordernum = od.ordernum " + + " AND QUARTER( o.order_date ) = ? " + + " GROUP BY o.salesrep " + + " ) totals " + + "WHERE e.empnum = totals.salesrep " + + "ORDER BY totals.total DESCENDING " + ) ; + + getTopReps.setInt( 1, whichQuarter ) ; + topReps[0] = getTopReps.executeQuery() ; +} http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/resources/source/totalPrice.java ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/resources/source/totalPrice.java b/docs/2.0.0/spj_guide/resources/source/totalPrice.java new file mode 100644 index 0000000..5e1ff15 --- /dev/null +++ b/docs/2.0.0/spj_guide/resources/source/totalPrice.java @@ -0,0 +1,47 @@ +// The TOTALPRICE procedure accepts the quantity, shipping speed, and price +// of an item, calculates the total price, including tax and shipping +// charges, and returns the total price to an input/output parameter. +// +// See http://trafodion.incubator.apache.org/docs/spj_guide/index.html#totalprice-procedure +// for more documentation. +public static void totalPrice( BigDecimal qtyOrdered + , String shippingSpeed + , BigDecimal[] price + ) throws SQLException +{ + BigDecimal shipcharge = new BigDecimal( 0 ) ; + + if ( shippingSpeed.equals( "economy" ) ) + { + shipcharge = new BigDecimal( 1.95 ) ; + } + else if ( shippingSpeed.equals( "standard" ) ) + { + shipcharge = new BigDecimal( 4.99 ) ; + } + else if ( shippingSpeed.equals( "nextday" ) ) + { + shipcharge = new BigDecimal( 14.99 ) ; + } + else + { + throw new SQLException( "Invalid value for shipping speed. " + + "Retry the CALL statement using " + + "'economy' for 7 to 9 days, " + + "'standard' for 3 to 5 days, or " + + "'nextday' for one day." + , "38002" + ) ; + } + + BigDecimal subtotal = price[0].multiply( qtyOrdered ) ; + BigDecimal tax = new BigDecimal( 0.0825 ) ; + BigDecimal taxcharge = subtotal.multiply( tax ) ; + BigDecimal charges = taxcharge.add( shipcharge ) ; + BigDecimal totalprice = subtotal.add( charges ) ; + + totalprice = totalprice.setScale( 2, BigDecimal.ROUND_HALF_EVEN ) ; + price[0] = totalprice ; + +} + http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/source-repository.html ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/source-repository.html b/docs/2.0.0/spj_guide/source-repository.html new file mode 100644 index 0000000..6db42b7 --- /dev/null +++ b/docs/2.0.0/spj_guide/source-repository.html @@ -0,0 +1,110 @@ +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> +<!-- Generated by Apache Maven Doxia at Apr 11, 2016 --> +<html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"> + <head> + <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> + <title>Source Repository</title> + <style type="text/css" media="all"> + @import url("./css/maven-base.css"); + @import url("./css/maven-theme.css"); + @import url("./css/site.css"); + </style> + <link rel="stylesheet" href="./css/print.css" type="text/css" media="print" /> + <meta name="Date-Revision-yyyymmdd" content="20160411" /> + <meta http-equiv="Content-Language" content="en" /> + + </head> + <body class="composite"> + <div id="banner"> + <div id="bannerLeft"> + Trafodion SPJ Guide + </div> + <div class="clear"> + <hr/> + </div> + </div> + <div id="breadcrumbs"> + + + <div class="xleft"> + <span id="publishDate">Last Published: 2016-04-11</span> + | <span id="projectVersion">Version: 2.0.0</span> + </div> + <div class="xright"> <a href="./" title="Trafodion SPJ Guide">Trafodion SPJ Guide</a> + + + </div> + <div class="clear"> + <hr/> + </div> + </div> + <div id="leftColumn"> + <div id="navcolumn"> + + + <h5>Parent Project</h5> + <ul> + <li class="none"> + <a href="index.html" title="Apache Trafodion">Apache Trafodion</a> + </li> + </ul> + <h5>Project Documentation</h5> + <ul> + <li class="expanded"> + <a href="project-info.html" title="Project Information">Project Information</a> + <ul> + <li class="none"> + <a href="team-list.html" title="Project Team">Project Team</a> + </li> + <li class="none"> + <a href="mail-lists.html" title="Mailing Lists">Mailing Lists</a> + </li> + <li class="none"> + <a href="issue-tracking.html" title="Issue Tracking">Issue Tracking</a> + </li> + <li class="none"> + <a href="license.html" title="Project License">Project License</a> + </li> + <li class="none"> + <a href="project-summary.html" title="Project Summary">Project Summary</a> + </li> + <li class="none"> + <strong>Source Repository</strong> + </li> + <li class="none"> + <a href="integration.html" title="Continuous Integration">Continuous Integration</a> + </li> + <li class="none"> + <a href="dependencies.html" title="Dependencies">Dependencies</a> + </li> + </ul> + </li> + </ul> + <a href="http://maven.apache.org/"; title="Built by Maven" class="poweredBy"> + <img class="poweredBy" alt="Built by Maven" src="./images/logos/maven-feather.png" /> + </a> + + + </div> + </div> + <div id="bodyColumn"> + <div id="contentBox"> + <div class="section"><h2>Overview<a name="Overview"></a></h2><a name="Overview"></a><p>This project uses <a class="externalLink" href="http://git-scm.com/";>Git</a> to manage its source code. Instructions on Git use can be found at <a class="externalLink" href="http://git-scm.com/documentation";>http://git-scm.com/documentation</a>.</p></div><div class="section"><h2>Web Browser Access<a name="Web_Browser_Access"></a></h2><a name="Web_Browser_Access"></a><p>The following is a link to a browsable version of the source repository:</p><div class="source"><pre><a class="externalLink" href="https://git-wip-us.apache.org/repos/asf?p=incubator-trafodion.git";>https://git-wip-us.apache.org/repos/asf?p=incubator-trafodion.git</a></pre></div></div><div class="section"><h2>Anonymous Access<a name="Anonymous_Access"></a></h2><a name="Anonymous_Access"></a><p>The source can be checked out anonymously from Git with this command (See <a class="externalLink" href="http://git-scm.com/docs/git-cl one">http://git-scm.com/docs/git-clone</a>):</p><div class="source"><pre>$ git clone http://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</pre></div></div><div class="section"><h2>Developer Access<a name="Developer_Access"></a></h2><a name="Developer_Access"></a><p>Only project developers can access the Git tree via this method (See <a class="externalLink" href="http://git-scm.com/docs/git-clone";>http://git-scm.com/docs/git-clone</a>).</p><div class="source"><pre>$ git clone https://git-wip-us.apache.org/repos/asf/incubator-trafodion.git</pre></div></div><div class="section"><h2>Access from Behind a Firewall<a name="Access_from_Behind_a_Firewall"></a></h2><a name="Access_from_Behind_a_Firewall"></a><p>Refer to the documentation of the SCM used for more information about access behind a firewall.</p></div> + </div> + </div> + <div class="clear"> + <hr/> + </div> + <div id="footer"> + <div class="xright"> + Copyright © 2015-2016 + <a href="http://www.apache.org";>Apache Software Foundation</a>. + All Rights Reserved. + + </div> + <div class="clear"> + <hr/> + </div> + </div> + </body> +</html> http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/spj_guide/team-list.html ---------------------------------------------------------------------- diff --git a/docs/2.0.0/spj_guide/team-list.html b/docs/2.0.0/spj_guide/team-list.html new file mode 100644 index 0000000..350d2fe --- /dev/null +++ b/docs/2.0.0/spj_guide/team-list.html @@ -0,0 +1,110 @@ +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> +<!-- Generated by Apache Maven Doxia at Apr 11, 2016 --> +<html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"> + <head> + <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> + <title>Project Team</title> + <style type="text/css" media="all"> + @import url("./css/maven-base.css"); + @import url("./css/maven-theme.css"); + @import url("./css/site.css"); + </style> + <link rel="stylesheet" href="./css/print.css" type="text/css" media="print" /> + <meta name="Date-Revision-yyyymmdd" content="20160411" /> + <meta http-equiv="Content-Language" content="en" /> + + </head> + <body class="composite"> + <div id="banner"> + <div id="bannerLeft"> + Trafodion SPJ Guide + </div> + <div class="clear"> + <hr/> + </div> + </div> + <div id="breadcrumbs"> + + + <div class="xleft"> + <span id="publishDate">Last Published: 2016-04-11</span> + | <span id="projectVersion">Version: 2.0.0</span> + </div> + <div class="xright"> <a href="./" title="Trafodion SPJ Guide">Trafodion SPJ Guide</a> + + + </div> + <div class="clear"> + <hr/> + </div> + </div> + <div id="leftColumn"> + <div id="navcolumn"> + + + <h5>Parent Project</h5> + <ul> + <li class="none"> + <a href="index.html" title="Apache Trafodion">Apache Trafodion</a> + </li> + </ul> + <h5>Project Documentation</h5> + <ul> + <li class="expanded"> + <a href="project-info.html" title="Project Information">Project Information</a> + <ul> + <li class="none"> + <strong>Project Team</strong> + </li> + <li class="none"> + <a href="mail-lists.html" title="Mailing Lists">Mailing Lists</a> + </li> + <li class="none"> + <a href="issue-tracking.html" title="Issue Tracking">Issue Tracking</a> + </li> + <li class="none"> + <a href="license.html" title="Project License">Project License</a> + </li> + <li class="none"> + <a href="project-summary.html" title="Project Summary">Project Summary</a> + </li> + <li class="none"> + <a href="source-repository.html" title="Source Repository">Source Repository</a> + </li> + <li class="none"> + <a href="integration.html" title="Continuous Integration">Continuous Integration</a> + </li> + <li class="none"> + <a href="dependencies.html" title="Dependencies">Dependencies</a> + </li> + </ul> + </li> + </ul> + <a href="http://maven.apache.org/"; title="Built by Maven" class="poweredBy"> + <img class="poweredBy" alt="Built by Maven" src="./images/logos/maven-feather.png" /> + </a> + + + </div> + </div> + <div id="bodyColumn"> + <div id="contentBox"> + <div class="section"><h2>The Team<a name="The_Team"></a></h2><a name="The_Team"></a><p>A successful project requires many people to play many roles. Some members write code or documentation, while others are valuable as testers, submitting patches and suggestions.</p><p>The team is comprised of Members and Contributors. Members have direct access to the source of a project and actively evolve the code-base. Contributors improve the project through submission of patches and suggestions to the Members. The number of Contributors to the project is unbounded. Get involved today. All contributions to the project are greatly appreciated.</p><div class="section"><h3>Members<a name="Members"></a></h3><a name="Members"></a><p>The following is a list of developers with commit privileges that have directly contributed to the project in one way or another.</p><table border="0" class="bodyTable"><tr class="a"><th>Image</th><th>Id</th><th>Name</th><th>Email</th><th>URL</th></tr><tr class= "b"><td><img src="http://www.gravatar.com/avatar/7a383ed4a993e46794e53cc34993b29f?d=mm&s=60"; alt="" /></td><td><a name="TrafodionDeveloper"></a>TrafodionDeveloper</td><td>See list of Trafodion developers</td><td><a class="externalLink" href="mailto:[email protected]";>[email protected]</a></td><td><a class="externalLink" href="https://cwiki.apache.org/confluence/display/TRAFODION/Contributors";>https://cwiki.apache.org/confluence/display/TRAFODION/Contributors</a></td></tr></table></div><div class="section"><h3>Contributors<a name="Contributors"></a></h3><a name="Contributors"></a><p>The following additional people have contributed to this project through the way of suggestions, patches or documentation.</p><table border="0" class="bodyTable"><tr class="a"><th>Image</th><th>Name</th><th>Email</th><th>URL</th></tr><tr class="b"><td><img src="http://www.gravatar.com/avatar/7a383ed4a993e46794e53cc34993b29f?d=mm&s=60"; alt="" /></td><td>See list of Trafodion contributors</td><td><a class="externalLink" href="mailto:[email protected]";>[email protected]</a></td><td><a class="externalLink" href="https://cwiki.apache.org/confluence/display/TRAFODION/Contributors";>https://cwiki.apache.org/confluence/display/TRAFODION/Contributors</a></td></tr></table></div></div> + </div> + </div> + <div class="clear"> + <hr/> + </div> + <div id="footer"> + <div class="xright"> + Copyright © 2015-2016 + <a href="http://www.apache.org";>Apache Software Foundation</a>. + All Rights Reserved. + + </div> + <div class="clear"> + <hr/> + </div> + </div> + </body> +</html> http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/sql_reference/Trafodion_SQL_Reference_Manual.pdf ---------------------------------------------------------------------- diff --git a/docs/2.0.0/sql_reference/Trafodion_SQL_Reference_Manual.pdf b/docs/2.0.0/sql_reference/Trafodion_SQL_Reference_Manual.pdf index 839e3fb..ebc4a99 100644 Binary files a/docs/2.0.0/sql_reference/Trafodion_SQL_Reference_Manual.pdf and b/docs/2.0.0/sql_reference/Trafodion_SQL_Reference_Manual.pdf differ http://git-wip-us.apache.org/repos/asf/incubator-trafodion-site/blob/11567d60/docs/2.0.0/sql_reference/dependencies.html ---------------------------------------------------------------------- diff --git a/docs/2.0.0/sql_reference/dependencies.html b/docs/2.0.0/sql_reference/dependencies.html index fd2440a..818f9d3 100644 --- a/docs/2.0.0/sql_reference/dependencies.html +++ b/docs/2.0.0/sql_reference/dependencies.html @@ -1,5 +1,5 @@ <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";> -<!-- Generated by Apache Maven Doxia at Mar 15, 2016 --> +<!-- Generated by Apache Maven Doxia at Apr 11, 2016 --> <html xmlns="http://www.w3.org/1999/xhtml"; xml:lang="en" lang="en"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> @@ -10,7 +10,7 @@ @import url("./css/site.css"); </style> <link rel="stylesheet" href="./css/print.css" type="text/css" media="print" /> - <meta name="Date-Revision-yyyymmdd" content="20160315" /> + <meta name="Date-Revision-yyyymmdd" content="20160411" /> <meta http-equiv="Content-Language" content="en" /> </head> @@ -27,7 +27,7 @@ <div class="xleft"> - <span id="publishDate">Last Published: 2016-03-15</span> + <span id="publishDate">Last Published: 2016-04-11</span> | <span id="projectVersion">Version: 2.0.0</span> </div> <div class="xright"> <a href="./" title="Trafodion SQL Reference Manual">Trafodion SQL Reference Manual</a>
