CREATE TABLE Plant(
plantID int NOT NULL,
plantName varchar(100) NOT NULL,
scientificName varchar(50) NOT NULL,
ediblePart varchar(50) NOT NULL,
soilType varchar(5) NOT NULL,
climateRef int NOT NULL
);

-- The following may be added to the CREATE statement
-- Primary Key Syntax (added after field name and NOT NULL)
--     PRIMARY KEY (fieldName)
--          or for compound PK
--     CONSTRAINT PK_compoundFieldName PRIMARY KEY (field1,field2)
-- Foreign Key Syntax (added after field list)
--     FOREIGN KEY (field name) REFERENCES tableName(fieldName)
-- Boolean Field Type
--     For some SQL environments 'bool' may have to be changed to 'int'
-- Text Field Max Size
--     Edit (255) to required length
-- Validation of length of text (added after create statement as new statement
--     CHECK (CHAR_LENGTH(fieldName) > value)
-- Range Check (added after field list)
--     CHECK (fieldName >= value)
--     CHECK (fieldName >= value AND fieldName <= value2)
-- Restricted choice
--     CHECK(fieldName in ('value1','value2','value3')) 

-- Note
-- At N5 Check constraints (that are ignored by a MySQL server) 
-- are being used instead of triggers.

INSERT INTO Plant VALUES(1111,"Strawberry","Fragaria","Fruit","Loam",101);
INSERT INTO Plant VALUES(1112,"Pea","Pisum","Seed","Sandy",102);
INSERT INTO Plant VALUES(1113,"Tomato","Solanum","Fruit","Peat",103);
INSERT INTO Plant VALUES(1114,"Spinach","Spinacia","Leaf","Clay",101);
INSERT INTO Plant VALUES(1115,"Carrot","Daucus","Root","Sandy",104);
INSERT INTO Plant VALUES(1116,"Lettuce","Lactuca","Leaf","Loam",103);
INSERT INTO Plant VALUES(1117,"Potato","Solanum tuberosum","Tuber","Sandy",104);
INSERT INTO Plant VALUES(1118,"Broccoli","Brassica oleracea","Floret","Clay",101);
INSERT INTO Plant VALUES(1119,"Blueberry","Vaccinium","Fruit","Peat",102);
INSERT INTO Plant VALUES(1120,"Cucumber","Cucumis sativus","Fruit","Loam",103);
INSERT INTO Plant VALUES(1121,"Pumpkin","Cucurbita pepo","Fruit","Sandy",102);
INSERT INTO Plant VALUES(1122,"Cherry Tomato","Solanum lycopersicum var. cerasiforme","Fruit","Loam",101);
INSERT INTO Plant VALUES(1123,"Green Bean","Phaseolus vulgaris","Pod","Sandy",104);
INSERT INTO Plant VALUES(1124,"Kale","Brassica oleracea","Leaf","Clay",103);
INSERT INTO Plant VALUES(1125,"Watermelon","Citrullus lanatus","Fruit","Sandy",102);
INSERT INTO Plant VALUES(1126,"Zucchini","Cucurbita pepo","Fruit","Loam",101);
INSERT INTO Plant VALUES(1127,"Bell Pepper","Capsicum annuum","Fruit","Peat",103);
INSERT INTO Plant VALUES(1128,"Sweet Potato","Ipomoea batatas","Tuber","Clay",101);
INSERT INTO Plant VALUES(1129,"Radish","Raphanus sativus","Root","Sandy",104);
INSERT INTO Plant VALUES(1130,"Cabbage","Brassica oleracea","Leaf","Loam",102);
INSERT INTO Plant VALUES(1131,"Apple","Malus domestica","Fruit","Loam",101);
INSERT INTO Plant VALUES(1132,"Grapes","Vitis vinifera","Fruit","Sandy",102);
INSERT INTO Plant VALUES(1133,"Peach","Prunus persica","Fruit","Clay",103);
INSERT INTO Plant VALUES(1134,"Avocado","Persea americana","Fruit","Sandy",104);
INSERT INTO Plant VALUES(1135,"Mango","Mangifera indica","Fruit","Loam",101);
INSERT INTO Plant VALUES(1136,"Pineapple","Ananas comosus","Fruit","Sandy",102);
INSERT INTO Plant VALUES(1137,"Blackberry","Rubus fruticosus","Fruit","Clay",103);
INSERT INTO Plant VALUES(1138,"Artichoke","Cynara scolymus","Flower Bud","Sandy",104);
INSERT INTO Plant VALUES(1139,"Asparagus","Asparagus officinalis","Shoot","Loam",101);
INSERT INTO Plant VALUES(1140,"Cantaloupe","Cucumis melo","Fruit","Sandy",102);
INSERT INTO Plant VALUES(1141,"Pomegranate","Punica granatum","Fruit","Clay",103);
INSERT INTO Plant VALUES(1142,"Lemon","Citrus limon","Fruit","Sandy",104);
INSERT INTO Plant VALUES(1143,"Raspberry","Rubus idaeus","Fruit","Loam",101);
INSERT INTO Plant VALUES(1144,"Chives","Allium schoenoprasum","Leaves","Sandy",102);
INSERT INTO Plant VALUES(1145,"Garlic","Allium sativum","Bulb","Clay",103);
INSERT INTO Plant VALUES(1146,"Oregano","Origanum vulgare","Leaves","Loam",101);
INSERT INTO Plant VALUES(1147,"Thyme","Thymus vulgaris","Leaves","Sandy",102);
INSERT INTO Plant VALUES(1148,"Rosemary","Rosmarinus officinalis","Leaves","Clay",103);
INSERT INTO Plant VALUES(1149,"Cilantro","Coriandrum sativum","Leaves","Sandy",104);
INSERT INTO Plant VALUES(1150,"Basil","Ocimum basilicum","Leaves","Loam",101);
INSERT INTO Plant VALUES(1151,"Cinnamon","Cinnamomum verum","Bark","Sandy",102);
INSERT INTO Plant VALUES(1152,"Mint","Mentha","Leaves","Clay",103);
INSERT INTO Plant VALUES(1153,"Lavender","Lavandula","Flowers","Sandy",104);
INSERT INTO Plant VALUES(1154,"Cranberry","Vaccinium macrocarpon","Fruit","Loam",101);
INSERT INTO Plant VALUES(1155,"Almond","Prunus dulcis","Seed","Sandy",102);
INSERT INTO Plant VALUES(1156,"Walnut","Juglans regia","Seed","Clay",103);
INSERT INTO Plant VALUES(1157,"Hazelnut","Corylus avellana","Seed","Sandy",104);
INSERT INTO Plant VALUES(1158,"Parsley","Petroselinum crispum","Leaves","Loam",101);
INSERT INTO Plant VALUES(1159,"Sage","Salvia officinalis","Leaves","Sandy",102);
INSERT INTO Plant VALUES(1160,"Dill","Anethum graveolens","Leaves","Clay",103);
INSERT INTO Plant VALUES(1161,"Chamomile","Matricaria chamomilla","Flowers","Sandy",104);
INSERT INTO Plant VALUES(1162,"Coriander","Coriandrum sativum","Leaves","Loam",101);
INSERT INTO Plant VALUES(1163,"Fennel","Foeniculum vulgare","Leaves","Sandy",102);
INSERT INTO Plant VALUES(1164,"Mustard","Brassica juncea","Leaves","Clay",103);
INSERT INTO Plant VALUES(1165,"Lemon Balm","Melissa officinalis","Leaves","Sandy",104);
