MySQL Testing/Creation/Syntax Check

Question

I have a project in my Relational Database class in which I have to move my Data Dictionary into a MySQL script to be used with Oracle SQL Developer.

I have the script written, but have no idea how to test it or if it works since from home I don't have a database to connect to. After doing some research, I'm not even entirely sure if my syntax is right (or if it is even being taught to us right). I looked into SQLFiddle but I'm so new to MySQL that I'm not even sure how to input my script into it. My instructors test scripts won't work in SQLFiddle either, and I'm not sure how to use the text to DLL that I'm assuming is required for you test in SQLFiddle.

Can anybody help me, whether by pointing me to a way to connect to a some sort of test server, how to use SQLFiddle, or just being able to look and see if there is anything wrong with the code itself (something that sticks out as wrong or whatever). This is the code that I have come up with:

drop TABLE student;

drop TABLE building;

drop TABLE course;

drop TABLE instructor;



Create TABLE student

    (stu_id CHAR(5), 

    stu_fname VARCHAR2(15), 

    stu_lname VARCHAR2(20), 

    stu_minital CHAR(1), 

    stu_phone CHAR(12),

    stu_address VARCHAR2(20),
    stu_city VARCHAR2(15),
    stu_state CHAR(2),
    stu_zip CHAR(5),
    stu_crs_id CHAR(5), 
    CONSTRAINT student_stu_id_pk PRIMARY KEY (stu_id),
    CONSTRAINT student_stu_crs_id_fk FOREIGN KEY (stu_crs_id)
    REFERENCES course(crs_id)); 



describe student;

Create TABLE building
    (bld_id CHAR(2),
    bld_address VARCHAR2(20),
    bld_city VARCHAR2(15),
    bld_state CHAR(2),
    bld_zip CHAR(5),
    CONSTRAINT bulding_bld_id_pk PRIMARY KEY (bld_id));

describe building;

Create TABLE course
    (crs_id CHAR(6),
    crs_name VARCHAR2(15),
    crs_room CHAR(3),
    crs_inst_id CHAR(5),
    crs_bld_id CHAR(2),
    CONSTRAINT course_crs_id_pk PRIMARY KEY (crs_id),
    CONSTRAINT course_crs_inst_id_fk FOREIGN KEY (crs_inst_id)
    REFERENCES instructor(inst_id),
    CONSTRAINT course_crs_bld_id_fk FOREIGN KEY (crs_bld_id)
    REFERENCES building(bld_id));

describe course;

Create TABLE instructor
    (inst_id CHAR(5),
    inst_fname VARCHAR2(15),
    inst_lname VARCHAR2(20),
    CONSTAINT instructor_inst_id_pk PRIMARY KEY (inst_id));

describe instructor;

SELECT * FROM student;
SELECT * FROM building;
SELECT * FROM course;
SELECT * FROM table;

Why don't you have a database to connect to? What part of this won't work in SQLFIDDLE? Surely you're not asking us how to copy-paste? — Strawberry, Jul 13 '15 at 10:40
No, I put this code in SQLFiddle and it breaks. However, I just went back to it and now it's taking my code, maybe it was down or something. As far as a DB to connect to, I have no idea. I've been wracking my brain trying to figure out how to set one up (to no avail) and wondering why that was a requirement of our project without the tools to make it work. — Chris Blackmon, Jul 13 '15 at 10:48
Well, it did work. Now I'm getting the crashes again, GATEWAY_TIMOUT, and "something went wrong" dialogs. — Chris Blackmon, Jul 13 '15 at 10:53

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

Chris, Onr possible solutions is to try and take a look at :

https://github.com/webyog/sqlyog-community

It's a free database tool (which i personally like very much), but it doesn't check your syntax how you want. I don't think any program could. You are the only one who knows what you want with that script. But, try this webYog. it should make a validation regarding the syntax, so that comma, type of column and so on are correct, not the name of the variables used and the logic behind them.

The second, if you do't have time try this is to take a look at these links:

Online SQL syntax checker conforming to multiple databases

, or

Are there any SQL Validators that can check syntax against multiple database servers?

Hope this links helps you!

edited May 23 '17 at 11:51

Community

1
1

answered Jul 13 '15 at 10:57

Eugen

785
6
22

This did help point me in the right condition. Thanks! I am getting this error in syntax, though. Would you have an idea on why? "Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'instructor_inst_id_pk PRIMARY KEY (inst_id))' at line 5" – Chris Blackmon Jul 13 '15 at 11:13
Disregard, I was trying to run on test and not the mysql table. I have a starting point now. Thanks for the help! – Chris Blackmon Jul 13 '15 at 11:15
No problem, glad I could help. – Eugen Jul 13 '15 at 11:57

MySQL Testing/Creation/Syntax Check

1 Answers1