Creating and using SQL tables

The tables are the transposition into rows and columns of data from a file. 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.