create table mountains(
id int auto_increment primary key,
name varchar (50),
height int,
country varchar(30)
);
create table mountainers(
id int auto_increment primary key,
nationality varchar (50),
experience int
);
alter table mountaineers add column name varchar(30);
create table sherpas(
id int auto_increment primary key,
name varchar (50),
nationality varchar(30)
);
 
create table teams(
id int auto_increment primary key,
name varchar (50),
country varchar(30)
);
create table expeditions(
id int auto_increment primary key,
mountain_id int,
team_id int,
year int,
foreign key (mountain_id) references mountains(id),
foreign key (team_id) references teams(id)
);
 
alter table mountainers rename to mountaineers;
create table exploits(
id int auto_increment primary key,
mountaineer_id int,
expedition_id int,
success boolean,
foreign key (mountaineer_id) references mountaineers(id),
foreign key (expedition_id) references expeditions(id)
);
create table participation(
mountaineer_id int,
expedition_id int,
role varchar(30),
foreign key (mountaineer_id) references mountaineers(id),
foreign key (expedition_id) references expeditions(id)
);
 
 
INSERT INTO Mountains VALUES
(1,'Everest',8848,'Nepal'),
(2,'K2',8611,'Pakistan'),
(3,'Kangchenjunga',8586,'Nepal/India'),
(4,'Lhotse',8516,'Nepal'),
(5,'Makalu',8485,'Nepal'),
(6,'ChoOyu',8188,'Nepal'),
(7,'Dhaulagiri',8167,'Nepal'),
(8,'Manaslu',8163,'Nepal'),
(9,'NangaParbat',8126,'Pakistan'),
(10,'Annapurna',8091,'Nepal');
 
INSERT INTO Mountaineers(id, name,nationality, experience) VALUES
(1,'Messner','Italy',50),
(2,'Hillary','NZ',30),
(3,'Tenzing','Nepal',25),
(4,'Kukuczka','Poland',40),
(5,'Moro','Italy',20),
(6,'Steck','Swiss',25),
(7,'Purja','Nepal',15),
(8,'Bonington','UK',35),
(9,'Urubko','Kazakh',30),
(10,'Hinkes','UK',28);
 
INSERT INTO Sherpas VALUES
(1,'Kami Rita','Nepal'),
(2,'Apa','Nepal'),
(3,'Phurba','Nepal'),
(4,'Ang Rita','Nepal'),
(5,'Pasang','Nepal'),
(6,'Lakpa','Nepal'),
(7,'Mingma','Nepal'),
(8,'Dawa','Nepal'),
(9,'Pemba','Nepal'),
(10,'Dorje','Nepal');
 
 
 
INSERT INTO Teams VALUES
(1,'Italian Team','Italy'),
(2,'British Team','UK'),
(3,'Nepal Team','Nepal'),
(4,'Polish Team', 'Poland'),
(5,'Swiss Team','Swiss'),
(6,'French Team','France'),
(7,'German Team','Germany'),
(8,'Spanish Team','Spain'),
(9,'US Team','USA'),
(10,'Japan Team','Japan');
 
INSERT INTO Expeditions VALUES
(1,1,3,1953),
(2,2,4,1986),
(3,3,4,1980),
(4,4,1,1970),
(5,5,2,1985),
(6,6,5,2000),
(7,7,6,1990),
(8,8,7,1995),
(9,9,8,2005),
(10,10,9,2010);
 
 
INSERT INTO Exploits VALUES
(1,2,1,TRUE),
(2,3,1,TRUE),
(3,1,2,TRUE),
(4,4,3,TRUE),
(5,5,5, TRUE ), (6,6,6, TRUE ), (7,7,9, TRUE ), (8,8,4, TRUE ), (9,9,2, TRUE ), (10,10,10, TRUE );
 
INSERT INTO Participation VALUES
(1,2,'Leader'),
(2,1,'Leader'),
(3,1,'Guide'),
(4,3,'Leader'),
(5,5,'Climber'),
(6,6,'Climber'),
(7,9,'Leader'),
(8,4,'Climber'),
(9,2,'Climber'),
(10,10,'Leader');
 
alter table mountaineers add column age int;
alter table teams rename to ClimbingTeams;
alter table sherpas add column experience int;
 
-- question1
 
select * from mountains;
 
-- question2
 
select * from mountaineers where experience>20;
 
-- question 3
select * from mountains order by height desc;
 
-- question 4
 
select * from Climbingteams order by country;
 
 
-- question 5
 
select * from expeditions where year>1990;
 
-- question 6
select * from mountaineers where nationality='Italy';
 
-- question 7
select count(*) as "nombre de sommets" from mountains;
 
-- question 8
 
select * from sherpas;
 
-- question 9
select * from ClimbingTeams where name like '%Team%';
 
-- question 10
 
select * from expeditions where mountain_id=1;
 
 
-- question 11
 
update mountaineers set experience=40 where id=2;
 
-- question 12
 
delete from expedition where id=1;
 
-- pour forcer la suppression
alter table expeditions drop constraint `expeditions_ibfk_1`;
alter table expeditions drop constraint `expeditions_ibfk_2`;
 
 
 
 
-- question 13
 
insert into mountains (name, height,country) values('Toubkal',4196,'MOROCCO');
select * from mountains;
 
-- question 14
 
update climbingteams set country='Maghrib' where id=1;
 
-- question 15
 
delete from mountaineers where id=10;
 
 
 
 
 
 
 
 

Y3JlYXRlIHRhYmxlIG1vdW50YWlucygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LApuYW1lIHZhcmNoYXIgKDUwKSwKaGVpZ2h0IGludCwKY291bnRyeSB2YXJjaGFyKDMwKQopOwpjcmVhdGUgdGFibGUgbW91bnRhaW5lcnMoCmlkIGludCBhdXRvX2luY3JlbWVudCBwcmltYXJ5IGtleSwKbmF0aW9uYWxpdHkgdmFyY2hhciAoNTApLApleHBlcmllbmNlIGludAopOwphbHRlciB0YWJsZSBtb3VudGFpbmVlcnMgYWRkIGNvbHVtbiBuYW1lIHZhcmNoYXIoMzApOwpjcmVhdGUgdGFibGUgc2hlcnBhcygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LApuYW1lIHZhcmNoYXIgKDUwKSwKbmF0aW9uYWxpdHkgdmFyY2hhcigzMCkKKTsKCmNyZWF0ZSB0YWJsZSB0ZWFtcygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LApuYW1lIHZhcmNoYXIgKDUwKSwKY291bnRyeSB2YXJjaGFyKDMwKQopOwpjcmVhdGUgdGFibGUgZXhwZWRpdGlvbnMoCmlkIGludCBhdXRvX2luY3JlbWVudCBwcmltYXJ5IGtleSwKbW91bnRhaW5faWQgaW50LAp0ZWFtX2lkIGludCwKeWVhciBpbnQsCmZvcmVpZ24ga2V5IChtb3VudGFpbl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbnMoaWQpLApmb3JlaWduIGtleSAodGVhbV9pZCkgcmVmZXJlbmNlcyB0ZWFtcyhpZCkKKTsKCmFsdGVyIHRhYmxlIG1vdW50YWluZXJzIHJlbmFtZSB0byBtb3VudGFpbmVlcnM7CmNyZWF0ZSB0YWJsZSBleHBsb2l0cygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LAptb3VudGFpbmVlcl9pZCBpbnQsCmV4cGVkaXRpb25faWQgaW50LApzdWNjZXNzIGJvb2xlYW4sCmZvcmVpZ24ga2V5IChtb3VudGFpbmVlcl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbmVlcnMoaWQpLApmb3JlaWduIGtleSAoZXhwZWRpdGlvbl9pZCkgcmVmZXJlbmNlcyBleHBlZGl0aW9ucyhpZCkKKTsKY3JlYXRlIHRhYmxlIHBhcnRpY2lwYXRpb24oCm1vdW50YWluZWVyX2lkIGludCwKZXhwZWRpdGlvbl9pZCBpbnQsCnJvbGUgdmFyY2hhcigzMCksCmZvcmVpZ24ga2V5IChtb3VudGFpbmVlcl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbmVlcnMoaWQpLApmb3JlaWduIGtleSAoZXhwZWRpdGlvbl9pZCkgcmVmZXJlbmNlcyBleHBlZGl0aW9ucyhpZCkKKTsKCgpJTlNFUlQgSU5UTyBNb3VudGFpbnMgVkFMVUVTCigxLCdFdmVyZXN0Jyw4ODQ4LCdOZXBhbCcpLAooMiwnSzInLDg2MTEsJ1Bha2lzdGFuJyksCigzLCdLYW5nY2hlbmp1bmdhJyw4NTg2LCdOZXBhbC9JbmRpYScpLAooNCwnTGhvdHNlJyw4NTE2LCdOZXBhbCcpLAooNSwnTWFrYWx1Jyw4NDg1LCdOZXBhbCcpLAooNiwnQ2hvT3l1Jyw4MTg4LCdOZXBhbCcpLAooNywnRGhhdWxhZ2lyaScsODE2NywnTmVwYWwnKSwKKDgsJ01hbmFzbHUnLDgxNjMsJ05lcGFsJyksCig5LCdOYW5nYVBhcmJhdCcsODEyNiwnUGFraXN0YW4nKSwKKDEwLCdBbm5hcHVybmEnLDgwOTEsJ05lcGFsJyk7CgpJTlNFUlQgSU5UTyBNb3VudGFpbmVlcnMoaWQsIG5hbWUsbmF0aW9uYWxpdHksIGV4cGVyaWVuY2UpIFZBTFVFUwooMSwnTWVzc25lcicsJ0l0YWx5Jyw1MCksCigyLCdIaWxsYXJ5JywnTlonLDMwKSwKKDMsJ1RlbnppbmcnLCdOZXBhbCcsMjUpLAooNCwnS3VrdWN6a2EnLCdQb2xhbmQnLDQwKSwKKDUsJ01vcm8nLCdJdGFseScsMjApLAooNiwnU3RlY2snLCdTd2lzcycsMjUpLAooNywnUHVyamEnLCdOZXBhbCcsMTUpLAooOCwnQm9uaW5ndG9uJywnVUsnLDM1KSwKKDksJ1VydWJrbycsJ0themFraCcsMzApLAooMTAsJ0hpbmtlcycsJ1VLJywyOCk7CgpJTlNFUlQgSU5UTyBTaGVycGFzIFZBTFVFUwooMSwnS2FtaSBSaXRhJywnTmVwYWwnKSwKKDIsJ0FwYScsJ05lcGFsJyksCigzLCdQaHVyYmEnLCdOZXBhbCcpLAooNCwnQW5nIFJpdGEnLCdOZXBhbCcpLAooNSwnUGFzYW5nJywnTmVwYWwnKSwKKDYsJ0xha3BhJywnTmVwYWwnKSwKKDcsJ01pbmdtYScsJ05lcGFsJyksCig4LCdEYXdhJywnTmVwYWwnKSwKKDksJ1BlbWJhJywnTmVwYWwnKSwKKDEwLCdEb3JqZScsJ05lcGFsJyk7CgoKCklOU0VSVCBJTlRPIFRlYW1zIFZBTFVFUwooMSwnSXRhbGlhbiBUZWFtJywnSXRhbHknKSwKKDIsJ0JyaXRpc2ggVGVhbScsJ1VLJyksCigzLCdOZXBhbCBUZWFtJywnTmVwYWwnKSwKKDQsJ1BvbGlzaCBUZWFtJywgJ1BvbGFuZCcpLAooNSwnU3dpc3MgVGVhbScsJ1N3aXNzJyksCig2LCdGcmVuY2ggVGVhbScsJ0ZyYW5jZScpLAooNywnR2VybWFuIFRlYW0nLCdHZXJtYW55JyksCig4LCdTcGFuaXNoIFRlYW0nLCdTcGFpbicpLAooOSwnVVMgVGVhbScsJ1VTQScpLAooMTAsJ0phcGFuIFRlYW0nLCdKYXBhbicpOwoKSU5TRVJUIElOVE8gRXhwZWRpdGlvbnMgVkFMVUVTCigxLDEsMywxOTUzKSwKKDIsMiw0LDE5ODYpLAooMywzLDQsMTk4MCksCig0LDQsMSwxOTcwKSwKKDUsNSwyLDE5ODUpLAooNiw2LDUsMjAwMCksCig3LDcsNiwxOTkwKSwKKDgsOCw3LDE5OTUpLAooOSw5LDgsMjAwNSksCigxMCwxMCw5LDIwMTApOwoKCklOU0VSVCBJTlRPIEV4cGxvaXRzIFZBTFVFUwooMSwyLDEsVFJVRSksCigyLDMsMSxUUlVFKSwKKDMsMSwyLFRSVUUpLAooNCw0LDMsVFJVRSksCig1LDUsNSwgVFJVRSApLCAoNiw2LDYsIFRSVUUgKSwgKDcsNyw5LCBUUlVFICksICg4LDgsNCwgVFJVRSApLCAoOSw5LDIsIFRSVUUgKSwgKDEwLDEwLDEwLCBUUlVFICk7CgpJTlNFUlQgSU5UTyBQYXJ0aWNpcGF0aW9uIFZBTFVFUwooMSwyLCdMZWFkZXInKSwKKDIsMSwnTGVhZGVyJyksCigzLDEsJ0d1aWRlJyksCig0LDMsJ0xlYWRlcicpLAooNSw1LCdDbGltYmVyJyksCig2LDYsJ0NsaW1iZXInKSwKKDcsOSwnTGVhZGVyJyksCig4LDQsJ0NsaW1iZXInKSwKKDksMiwnQ2xpbWJlcicpLAooMTAsMTAsJ0xlYWRlcicpOwoKYWx0ZXIgdGFibGUgbW91bnRhaW5lZXJzIGFkZCBjb2x1bW4gYWdlIGludDsKYWx0ZXIgdGFibGUgdGVhbXMgcmVuYW1lIHRvIENsaW1iaW5nVGVhbXM7CmFsdGVyIHRhYmxlIHNoZXJwYXMgYWRkIGNvbHVtbiBleHBlcmllbmNlIGludDsKCi0tIHF1ZXN0aW9uMQoKc2VsZWN0ICogZnJvbSBtb3VudGFpbnM7CgotLSBxdWVzdGlvbjIKCnNlbGVjdCAqIGZyb20gbW91bnRhaW5lZXJzIHdoZXJlIGV4cGVyaWVuY2U+MjA7CgotLSBxdWVzdGlvbiAzCnNlbGVjdCAqIGZyb20gbW91bnRhaW5zIG9yZGVyIGJ5IGhlaWdodCBkZXNjOwoKLS0gcXVlc3Rpb24gNAoKc2VsZWN0ICogZnJvbSBDbGltYmluZ3RlYW1zIG9yZGVyIGJ5IGNvdW50cnk7CgoKLS0gcXVlc3Rpb24gNQoKc2VsZWN0ICogZnJvbSBleHBlZGl0aW9ucyB3aGVyZSB5ZWFyPjE5OTA7CgotLSBxdWVzdGlvbiA2CnNlbGVjdCAqIGZyb20gbW91bnRhaW5lZXJzIHdoZXJlIG5hdGlvbmFsaXR5PSdJdGFseSc7CgotLSBxdWVzdGlvbiA3CnNlbGVjdCBjb3VudCgqKSBhcyAibm9tYnJlIGRlIHNvbW1ldHMiIGZyb20gbW91bnRhaW5zOwoKLS0gcXVlc3Rpb24gOAoKc2VsZWN0ICogZnJvbSBzaGVycGFzOwoKLS0gcXVlc3Rpb24gOQpzZWxlY3QgKiBmcm9tIENsaW1iaW5nVGVhbXMgd2hlcmUgbmFtZSBsaWtlICclVGVhbSUnOwoKLS0gcXVlc3Rpb24gMTAKCnNlbGVjdCAqIGZyb20gZXhwZWRpdGlvbnMgd2hlcmUgbW91bnRhaW5faWQ9MTsKCgotLSBxdWVzdGlvbiAxMQoKdXBkYXRlIG1vdW50YWluZWVycyBzZXQgZXhwZXJpZW5jZT00MCB3aGVyZSBpZD0yOwoKLS0gcXVlc3Rpb24gMTIKCmRlbGV0ZSBmcm9tIGV4cGVkaXRpb24gd2hlcmUgaWQ9MTsKCi0tIHBvdXIgZm9yY2VyIGxhIHN1cHByZXNzaW9uCmFsdGVyIHRhYmxlIGV4cGVkaXRpb25zIGRyb3AgY29uc3RyYWludCBgZXhwZWRpdGlvbnNfaWJma18xYDsKYWx0ZXIgdGFibGUgZXhwZWRpdGlvbnMgZHJvcCBjb25zdHJhaW50IGBleHBlZGl0aW9uc19pYmZrXzJgOwoKCgoKLS0gcXVlc3Rpb24gMTMKCmluc2VydCBpbnRvIG1vdW50YWlucyAobmFtZSwgaGVpZ2h0LGNvdW50cnkpIHZhbHVlcygnVG91YmthbCcsNDE5NiwnTU9ST0NDTycpOwpzZWxlY3QgKiBmcm9tIG1vdW50YWluczsKCi0tIHF1ZXN0aW9uIDE0Cgp1cGRhdGUgY2xpbWJpbmd0ZWFtcyBzZXQgY291bnRyeT0nTWFnaHJpYicgd2hlcmUgaWQ9MTsKCi0tIHF1ZXN0aW9uIDE1CgpkZWxldGUgZnJvbSBtb3VudGFpbmVlcnMgd2hlcmUgaWQ9MTA7CgoKCgoKCgo=