Structured Query Language
The Seeds Database
Throughout the remainder of this guide, you will create and manipulate a database of seeds using MySQL. Your first job is to define the table. To do this, go to your web server and browse to PHPMyAdmin. Create a new database (an option to do so is on the first page you see when PHPMyAdmin loads).
Data Dictionary
Field Name | Explanation | Type |
---|---|---|
Name | The name of the plant. | varchar(25) |
Type | The type of plant (eg hardy annual) | varchar(40) |
Seed | The number of seeds in a gram | int(5) |
Where | Where the seeds should be planted | varchar(11) |
When | The month when the seeds should be planted | varchar(9) |
Time | The time it takes the plant to germinate | int(2) |
Flowers | The month when the flowers start to appear on the plant | varchar(9) |
Variety | The variety of this particular plant | varchar(30) |
Description | A description of the plant | varchar(100) |
Price | The cost of a gram of the seeds. | float(2,2) |
Notice that the data types are expressed differently in MySQL. A manual can be downloaded from the MySQL home page which will explain data types further. The purpose of including this here is to show how MySQL allows SQL statements for data definitionas well as data manipulation.
The SQL to create this is as follows,
CREATE TABLE `tblseeds` (
`name` VARCHAR( 25 ) NOT NULL ,
`type` VARCHAR( 40 ) NOT NULL ,
`seed` INT( 5 ) NOT NULL ,
`where` VARCHAR( 11 ) NOT NULL ,
`when` VARCHAR( 9 ) NOT NULL ,
`time` VARCHAR( 2 ) NOT NULL ,
`flowers` VARCHAR( 9 ) NOT NULL ,
`variety` VARCHAR( 30 ) NOT NULL ,
`description` VARCHAR( 100 ) NOT NULL ,
`price` FLOAT( 2, 2 ) NOT NULL ,
PRIMARY KEY ( `name` ) );
Create your table by typing or copying the SQL into the query window in PHPMyAdmin
Importing The Data
Click on the import link in PHPMyAdmin and choose to import the following file (which includes SQL statements to insert rows in the table).
If you click, rather than download the link, you should see the format for an INSERT query. Find a few more records to add (making things up if you have to) and create INSERT queries to add them to the table.