SQL tables

Tables are the transfer of data from a file to rows and columns. Conceptually, it is easier to place records of each article online and in a column with information on each of them. You can add parts by adding rows, while adding columns represents a change in the table structure.

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

Creating a Table

The CREATE TABLE command inserts a new table into the database.

Format:

CREATE TABLE nom ( colonne, colonne, ....)

A name is defined for each column, followed by a data type definition in the column.

Examples:

CHAR(10)

NUMBER(8,2)

DATE

VARCHAR(32000)

Let's take a closer look at the list of data types. However, knowing that the char type corresponds to a fixed-size character string, varchar means variable-length text, such as a journal entry, with the maximum allowable size.

Column Options

The column may or may not be empty. By default, it may be, otherwise you can add NOT NULL to the type definition:

DATE NOT NULL

Example

We will create a real estate agency database that contains a number of houses or apartments.
Base name: Houses.
The database contains a list. For each list item:
- unique identification code.
- City.
- surface.
- Date of acquisition.
- Name of the Lessee.

The table will look like this:

Code	City	Area	Date	Tenant
01	Paris	45	01/01/2007	Del Ichio Us
02	Marseille	70	02/02/2008	Google

The column name is placed in the header row.
A table is created with the column names and the definition of the corresponding data type.

CREATE TABLE maisons(
     code NUMBER(4),
     ville CHAR(30),
     surface NUMBER(4),
     date DATE,
     locataire CHAR(25)
     ) 

It remains only to fill the table with agency data, this will be the subject of the next chapter.