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: