SQL

This documentation respect the SQL standard.

Select

Select all data from table: SELECT * FROM table;

Update

Set all price to 100 (for each line): UPDATE table SET price=100;

Delete

Delete all data on table: DELETE FROM table;

Comment

-- This is a comment
-- And you could also use the annotation
-- @file file.sql

Join

Notice :

• LEFT OUTER JOIN is equivalent to LEFT JOIN.
• INNER JOIN is equivalent to JOIN.

Create table

DROP TABLE IF EXISTS parmentier.animal CASCADE;

CREATE TABLE animal
(
  idani CHAR(8) NOT NULL,
  numcl CHAR(5) DEFAULT NULL,
  cotypa CHAR(2) NOT NULL ,
  noman VARCHAR(20) DEFAULT NULL,
  race VARCHAR(20) DEFAULT NULL,
  daten DATE DEFAULT NULL,
  sexe CHAR(1) NOT NULL,
  coule VARCHAR(20) NOT NULL,
  steril CHAR(1) DEFAULT 'N',
  taill FLOAT NOT NULL,
  poids FLOAT NOT NULL,
  deced CHAR(1) DEFAULT 'N',
  comme VARCHAR(250) DEFAULT NULL,

  CONSTRAINT animal_idani_pk PRIMARY KEY(idani),
  CONSTRAINT animal_numcl_fk FOREIGN KEY(numcl) REFERENCES parmentier.client(numcl),
  CONSTRAINT animal_cotypa_fk FOREIGN KEY(cotypa) REFERENCES parmentier.type_animal(cotypa),

  CONSTRAINT animal_sexe CHECK(sexe IN ('M','F','I')),
  CONSTRAINT animal_steril CHECK(steril IN ('O','N')),
  CONSTRAINT animal_taill CHECK(taill BETWEEN 1 AND 300),
  CONSTRAINT animal_poids CHECK(poids BETWEEN 0 AND 1500),
  CONSTRAINT animal_deced CHECK(deced IN ('O','N'))
);

Notice: was tested on postgres.

Notice: NOT NULL is optional when you use PRIMARY KEY

Functions

Cannot be used in WHERE you need to use them in SELECT or in a SUB-REQUEST (see below), or in HAVING((HAVING require to use GROUP BY)).

SELECT "Prix total general"
FROM depenses_clients
WHERE "Prix total general" > (SELECT SUM("Prix total general")/COUNT(*) FROM depenses_clients);