-- upper or lower case is accepted
-- SQL-language not fully implemented 
-- what is necessary is available
-- tables do not need primary keys
-- indizes can be defined but are not functional
-- unique indizes checks for unique items
-- cursors are implemented
-- cursors must be in transactions
-- on transaction end cursors are automatically closed
-- multi-connects to database possible
-- transactions are implemented
-- no parallel transactions, but parallel selects
-- no nested transactions
-- no security-functions
-- subselects implemented
-- distinct implemented
-- join implemented
-- order by implemented
-- limiting evaluation results implemented
-- alter table not implemented
-- attributes that are NULL are defined as 0 (NULL only checked with insert)
-- the BETWEEN- and LIKE-operators can only process constant operands
-- the IN-operator can only process constant operands or subselects
-- the LIKE-operator must have a % at the end if blanks are at the end

DROP TABLE test;
DROP TABLE test1;
DROP TABLE test2;
DROP TABLE test3;

CREATE TABLE test (
  code CHARACTER(4) NOT NULL,
  small SMALLINT NOT NULL
);

BEGIN;
INSERT INTO test (code, small) VALUES ('abcd', 10);
END;

SELECT * FROM test;

SELECT COUNT(*) FROM test;

SELECT COUNT(*), a.*, b.small, a.small FROM test a, test b;

SELECT COUNT(*) from test a, test b;

SELECT a.*, b.small, a.small FROM test a, test b;

BEGIN;
INSERT INTO test (code, small) VALUES ('abcd', 11);
DECLARE cur1 CURSOR FOR SELECT * FROM test;
FETCH ALL IN cur1;
CLOSE cur1;
DECLARE cur1 CURSOR FOR SELECT * FROM test;
FETCH IN cur1;
CLOSE cur1;
END;

SELECT MIN(small) FROM test;

SELECT MAX(small) FROM test;

SELECT DISTINCT code FROM test;

CREATE TABLE test1 (
  code CHARACTER(4) NOT NULL,
  small SMALLINT NOT NULL
);

BEGIN;
INSERT INTO test1 (code, small) VALUES ('abcd', 10);
END;

SELECT * FROM test a
WHERE EXISTS (SELECT * FROM test1 b WHERE a.small = b.small);

SELECT * FROM test a
WHERE NOT EXISTS (SELECT * FROM test1 b WHERE a.small = b.small);

SELECT a.* FROM test a, test1 b
WHERE a.small = b.small;

SELECT * FROM test a, test1 b
WHERE a.small = b.small;

SELECT * FROM test WHERE small IN (SELECT small FROM test1);

SELECT * FROM test ORDER BY small;
SELECT * FROM test ORDER BY small ASC;
SELECT * FROM test ORDER BY small DESC;

BEGIN;
INSERT INTO test (code, small) VALUES ('bbcd', 10);
END;

SELECT * FROM test ORDER BY small ASC, code;

BEGIN;
INSERT INTO test1 (code, small) VALUES ('abcd', 11);
END;

SELECT * FROM test a, test1 b ORDER BY a.small ASC, a.code;
SELECT DISTINCT * FROM test a, test1 b ORDER BY a.small ASC, a.code;

SELECT a.code, a.small FROM test a, test1 b 
ORDER BY a.small ASC, a.code;

SELECT DISTINCT a.code, a.small FROM test a, test1 b 
ORDER BY a.small ASC, a.code;

SELECT a.code, b.code FROM test a, test1 b;
SELECT DISTINCT a.code, b.code FROM test a, test1 b;

CREATE TABLE test2 (
  code CHARACTER(4),
  small SMALLINT
);

BEGIN;
INSERT INTO test2 (code) VALUES ('bbcd');
END;

SELECT * FROM test2;
SELECT * FROM test2 WHERE small = 0;

CREATE TABLE test3 (
  code CHARACTER(4),
  small SMALLINT,
  int INTEGER,
  double FLOAT,
  datum DATE,
  zeit TIME,
  zeitstempel TIMESTAMP
);

CREATE UNIQUE INDEX i1test3 ON test3 (
  code
);

CREATE UNIQUE INDEX i2test3 ON test3 (
  small, int
);

CREATE INDEX i3test3 ON test3 (
  small, int
);

CREATE INDEX i4test3 ON test3 (
  small, int
);

BEGIN;
INSERT INTO test3 (code) VALUES ('');
END;

SELECT * FROM test3;

BEGIN;
UPDATE test3 SET datum = '2001-04-18';
END;

SELECT * FROM test3;

BEGIN;
UPDATE test3 SET zeit = '20:10:30';
END;

SELECT * FROM test3;

BEGIN;
UPDATE test3 SET zeitstempel = '2001-04-18-20.10.30.123456';
END;

SELECT * FROM test3;

BEGIN;
UPDATE test3 SET code = '10', small = 10, int = 10, double = 10;
END;

SELECT * FROM test3;

BEGIN;
INSERT INTO test3 VALUES ('11', 11, 11, 11, 
                          '2001-05-18', '21:10:30',
                          '2001-05-18-21.10.30.123456'
                         );
END;

SELECT * FROM test3;
SELECT * FROM test3 ORDER BY code;
SELECT * FROM test3 ORDER BY small;
SELECT * FROM test3 ORDER BY int;
SELECT * FROM test3 ORDER BY double;
SELECT * FROM test3 ORDER BY datum;
SELECT * FROM test3 ORDER BY zeit;
SELECT * FROM test3 ORDER BY zeitstempel;

BEGIN;
INSERT INTO test3 VALUES ('11', 11, 11, 11, 
                          '2001-05-18', '21:10:30',
                          '2001-05-18-21.10.30.123456'
                         );
END;

SELECT * FROM test3;

DROP INDEX i1test3;

BEGIN;
INSERT INTO test3 VALUES ('11', 11, 11, 11, 
                          '2001-05-18', '21:10:30',
                          '2001-05-18-21.10.30.123456'
                         );
END;

SELECT * FROM test3;

DROP INDEX i2test3;

BEGIN;
INSERT INTO test3 VALUES ('11', 11, 11, 11, 
                          '2001-05-18', '21:10:30',
                          '2001-05-18-21.10.30.123456'
                         );
END;

SELECT * FROM test3;



DROP INDEX i4test3;

SELECT * FROM test3;
SELECT * FROM test3 ORDER BY code;
SELECT * FROM test3 ORDER BY small;
SELECT * FROM test3 ORDER BY int;
SELECT * FROM test3 ORDER BY double;
SELECT * FROM test3 ORDER BY datum;
SELECT * FROM test3 ORDER BY zeit;
SELECT * FROM test3 ORDER BY zeitstempel;

SELECT * FROM test3 WHERE zeitstempel > '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE zeitstempel >= '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE zeitstempel = '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE zeitstempel <= '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE zeitstempel < '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE zeitstempel <> '2001-05-18-21.10.30.123456';

SELECT * FROM test3 WHERE zeit > '21:10:30';
SELECT * FROM test3 WHERE zeit >= '21:10:30';
SELECT * FROM test3 WHERE zeit = '21:10:30';
SELECT * FROM test3 WHERE zeit <= '21:10:30';
SELECT * FROM test3 WHERE zeit < '21:10:30';
SELECT * FROM test3 WHERE zeit <> '21:10:30';

SELECT * FROM test3 WHERE datum > '2001-05-18';
SELECT * FROM test3 WHERE datum >= '2001-05-18';
SELECT * FROM test3 WHERE datum = '2001-05-18';
SELECT * FROM test3 WHERE datum <= '2001-05-18';
SELECT * FROM test3 WHERE datum < '2001-05-18';
SELECT * FROM test3 WHERE datum <> '2001-05-18';

SELECT * FROM test3 WHERE double > 11;
SELECT * FROM test3 WHERE double >= 11;
SELECT * FROM test3 WHERE double = 11;
SELECT * FROM test3 WHERE double <= 11;
SELECT * FROM test3 WHERE double < 11;
SELECT * FROM test3 WHERE double <> 11;

SELECT * FROM test3 WHERE int > 11;
SELECT * FROM test3 WHERE int >= 11;
SELECT * FROM test3 WHERE int = 11;
SELECT * FROM test3 WHERE int <= 11;
SELECT * FROM test3 WHERE int < 11;
SELECT * FROM test3 WHERE int <> 11;

SELECT * FROM test3 WHERE small > 11;
SELECT * FROM test3 WHERE small >= 11;
SELECT * FROM test3 WHERE small = 11;
SELECT * FROM test3 WHERE small <= 11;
SELECT * FROM test3 WHERE small < 11;
SELECT * FROM test3 WHERE small <> 11;

SELECT * FROM test3 WHERE code > '11';
SELECT * FROM test3 WHERE code >= '11';
SELECT * FROM test3 WHERE code = '11';
SELECT * FROM test3 WHERE code <= '11';
SELECT * FROM test3 WHERE code < '11';
SELECT * FROM test3 WHERE code <> '11';

SELECT * FROM test3 WHERE NOT code > '11';
SELECT * FROM test3 WHERE NOT code >= '11';
SELECT * FROM test3 WHERE NOT code = '11';
SELECT * FROM test3 WHERE NOT code <= '11';
SELECT * FROM test3 WHERE NOT code < '11';
SELECT * FROM test3 WHERE NOT code <> '11';

SELECT * FROM test3 WHERE code > '11' OR code = '11';
SELECT * FROM test3 WHERE code >= '11' OR code = '11';
SELECT * FROM test3 WHERE code = '11' OR code = '11';
SELECT * FROM test3 WHERE code <= '11' OR code = '11';
SELECT * FROM test3 WHERE code < '11' OR code = '11';
SELECT * FROM test3 WHERE code <> '11' OR code = '11';

SELECT * FROM test3 WHERE code > '11' AND code = '11';
SELECT * FROM test3 WHERE code >= '11' AND code = '11';
SELECT * FROM test3 WHERE code = '11' AND code = '11';
SELECT * FROM test3 WHERE code <= '11' AND code = '11';
SELECT * FROM test3 WHERE code < '11' AND code = '11';
SELECT * FROM test3 WHERE code <> '11' AND code = '11';

SELECT * FROM test3 WHERE NOT (code > '11' AND code = '11');
SELECT * FROM test3 WHERE NOT (code >= '11' AND code = '11');
SELECT * FROM test3 WHERE NOT (code = '11' AND code = '11');
SELECT * FROM test3 WHERE NOT (code <= '11' AND code = '11');
SELECT * FROM test3 WHERE NOT (code < '11' AND code = '11');
SELECT * FROM test3 WHERE NOT (code <> '11' AND code = '11');

SELECT * FROM test3 WHERE small > 11 AND small = 11;
SELECT * FROM test3 WHERE NOT (small > 11 AND small = 11);

SELECT * FROM test3 WHERE int > 11 AND int = 11;
SELECT * FROM test3 WHERE NOT (int > 11 AND int = 11);

SELECT * FROM test3 WHERE double > 11 AND double = 11;
SELECT * FROM test3 WHERE NOT (double > 11 AND double = 11);

SELECT * FROM test3 WHERE datum > '2001-05-18' AND datum = '2001-05-18';
SELECT * FROM test3 WHERE NOT (datum > '2001-05-18' AND datum = '2001-05-18');

SELECT * FROM test3 WHERE zeit > '21:10:30' AND zeit = '21:10:30';
SELECT * FROM test3 WHERE NOT (zeit > '21:10:30' AND zeit = '21:10:30');

SELECT * FROM test3 WHERE zeitstempel > '2001-05-18-21.10.30.123456' AND zeitstempel = '2001-05-18-21.10.30.123456';
SELECT * FROM test3 WHERE NOT (zeitstempel > '2001-05-18-21.10.30.123456' AND 
                               zeitstempel = '2001-05-18-21.10.30.123456');

SELECT a.code, b.code, c.code FROM test a, test1 b, test3 c 
WHERE a.code = b.code AND b.code = c.code;

SELECT a.code, b.code, c.code FROM test a, test1 b, test3 c 
WHERE a.code = b.code AND b.code <> c.code;

SELECT a.code, b.code, c.code FROM test a, test1 b, test3 c 
WHERE a.code = b.code OR (b.code <> c.code AND a.code = b.code);

SELECT a.code, b.code FROM test a, test1 b 
WHERE a.code = b.code AND 
  EXISTS (SELECT * FROM test3 c WHERE a.code <> c.code);

SELECT a.code, b.code FROM test a, test1 b 
WHERE a.code = b.code AND 
  (NOT EXISTS (SELECT * FROM test3 c WHERE a.code = c.code));

SELECT a.code, b.code FROM test a, test1 b 
WHERE a.code = b.code AND 
  NOT EXISTS (SELECT * FROM test3 c WHERE a.code = c.code);

SELECT a.code, b.code FROM test a, test1 b 
WHERE a.code = b.code AND 
  EXISTS (SELECT * FROM test3 c WHERE a.code = c.code);

SELECT * FROM test3 WHERE zeitstempel BETWEEN '2001-04-18-20.10.30.123456' AND 
                                              '2001-05-18-21.10.30.123456';


SELECT * FROM test3 WHERE code IN ('10', '11', '12');

SELECT * FROM test3 WHERE code LIKE '1%';

SELECT * FROM test3 WHERE code LIKE '10%';

SELECT COUNT(int) FROM test3;

SELECT COUNT(a.*) FROM test3 a;

SELECT * FROM test3 WHERE int = (SELECT int FROM test3);

SELECT * FROM test3 WHERE int <> (SELECT int FROM test3);

