21 Déc2015
Les Vues SQL - Base de données EMPLOYES
Objectif: Créer et utiliser des Vues SQL.
Soit le schéma relationnel :
Modèle Relationnel : EMPLOYES
Travail à faire:
- Créer la base de données EMPLOYES.
- Créez une vue V_EMP contenant : le matricule, le nom, le numéro de département, la somme de la commission et du salaire nommé GAINS, le lieu du département.
- Sélectionnez les lignes de V_EMP dont le salaire total est supérieur à 10.000
- Essayez de mettre à jour le nom de l'employé de matricule 1 à travers la vue V_EMP.
- Créez une vue V_EMP10 qui ne contienne que les employés du département 10 de la table EMP (n'utilisez pas l'option CHECK pour cette création). Insérez dans cette vue un employé qui appartient au département 20.
Essayez ensuite de retrouver cet employé au moyen de la vue V_EMP10 puis au moyen de la table EMP. - Détruisez cette vue VEMP10 et recréez-la avec l'option CHECK.
- Essayez d'insérer un employé pour le département 30. Que se passe-t-il ? Essayez de modifier le département d'un employé visualisé à l'aide de cette vue.
- Afficher la liste des matricules, noms et salaires des employés avec le pourcentage par rapport au total des salaires de leur département (utilisez une vue qui fournira le total des salaires).
//Auteur: CHAOULID
//Copyright: Exelib.net
//Q1 create database EMPLOYES go use EMPLOYES create table EMP(matricule varchar(30) primary key,Nom varchar(30),Prenom varchar(30),Salaire float,Commission float, Num_Dept int foreign key references DEPT(Num_Dept)) create table DEPT(Num_Dept int primary key,Nom_Dept varchar(30),Ville_Dept varchar(30)) //Q2 create view V_EMP as select e.matricule,e.Nom,e.Prenom,e.Num_Dept,e.Salaire+ISNULL(e.Commission,0) as "GAINS",d.Ville_Dept from EMP e inner join DEPT d on e.Num_Dept=d.Num_Dept select * from V_EMP //Q3 select * from V_EMP where GAINS>10000 //Q4 update V_EMP set Nom='NOM11' where matricule=1 //Q5 create view V_EMP10 as select * from EMP where Num_Dept=10 insert into V_EMP10 values(6,'NOM6','PRENOM6',6000,400,20) select * from V_EMP10 select * from EMP //Q6 drop view V_EMP10 create view V_EMP10 as select * from EMP where Num_Dept=1 with check option //Q7 select * from V_EMP10 insert into V_EMP10 values(7,'NOM7','PRENOM7',7000,400,30) update V_EMP10 set Num_Dept=20 where matricule=2
// les deux requêtes ci-dessus échouent parce que les modifications apportées ne satisfont pas la condition spécifiée dans la vue crée avec l'option "with check option" //Q8 create view V_SOMME_SALAIRES_DEPT as select Num_Dept,SUM(Salaire) as "Somme_Salaires" from EMP group by Num_Dept select * from V_SOMME_SALAIRES_DEPT select e.matricule,e.Nom,e.Prenom,e.Salaire,cast((e.Salaire*100)/s.Somme_Salaires as varchar) +'%' as "Pourcentage" from EMP e inner join V_SOMME_SALAIRES_DEPT s on e.Num_Dept=s.Num_Dept
