INF4211

Bases de données pour les organisations

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

Exemple du cours du 2021-02-08

--create
CREATE TABLE covid ( pCovid numeric, cCovid varchar(30), nYear numeric(4) );

--alter
ALTER TABLE covid add column dEclosion date;
ALTER TABLE covid add column nEclosion numeric default 1;
ALTER TABLE covid ALTER column pcovid set NOT NULL;

--doublons possible pas de contraintes
INSERT INTO covid (pcovid, ccovid) VALUES (1,'variante 1');
INSERT INTO covid (pcovid, ccovid) VALUES (1,'variante 1');
INSERT INTO covid (pcovid, ccovid) VALUES (1,'variante 1');
INSERT INTO covid (pcovid, ccovid) VALUES (2,'variante 2');
INSERT INTO covid (pcovid, ccovid) VALUES (3,'variante 3');
INSERT INTO covid (pcovid, ccovid) VALUES (4,'variante 4');

--distinct
SELECT DISTINCT * FROM covid;
SELECT DISTINCT pcovid, ccovid FROM covid;

--Litéral
UPDATE covid SET nEclosion=100+pcovid;

--avancé
SELECT NOW(), 
DATE_PART('year', NOW()) as annee,
DATE_PART('month', now())+50 as mois,
CONCAT(DATE_PART('year', NOW()), DATE_PART('month', now())+50) as v2;

--exemples avec le TP0
SELECT *
FROM classroom
WHERE capacity > 50
OR room_number = '514'


SELECT distinct *
--c.course_id, title 
FROM course c, teaches t
WHERE t.course_id = c.course_id --JOINTURE
AND c.credits = 4 --FILTRE


SELECT distinct c.course_id, title 
FROM course c, teaches t
WHERE t.course_id = c.course_id --JOINTURE
AND c.credits = 4 --FILTRE

SELECT 'MON cours est : ' || LOWER(c.course_id) || ' ' || c.title as V1
FROM course c, teaches t
WHERE t.course_id = c.course_id --JOINTURE
AND c.credits = 4; --FILTRE

SELECT *
FROM course c, teaches t
WHERE t.course_id = c.course_id --JOINTURE
--AND credits >= 3 AND credits <= 4
AND credits BETWEEN 3 AND 4
ORDER BY dept_name ASC, c.course_id;

SELECT *
--course_id 
from section
WHERE semester = 'Fall'
AND year = 2017
AND course_id NOT IN (SELECT course_id FROM section WHERE room_number = '101');

SELECT COUNT(*), dept_name 
FROM course
GROUP BY dept_name;

SELECT avg(c.credits) as ok, dept_name 
FROM course c
GROUP BY c.dept_name
having avg(c.credits) > 3;