Creating and using SQL tables

Tables store data from a file into rows and columns.

It is conceptually easier to put in rows records of each article and in column information relating to each. You can add items by adding rows, but adding columns constitutes a change in the structure of the table.

If our file contains a list of apartments, we will have a row for each apartment and columns correspond to the information on each, such as the city where it is located, the acquisition date, the name of the owner...

Creating a Table

The CREATE TABLE command allows to insert a new table in the database.

Format:

CREATE TABLE name (column, column...)

For each column definition a name is followed by the type of data in the column.

Examples:

CHAR (10)

NUMBER (8.2)

DATE

VARCHAR (32000)

We shall see in detail the list of data types. We have to know that fixed-size strings have the CHAR type, while the VARCHAR type is used for texts stored with a variable size, such as posts of blogs.

Parameters of columns

A column could be empty or not. It not, NOT NULL must be added to its definition:

DATE NOT NULL

Exemple

We will create a database of a real estate agency whose database contains a number of houses and apartments.
Name of the base: houses.
The database contains a list of houses and apartments. For each item in the list:
- A unique identification code.
- Town.
- Surface.
- Date of acquisition.
- Name of owner

The table could look as this:

Code	City	Surface	Date	Owner
01	Miami	45	01/01/2007	Del Icio Us
02	Mountain View	70	02/02/2008	Google

In the header was placed names of columns.

We create a table with the column names and the definition of the type of data:

CREATE TABLE houses(
     code NUMBER(4),
     city CHAR(30),
     surface NUMBER(4),
     date DATE,
     owner CHAR(25)
     )

We have now to fill the table with data from the agency, this will be the topic of the next chapter.