--  CREATE VIEW <view name> AS <view definition> ;
--  you can rename attributes in a VIEW
--  Most views are not updatable
--  A view is updatable if 
--  1. it is constructed from just one table R
--  2. WHERE does not have a subquery involving R
--  3. SELECT contains only attributes of R
--  4. the attributes of R missing in the view can be completed by
--     NULL.
--  Then modyfying view modifies the underlying table R.

--  IMPORTANT: Views are preserved from one session to another
--  unless you make DR0P VIEW
-----------------------------------------------------------------
--  DROP VIEW salaire_moy_dept;

-- la vue avec les salaires moyens par departement
CREATE VIEW salaire_moy_dept(dnumero,dnom,salaire)
AS SELECT D.dnumero, D.dnom , CAST( avg(E.salaire) AS Dec(8,2) )
     FROM employe E, departement D
     WHERE E.dnumero = D.dnumero
     GROUP BY D.dnom, D.dnumero ;

-- Maintenant on peut utiliser une vue comme une table:     
SELECT salaire AS "salaire moy maximal", dnumero, dnom
FROM   salaire_moy_dept
WHERE salaire = (SELECT max(salaire) FROM salaire_moy_dept);

----------------------------------------------------------------
--DROP VIEW dept_sept;
DELETE FROM employe WHERE nom LIKE 'Kapus%';

--creer une vue du departement 7
CREATE VIEW dept_sept
AS     SELECT nom, prenom, ssn
       FROM   employe
       WHERE  dnumero = 7;

SELECT * FROM dept_sept;

INSERT INTO employe(nom,prenom,ssn,dnumero)
VALUES  ('Kapuscinski','Artur','333555777',7);

SELECT * FROM dept_sept;