[alan86]

Table creation

CREATE TABLE table_name (name type [, name type [, ...]]);

SQL standard types are:


CHARACTER(n)String size n
n from 1 up to 15000



DATEDate in mm/gg/aa format



TIMEHour in hh:mm format



INTEGER(p)Integer number with p precision
p from 1 up to 45
p da 1 a 45


SMALLINTInteger number with precision 5
from -32768 up to 32767



INTEGERInteger number with precision 10
from -2.147.483.648 up to 2.147.483.647



DECIMAL(p,s)Decimal number with precision p and s cifre
p from 1 up to 45 and s from 0 up to p



REALReal number Numero reale con mantissa di precisione 7valore 0 oppure valore assoluto da 1E-38 a 1E+38


FLOATNumero reale con mantissa di precisione 15valore 0 oppure valore assoluto da 1E-38 a 1E+38


FLOAT(p)Numero reale con mantissa di precisione pp da 1 a 45



For example, to create a table with the data of dependents of a company we have to use this command:

CREATE TABLE tblDependents(
   Id     char(5),
   Surname   char(30),
   Name    char(20),
   FiscalCode char(16) not null,
   Engaged   date,
   Branch   smallint,
   Function  char(15),
   Level    smallint,
   Salary   integer,
   Address   char(25),
   Cap     char(5),
   City    char(20),
   );

Data manipulation

To insert a new row we use INSERT command:

INSERT INTO table_name(name) VALUES(value);

To modify one or more rows we use UPDATE command:

UPDATE table_name SET name = value [, name = value [, ...]] WHERE name = value [, name = value [, ...]]

To delete one ore more rows we use DELETE command:

DELETE FROM table_name WHERE name = value [, name = value [, ...]]

In our example we will have:

INSERT INTO tblDependents
(Id, Surname, Name, FiscalCode, Engaged, Branch, Function, Level, 
  Salary, Address, Cap, City)
VALUES ('AB541', 'Rossi', 'Paolo', 'RSSPLA65M20R341E', '09/15/1997', 3, 'Impiegato', 5, 
  890, 'via roma 34', '20100', 'Milano');

If we want to modify the level of dependent having id AA345:

UPDATE tblPersonale
  SET Livello = 6
  WHERE Id = 'AA345';

To delete dependend having id AQ123:

DELETE FROM tblPersonale
  WHERE Id = 'AQ123';

SELECT command

The general structure is the following:

SELECT name [, name [, ...]]
  FROM table_name [, table_name [, ...]]
  WHERE name = value
   [AND name = value]

For example, we can have the list with surname, name and fiscal code of all dependents with Impiegato function in this way:

SELECT Surname, Name, FiscalCode
  FROM tblDependent
  WHERE Function = 'Impiegato';

If we want all data of those who live in Milan:

SELECT *
  FROM tblDependent
  WHERE City = 'Milano'

If you'd like to have more information, you can visit [http://www.alangiacomin.net/forum/index.php?board=21.0]here[/url]

[watchwood]

A useful reference, but I assume that by "Bases" you mean "Basics"?

[alan86]

Originally Posted by watchwood

A useful reference, but I assume that by "Bases" you mean "Basics"?

yes, you are right.

[willyoumind]

I didn't understand one thing here...

Why the "Milan" transformed into "Milano" inside the code? Is it a sligthly different between of the words and code?

[Digit441]

In the "Milan" example you're searching for the partial keyword of "Milan" therefore any word containing that will show up including Milano, Milana etc. I believe it is case sensitive as well from what I remember so "milan" won't be the same as "Milan" and spacing definitely matters.