MySQL Help - Beginner

Question

MySQL Help - Beginner

d_SQL_H

Valued Member

Points: 62
More actions
September 6, 2024 at 3:15 pm

#4454675

Hello I am new to using MySQl and I've created from university an example of how it could be used in an organisation with made up data. But I am getting errors everything I try to execute this in MySQL, please could someone look through and see where I am going wrong on this?
CREATE DATABASE qanetwork_1
USE qanetwork_1
CREATE TABLE Agency (
Agency_ID INTEGER NOT NULL,
Agency_Name char(30) NOT NULL,
Location VARCHAR(25) NOT NULL,
CONSTRAINT PK_Agency_ID PRIMARY KEY (Agency_ID),
);
INSERT INTO Agency (Agency_ID, Agency_Name, Location)
VALUES
('ASH', 'Ashby NFUM Agent', 'Ashby de la Zouch'),
('STRAT', 'Statford NFUM Agency', 'Startford upon Avon'),
('LNDN', 'London NFUM Agency', 'London'),
('CARD', 'Cardiff NFUM Agency', 'Cardiff'),
('EDIN', 'Edinburgh NFUM Agency', 'Edinburgh'),
('YORK', 'Yorkshire NFUM Agency', 'Yorkshire'),
('HULL', 'Hull NFUM Agency', 'Hull'),
('MAN', 'Manchester NFUM Agency', 'Manchester'),
('DER', 'Derby NFUM Agency', 'Derby');
CREATE TABLE Agent (
--Agent_ID INT PRIMARY KEY,
Agent_ID INTEGER NOT NULL,
Agent_name char(20) NOT NULL,
Contact_Details VARCHAR(25) NOT NULL,
Agency_ID INTEGER NOT NULL,
CONSTRAINT PK_Agent_ID PRIMARY KEY (Agent_ID),
CONSTRAINT FK_Agency_ID FOREIGN KEY (Agency_ID) REFERENCES Agency (Agency_ID)
);
INSERT INTO Agent (Agent_ID, Agent_name, Contact_Details, Agency_ID)
VALUES
('101', 'Jason Smith', 'J.smith@email.com', 'ASH'),
('102', 'Julia Parker', 'Julia1995@email.com', 'HULL'),
('103', 'Faye Sykes', 'F.S.1990@email.com', 'LNDN'),
('104', 'Liam Woodfield', 'Liam_Wood@email.com', 'CARD'),
('105', 'Daniel Edwards', 'Dedwards@email.com', 'EDIN'),
('106', 'Lucy Kirven', 'KirLucy@email.com', 'EDIN'),
('107', 'Joely Martin', 'Joel_Martin@email.com', 'YORK'),
('108', 'Carol Jones', 'Carol_Jones@email.com', 'DER');
CREATE TABLE Customer (
Customer_ID INTEGER NOT NULL,
Name VARCHAR(100) NOT NULL,
Customer_contact_Details VARCHAR(100) NOT NULL,
Agency_ID INTEGER NOT NULL,
CONSTRAINT PK_Customer_ID PRIMARY KEY (Customer_ID),
CONSTRAINT FK_Agency_ID FOREIGN KEY (Agency_ID) REFERENCES Agency (Agency_ID)
);
INSERT INTO Customer (Customer_ID, Name, Customer_contact_Details, Agency_ID)
VALUES
('0991', 'Nanette Stark', 'Nan_Sta@email.com', 'ASH'),
('0992', 'Georgia Lewis', 'GLew1995@email.com', 'DER'),
('0993', 'Lewis Martin', 'Martin_L90@email.com', 'DER'),
('0994', 'Tegan Weir', 'Tegan_Weir@email.com', 'CARD'),
('0995', 'Luke Moore', 'Moore_Luke@email.co.uk', 'EDIN'),
('0996', 'Kamran Hussain', 'Kam_Hussain@email.co.uk', 'LNDN'),
('0997', 'Chloe Miles', 'Chlo_Miles@email.co.uk', 'YORK'),
('0998', 'Joshua Parker', 'Josh_Parker@email.com', 'MAN');
CREATE TABLE Policy (
Policy_ID INTEGER NOT NULL,
Policy_Name VARCHAR(50) NOT NULL,
Policy_Type VARCHAR(25) NOT NULL,
Start_Date DATE NOT NULL,
End_Date DATE NOT NULL,
Customer_ID INTEGER NOT NULL,
CONSTRAINT PK_Policy_ID PRIMARY KEY (Policy_ID),
CONSTRAINT FK_Customer_ID FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID)
);
INSERT INTO Policy (Policy_ID, Policy_Name, Policy_Type, Start_Date, End_Date, Customer_ID)
VALUES
('1111', 'Home and Lifestype Insurance', 'Comprehensive Buildings and Contents Insurance', '05-05-2023', '05-05-2024', '0991'),
('2222', 'Car Insurance', 'Comprehensive Motor Insurance', '02-05-2023', '05-05-2024', '0998'),
('3333', 'Farm Insurance', 'Agriculture Insurance', '01-01-2024', '01-01-2025', '0997'),
('4444', 'Motor Fleet Insurance', 'Fleet Insurance', '20-08-2023', '20-08-2024', '0993'),
('5555', 'Equine Insurance', 'Horse and Rider Insurance', '12-06-2024', '12-06-2025', '0992'),
('6666', 'Pet Insurance', 'Veterinary Insurance', '05-05-2023', '05-05-2024', '0995'),
('7777', 'Legal Expenses Insurance', 'Legal Protection Insurance', '05-05-2023', '05-05-2024', '0991'),
('8888', 'Travel Insurance', 'Comprehensive Travel Insurance', '15-12-2023', '15-12-2024', '0993');
CREATE TABLE Item (
Item_ID INTEGER NOT NULL,
Item_Name VARCHAR(20) NOT NULL,
Item_Value DECIMAL(10, 2) NOT NULL,
Policy_ID INTEGER NOT NULL,
CONSTRAINT PK_Item_ID PRIMARY KEY (Item_ID),
CONSTRAINT FK_Policy_ID FOREIGN KEY (Policy_ID) REFERENCES Policy (Policy_ID)
);
INSERT INTO Item (Item_ID, Item_Name, Item_Value, Policy_ID)
VALUES
('B001', 'Victorian House', '500,000.00', '1111'),
('C001', '2022 BMW X5', '45,000.00', '4444'),
('B002', 'John Deere Tractor', '75,000.00', '4444'),
('T001', 'Golden Retriever', '2000.00', '6666'),
('L001', 'Legal Dispute Coverage', '10,000.00', '7777'),
('M001', 'European Vacation', '5,000.00', '8888'),
('T001', 'Labrador', '1500.00', '6666'),
('C001', '2023 Range Rover Evoke', '75,000.00', '2222');
CREATE TABLE Appointment (
Appointment_ID INTEGER NOT NULL,
Date DATE NOT NULL,
Time TIME NOT NULL,
Location VARCHAR(50) NOT NULL,
Customer_ID INTEGER NOT NULL,
Agent_ID INTEGER NOT NULL,
Agency_ID INTEGER NOT NULL,
CONSTRAINT PK_Appointment_ID PRIMARY KEY (Appointment_ID),
CONSTRAINT FK_Customer_ID FOREIGN KEY (Customer_ID) REFERENCES Customer (Customer_ID),
CONSTRAINT FK_Agent_ID FOREIGN KEY (Agent_ID) REFERENCES Agent (Agent_ID),
CONSTRAINT FK_Agency_ID FOREIGN KEY (Agency_ID) REFERENCES Agency (Agency_ID)
);
INSERT INTO Appointment (Appointment_ID, Date, Time, Location, Customer_ID, Agent_ID, Agency_ID)
VALUES
('APPT001', '09-10-2024', '13:30', 'Derby NFUM Agency', '0992', '108', 'DER'),
('APPT034', '05-01-2024', '09:00', 'York NFUM Agency', '0997', '107', 'YORK'),
('APPT003', '05-11-2024', '15:00', 'Derby NFUM Agency', '0992', '108', 'DER'),
('APPT004', '09-10-2023', '09:00', 'Cardiff NFUM Agency', '0994', '104', 'CARD'),
('APPT010', '03-05-2024', '10:00', 'Ashby de la Zouch NFUM Agency', '0991', '101', 'ASH'),
('APPT005', '14-02-2024', '09:00', 'Edinburgh NFUM Agency', '0995', '105', 'EDIN'),
('APPT067', '25-06-2024', '16:00', 'Derby NFUM Agency', '0992', '108', 'DER'),
('APPT023', '11-12-2023', '09:00', 'Cardiff NFUM Agency', '0994', '104', 'CARD'),
('APPT104', '23-11-2023', '09:30', 'London NFUM Agency', '0996', '103', 'LNDN');
CREATE TABLE Document (
Document_ID INTEGER NOT NULL,
Document_Name VARCHAR(50) NOT NULL,
Document_Type VARCHAR(20) NOT NULL,
Appointment_ID INTEGER NOT NULL,
CONSTRAINT PK_Documemt_ID PRIMARY KEY (Document_ID),
CONSTRAINT FK_Appointment_ID FOREIGN KEY (Appointment_ID) REFERENCES Appointment (Appointment_ID)
);
INSERT INTO Document (Document_ID, Document_Name, Document_Type, Appointment_ID)
VALUES
('DOC001', 'Policy Aggreement', 'PDF', 'APPT034'),
('DOC002', 'Policy Aggreement', 'PDF', 'APPT104'),
('DOC003', 'Claim Form', 'Word Document', 'APPT003'),
('DOC004', 'Annual Report', 'PDF', 'APPT004'),
('DOC005', 'Claim Form', 'Word Document', 'APPT067'),
('DOC006', 'Policy Aggreement', 'PDF', 'APPT001'),
('DOC007', 'Policy Agreement', 'PDF', 'APPT010'),
('DOC008', 'Policy Aggreement', 'PDF', 'APPT005');
SELECT * FROM Agency;
SELECT * FROM Agent;
SELECT * FROM Customer;
SELECT * FROM Policy;
SELECT * FROM Appointment;
SELECT * FROM Item;
SELECT * FROM Document;
September 13, 2024 at 6:31 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

MySQL and SQL Server are not the same thing. The script you have provided looks like a SQL Server script, so if you are attempting to run it in MySQL, that may be the reason for errors

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

Just to add to what Phil said, it is REALLY hard to guess what is wrong without knowing what the error is. BUT if I had to guess on the error, it is probably a misleading one and the real issue is related to missing statement terminators. I say it is likely misleading as I have yet to find ANY language that gives you good error messages on missing line terminators. My favorite one I've seen when missing a character was "missing close bracket". There were no missing brackets, that one was actually a missing comma. But you have multiple statements in there that have no statement terminator in them. CREATE DATABASE for example needs one. USE DATABASE needs one. And so on. ALL statements in MySQL (from my understanding) require a statement terminator.

In SQL Server they are MOSTLY optional, with WITH statements being the case where the previous statement must be terminated. I recommend using them in SQL server and being consistent in your code (if you use them at one place in your code, use them everywhere).

And even if statement terminators are NOT required in MySQL (pretty sure they are BUT I could be wrong), I would STRONGLY encourage you to be consistent. SOME of your statements have terminators while others don't. My opinion, code consistency helps with readability and makes the code seem more professional.

OR it could be failing due to the database and/or tables already existing, or probably any number of things. Statement terminators is just my first guess.

Next a tip about troubleshooting large queries - break them down into small manageable chunks. Take your large query and break it down. Start with the very first statement (CREATE DATABASE) and run that on it's own. If it succeeds, then run the next statement. Repeat until you have an error then correct that statement. Once you have the script running in chunks, then drop the objects you created and run the whole batch.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Lowell SSC Guru Points: 323518 More actions · Answer 3

this is the educational part about learning to code...working through the errors.

I am assuming you are using mySQL workbench for development.

there is an output window that might actually be hidden, or where your monitor is not showing it, and you have to pull the divider up.

I pasted your code and started working thru myself, and here, in general, is what i got:

start using better practices up front: IF EXISTS and IF NOT EXISTS look better, and make your code re-runnable:

CREATE DATABASE IF NOT EXISTS qanetwork_1;CREATE TABLE IF NOT EXISTS Agency ...

as others have noted, MySQL is unforgiving on missing semi colons. if you are running multiple statements, EVERY statement must have a terminating semi colon.

you have to fix the CREATE database and USE database to have the missing semi colons..
your first table Agency has a comma at the end of the column definitions, so it's invalid syntax in the create table.
your first table defines the first columns as Agency_ID INTEGER NOT NULL, but your INSERT tries to insert 'ASH' and raises this explicit error: Error Code: 1366. Incorrect integer value: 'ASH' for column 'Agency_ID' at row 1
You have a comment --Agent_ID INT PRIMARY KEY,, but comments are slightly different than SQL Server: hyphen hyphen space, not just hyphen hyphen. -- Agent_ID INT PRIMARY KEY,

fix each item as you go

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!