INF4211

Bases de données pour les organisations

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

Les contraintes, défaut

DROP TABLE nhl;

CREATE TABLE NHL (pTeam numeric, cTeam varchar(30));
INSERT INTO NHL (cTeam) VALUES ('Jets');
INSERT INTO NHL (cTeam) VALUES ('Leafs');
SELECT * FROM NHL;
UPDATE nhl set pteam=12 where cteam='Jets';

--EXEMPLE Bonus
SELECT pteam, cteam, ROW_NUMBER() OVER (order by cteam) 
FROM nhl where pteam is null;

--EXEMPLE Bonus (qqc de très puissant
SELECT ctid from nhl;

-- des programmes
do $$
declare 
   var_i integer := 0;
   var_max integer := 0;
begin
  SELECT COUNT(*) FROM nhl INTO var_i;
  SELECT COALESCE(MAX(pteam),1) FROM nhl INTO var_max;
   while var_i <> 0 loop
     UPDATE nhl set pteam = var_max
     WHERE pteam is null
     AND ctid IN (SELECT MIN(ctid) FROM nhl WHERE pteam is null);
     
	   var_i := var_i - 1;
	   var_max := var_max + 1;
   end loop;
end$$;