📄 dml060.out
字号:
ij> AUTOCOMMIT OFF;ij> -- MODULE DML060-- SQL Test Suite, V6.0, Interactive SQL, dml060.sql-- 59-byte ID-- TEd Version #-- AUTHORIZATION HU set schema HU;0 rows inserted/updated/deletedij> --O SELECT USER FROM HU.ECCO; VALUES USER;1 --------------------------------------------------------------------------------------------------------------------------------HU ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment-- date_time print-- TEST:0261 WHERE (2 * (c1 - c2)) BETWEEN! SELECT COL1, COL2 FROM VTABLE WHERE(2*(COL3 - COL2)) BETWEEN 5 AND 200 ORDER BY COL1;COL1 |COL2 -----------------------10 |20 100 |200 ij> -- PASS:0261 If 2 rows are selected ?-- PASS:0261 If first row is ( 10, 20)?-- PASS:0261 If second row is (100, 200)?-- END TEST >>> 0261 <<< END TEST-- ********************************************************************-- TEST:0262 WHERE clause with computation, ANY/ALL subqueries! UPDATE VTABLE SET COL1 = 1 WHERE COL1 = 0;1 row inserted/updated/deletedij> -- PASS:0262 If 1 row is updated? SELECT COL1, COL2 FROM VTABLE WHERE (COL3 * COL2/COL1) > ALL (SELECT HOURS FROM WORKS) OR -(COL3 * COL2/COL1) > ANY (SELECT HOURS FROM WORKS) ORDER BY COL1;COL1 |COL2 -----------------------100 |200 1000 |-2000 ij> -- PASS:0262 If 2 rows are selected?-- PASS:0262 If first row is ( 100, 200)?-- PASS:0262 If second row is (1000, -2000)?-- restore ROLLBACK WORK;ij> -- END TEST >>> 0262 <<< END TEST-- ******************************************************************-- TEST:0263 Computed column in ORDER BY! SELECT COL1, (COL3 * COL2/COL1 - COL2 + 10) FROM VTABLE WHERE COL1 > 0 ORDER BY 2;COL1 |2 -----------------------1000 |-3990 10 |50 100 |410 ij> -- PASS:0263 If 3 rows are selected in order with values:?-- PASS:0263 (1000, -3990)?-- PASS:0263 ( 10, 50)?-- PASS:0263 ( 100, 410)?-- END TEST >>> 0263 <<< END TEST-- ********************************************************************-- TEST:0265 Update:searched - view with check option!-- setup INSERT INTO WORKS VALUES('E3','P4',50);1 row inserted/updated/deletedij> -- PASS:0265 If 1 row is inserted? SELECT EMPNUM, PNUM, HOURS FROM SUBSP;EM&|PN&|HOURS --------------E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0265 If 2 rows are selected? SELECT * FROM WORKS;EM&|PN&|HOURS --------------E1 |P1 |40 E1 |P2 |20 E1 |P3 |80 E1 |P4 |20 E1 |P5 |12 E1 |P6 |12 E2 |P1 |40 E2 |P2 |80 E3 |P2 |20 E4 |P2 |20 E4 |P4 |40 E4 |P5 |80 E3 |P4 |50 ij> -- PASS:0265 If 13 rows selected?--O UPDATE SUBSP --O SET EMPNUM = 'E9'--O WHERE PNUM = 'P2';-- PASS:0265 If ERROR, view check constraint, 0 rows are updated? SELECT * FROM WORKS;EM&|PN&|HOURS --------------E1 |P1 |40 E1 |P2 |20 E1 |P3 |80 E1 |P4 |20 E1 |P5 |12 E1 |P6 |12 E2 |P1 |40 E2 |P2 |80 E3 |P2 |20 E4 |P2 |20 E4 |P4 |40 E4 |P5 |80 E3 |P4 |50 ij> -- PASS:0265 If 13 rows selected and no EMPNUM = 'E9'?-- restore ROLLBACK WORK;ij> -- END TEST >>> 0265 <<< END TEST-- ******************************************************************-- TEST:0266 Update:searched - UNIQUE violation under view!-- setup INSERT INTO WORKS VALUES('E3','P4',50);1 row inserted/updated/deletedij> -- PASS:0266 If 1 row is inserted? SELECT EMPNUM, PNUM, HOURS FROM SUBSP;EM&|PN&|HOURS --------------E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0266 If 2 rows are selected? SELECT * FROM WORKS WHERE EMPNUM = 'E3';EM&|PN&|HOURS --------------E3 |P2 |20 E3 |P4 |50 ij> -- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?--O UPDATE SUBSP--O SET PNUM = 'P6'--O WHERE EMPNUM = 'E3';-- PASS:0266 If ERROR, unique constraint, 0 rows updated?--O SELECT EMPNUM, PNUM, HOURS--O FROM SUBSP;-- PASS:0266 If 2 rows are selected?--O SELECT * FROM WORKS WHERE EMPNUM = 'E3';-- PASS:0266 If 2 rows selected and PNUM values are 'P2' and 'P4'?-- restore ROLLBACK WORK;ij> -- END TEST >>> 0266 <<< END TEST-- ******************************************************************-- TEST:0267 Update compound key, interim uniqueness conflict! DELETE FROM WORKS1;0 rows inserted/updated/deletedij> -- Making sure the table is empty-- setup INSERT INTO WORKS1 VALUES ('P1','P6',1);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P6',2);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P6',3);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P6',4);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P6',5);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P6',6);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P1','P5',7);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P5',8);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P5',9);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P5',10);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P5',11);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P5',12);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P1','P4',13);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P4',14);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P4',15);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P4',16);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P4',17);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P4',18);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P1','P3',19);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P3',20);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P3',21);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P3',22);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P3',23);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P3',24);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P1','P2',25);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P2',26);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P2',27);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P2',28);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P2',29);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P2',30);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P1','P1',31);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P2','P1',32);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P3','P1',33);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P4','P1',34);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P5','P1',35);1 row inserted/updated/deletedij> INSERT INTO WORKS1 VALUES ('P6','P1',36);1 row inserted/updated/deletedij> UPDATE WORKS1 SET PNUM = EMPNUM, EMPNUM = PNUM;36 rows inserted/updated/deletedij> -- PASS:0267 If 36 rows are updated?--O SELECT COUNT(*) SELECT * FROM WORKS1 WHERE EMPNUM = 'P1' AND HOURS > 30;EM&|PN&|HOURS --------------P1 |P1 |31 P1 |P2 |32 P1 |P3 |33 P1 |P4 |34 P1 |P5 |35 P1 |P6 |36 ij> -- PASS:0267 If count = 6?-- restore ROLLBACK WORK;ij> -- END TEST >>> 0267 <<< END TEST-- *************************************************////END-OF-MODULE;ij> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -