-- TP N° 4 SQL
 
CREATE DATABASE HimalayaDB;
USE HimalayaDB;
 
Create table Mountains(
id int auto_increment primary key,
name varchar(40),
height int,
country varchar(20));
 
create table Mountaineers(
id int auto_increment primary key,
name varchar(40),
nationality varchar(20),
experience int);
 
create table sherpas(
id int auto_increment primary key,
name varchar(40),
nationality varchar(20));
 
 
create table teams(
id int auto_increment primary key,
nationality varchar(20),
country varchar(20));
 
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));
 
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(20),
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 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');
 
-- question 1
select * from mountains;
 
-- question 2
 
select * from mountaineers where experience >20;
 
-- question 3
 
select * from mountains order by height desc;
 
-- question 4
alter table teams rename to ClimbingTeams;
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(*) from mountains;
 
-- question 8
 
select * from sherpas;
 
-- question 9
 
alter table ClimbingTeams rename column nationality to name;
select * from ClimbingTeams where name like '%Team%';
 
-- question 10
select * from expeditions where mountain_id=1;
 
-- question 11
 
select * from mountaineers;
update mountaineers set experience = 51 where id=1;
 
-- question 12
 
delete from expeditions where id=1;
select * from exploits;
select * from participation;
alter table exploits drop constraint exploits_ibfk_2;
alter table participation drop constraint participation_ibfk_2;
alter table exploits add constraint exploits_ibfk_2 foreign key (expedition_id) references expeditions(id) on delete set null;
alter table participation add constraint participation_ibfk_2 foreign key (expedition_id) references expeditions(id) on delete cascade;
 
 
-- question 13
 
insert into mountains (name, height, country) values ('Toubkal',4167, 'Morocco'),(' ighil Mgoun', 4073, 'Morocco'), ('Mont blanc', 5520, 'Swiss');
 
-- question 14
 
update ClimbingTeams set country ='Morocco' where id=1;
 
-- question 15
 
delete from mountaineers where id=1;
 
alter table exploits drop constraint exploits_ibfk_1;
alter table participation drop constraint participation_ibfk_1;
alter table exploits add constraint exploits_ibfk_1 foreign key (mountaineer_id) references mountaineers(id) on delete set null;
alter table participation add constraint participation_ibfk_1 foreign key (mountaineer_id) references mountaineers(id) on delete set null;
 
 
 
 

LS0gVFAgTsKwIDQgU1FMCgpDUkVBVEUgREFUQUJBU0UgSGltYWxheWFEQjsKVVNFIEhpbWFsYXlhREI7CgpDcmVhdGUgdGFibGUgTW91bnRhaW5zKAppZCBpbnQgYXV0b19pbmNyZW1lbnQgcHJpbWFyeSBrZXksCm5hbWUgdmFyY2hhcig0MCksCmhlaWdodCBpbnQsCmNvdW50cnkgdmFyY2hhcigyMCkpOwoKY3JlYXRlIHRhYmxlIE1vdW50YWluZWVycygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LApuYW1lIHZhcmNoYXIoNDApLApuYXRpb25hbGl0eSB2YXJjaGFyKDIwKSwKZXhwZXJpZW5jZSBpbnQpOwoKY3JlYXRlIHRhYmxlIHNoZXJwYXMoCmlkIGludCBhdXRvX2luY3JlbWVudCBwcmltYXJ5IGtleSwKbmFtZSB2YXJjaGFyKDQwKSwKbmF0aW9uYWxpdHkgdmFyY2hhcigyMCkpOwoKCmNyZWF0ZSB0YWJsZSB0ZWFtcygKaWQgaW50IGF1dG9faW5jcmVtZW50IHByaW1hcnkga2V5LApuYXRpb25hbGl0eSB2YXJjaGFyKDIwKSwKY291bnRyeSB2YXJjaGFyKDIwKSk7CgpjcmVhdGUgdGFibGUgZXhwZWRpdGlvbnMoCmlkIGludCBhdXRvX2luY3JlbWVudCBwcmltYXJ5IGtleSwKbW91bnRhaW5faWQgaW50LAp0ZWFtX2lkIGludCwKeWVhciBpbnQsCmZvcmVpZ24ga2V5IChtb3VudGFpbl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbnMoaWQpLApmb3JlaWduIGtleSAodGVhbV9pZCkgcmVmZXJlbmNlcyB0ZWFtcyhpZCkpOwoKY3JlYXRlIHRhYmxlIGV4cGxvaXRzKAppZCBpbnQgYXV0b19pbmNyZW1lbnQgcHJpbWFyeSBrZXksCm1vdW50YWluZWVyX2lkIGludCwKZXhwZWRpdGlvbl9pZCBpbnQsCnN1Y2Nlc3MgYm9vbGVhbiwKZm9yZWlnbiBrZXkgKG1vdW50YWluZWVyX2lkKSByZWZlcmVuY2VzIG1vdW50YWluZWVycyhpZCksCmZvcmVpZ24ga2V5IChleHBlZGl0aW9uX2lkKSByZWZlcmVuY2VzIGV4cGVkaXRpb25zKGlkKSk7CgpjcmVhdGUgdGFibGUgcGFydGljaXBhdGlvbigKbW91bnRhaW5lZXJfaWQgaW50LApleHBlZGl0aW9uX2lkIGludCwgCnJvbGUgdmFyY2hhcigyMCksCmZvcmVpZ24ga2V5IChtb3VudGFpbmVlcl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbmVlcnMoaWQpLApmb3JlaWduIGtleSAoZXhwZWRpdGlvbl9pZCkgcmVmZXJlbmNlcyBleHBlZGl0aW9ucyhpZCkpOwoKCklOU0VSVCBJTlRPIE1vdW50YWlucyBWQUxVRVMKKDEsJ0V2ZXJlc3QnLDg4NDgsJ05lcGFsJyksCigyLCdLMicsODYxMSwnUGFraXN0YW4nKSwKKDMsJ0thbmdjaGVuanVuZ2EnLDg1ODYsJ05lcGFsL0luZGlhJyksCig0LCdMaG90c2UnLDg1MTYsJ05lcGFsJyksCig1LCdNYWthbHUnLDg0ODUsJ05lcGFsJyksCig2LCdDaG9PeXUnLDgxODgsJ05lcGFsJyksCig3LCdEaGF1bGFnaXJpJyw4MTY3LCdOZXBhbCcpLAooOCwnTWFuYXNsdScsODE2MywnTmVwYWwnKSwKKDksJ05hbmdhUGFyYmF0Jyw4MTI2LCdQYWtpc3RhbicpLAooMTAsJ0FubmFwdXJuYScsODA5MSwnTmVwYWwnKTsKCklOU0VSVCBJTlRPIE1vdW50YWluZWVycyBWQUxVRVMKKDEsJ01lc3NuZXInCiwKJ0l0YWx5Jyw1MCksCigyLCdIaWxsYXJ5JwosCidOWicsMzApLAooMywnVGVuemluZycKLAonTmVwYWwnLDI1KSwKKDQsJ0t1a3VjemthJwosCidQb2xhbmQnLDQwKSwKKDUsJ01vcm8nCiwKJ0l0YWx5JywyMCksCig2LCdTdGVjaycKLAonU3dpc3MnLDI1KSwKKDcsJ1B1cmphJwosCidOZXBhbCcsMTUpLAooOCwnQm9uaW5ndG9uJwosCidVSycsMzUpLAooOSwnVXJ1YmtvJwosCidLYXpha2gnLDMwKSwKKDEwLCdIaW5rZXMnCiwKJ1VLJywyOCk7CgpJTlNFUlQgSU5UTyBTaGVycGFzIFZBTFVFUwooMSwnS2FtaSBSaXRhJywnTmVwYWwnKSwKKDIsJ0FwYScsJ05lcGFsJyksCigzLCdQaHVyYmEnLCdOZXBhbCcpLAooNCwnQW5nIFJpdGEnLCdOZXBhbCcpLAooNSwnUGFzYW5nJywnTmVwYWwnKSwKKDYsJ0xha3BhJywnTmVwYWwnKSwKKDcsJ01pbmdtYScsJ05lcGFsJyksCig4LCdEYXdhJywnTmVwYWwnKSwKKDksJ1BlbWJhJywnTmVwYWwnKSwKKDEwLCdEb3JqZScsJ05lcGFsJyk7CgoKCklOU0VSVCBJTlRPIFRlYW1zIFZBTFVFUwooMSwnSXRhbGlhbiBUZWFtJywnSXRhbHknKSwKKDIsJ0JyaXRpc2ggVGVhbScsJ1VLJyksCigzLCdOZXBhbCBUZWFtJywnTmVwYWwnKSwKKDQsJ1BvbGlzaCBUZWFtJywgJ1BvbGFuZCcpLAooNSwnU3dpc3MgVGVhbScsJ1N3aXNzJyksCig2LCdGcmVuY2ggVGVhbScsJ0ZyYW5jZScpLAooNywnR2VybWFuIFRlYW0nLCdHZXJtYW55JyksCig4LCdTcGFuaXNoIFRlYW0nLCdTcGFpbicpLAooOSwnVVMgVGVhbScsJ1VTQScpLAooMTAsJ0phcGFuIFRlYW0nLCdKYXBhbicpOwoKSU5TRVJUIElOVE8gRXhwZWRpdGlvbnMgVkFMVUVTCigxLDEsMywxOTUzKSwKKDIsMiw0LDE5ODYpLAooMywzLDQsMTk4MCksCig0LDQsMSwxOTcwKSwKKDUsNSwyLDE5ODUpLAooNiw2LDUsMjAwMCksCig3LDcsNiwxOTkwKSwKKDgsOCw3LDE5OTUpLAooOSw5LDgsMjAwNSksCigxMCwxMCw5LDIwMTApOwoKCklOU0VSVCBJTlRPIEV4cGxvaXRzIFZBTFVFUwooMSwyLDEsVFJVRSksCigyLDMsMSxUUlVFKSwKKDMsMSwyLFRSVUUpLAooNCw0LDMsVFJVRSksCig1LDUsNSwgVFJVRSApLCAoNiw2LDYsIFRSVUUgKSwgKDcsNyw5LCBUUlVFICksICg4LDgsNCwgVFJVRSApLCAoOSw5LDIsIFRSVUUgKSwgKDEwLDEwLDEwLCBUUlVFICk7CgpJTlNFUlQgSU5UTyBQYXJ0aWNpcGF0aW9uIFZBTFVFUwooMSwyLCdMZWFkZXInKSwKKDIsMSwnTGVhZGVyJyksCigzLDEsJ0d1aWRlJyksCig0LDMsJ0xlYWRlcicpLAooNSw1LCdDbGltYmVyJyksCig2LDYsJ0NsaW1iZXInKSwKKDcsOSwnTGVhZGVyJyksCig4LDQsJ0NsaW1iZXInKSwKKDksMiwnQ2xpbWJlcicpLAooMTAsMTAsJ0xlYWRlcicpOwoKLS0gcXVlc3Rpb24gMQpzZWxlY3QgKiBmcm9tIG1vdW50YWluczsKCi0tIHF1ZXN0aW9uIDIKCnNlbGVjdCAqIGZyb20gbW91bnRhaW5lZXJzIHdoZXJlIGV4cGVyaWVuY2UgPjIwOwoKLS0gcXVlc3Rpb24gMwoKc2VsZWN0ICogZnJvbSBtb3VudGFpbnMgb3JkZXIgYnkgaGVpZ2h0IGRlc2M7CgotLSBxdWVzdGlvbiA0CmFsdGVyIHRhYmxlIHRlYW1zIHJlbmFtZSB0byBDbGltYmluZ1RlYW1zOwpzZWxlY3QgKiBmcm9tIENsaW1iaW5ndGVhbXMgIG9yZGVyIGJ5IGNvdW50cnk7CgotLSBxdWVzdGlvbiA1CgpzZWxlY3QgKiBmcm9tIGV4cGVkaXRpb25zIHdoZXJlIHllYXIgPjE5OTA7CgotLSBxdWVzdGlvbiA2CgpzZWxlY3QgKiBmcm9tIG1vdW50YWluZWVycyB3aGVyZSBuYXRpb25hbGl0eT0nSXRhbHknOwoKLS0gcXVlc3Rpb24gNwoKc2VsZWN0IGNvdW50KCopIGZyb20gbW91bnRhaW5zOwoKLS0gcXVlc3Rpb24gOAoKc2VsZWN0ICogZnJvbSBzaGVycGFzOwoKLS0gcXVlc3Rpb24gOQoKYWx0ZXIgdGFibGUgQ2xpbWJpbmdUZWFtcyByZW5hbWUgY29sdW1uIG5hdGlvbmFsaXR5IHRvIG5hbWU7CnNlbGVjdCAqIGZyb20gQ2xpbWJpbmdUZWFtcyB3aGVyZSBuYW1lIGxpa2UgJyVUZWFtJSc7CgotLSBxdWVzdGlvbiAxMApzZWxlY3QgKiBmcm9tIGV4cGVkaXRpb25zIHdoZXJlIG1vdW50YWluX2lkPTE7CgotLSBxdWVzdGlvbiAxMQoKc2VsZWN0ICogZnJvbSBtb3VudGFpbmVlcnM7CnVwZGF0ZSBtb3VudGFpbmVlcnMgc2V0IGV4cGVyaWVuY2UgPSA1MSB3aGVyZSBpZD0xOwoKLS0gcXVlc3Rpb24gMTIKCmRlbGV0ZSBmcm9tIGV4cGVkaXRpb25zIHdoZXJlIGlkPTE7CnNlbGVjdCAqIGZyb20gZXhwbG9pdHM7CnNlbGVjdCAqIGZyb20gcGFydGljaXBhdGlvbjsKYWx0ZXIgdGFibGUgZXhwbG9pdHMgZHJvcCBjb25zdHJhaW50IGV4cGxvaXRzX2liZmtfMjsKYWx0ZXIgdGFibGUgcGFydGljaXBhdGlvbiBkcm9wIGNvbnN0cmFpbnQgcGFydGljaXBhdGlvbl9pYmZrXzI7CmFsdGVyIHRhYmxlIGV4cGxvaXRzIGFkZCBjb25zdHJhaW50IGV4cGxvaXRzX2liZmtfMiBmb3JlaWduIGtleSAoZXhwZWRpdGlvbl9pZCkgcmVmZXJlbmNlcyBleHBlZGl0aW9ucyhpZCkgb24gZGVsZXRlIHNldCBudWxsOwphbHRlciB0YWJsZSBwYXJ0aWNpcGF0aW9uIGFkZCBjb25zdHJhaW50IHBhcnRpY2lwYXRpb25faWJma18yIGZvcmVpZ24ga2V5IChleHBlZGl0aW9uX2lkKSByZWZlcmVuY2VzIGV4cGVkaXRpb25zKGlkKSBvbiBkZWxldGUgY2FzY2FkZTsKCgotLSBxdWVzdGlvbiAxMwoKaW5zZXJ0IGludG8gbW91bnRhaW5zIChuYW1lLCBoZWlnaHQsIGNvdW50cnkpIHZhbHVlcyAoJ1RvdWJrYWwnLDQxNjcsICdNb3JvY2NvJyksKCcgaWdoaWwgTWdvdW4nLCA0MDczLCAnTW9yb2NjbycpLCAoJ01vbnQgYmxhbmMnLCA1NTIwLCAnU3dpc3MnKTsKCi0tIHF1ZXN0aW9uIDE0Cgp1cGRhdGUgQ2xpbWJpbmdUZWFtcyBzZXQgY291bnRyeSA9J01vcm9jY28nIHdoZXJlIGlkPTE7CgotLSBxdWVzdGlvbiAxNQoKZGVsZXRlIGZyb20gbW91bnRhaW5lZXJzIHdoZXJlIGlkPTE7CgphbHRlciB0YWJsZSBleHBsb2l0cyBkcm9wIGNvbnN0cmFpbnQgZXhwbG9pdHNfaWJma18xOwphbHRlciB0YWJsZSBwYXJ0aWNpcGF0aW9uIGRyb3AgY29uc3RyYWludCBwYXJ0aWNpcGF0aW9uX2liZmtfMTsKYWx0ZXIgdGFibGUgZXhwbG9pdHMgYWRkIGNvbnN0cmFpbnQgZXhwbG9pdHNfaWJma18xIGZvcmVpZ24ga2V5IChtb3VudGFpbmVlcl9pZCkgcmVmZXJlbmNlcyBtb3VudGFpbmVlcnMoaWQpIG9uIGRlbGV0ZSBzZXQgbnVsbDsKYWx0ZXIgdGFibGUgcGFydGljaXBhdGlvbiBhZGQgY29uc3RyYWludCBwYXJ0aWNpcGF0aW9uX2liZmtfMSBmb3JlaWduIGtleSAobW91bnRhaW5lZXJfaWQpIHJlZmVyZW5jZXMgbW91bnRhaW5lZXJzKGlkKSBvbiBkZWxldGUgc2V0IG51bGw7CgoKCg==