DROP TABLE take1;
DROP TABLE course1;
DROP TABLE student1;
DROP SEQUENCE student1_seq;

CREATE SEQUENCE student1_seq START WITH 1 INCREMENT BY 1 CACHE 100;
CREATE TABLE student1 (
  sid    INTEGER PRIMARY KEY,
  name   VARCHAR(64) NOT NULL,
  email  VARCHAR(32) NOT NULL );

INSERT INTO student1 VALUES( student1_seq.NEXTVAL, 'Super Mario', 'super.mario@und.edu' );
INSERT INTO student1 VALUES( student1_seq.NEXTVAL, 'Digi Mon', 'digi.mon@ndus.edu' );
INSERT INTO student1 VALUES( student1_seq.NEXTVAL, 'Poke Mon', 'poke.mon@und.edu' );
INSERT INTO student1 VALUES( student1_seq.NEXTVAL, 'Sponge Bob', 'sponge@gmail.com' );

CREATE TABLE course1 (
  cid     CHAR(12) PRIMARY KEY,
  title   VARCHAR(64) NOT NULL,
  room    VARCHAR(16) NOT NULL,
  credit  INTEGER NOT NULL );

INSERT INTO course1 VALUES( 'CSCI260', '.NET and World Wide Web Programming', 'CEC 205', 3 );
INSERT INTO course1 VALUES( 'DATA520', 'Databases', 'Upson II 265', 3 );
INSERT INTO course1 VALUES( 'DATA525', 'Data Engineering and Mining', 'Harrington 218', 3 );
INSERT INTO course1 VALUES( 'CSCI513', 'Advanced Databases', 'Harrington 364', 3 );

CREATE TABLE take1 (
  sid       INTEGER NOT NULL,
  cid       CHAR(12) NOT NULL,
  semester  VARCHAR(12) NOT NULL,
  PRIMARY KEY ( sid, cid ),
  FOREIGN KEY ( sid ) REFERENCES student1( sid ) ON DELETE CASCADE,
  FOREIGN KEY ( cid ) REFERENCES course1( cid ) ON DELETE CASCADE );

INSERT INTO take1 SELECT sid, 'DATA520', 'Fall 2022'
  FROM student1 WHERE name ='Poke Mon';
INSERT INTO take1 SELECT sid, 'DATA525', 'Fall 2022'
  FROM student1 WHERE name='Poke Mon';
INSERT INTO take1 SELECT sid, 'CSCI260', 'Fall 2022'
  FROM student1 WHERE name='Digi Mon';
INSERT INTO take1 SELECT sid, 'CSCI513', 'Spring 2023' 
  FROM student1 WHERE name='Sponge Bob'; 

SELECT s.name, c.title FROM student1 s, course1 c, take1 t
  WHERE UPPER(s.name) LIKE '%MON%' AND s.sid=t.sid AND t.cid=c.cid; 

COMMIT;