/*I can't get my code to run with mysql. I'm not sure what I did wrong. */

DROP TABLE courses;
DROP TABLE students;
DROP TABLE instructors;
DROP TABLE sections;
DROP TABLE registration;

CREATE TABLE courses (
cid varchar2(10) NOT NULL PRIMARY KEY,
cname varchar2(50) NOT NULL,
credits number(1) DEFAULT 3,
prereq varchar2(15)
);
INSERT INTO courses VALUES ('CSCI 1011','Intro to Computer Info Systems',3,null);
INSERT INTO courses VALUES ('CSCI 1911','Foundations of Programming',3,null);
INSERT INTO courses VALUES ('CSCI 2711','Assembly Lang & Sys Programming',3,'CSCI 2912');
INSERT INTO courses VALUES ('CSCI 2761','HTML & Web Design',3,'CSCI 1011');
INSERT INTO courses VALUES ('CSCI 2911','Computer Science 1',3,null);
INSERT INTO courses VALUES ('CSCI 2912','Computer Science 2',3,'CSCI 2911');
INSERT INTO courses VALUES ('CSCI 2913','Data Structures',3,'CSCI 2912');
INSERT INTO courses VALUES ('CSCI 3101','Algorithms',3,'CSCI 2913');
INSERT INTO courses VALUES ('CSCI 3201','Spreadsheets/DBases',3,'CSCI 1011');
INSERT INTO courses VALUES ('CSCI 3211','System Analysis',3,'CSCI 3301');
INSERT INTO courses VALUES ('CSCI 3301','Database Technologies',3,'CSCI 2911');
INSERT INTO courses VALUES ('CSCI 3401','Data Communications',3,'CSCI 2912');
INSERT INTO courses VALUES ('CSCI 3501','Computer Organization',3,'CSCI 2913');
INSERT INTO courses VALUES ('CSCI 3601','Operating Systems',3,'CSCI 2913');
INSERT INTO courses VALUES ('CSCI 3753','Java Programming 1',3,'CSCI 2912');
INSERT INTO courses VALUES ('CSCI 3754','Java Programming 2',3,'CSCI 3753');
INSERT INTO courses VALUES ('CSCI 4702','Mobile Applicaton Development',3,'CSCI 2912');
INSERT INTO courses VALUES ('CSCI 4901','Software Project',3,'instruct appr');
INSERT INTO courses VALUES ('CSCI 4921','Management Info Systems',3,'instruct appr');


CREATE TABLE students (
sid char(9) NOT NULL PRIMARY KEY,
lname varchar(30) NOT NULL ,
fname varchar2(30) NOT NULL ,
gender char(1) NOT NULL ,
addr varchar2(50) NOT NULL ,
city varchar2(20) NOT NULL ,
state char(2) NOT NULL ,
zip varchar2(10) NOT NULL ,
phone varchar2(14) NULL ,
birthdate date NULL ,
tuitionRate number(7, 2) NOT NULL ,
creditsEarned number(3) NOT NULL
);

INSERT INTO students VALUES ('100000001','Lee','George','M','15 Merchant Street','Honolulu','HI','96818','808-524-3333','01-MAY-1985',5000.00,47);
INSERT INTO students VALUES ('100000002','Yamamoto','Bill','M','3432 Birch Street','Honolulu','HI','96814','808-522-2212','03-JUN-1988',5000.00,12);
INSERT INTO students VALUES ('100000003','Carver','Hillary','F','22 Aardvark Avenue','Washington','DC','10101','800-212-3246','23-AUG-1981',5000.00,69);
INSERT INTO students VALUES ('100000004','King','Linda','F','341 Kaapahu Road','Paauilo','HI','96776',NULL,'01-SEP-1980',4399.00,6);
INSERT INTO students VALUES ('100000005','Rollings','Willie','M','1221 Ala Moana Blvd','Honolulu','HI','96814',NULL,NULL,4888.00,0);
INSERT INTO students VALUES ('100000006','Alexander','Wanda','F','93-123 Old Mill Road','Honokaa','HI','96727','808-776-2313','02-OCT-1977',5000.00,99);
INSERT INTO students VALUES ('100000007','Carver','Bill','M','33 Richards Street','Honolulu','HI','96813',NULL,'22-OCT-1980',5000.00,0);
INSERT INTO students VALUES ('100000008','DeLuz','Bob','M','102 Orleans Ave','San Francisco','CA','97745','808-555-3324','01-MAR-1978',5000.00,14);
INSERT INTO students VALUES ('100000009','Lee','Lisa','F','45 Fong Avenue','San Francisco','CA','97767','808-333-3432','21-APR-1977',5000.00,26);
INSERT INTO students VALUES ('100000010','Garcia','Sherrie','F','2 S. Beretania','Honolulu','HI','96817','808-663-4453','03-DEC-1980',5000.00,29);
INSERT INTO students VALUES ('100000011','Kamaka','Oscar','M','34 Kapolani Blvd','Honolulu','HI','96813','808-533-3332','12-FEB-1978',5000.00,0);
INSERT INTO students VALUES ('100000012','Shin','Hong-Seung','M','15 Honolulu Ave','Honolulu','HI','96818','808-524-5555','05-MAY-1985',5000.00,87);
INSERT INTO students VALUES ('100000001','Washum','Joshua','M','3753 Moanalua Loop','Aiea','HI','96818','808-486-3353','11-FEB-1978',5000.00,110);


CREATE TABLE instructors (
inId char(9) NOT NULL PRIMARY KEY,
iLname varchar2(30) NOT NULL,
iFname varchar2(30) NOT NULL,
rank varchar2(10) NOT NULL,
office varchar2(10) NULL,
phone varchar2(20) NULL,
salary number(8,2) DEFAULT 0
);

INSERT INTO instructors VALUES ('200000001','Souza','Edward','Professor','LM101','808-533-4241',5000.00);
INSERT INTO instructors VALUES ('200000002','Bryant','Karen','Professor','LM102','808-533-4244',5000.00);
INSERT INTO instructors VALUES ('200000003','Eredita','Raphael','Professor','LM103','808-533-4242',5000.00);
INSERT INTO instructors VALUES ('200000004','Schaper','Gregory','Professor','LM104','808-533-4041',5000.00);
INSERT INTO instructors VALUES ('200000005','Tenzer','Laurie','Professor','LM105','808-533-4001',5000.00);
INSERT INTO instructors VALUES ('200000006','Farrel','Carl','Professor','LM110','808-533-4235',5000.00);
INSERT INTO instructors VALUES ('200000007','Samson','Dolly','Professor','LM111','808-533-4241',8000.00);
INSERT INTO instructors VALUES ('200000008','Powley','Curt','Professor','LM109','808-533-4221',5000.00);
INSERT INTO instructors VALUES ('200000009','Metcalf','Cathrine','Professor','LM112','808-533-5241',5000.00);
INSERT INTO instructors VALUES ('200000010','Ku','Tom','Professor','LM115','808-533-5341',5000.00);
INSERT INTO instructors VALUES ('200000001','Souza','Edward','Professor','LM101','808-533-4241',5000.00);


CREATE TABLE sections (
crn char(4) NOT NULL PRIMARY KEY,
cid varchar2(9) NOT NULL,
section char DEFAULT 'A',
inId char(9) NOT NULL,
days varchar2(10) DEFAULT 'TBA',
time varchar2(16) DEFAULT 'TBA',
room varchar2(10) NULL
);

CREATE SEQUENCE CRN_SEQ
START WITH 1000;

INSERT INTO sections VALUES ('1001','CSCI 1011','A','200000002','TTH','15:40 - 17:05','CL100');
INSERT INTO sections VALUES ('1002','CSCI 1011','B','200000002','T','17:15 - 20:10','CL100');
INSERT INTO sections VALUES ('1003','CSCI 1011','B','200000002','TTH','14:05 - 15:30','CL100');
INSERT INTO sections VALUES ('1004','CSCI 1011','B','200000003','T','17:15 - 20:10','CL100');
INSERT INTO sections VALUES ('1005','CSCI 1911','B','200000004','TH','10:50 - 12:15','CL100');
INSERT INTO sections VALUES ('1006','CSCI 2711','C','200000004','MWF','10:45 - 11:40','CL102');
INSERT INTO sections VALUES ('1007','CSCI 2761','A','200000005','TTh','17:15 - 20:30','CL340');
INSERT INTO sections VALUES ('1008','CSCI 2911','A','200000006','TTh','14:05 - 15:30','CL340');
INSERT INTO sections VALUES ('1009','CSCI 2911','A','200000006','S','11:05 - 14:00','CL340');
INSERT INTO sections VALUES ('1010','CSCI 2912','A','200000007','TTh','14:05 - 15:30','CL340');
INSERT INTO sections VALUES ('1011','CSCI 2913','A','200000004','M','17:15 - 20:30','CL340');
INSERT INTO sections VALUES ('1012','CSCI 3101','A','200000008','T','17:15 - 20:10','CL340');
INSERT INTO sections VALUES ('1013','CSCI 3201','A','200000002','TTh','10:50 - 12:15','CL340');
INSERT INTO sections VALUES ('1014','CSCI 3201','A','200000008','TTh','10:50 - 12:15','CL340');
INSERT INTO sections VALUES ('1015','CSCI 3211','A','200000009','M','17:15 - 20:30','CL340');
INSERT INTO sections VALUES ('1016','CSCI 3301','A','200000001','W','17:15 - 20:10','CL340');
INSERT INTO sections VALUES ('1017','CSCI 3401','A','200000010','S','14:10 - 17:05','CL340');
INSERT INTO sections VALUES ('1018','CSCI 3501','A','200000004','MWF','09:30 - 10:45','CL340');
INSERT INTO sections VALUES ('1019','CSCI 3601','A','200000007','TTh','12:30 - 13:55','CL340');
INSERT INTO sections VALUES ('1020','CSCI 3753','A','200000007','W','17:15 - 20:10','CL340');
INSERT INTO sections VALUES ('1021','CSCI 4702','A','200000007','TTh','15:40 - 17:05','CL340');
INSERT INTO sections VALUES ('1022','CSCI 4911','A','200000006','Th','17:15 - 20:10','CL340');
INSERT INTO sections VALUES ('1023','CSCI 4921','A','200000006','Th','17:15 - 20:10','CL340');


CREATE TABLE registration (
crn char(4) NOT NULL,
sid char(9) NOT NULL,
CONSTRAINT pk_registration PRIMARY KEY (crn,sid)
);

INSERT INTO registration VALUES ('1000','100000001');
INSERT INTO registration VALUES ('1003','100000001');
INSERT INTO registration VALUES ('1005','100000001');
INSERT INTO registration VALUES ('1001','100000002');
INSERT INTO registration VALUES ('1004','100000002');
INSERT INTO registration VALUES ('1005','100000003');
INSERT INTO registration VALUES ('1002','100000004');
INSERT INTO registration VALUES ('1003','100000004');
INSERT INTO registration VALUES ('1005','100000004');
INSERT INTO registration VALUES ('1000','100000005');
INSERT INTO registration VALUES ('1003','100000005');
INSERT INTO registration VALUES ('1002','100000008');
INSERT INTO registration VALUES ('1004','100000008');
INSERT INTO registration VALUES ('1002','100000009');
INSERT INTO registration VALUES ('1005','100000009');
INSERT INTO registration VALUES ('1002','100000010');
INSERT INTO registration VALUES ('1005','100000010');
INSERT INTO registration VALUES ('1000','100000011');
INSERT INTO registration VALUES ('1003','100000011');
INSERT INTO registration VALUES ('1005','100000011');
commit;