errors in my commands on easyphp devserver

Question

i wish to run the following command on my easyphp devserver from the server thats running on my machine but there are so many errors that i dont know the meaning of.

i went from an old version of easy php(old version that school uses) to the recent build.

CREATE DATABASE UserCredentialsADVHproject IF NOT EXISTS UserCredentialsADVHproject;
USE UserCredentialsADVHproject;
CREATE TABLE Credentials(
    Username varchar(16) NOT NULL UNIQUE PRIMARY KEY CHECK (LEN(Username)<=16),
    Password varchar(16) NOT NULL CHECK (LEN(Password)<=16));


INSERT INTO Credentials (Username, Password)
VALUES ("User", "Password");

CREATE DATABASE ExercisesADVHproject IF NOT EXISTS ExercisesADVHproject;
USE ExercisesADVHproject;
CREATE TABLE Exercises(
    Name varchar(25) NOT NULL UNIQUE PRIMARY KEY,
    Position varchar(8) NOT NULL CHECK(Type IN(“Seated”, ”Standing”, “Lying”, “Kneeling”)),
    Equipment varchar(9) NOT NULL CHECK(Equipment IN("Dumbbell", "Barbell", "Bodyweight", "Machine", "Cable")),
    bodyPart varchar(5) NOT NULL CHECK(bodyPart IN(“Legs”, ”Back”, “Chest”, “Arms”, “Core”))
    beginnerStandard int NOT NULL UNIQUE CHECK(beginnerStandard<100),
    noviceStandard int NOT NULL UNIQUE CHECK(noviceStandard<150),
    intermediateStandard int NOT NULL UNIQUE CHECK(intermediateStandard<200),
    advancedStandard int NOT NULL UNIQUE CHECK(advancedStandard<250),
    eliteStandard int NOT NULL UNIQUE CHECK(eliteStandard<500)
    );


INSERT INTO Exercises (Name, Position, Equipment, bodyPart, beginnerStandard, noviceStandard, intermediateStandard, advancedStandard, eliteStandard)
VALUES("Squat", "Standing", "Barbell", "Legs", 64, 93, 130, 173, 219),
      ("Hamstring Curls", "Seated", "Machine", "Legs", 29, 50, 79, 114, 154),
      ("Leg Extension", "Seated", "Machine", "Legs", 35, 61, 96, 139, 186),
      ("Hip Abduction", "Seated", "Machine", "Legs", 30, 59, 101, 153, 213),
      ("Hip Adduction", "Seated", "Machine", "Legs", 32, 64, 109, 167, 233),
      ("Smith-Machine Calf Raises", "Standing", "Machine", "Legs", 31, 72, 134, 214, 304),
      ("Hip thrusts", "Seated", "Barbell", "Legs", 38, 76, 129, 196, 273),
      ("Incline Chest Press", "Lying", "Dumbbell", "Chest", 19, 28, 39, 53, 68),
      ("Chest Fly", "Standing", "Cable", "Chest", 5, 18, 39, 68, 103),
      ("Lat Pulldown", "Seated", "Machine", "Back", 38, 58, 82, 110, 141),
      ("Face Pulls", "Standing", "Cable", "Back", 12, 26, 46, 71, 101),
      ("Bent-Over Rows", "Standing", "Barbell", "Back", 14, 60, 85, 115, 147),
      ("Preacher Curls", "Standing", "Barbell", "Arms", 17, 29, 46, 66, 89),
      ("Lateral Raises", "Standing", "Dumbbell", "Arms", 4, 8, 15, 25, 36),
      ("Tricep Pushdowns", "Standing", "Cable", "Arms", 17, 34, 57, 86, 119),
      ("Incline Bicep Hammer Curl", "Seated", "Dumbbell", "Arms", 5, 11, 21, 35, 50),
      ("Shoulder Press", "Seated", "Dumbbell", "Arms", 13, 21, 32, 45, 60),
      ("Overhead Tricep Extension", "Standing", "Cable", "Arms", 10, 23, 42, 67, 97),
      ("Reverse (Zottoman) Curls", "Standing",  "Dumbbell", "Arms", 3, 9, 21, 36, 55),
      ("Crunch", "Seated", "Machine", "Core", 26, 51, 85, 128, 177),
      ("Torso Rotations", "Kneeling", "Machine", "Core", 21, 41, 62, 82, 103));

CREATE DATABASE TipADVHproject IF NOT EXISTS TipADVHproject;
USE TipADVHproject;
CREATE TABLE Tips(
    tipId int NOT NULL UNIQUE AUTO_INCREMENT PRIMARY KEY,
    tipName varchar(32) NOT NULL,
    tipDesc varchar(255) NOT NULL);

[]

the following images are the error prompts: errors on ln4 to do with check statement error on ln4 to do with password column name ln11 has same issue as ln1 error on ln15 with check line 49 also has same issue and ln 1 and ln11

I tried searching up and looking at syntax of the errors but nothing really helped. i retyped the whole thing instead of copying and pasting it from a txt file. nothing changed

i appreciate all the help and feedback and if any addition info is needed pls ask

Answer 1

Why are you creating a database per table?

Your syntax for IF NOT EXISTS is incorrect:

CREATE DATABASE UserCredentialsADVHproject IF NOT EXISTS UserCredentialsADVHproject;

should be:

CREATE DATABASE IF NOT EXISTS UserCredentialsADVHproject;

There's no LEN() function and you should get a 1406 error if the data you are inserting is too long:

CREATE TABLE Credentials(
    Username varchar(16) NOT NULL UNIQUE PRIMARY KEY CHECK (LEN(Username)<=16),
    Password varchar(16) NOT NULL CHECK (LEN(Password)<=16));

should be:

CREATE TABLE Credentials(
    Username varchar(16) NOT NULL PRIMARY KEY,
    Password varchar(16) NOT NULL
);

The use of IN() to check specific values in your VARCHAR is causing an error due to the quoting “”:

Position varchar(8) NOT NULL CHECK(Type IN(“Seated”, ”Standing”, “Lying”, “Kneeling”)),

should be:

Position varchar(8) NOT NULL CHECK(Position IN('Seated', 'Standing', 'Lying', 'Kneeling')),

or maybe switch to an ENUM which will provide the constraint on values and use less space:

Position ENUM('Seated', 'Standing', 'Lying', 'Kneeling') NOT NULL,