SQL help needed
Results 1 to 5 of 5

Thread: SQL help needed

  1. #1
    Senior Member
    Join Date
    Jan 2004
    Posts
    228

    SQL help needed

    /*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;

  2. #2
    Senior Member
    Join Date
    Oct 2002
    Posts
    1,130
    Well without knowing the error(s) you got, I highly doubt anyone can be of much help.. the sql statements look fine to me...
    Government is like fire - a handy servant, but a dangerous master - George Washington
    Government is not reason, it is not eloquence - it is force. - George Washington.

    Join the UnError community!

  3. #3
    Senior Member
    Join Date
    Jan 2004
    Posts
    228
    I couldn't run it at home. The Oracle server I usually log into is offline. My friend tried running it on his mysql database at home and said it gave him syntax errors.

  4. #4
    Just Another Geek
    Join Date
    Jul 2002
    Location
    Rotterdam, Netherlands
    Posts
    3,403
    Try to do one thing at a time to see where the error is...

    First the CREATEs, then the INSERTs etc..
    Oliver's Law:
    Experience is something you don't get until just after you need it.

  5. #5
    Senior Member
    Join Date
    Jan 2004
    Posts
    228
    I figured the problem out. SQL format is different for Oracle and MYSQL. Thanks a lot for all the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •