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.
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
We will create a
database of a real estate agency whose database contains a number of houses
Name of the base: houses.
The database contains a list of houses and apartments. For each item in the list:
- A unique identification code.
- Date of acquisition.
- Name of owner
The table could look as this:
|01||Miami||45||01/01/2007||Del Icio Us|
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.