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.


CREATE TABLE name (column, column...) 

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


CHAR (10)    
NUMBER (8.2)    
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:



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:

     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.