INF4211

Bases de données pour les organisations

Project maintained by guyfrancoeur Hosted on GitHub Pages — Theme by mattgraham

Les types et domaines

--INSERT INTO def VALUES  'valeur';

--ne pas utiliser ceci : le back tic
--INSERT INTO def VALUES  `valeur`;

 CREATE DOMAIN dollars numeric(12,2);

CREATE type weekend as ENUM ('SAMEDI','DIMANCHE');

CREATE TABLE N1 (jour weekend);

INSERT INTO N1 VALUES ('LUNDI');
INSERT INTO N1 VALUES ('SAMEDI');
INSERT INTO N1 VALUES ('SamEdi');

--DDL : Data Definition Language
  -- CREATE, DROP, ALTER 

--DML : Data Manipulation Language
  -- UPDATE, DELETE, INSERT, SELECT 

SELECT * FROM N1;

CREATE INDEX idx_instrustor_pk on instructor (id);

SELECT * FROM CODEPERMANENT.public.course;

GRANT SELECT,DELETE,INSERT,UPDATE ON course TO USERNAME; --TEST001112233

Des exemples

DROP TABLE nhl;

CREATE TABLE NHL (pTeam numeric, cTeam varchar(30));
--CREATE INDEX idx_nhl on NHL (pTeam);
SELECT * FROM NHL WHERE cTeam LIKE 'Canad%';
INSERT INTO NHL (cTeam) VALUES ('Jets');
SELECT * FROM NHL;
UPDATE nhl set pteam=12 where cteam='Jets';

--NEST PAS NECESSAIRE POURQUOI ? 
--ALTER TABLE nhl alter column pTeam set not null;

ALTER TABLE nhl add constraint pk_nhl primary key (pteam);

DELETE FROM NHL;
UPDATE nhl set cTeam = 'Rangers' WHERE pTeam = 10;