gsd-questions/queries/task2-constraints-and-integ...

SET FEEDBACK OFF
DROP TABLE T1 CASCADE CONSTRAINTS;
DROP TABLE T2 CASCADE CONSTRAINTS;
CREATE TABLE T1 (A INT, B INT, C INT, CONSTRAINT T1_PS PRIMARY KEY (A,B), CONSTRAINT T1_SK UNIQUE (C)); 
CREATE TABLE T2 (A INT, B INT, C INT, D INT, E INT, CONSTRAINT T2_PS PRIMARY KEY (A), CONSTRAINT T2_FS1 FOREIGN KEY (B,C) REFERENCES T1(A,B), CONSTRAINT T2_FS2 FOREIGN KEY (D) REFERENCES T1(C), CONSTRAINT T2_E_NN CHECK (E IS NOT NULL), CONSTRAINT T2_E_13 CHECK (E BETWEEN 1 AND 3));
SET FEEDBACK OFF

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T1 VALUES (1, 2, 5);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T1 VALUES (1, 2, 30);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T1 VALUES (1, 1, 50);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T1 VALUES (1, NULL, NULL);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T2 VALUES (117, 2, 2, 5, 5);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T2 VALUES (109, 3, 1, 10, 3);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T2 VALUES (103, 1, 1, 20, 2);

SET FEEDBACK OFF
DELETE FROM T2;
DELETE FROM T1;
INSERT INTO T1 VALUES (1, 1, 5);
INSERT INTO T1 VALUES (2, 2, 10);
INSERT INTO T1 VALUES (3, 3, 15);
INSERT INTO T1 VALUES (4, 4, 20);
INSERT INTO T1 VALUES (5, 5, 25);
INSERT INTO T2 VALUES (100, NULL, NULL, 15, 1);
INSERT INTO T2 VALUES (101, 2, 2, 25, 2);
INSERT INTO T2 VALUES (102, 2, 2, NULL, 3);
INSERT INTO T2 VALUES (103, 3, 3, 10, 3);
INSERT INTO T2 VALUES (104, 3, 3, 10, 3);
SET FEEDBACK ON

INSERT INTO T2 VALUES (110, 4, 4, 35, 1);