DROP TABLE student;
DROP SEQUENCE student_seq;
CREATE TABLE student
(
  name       VARCHAR(20) NOT NULL,
  city 		 VARCHAR(30) NOT NULL,
  sex        VARCHAR(2) NOT NULL,
  student_id NUMBER NOT NULL,
  PRIMARY KEY (student_id)
);

CREATE SEQUENCE student_seq
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER student_bri
BEFORE INSERT
ON student
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT student_seq.nextval INTO :NEW.student_id FROM dual;
END;
/

INSERT INTO student VALUES ('Megan','Budapest','F',NULL);
INSERT INTO student VALUES ('Joseph','Budapest','M',NULL);
INSERT INTO student VALUES ('Kyle','London','M',NULL);
INSERT INTO student VALUES ('Katie','Budapest','F',NULL);
INSERT INTO student VALUES ('Abby','Budapest','F',NULL);
INSERT INTO student VALUES ('Nathan','London','M',NULL);
INSERT INTO student VALUES ('Liesl','London','F',NULL);
INSERT INTO student VALUES ('Ian','London','M',NULL);
INSERT INTO student VALUES ('Colin','Leuven','M',NULL);
INSERT INTO student VALUES ('Peter','Leuven','M',NULL);
INSERT INTO student VALUES ('Michael','Leuven','M',NULL);
INSERT INTO student VALUES ('Thomas','Leuven','M',NULL);
INSERT INTO student VALUES ('Devri','Munchen','F',NULL);
INSERT INTO student VALUES ('Ben','Munchen','M',NULL);
INSERT INTO student VALUES ('Aubrey','Munchen','F',NULL);
INSERT INTO student VALUES ('Rebecca','Strasbourg','F',NULL);
INSERT INTO student VALUES ('Will','Strasbourg','M',NULL);
INSERT INTO student VALUES ('Max','Strasbourg','M',NULL);
INSERT INTO student VALUES ('Rianne','Strasbourg','F',NULL);
INSERT INTO student VALUES ('Avery','Strasbourg','F',NULL);
INSERT INTO student VALUES ('Lauren','Strasbourg','F',NULL);
INSERT INTO student VALUES ('Becca','Gent','F',NULL);
INSERT INTO student VALUES ('Gregory','Gent','M',NULL);
INSERT INTO student VALUES ('Sarah','Gent','F',NULL);
INSERT INTO student VALUES ('Robbie','New York','M',NULL);
INSERT INTO student VALUES ('Keaton','New York','M',NULL);
INSERT INTO student VALUES ('Carter','Budapest','M',NULL);
INSERT INTO student VALUES ('Teddy','New York','M',NULL);
INSERT INTO student VALUES ('Gabrielle','New York','F',NULL);
INSERT INTO student VALUES ('Grace','New York','F',NULL);
INSERT INTO student VALUES ('Emily','Budapest','F',NULL);
commit;


