CREATE TABLE emp(
numEmp int(11),
nomEmp varchar(20) ,
PrenomEmp varchar(20) ,
Fonction varchar(20),
Adresse varchar(20) ,
Sexe varchar(10) ,
AgeE int(2) ,
Salaire int(11) ,
Commission int(11) ,
NumChef int(11) ,
NumDept int(11) ,
PRIMARY KEY (numEmp)
);
CREATE TABLE dept (
NumDept int(11) NOT NULL ,
NomDept varchar(20) ,
Adresse varchar(20) ,
NumDirecteur int(11) ,
PRIMARY KEY (NumDept)
) ;
CREATE TABLE grade (
NumGrade int(11) ,
NomGrade varchar(20) ,
SalaireMin int(11) ,
SalaireMax int(11),
PRIMARY KEY (NumGrade)
) ;
CREATE TABLE projet(
NumProjet int(11),
NomProjet varchar(20),
Local varchar(20),
NumDept int(11),
PRIMARY KEY (NumProjet)
) ;
CREATE TABLE travaillersur (
NumEmp int(11) NOT NULL,
NumProjet int(11) NOT NULL,
NbreH int(11) NOT NULL,
PRIMARY KEY (NumEmp,NumProjet)
);
CREATE TABLE candidat(
NumDos int(11),
NomC varchar(20) ,
PrenomC varchar(20) ,
Poste varchar(20),
Adresse varchar(20) ,
Sexe varchar(10) ,
AgeC int(2),
NbrAnneeExp int(2) ,
DateD DATE ,
Accep boolean,
PRIMARY KEY (numDos)
);
ALTER TABLE dept ADD CONSTRAINT deptibfk1 FOREIGN KEY (NumDirecteur) REFERENCES emp (numEmp);
ALTER TABLE emp
ADD CONSTRAINT empibfk4 FOREIGN KEY (numDept) REFERENCES dept (NumDept),
ADD CONSTRAINT empibfk3 FOREIGN KEY (numChef) REFERENCES emp (numEmp);
ALTER TABLE projet ADD CONSTRAINT FKProjet FOREIGN KEY (NumDept) REFERENCES dept (NumDept);
ALTER TABLE travaillersur
ADD CONSTRAINT FKEmpt FOREIGN KEY (NumEmp) REFERENCES emp (numEmp),
ADD CONSTRAINT FKPrj FOREIGN KEY (NumProjet) REFERENCES projet (NumProjet);
INSERT INTO dept VALUES(100, 'Informatique', 'Charguia2', Null);
INSERT INTO dept VALUES(200, 'Industriel', 'Lac2', Null);
INSERT INTO dept VALUES(300, 'RH', 'Centre Ville', Null);
INSERT INTO dept VALUES(400, 'Production', 'Charguia2', Null);
INSERT INTO dept VALUES(500, 'Comptabilité', 'Cité Ennasr', Null);
INSERT INTO emp VALUES(10, 'Ben Youssef', 'Mehdi', 'PDG', 'Nasr2', 'M', 46, 3566, 2000, NULL, 100);
INSERT INTO emp VALUES(20, 'Ghedira', 'Olfa', 'DG', 'Le Bardo', 'F', 51, 2500, 1700, 10, 200);
INSERT INTO emp VALUES(30, 'Mabrouk', 'Nasr', 'Ingenieur', 'Mourouj', 'M', 46, 1300, 1000, 20, 300);
INSERT INTO emp VALUES(40, 'Bousseta', 'Kacem', 'Ingenieur', 'Tunis', 'M', 35, 1200, 500, 10, 400);
INSERT INTO emp VALUES(50, 'Yaakoubi', 'Jaafer', 'Cadre', 'Sfax', 'M', 51, 900, 460, 30, 400);
INSERT INTO emp VALUES(60, 'Gasmi', 'Ramzi', 'Comptable', 'Kef', 'M', 37, 1900, 230, NULL, NULL);
INSERT INTO emp VALUES(70, 'Beji', 'Achraf', 'Technicien', 'Gabes', 'M', 33, 1000, 0, Null,100);
UPDATE dept SET NumDirecteur = 10 WHERE NumDept =100;
UPDATE dept SET NumDirecteur = 20 WHERE NumDept =200;
UPDATE dept SET NumDirecteur = 30 WHERE NumDept =300;
UPDATE dept SET NumDirecteur = 40 WHERE NumDept =400;
INSERT INTO grade VALUES(1, 'A', 500, 1100);
INSERT INTO grade VALUES(2, 'B', 1200, 1900);
INSERT INTO grade VALUES(3, 'C', 2000, 7000);
INSERT INTO projet VALUES(1, 'Automobile', 'Borj Sedria', 400);
INSERT INTO projet VALUES(2, 'Securite', 'Charguia', 100);
INSERT INTO projet VALUES(3, 'Web', 'Charguia', 100);
INSERT INTO projet VALUES(4, 'VenteEnLigne', 'Hammamet', 200);
INSERT INTO projet VALUES(5, 'Phamarceutique', 'Hammamet', NULL);
INSERT INTO travaillersur VALUES(30, 1, 15);
INSERT INTO travaillersur VALUES(20, 2, 300);
INSERT INTO travaillersur VALUES(30, 3, 355);
INSERT INTO travaillersur VALUES(50, 1, 400);
INSERT INTO travaillersur VALUES(40, 4, 102);
INSERT INTO candidat VALUES(200, 'Cherif', 'Samir', 'Consultant', 'Charguia', 'M', 33,4,'2013-03-10', false);
INSERT INTO candidat VALUES(300, 'ElCherif', 'Souhail', 'Ouvrier', 'Sfax', 'M', 33,4,'2011-12-30', false);
INSERT INTO candidat VALUES(400, 'Smaoui', 'Ahmed', 'Ingenieur', 'Tunis', 'M', 30, 1, '2012-06-25', false);
INSERT INTO candidat VALUES(500, 'Dridi', 'Yosra', 'Ingenieur', 'La Marsa', 'F', 32, 5, '2012-09-03', false);
INSERT INTO candidat VALUES(600, 'Beji', 'Amina', 'Technicien', 'Sousse', 'F', 33, 2, '2011-02-11', false);
INSERT INTO candidat VALUES(700, 'Touil', 'Majdi', 'Comptable', 'Nabeur', 'M', 36, 3, '2012-12-30', false);
INSERT INTO candidat VALUES(800, 'ELdali', 'Alia', 'Ingenieur', 'Bizerte', 'F', 36, 1, '2012-08-08', false);
INSERT INTO candidat VALUES(900, 'Hamdi', 'Ossama', 'Ingenieur', 'charguia', 'M', 32, 5, '2013-01-01', false);
INSERT INTO candidat VALUES(1000, 'Othmani', 'Amal', 'Comptable', 'Nabeur', 'F', 37, 3, '2012-11-26', false);
SELECT *
FROM emp ;
SELECT *
FROM dept ;
SELECT *
FROM projet ;
SELECT p.NomProjet , d.Adresse
FROM projet p LEFT JOIN dept d ON p.NumDept=d.NumDept
WHERE p.Local="hammamet" ;
Telegram: DB developers channel