create database adoExemple;
use adoExemple;


create table programmes
(
code_prog smallint not null,
nom_programme varchar(40) not null,
constraint pk_programme primary key(code_prog)
);


create table etudiants
(
numad int identity(1,1),
nom varchar(30) not null,
prenom varchar(30) not null,
code_prog smallint ,
constraint pk_etudiant primary key(numad),
constraint fk_programme foreign key(code_prog) references programmes(code_prog)
);

begin transaction;
insert into programmes values(420,'informatique');
insert into programmes values(320,'soins infirmiers');
insert into programmes values(430,'génie électrique');
insert into programmes values(412,'arts visuels');
insert into programmes values(300,'éducation à l''enfance');

insert into etudiants(nom,prenom,code_prog) values('Patoche','Alain',420);
insert into etudiants(nom,prenom,code_prog) values('Leroy','Simba',420);
insert into etudiants(nom,prenom,code_prog) values('Fafar','Marie',420);
insert into etudiants(nom,prenom,code_prog) values('Couturier','Sylvain',420);
----
insert into etudiants(nom,prenom,code_prog) values('Poitras','Charlie',320);
insert into etudiants(nom,prenom,code_prog) values('Vampire','Primogen',320);
insert into etudiants(nom,prenom,code_prog) values('Astre','Soleil',320);
---
insert into etudiants(nom,prenom,code_prog) values('Clément','Sylvain',430);
insert into etudiants(nom,prenom,code_prog) values('Saturne','lune',430);

insert into etudiants(nom,prenom,code_prog) values('Lechat','Remi',412);
insert into etudiants(nom,prenom,code_prog) values('Lami','Vlade',412);

insert into etudiants(nom,prenom,code_prog) values('Simpson','Bart',300);
commit transaction;