SQL tutorial-Create Alter and Delete tables

Create , alter,  and delete tables

SQL not only provides you the ability to work with the records of a table but also allows you create, delete, and alter a table or queries.

To create a new table using SQL you need to use the CREATE Statement.
This statement has the following prototype:
CREATE TABLE table-name(field-name1 type [constrains], field-name2 type [constrains],...);

Example: create a table named TblBook that has the following field names and attributes:

-BookID: This field is primary key. It is a text type field and indicates the unique id of the books in this table. You can enter maximum 10 characters in to this column.
-Title: This is the text type field that tells us about the title of the books. You can enter maximum 100 characters in to this column. This column can not be blank.
-ISBN: It is the text type field indicating the isbn number of the books. You can enter maximum 20 characters in to this column.
-PubID: It is the text type field telling us the id of the publisher of the books.

CREATE TABLE TblBook(BookID TEXT(10)  PRIMARY KEY, Title TEXT(100) NOT NULL, ISBN TEXT(20));

To change the structure of the table such as adding new fields, deleting fields, changing the properties of the fields, you can use ALTER statement.  This statement has the following prototype:

ALTER TABLE Table-name

ADD COLUMN field-name type [constrains] |

ALTER COLUMNfield-name type [constrains] |

DROP COLUMN field-name;

Example: Now we want to change the field ISBN not to allow the user to enter the blank value and to add a new field call PubID that was missed in the previous example.

ALTER TABLE TblBook

ALTER COLUMN(ISBN TEXT(20) NOT NULL);

ALTER TABLE TblBook

ADD COLUMN(PubID TEXT);

To delete a table is a simple task. You simply use the DROP TABLE statement followed by the name of table that you want to delete.

Example: DROP TABLE TblBook;