INF4211

Bases de données pour les organisations

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

Les sous requêtes

SELECT * FROM course WHERE course_id NOT IN (
   SELECT course_id 
   FROM course
   WHERE dept_name='Biology' OR dept_name ='Finance'
);

SELECT *
FROM teaches t, course c
WHERE t.course_id = c.course_id
AND id IN (SELECT id FROM instructor WHERE dept_name LIKE '%Comp%' );


SELECT * FROM classroom WHERE room_number NOT IN ('100', '101');

SELECT * FROM classroom WHERE room_number <> '100' OR room_number <> '101';

-- EXEMPLE LONG
SELECT count(distinct id) FROM takes ta
-- ici c'est long a ecrire ...
--, teaches te
--WHERE ta.course_id = te.course_id
--AND ta.sec_id = te.sec.id 
--AND ta.semester = te.semester
-- ... 

WHERE (course_id, sec_id, semester, year) in (
  SELECT course_id, sec_id, semester, year from teaches WHERE id = '10101'
)

SELECT * FROM 
 (SELECT dept_name, avg(salary) as avg_sal
  FROM instructor 
  GROUP BY dept_name ) t
 WHERE avg_sal > 72000;

with

 with max_budget(valeur) as 
 (SELECT max(budget) from department)
  SELECT d.dept_name from department d, max_budget
  WHERE d.budget = max_budget.valeur;
  
  
--- EXEMPLE similaire
-- sous-requete dans un colonne
  SELECT dept_name, 
        (SELECT count(*) from instructor
         WHERE department.dept_name=instructor.dept_name) as numinst
FROM department;

SELECT d.dept_name, num_ins 
FROM (
SELECT dept_name, count(*) as num_ins from instructor GROUP BY dept_name
) t, department d
WHERE d.dept_name=t.dept_name;

SELECT avg(salary) from instructor;

Le DML

-- TABLE sans PK
CREATE TABLE test (col1 numeric, col2 varchar(20));

INSERT INTO test (col2) values ('co');
INSERT INTO test (col1) values (333);

SELECT COUNT(*) FROM test;

DELETE FROM test where col2 = 'co';

DELETE FROM test;

SELECT * FROM test;

--AVEC CONCATENATION
insert into test (col1, col2)
  SELECT credits*3, 'DATA ' || credits  FROM course;