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 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.
Challenge see if you can find out one extra fact on this topic that we haven't already told you
Click on this link: SQL create table
Copyright © www.teach-ict.com