CREATE TABLE Professoren
(PersNr INTEGER  PRIMARY KEY,
 Name   VARCHAR(30) NOT NULL,
 Rang   CHAR(2) CHECK (Rang in ('C2','C3','C4')),
 Raum   INTEGER UNIQUE );

CREATE SEQUENCE pers_sequence
START WITH 100
INCREMENT BY 20
MINVALUE 1
MAXVALUE 200
NO CYCLE
CACHE 1000;
-- zu hoher CACHE Wert gibt keinen Fehler; 
-- egal ob CYCLE oder NO CYCLE.

SELECT * FROM pers_sequence

INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof A', 'C3', '4711');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof B', 'C3', '4712');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof C', 'C3', '4713');

SELECT currval('pers_sequence');
SELECT  * FROM Professoren;

INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof D', 'C4', '4714');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof E', 'C4', '4715');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof F', 'C4', '4716');

SELECT currval('pers_sequence');
SELECT  * FROM Professoren;

INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof G', 'C3', '4717');

DELETE FROM Professoren;
SELECT * FROM Professoren;

DROP SEQUENCE pers_sequence;


-- Sequence  mit CYCLE:
CREATE SEQUENCE pers_sequence
START WITH 100
INCREMENT BY 20
MINVALUE 1
MAXVALUE 200
CYCLE
CACHE 1;
-- CACHE 100 an Stelle von CACHE 1 gibt keinen Fehler.

INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof A', 'C3', '4711');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof B', 'C3', '4712');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof C', 'C3', '4713');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof D', 'C4', '4714');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof E', 'C4', '4715');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof F', 'C4', '4716');

SELECT currval('pers_sequence');
SELECT  * FROM Professoren;

INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof G', 'C3', '4717');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof H', 'C3', '4718');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof I', 'C3', '4719');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof J', 'C4', '4720');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof K', 'C4', '4721');
INSERT INTO Professoren VALUES(nextval('pers_sequence'), 'Prof L', 'C4', '4722');

SELECT currval('pers_sequence');
SELECT  * FROM Professoren;

DELETE FROM Professoren;

DROP SEQUENCE pers_sequence;

-- Sequence  mit default Werten:
CREATE SEQUENCE test_sequence;

SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');

DROP SEQUENCE test_sequence;


-- Sequence  mit CYCLE, ohne MINVALUE:
CREATE SEQUENCE test_sequence
START WITH 3000
INCREMENT BY 10
MAXVALUE 3025
CYCLE
CACHE 1;

SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');

DROP SEQUENCE test_sequence;


-- Sequence  mit negativem INCREMENT Wert:

CREATE SEQUENCE test_sequence
START WITH 335
INCREMENT BY -100
MINVALUE 1
MAXVALUE 3025
CYCLE
CACHE 1;

SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');
SELECT nextval('test_sequence');

DROP SEQUENCE test_sequence;
DROP TABLE Professoren;
