1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Liste requete sql

abdelouafiOct 21, 2017

    1. abdelouafi

      abdelouafi Administrator Staff Member

      Messages:
      399
      Likes Received:
      9
      Trophy Points:
      18
      Joined
      Sep 13, 2016
      Soit le schéma suivant:
      liste requete sql.png


      Code:
      create database employe
      
      use employe
      
      
      create table departement (
      codeD int primary key not null,
      nom varchar(50),
      
      )
      
      create table Sexe (
      codesexe int primary key not null,
      sexe varchar(50),
      
      )
      
      create table employ (
      codeemploye int primary key not null,
      nom varchar(50),
      prenom varchar(50),
      ville varchar(50),
      codeD int foreign key(codeD) references departement(codeD),
      codesexe int foreign key (codesexe) references sexe(codesexe)
      )
      
      
      insert into departement values(1,'Windows')
      insert into departement values(2,'informatique')
      insert into departement values(3,'physique')
      insert into departement values(4,'Table')
      insert into departement values(5,'PC')
      insert into departement values(12,'PC')
      
      insert into sexe values(1,'Feminin')
      insert into sexe values(2,'Masculin')
      insert into employ values(1,'abdo','boukh','Marrakech',1,1)
      insert into employ values(2,'samir','Nouh','Fes',1,1)
      insert into employ values(3,'oumaom','ammo','casablanca',2,1)
      insert into employ values(4,'sara','Mary','Marrakech',2,2)
      insert into employ values(5,'Brahim','boukh','Marrakech',3,2)
      insert into employ values(6,'aymen','achraf','casablanca',3,2)
      
      
      
      
      
      
      
      
      
      
      
      
      
      
      select * from Sexe
      select * from departement
      select * from employ
      
      select top 1 * from employ order by codeemploye desc
      SELECT * FROM employ where codeemploye =(select MAX(codeemploye) from employ)
      
      SELECT * FROM employ
      WHERE codeemploye not in (SELECT TOP (SELECT COUNT(1)-1
                                   FROM employ)
                              codeemploye
                       FROM employ)
      
            
      
      
      insert into employ values(0,'fff','vv','casablanca',3,2)
      insert into employ (codeemploye,nom,prenom,ville) values(10,'fff','vv','casablanca')
      
      select employ.nom, departement.nom, Sexe.sexe
      from employ
      join departement on employ.codeD=departement.codeD
      join Sexe on employ.codesexe=sexe.codesexe
      
      select nom, ville, codeemploye AS "Numéro employé"
      from employ
      where codeD
      in ( select codeD from departement where employ.codeD= departement.codeD)
      
      select nom, codeD AS "Code département"
      from departement
      where codeD
      not in ( select codeD from employ )
      
      
      select nom, ville, codeD as "Code département"
      from employ
      where codeD>all
      (select codeD from employ where ville='Fes')
      
      select * from employ e
      left outer join departement d
      on e.codeD=d.codeD
      use employe
      
      select codeemploye as "Num Employé", e.nom, ville
      from employ e
      left outer join departement d
      on e.codeD=d.codeD
      where e.codeD is not null
      
      
      create view empl (codeemploye) AS select(codeemploye) from employ
      update employ set nom='abdelouafi', prenom='Boukhris' where codeemploye=1
      
      select codeD,nom,ville
      from employ
      group by codeD,nom,ville
      having nom like '%m%'
      order by codeD
      
      select AVG(distinct codeD) moyenne,ville
      from employ e
      group by ville
      
      select AVG(distinct codeD*6) moyenne
      from employ e
      
      select * from employ where codeD between 10 and 50
      select * from employ where codeD in('1','12')
      select * from employ where codeD not in('1','12')
      select COUNT(codeD) 'code department' from employ
      select ville, COUNT(codeD) 'code department' from employ group by ville
      select ville, COUNT(distinct codeD) 'code department' from employ group by ville
      update  employ set codeD=12 where codeemploye=1
      
      /*ancienne requette*/
      select e.nom 'Nom Employé',d.nom 'Nom Département'  from employ e, departement d
      where e.codeD=d.codeD
      
      --nouvelle jointure
      select employ.nom,employ.codesexe, departement.nom from employ inner join departement on employ.codeD=departement.codeD
      where employ.codesexe=2
      
      select employ.nom,e2.codesexe,employ.codeemploye
      from employ inner join employ as e2
      on employ.codesexe=e2.codesexe
      
      select top 1 * from employ order by codeemploye DESC
      
      select MAX(codeemploye)  from employ
      
      
      select codeD,nom,ville
      from employ
      group by codeD,nom,ville
      having nom like '%m%'
      order by codeD
      
      alter table employ add date_e datetime
      update employ set date_e='31/05/1981'
      select * from employ
      
      --extraire une partie de la date
      select DATENAME (day, e.date_e) from employ e
      
      select DATEPART (year, e.date_e) from employ e
      
      SELECT SYSDATETIME()
      SELECT SYSDATETIME()
          ,SYSDATETIMEOFFSET()
          ,SYSUTCDATETIME()
          ,CURRENT_TIMESTAMP
          ,GETDATE()
          ,GETUTCDATE();
      SELECT CONVERT (date, SYSDATETIME())
      SELECT CONVERT (date, GETDATE())
      
      
      ---concaténation des chaînes de caractère
      select ('Message  :'+nom+' '+prenom ) as name from employ
      
      select distinct * from departement where nom like '%m%'
      
      insert into departement values(7,'Windows')
      
      select * from master.dbo.client
       
      Loading...

Share This Page

Share