2. Creating a database and table

The first command to issue within the RDBMS is to set up a database. This is quite straightforward, namely

CREATE DATABASE test

This SQL command will cause the RDBMS to create the supporting structures for a database called 'test'.

At this point it is an empty database.

The next step is to create a table within the database. And this is done as follows

CREATE TABLE Clubs ( ClubID INT NOT NULL, ClubName VARCHAR(50) NOT NULL, PRIMARY KEY (ClubID))

The figure below shows a real table being created in open source MySQL with the free admin tool called PHPMyAdmin

The top part shows the SQL generated to create the table and the lower part shows the resulting table structure.

Let's take each element at a time.

The first command is the standard SQL command to create a table CREATE TABLE

This is followed by the table's name 'Clubs' and then a set of parameters within a pair of brackets

CREATE TABLE Clubs ( .....)

The parameters within the brackets define, as a minimum, each field name and their data type. For example the first parameter in this example is 'ClubID' and it is of type Integer.

CREATE TABLE Clubs ( ClubID INT .....

The second field is 'ClubName' and is of type VarChar. The length of VarChar is 50 characters.

CREATE TABLE Clubs ( ClubID INT, ClubName VARCHAR(50) ....

A comma separates each field declaration.

SQL also support some additional declarations. Note the PRIMARY KEY in the example. This tells the system that the primary key is the field ClubID. In the figure above the ClubID is underlined to show that it is the primary key of the table

You can insist on the field value not being empty by using the term NOT NULL. So the first field ends up as

CREATE TABLE Clubs ( ClubID Integer NOT NULL,

Regarding the VarChar, notice that 50 elements of storage has been allocated to this field. When designing a field you should think carefully about the expected length of the field values - too short and the values may become truncated, too long and you could waste a lot of space.