blob: 3fc3da145ab6fb62bff265d4d1e0954e8b919cda [file] [log] [blame]
ij> AUTOCOMMIT OFF;
ij> -- MODULE DML114
-- SQL Test Suite, V6.0, Interactive SQL, dml114.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION FLATER
set schema FLATER;
0 rows inserted/updated/deleted
ij> --0 SELECT USER FROM HU.ECCO;
VALUES USER;
1
--------------------------------------------------------------------------------------------------------------------------------
FLATER
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
ROLLBACK WORK;
ij> -- date_time print
-- TEST:0635 Feature 13, grouped operations (static)!
CREATE VIEW WORKWEEK AS
SELECT EMPNUM, HOURS FROM HU.WORKS
GROUP BY HOURS, EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0635 If table is created?
COMMIT WORK;
ij> SELECT EMPNUM, SUM (HOURS)
FROM WORKWEEK
WHERE HOURS > 20
GROUP BY EMPNUM
HAVING EMPNUM = 'E1';
EM&|2
---------------
E1 |120
ij> -- PASS:0635 If 1 row selected and EMPNUM = 'E1' and SUM(HOURS) = 120?
SELECT COUNT(*)
FROM WORKWEEK WHERE HOURS > 40;
1
-----------
3
ij> -- PASS:0635 If count = 3?
SELECT EMPNAME
FROM HU.STAFF, WORKWEEK
WHERE HU.STAFF.EMPNUM = WORKWEEK.EMPNUM
AND HOURS = 12;
EMPNAME
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
SELECT COUNT(*), MAX(EMPNUM), MIN(EMPNUM), AVG(HOURS)
FROM WORKWEEK;
1 |2 |3 |4
-----------------------------------
10 |E4 |E1 |43.2000
ij> -- PASS:0635 If 1 row selected and count = 10 and MAX(EMPNUM) = 'E4'?
-- PASS:0635 AND MIN(EMPNUM) = 'E1' and AVG(HOURS) = 43 (approximately)?
SELECT EMPNAME
FROM HU.STAFF WHERE EMPNUM =
(SELECT EMPNUM FROM WORKWEEK
WHERE HOURS = 12);
EMPNAME
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
SELECT EMPNAME
FROM HU.STAFF WHERE EMPNUM =
(SELECT EMPNUM FROM HU.WORKS
GROUP BY EMPNUM, HOURS
HAVING HOURS = 12);
EMPNAME
--------------------
Alice
ij> -- PASS:0635 If 1 row selected and EMPNAME = 'Alice'?
-- NOTE:0635 Cursor subtest deleted.
COMMIT WORK;
ij> --0 DROP VIEW WORKWEEK CASCADE;
DROP VIEW WORKWEEK ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0635 <<< END TEST
-- *********************************************
-- TEST:0637 Feature 14, Qualified * in select list (static)!
CREATE VIEW QUALSTAR AS
SELECT HU.STAFF.*, HOURS FROM HU.STAFF, HU.WORKS
WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
COMMIT WORK;
ij> CREATE VIEW CORRQUALSTAR AS
SELECT BLAH.*, HOURS FROM HU.STAFF BLAH, HU.WORKS
WHERE BLAH.EMPNUM = HU.WORKS.EMPNUM;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
COMMIT WORK;
ij> CREATE VIEW SUBQ2 AS
SELECT DISTINCT * FROM QUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
COMMIT WORK;
ij> CREATE VIEW CORRSUBQ2 AS
SELECT DISTINCT * FROM CORRQUALSTAR;
0 rows inserted/updated/deleted
ij> -- PASS:0637 If view is created?
COMMIT WORK;
ij> SELECT COUNT(*) FROM QUALSTAR;
1
-----------
12
ij> -- PASS:0637 If count = 12?
SELECT COUNT(*) FROM SUBQ2;
1
-----------
10
ij> -- PASS:0637 If count = 10?
SELECT EMPNUM, GRADE, CITY, HOURS
FROM QUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY |HOURS
--------------------------------
E3 |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and GRADE = 13?
-- PASS:0637 AND CITY = 'Vienna' and HOURS = 20?
-- NOTE:0637 Cursor subtest deleted.
SELECT HU.STAFF.*, HOURS
FROM HU.STAFF, HU.WORKS
WHERE HU.STAFF.EMPNUM = HU.WORKS.EMPNUM
AND EMPNAME = 'Carmen';
EM&|EMPNAME |GRADE|CITY |HOURS
-----------------------------------------------------
E3 |Carmen |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3' and EMPNAME = 'Carmen'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
SELECT COUNT(*) FROM CORRQUALSTAR;
1
-----------
12
ij> -- PASS:0637 If count = 12?
SELECT COUNT(*) FROM CORRSUBQ2;
1
-----------
10
ij> -- PASS:0637 If count = 10?
SELECT EMPNUM, GRADE, CITY, HOURS
FROM CORRQUALSTAR WHERE EMPNAME = 'Carmen';
EM&|GRADE|CITY |HOURS
--------------------------------
E3 |13 |Vienna |20
ij> -- PASS:0637 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0637 AND GRADE = 13 and CITY = 'Vienna' and HOURS = 20?
COMMIT WORK;
ij> --0 DROP VIEW QUALSTAR CASCADE;
DROP VIEW SUBQ2 ;
0 rows inserted/updated/deleted
ij> DROP VIEW QUALSTAR ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> --0 DROP VIEW CORRQUALSTAR CASCADE;
DROP VIEW CORRSUBQ2 ;
0 rows inserted/updated/deleted
ij> DROP VIEW CORRQUALSTAR ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0637 <<< END TEST
-- *********************************************
-- TEST:0639 Feature 15, Lowercase Identifiers (static)!
create view Staff (Empnum, empname, Grade, City) as
select empnum, EMPNAME, Grade, cItY from Hu.Staff;
0 rows inserted/updated/deleted
ij> -- PASS:0639 If view is created?
commit work;
ij> SELECT EMPNUM as WhatsHisNumber, GRADE, CITY
FROM Flater.staff FLaterStaff_Flater
WHERE EMPNAME = 'Carmen'
--0 SQL92 does not scope renames into the query, just outside it -- ALC
--0 AND FLATERstaff_fLATER.whatshisnumber = 'E3';
AND FLATERstaff_fLATER.empnum = 'E3';
WH&|GRADE|CITY
-------------------------
E3 |13 |Vienna
ij> -- PASS:0639 If 1 row selected and EMPNUM = 'E3'?
-- PASS:0639 AND GRADE = 13 and CITY = 'Vienna'?
-- NOTE:0639 Cursor subtest deleted.
COMMIT WORK;
ij> --0 DROP VIEW STAFF CASCADE;
DROP VIEW STAFF ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0639 <<< END TEST
-- *********************************************
-- TEST:0641 Feature 16, PRIMARY KEY enhancement (static)!
CREATE TABLE FEAT16 (
EMPNUM INT NOT NULL CONSTRAINT FEAT16_PK PRIMARY KEY,
PNUM INT NOT NULL CONSTRAINT FEAT16_PNUM UNIQUE);
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
COMMIT WORK;
ij> CREATE TABLE BARNO (
P1 INT NOT NULL, P2 CHAR NOT NULL, X1 INT NOT NULL, X2 CHAR NOT NULL,
CONSTRAINT BARNO_UNIQUE UNIQUE (X2, X1),
CONSTRAINT BARNO_PK PRIMARY KEY (P1, P2));
0 rows inserted/updated/deleted
ij> -- PASS:0641 If view is created?
COMMIT WORK;
ij> INSERT INTO FEAT16 VALUES (1, 10);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (2, 20);
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (1, 30);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'FEAT16_PK' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO FEAT16 VALUES (3, 20);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'FEAT16_PNUM' defined on 'FEAT16'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO FEAT16 VALUES (3, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (4, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO FEAT16 VALUES (5, NULL);
ERROR 23502: Column 'PNUM' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (1, 'A', 10, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (2, 'A', 20, 'a');
1 row inserted/updated/deleted
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (1, 'A', 30, 'a');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'BARNO_PK' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, 'A', 20, 'a');
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'BARNO_UNIQUE' defined on 'BARNO'.
ij> -- PASS:0641 If ERROR, unique constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, NULL, 30, 'a');
ERROR 23502: Column 'P2' cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, NULL, 30, 'b');
ERROR 23502: Column 'P2' cannot accept a NULL value.
ij> -- PASS:0641 If ERROR, PRIMARY KEY constraint, 0 rows inserted?
-- PASS:0641 OR ERROR, NOT NULL constraint, 0 rows inserted?
INSERT INTO BARNO VALUES (3, 'A', 30, NULL);
ERROR 23502: Column 'X2' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (3, 'B', 30, NULL);
ERROR 23502: Column 'X2' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
INSERT INTO BARNO VALUES (4, 'B', NULL, NULL);
ERROR 23502: Column 'X1' cannot accept a NULL value.
ij> -- PASS:0641 If 1 row is inserted?
COMMIT WORK;
ij> --0 DROP TABLE FEAT16 CASCADE;
DROP TABLE FEAT16 ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> --0 DROP TABLE BARNO CASCADE;
DROP TABLE BARNO ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0641 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>