/************************/
/* Adil el hajiz */
/************************/
CREATE DATABASE fst on default=1
GO
USE fst
go
CREATE TABLE etudiant
(
cin numeric(18, 0) NOT NULL,
nom varchar(50) NULL,
prenom varchar(50) NULL,
date_naiss datetime NULL,
adress varchar(60) NULL,
ville varchar(50)default'casablanca' NULL,
téleph varchar(50) NULL,
CONSTRAINT TPK_etudiant PRIMARY KEY (cin),
constraint ff check(ascii(substring(prenom,1,len(prenom)))between 65 and 90
or ascii(substring(prenom,1,len(prenom)))between 97 and 122 )
)
create table inscriptions
(N_inscription numeric identity(1,1)primary key,
filiere varchar(40),
date_inscription datetime,
cin numeric,
constraint fk_etudiant foreign key (cin)references etudiant(cin)
)
create table modules
(n_module numeric primary key,
libellé varchar(50)not null)
create table formateurs
(n_formateur numeric primary key,
nom varchar(50),
prenom varchar(50),
salaire money,
check(ascii(substring(prenom,1,len(prenom)))between 65 and 90
or ascii(substring(prenom,1,len(prenom)))between 97 and 122 ))
create table examens
(n_inscription numeric,
n_module numeric,
n_formateur numeric ,
note numeric(4,2),
constraint pk primary key(n_inscription,n_module,n_formateur),
constraint ck_ check(note between 0 and 20)),
constraint fk_inscription foreign key(n_inscription)references ,
constraint fk_module foreign key(n_module),
constraint fk_formateur foreign key(n_formateur)
)
insert into etudiant
values(1,'hajar','badaoui','01/05/1988','hay nassim 10 ','casa','022-34-66-33')
insert into inscriptions
values ('trssi','01/04/2005',1)
insert into modules
values(1,'administration'),''
insert into formateurs
values(1,'morchid','omar',14)
insert into examens
values (1,1,1,13)
--modifucation
sp_rename 'inscriptions.filiere','spécialite','column'
alter table formateurs
add date_naiss datetime null
select * from formateurs
alter table etudiant
use master
go
alter database hajar
modify file
(name=hajar_log,
maxsize=100mb)
create index ll
on etudiant(nom)
-------------les Requetes
11)
SELECT etudiant.nom, etudiant.prenom, inscriptions.spécialite
FROM etudiant INNER JOIN
inscriptions ON etudiant.cin = inscriptions.cin
WHERE inscriptions.spécialite = 'trssi'
12)
SELECT nom, prenom, date_naiss, ville
FROM etudiant
WHERE ville = 'casa' AND YEAR(date_naiss) = 1988
ORDER BY nom DESC
13)
SELECT TOP 5 etudiant.nom, etudiant.prenom, inscriptions.spécialite
from etudiant inner JOIN
inscriptions ON etudiant.cin = inscriptions.cin
where inscriptions.spécialite = 'trssi'
14)SELECT modules.libellé, AVG(examens.note) AS moyen
FROM examens INNER JOIN
modules ON examens.n_module = modules.n_module
GROUP BY modules.libellé
15)
SELECT formateurs.nom, formateurs.salaire
FROM formateurs INNER JOIN
examens ON formateurs.n_formateur = examens.n_formateur
INNER JOIN
modules ON examens.n_module = modules.n_module
where (formateurs.salaire >
(SELECT MAX(formateurs.salaire)
FROM formateurs INNER JOIN
examens ON
formateurs.n_formateur = examens.n_formateur INNER JOIN
modules ON examens.n_module =
modules.n_module
WHERE modules.libellé = 'administration'))
16)
SELECT UPPER(etudiant.nom) + ' ' + etudiant.prenom AS [nom complet],
etudiant.adress + ' ' + etudiant.ville AS domicle, DATEDIFF(yyyy,
inscriptions.date_inscription, GETDATE()) AS duree
FROM etudiant INNER JOIN
inscriptions ON etudiant.cin = inscriptions.cin
WHERE (DATEDIFF(year, inscriptions.date_inscription, GETDATE()) = 2)
--------------vue-----------------
17)
create view view1
as
SELECT n_module, libellé
FROM modules
WHERE (NOT (libellé LIKE 'a%'))
18)
create view view2
as
SELECT modules.libellé, formateurs.nom
FROM examens INNER JOIN
formateurs ON examens.n_formateur = formateurs.n_formateur
INNER JOIN
modules ON examens.n_module = modules.n_module
----------------proc------------------
19)create proc proc1
@x datetime,
@y datetime
as
SELECT cin, nom, prenom, date_naiss, adress, ville, téleph
FROM etudiant
where date_naiss between @x and @y
exec proc1 '12/01/1980','12/01/1990'
create proc gg
@g numeric
as
SELECT etudiant.cin, etudiant.nom, etudiant.prenom, etudiant.date_naiss,
etudiant.adress, etudiant.ville, etudiant.téleph
FROM etudiant INNER JOIN
inscriptions ON etudiant.cin = inscriptions.cin INNER JOIN
examens ON inscriptions.N_inscription =
examens.n_inscription
where examens.note=@g
20)
alter proc hh
as
create table salles
(n_salle numeric primary key,n_formateur numeric,
libellé varchar(30),
constraint fffff foreign key (n_formateur)references formateurs(n_formateur) )
----------------administration--------------
26)
sp_addlogin 's1'
sp_addlogin 's2'
sp_addlogin 's3'
27)
sp_grantlogin 'N-1W63RUQRRUI36\Administrateur'
28)
sp_adduser 's1','user1'
sp_adduser 's2','user2'
sp_adduser 's3','user3'
29)
sp_revokedbaccess 's3'
exec sp_addumpdevice 'gg','mm'
Pour revenir le Tp
