Examen de Fin de Formation Juillet 2017: Niveau TS - Epreuve pratique V3-1 | Exam-Lib
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
Dismiss Notice
Welcome to our Education website, plz like our page facebook to support us. Thank You and wish you good navigation

Examen de Fin de Formation Juillet 2017: Niveau TS - Epreuve pratique V3-1

abdelouafiDec 27, 2017

    1. abdelouafi

      abdelouafi Administrator Staff Member

      Messages:
      816
      Likes Received:
      14
      Trophy Points:
      18
      Joined
      Sep 13, 2016
      Examen de Fin de Formation
      Session Juillet 2017
      Niveau: TS Epreuve: pratique V3-1
      Durée: 4h30min


      1.jpg
      2.jpg
      3.jpg


       
      Last edited: Dec 27, 2017
      Loading...

      Merci de partager ce post sur facebook

      1-
      Code:
      create database vacataire
      use vacataire
      
      
      create table filiere (idf int not null primary key, nomf varchar(40), niveau varchar(40))
      
      create table groupe (idg int not null primary key, nomg varchar(40), annee int, idf int foreign key(idf) references
      filiere(idf) )
      
      create table module (idmod int not null primary key, nommod varchar(40),
          objectimod varchar(40),
          mh int, idf int foreign key(idf) references filiere(idf) )
      
      
      
      create table formateurvacataire (idfv int not null primary key, nomfv varchar(40),
          prenfv varchar(40), nheure int, prixheure float, mpasse varchar(40))
      
      
      create table vacation (idvac int not null primary key, datevac Datetime, nbreheure int,
          idfv int foreign key(idfv) references formateurvacataire(idfv))
      
      
      
      
      insert into filiere values(1,'TDI','premiere annee')
      insert into filiere values(2,'TDI','2eme annee')
      insert into filiere values(3,'TRI','premiere annee')
      insert into filiere values(4,'TRI','2eme annee')
      insert into filiere values(5,'TMSIR','premiere annee')
      insert into filiere values(6,'TMSIR','2eme annee')
      insert into filiere values(7,'TELECOM','premiere annee')
      insert into filiere values(8,'TELECOM','2eme annee')
      
      
      insert into groupe values(1,'groue A',2017,1)
      insert into groupe values(2,'groupe A',2017,2)
      
      insert into groupe values(3,'groue B',2017,1)
      insert into groupe values(4,'groupe B',2017,2)
      
      insert into groupe values(5,'groue C',2017,1)
      insert into groupe values(6,'groupe C',2017,2)
      
      insert into groupe values(7,'groue A',2017,3)
      insert into groupe values(8,'groupe A',2017,4)
      
      insert into groupe values(9,'groue B',2017,3)
      insert into groupe values(10,'groupe B',2017,4)
      
      
      insert into groupe values(11,'groue C',2017,3)
      insert into groupe values(12,'groupe C',2017,4)
      
      
      insert into groupe values(13,'groue A',2017,5)
      insert into groupe values(14,'groupe A',2017,6)
      
      insert into groupe values(15,'groue B',2017,5)
      insert into groupe values(16,'groupe B',2017,6)
      
      insert into groupe values(17,'groue C',2017,5)
      insert into groupe values(18,'groupe C',2017,6)
      
      
      
      
      insert into module values(1,'porg c','maitriser langage C',80,3)
      insert into module values(2,'uml','maitriser uml',120,1)
      insert into module values(3,'sgbd 1','maitriser sgbd 1',120,2)
      insert into module values(4,'sgbd 2','maitriser sgbd 2',120,2)
      
      
      
      insert into formateurvacataire values(1,'BOUKHRIS','ABDELOUAFI',8,150,'Abdelouafi1')
      insert into formateurvacataire values(2,'sammou','Mustapha',8,150,'123456789')
      insert into formateurvacataire values(3,'bado','abderafiaa',8,200,'bado1970')
      insert into formateurvacataire values(4,'lachguer','lachgaer',8,1500,'lachgaer123')
      
      
      insert into vacation values(1,'01-09-2012',8,1,1,1)
      insert into vacation values(2,'01/09/2017',8,1,1,2)
      insert into vacation values(3,'01/09/2017',8,1,2,1)
      insert into vacation values(4,'01/09/2017',8,1,2,1)
      



      2-
      select * from module where idf=0

      3-
      Code:
      Create Procedure supprimervacataire @idvac int
      as
      
      begin
      delete from vacation where idfv=@idvac
      delete from formateurvacataire where idfv=@idvac
      end
      
      Exec supprimervacataire 1
      
      

      4-

      Code:
      create function f6(@idmod int)
      returns float
      as
      begin
        declare @nbheure int, @prixheure int,@idfv int,@prixmoyen float;
        set @nbheure=(select sum(nbreheure) from vacation where idmod=@idmod)
        set @idfv=(select distinct idfv from vacation where idmod=@idmod)
        set @prixheure=(select prixheure from formateurvacataire where idfv=@idfv)
        set @prixmoyen=@nbheure*@prixheure
        return @prixmoyen
      
      end
      
      
      
      
      
      print vacataire.dbo.f6(1)
      go

Share This Page

Share