'>

exercice resolu SQL Serveur (examen de fin du module)

 create database efm_sgbd1
on primary(name=efm_mdf,filename='D:\2eme annee\sql\bd\efm.mdf',filegrowth=5%,size=100mb, maxsize=200mb)
log on(name=efm_ldf,filename='D:\2eme annee\sql\bd\efm.ldf',filegrowth=5%,size=25mb)
 use efm_sgbd1
 create table client(codeclt varchar(50) primary key,nomclt varchar(50),prenom varchar(50),adresse varchar(255),ville varchar(50))
 create table produit(reference varchar(50) primary key ,disignation varchar(100),prix money,qtitenstock int )
 alter table produit add constraint deuxchifr check (reference like '[a-z][a-z]%')

 create table technicien(codetec varchar(50) primary key ,
nomtec varchar(50), prenomtec varchar(50),
 tauxhoraire int constraint taux_houraire check(tauxhoraire in (100,75,50)))

 create table intervention( numero int primary key,dateintervention datetime default getdate(),
 codetec varchar(50) foreign key references technicien, reference varchar(50) references produit,codeclt varchar(50),raison varchar(100))

 --produit
 insert into produit values('aa','designition 1' ,10,5)
  insert into produit values('az','designition 1' ,15,5)
 insert into produit values('ae','designition 1' ,36,5)
 insert into produit values('at','designition 1' ,20,5)
 --technicien
 insert into technicien values(1,'techniecien1','amine',75)
 insert into technicien values(2,'techniecien2','reda',50)
 insert into technicien values(3,'techniecien3','wafae',50)
 insert into technicien values(4,'techniecien4','fatna',100)
--client
insert into client values(1,'cleint 1','izeddine','rue x',' ville a')
insert into client values(2,'cleint 2','touria','rue t',' mbk')
insert into client values(3,'cleint 3','ahmed','rue a','rabat')
insert into client values(4,'cleint 4','re7ema','rue r','sallé')
--intervention
insert into intervention values(5,GETDATE(),1,'aa',1,'raison 1')
insert into intervention values(2,GETDATE(),2,'aa',2,'raison 2')
insert into intervention values(3,GETDATE(),3,'ae',1,'raison 3')
insert into intervention values(4,GETDATE(),1,'az',1,'raison 4')


 select reference,disignation from produit order by reference,disignation
  insert intervention values(1,GETDATE(),10,5)
  select * from intervention

 select codetec,COUNT(*) as [nb intervation] from intervention group by codetec
 select disignation,AVG(prix) as[moyenne prix] from produit group by disignation having avg(prix)>300
 select p.disignation,p.reference,COUNT(i.reference) from produit p,intervention i where p.reference=i.reference group by p.disignation,p.reference having COUNT(*)>1
 SELECT * FROM client
 SELECT * FROM intervention
 select * from technicien where codetec in(select codetec from intervention where datepart(year,dateintervention)=DATEPART(year,getdate()))
 select t.nomtec,t.codetec,COUNT(i.codetec)from technicien t,intervention i where i.codetec=t.codetec group by t.nomtec,t.codetec having COUNT(i.codetec)>1
 select *from intervention i1, intervention i2 where i1.raison=i2.raison and i1.numero < i2.numero

 update produit set prix=prix-prix*1/10 where qtitenstock>100
  delete from client where codeclt not in(select codeclt from intervention i,produit p where i.reference=p.reference)
Publié par Drupal Study