1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
|
create table element (E_id int, E_symbole char(3), E_nom char(30), E_poids int)
insert into element values (4,'Br','Brome' ,10000000)
insert into element values (1,'C','Carbone' ,1000000)
insert into element values (2,'Cl','Chlore' ,100000)
insert into element values (5,'F','Fluor' ,10000)
insert into element values (6,'H','Hydrogene' ,1000)
insert into element values (3,'N','Azote' ,100)
insert into element values (7,'O','Oxygene' ,10)
create table formule (F_id int, F_Formule char(30), F_Nom char (200))
insert into formule values (1,'C','Formule 1')
insert into formule values (2,'CClN4','Formule 2')
insert into formule values (3,'C4Cl2','Formule 3')
insert into formule values (4,'C3ClN2','Formule 4')
insert into formule values (5,'C2Cl3N2','Formule 5')
insert into formule values (6,'C2Cl3N4','Formule 6')
create table contenir (C_idFormule int, C_IdElement int, C_NbAtomes int)
insert into contenir values (1,1,1)
insert into contenir values (2,1,1)
insert into contenir values (2,2,1)
insert into contenir values (2,3,4)
insert into contenir values (3,1,4)
insert into contenir values (3,2,2)
insert into contenir values (4,1,3)
insert into contenir values (4,2,1)
insert into contenir values (4,2,3)
insert into contenir values (5,1,2)
insert into contenir values (5,2,3)
insert into contenir values (5,3,2)
insert into contenir values (6,1,2)
insert into contenir values (6,2,3)
insert into contenir values (6,3,4)
select a.F_Formule from formule a inner join contenir b on (a.F_id=b.C_idFormule)
inner join element c on (c.E_id = b.C_IdElement)
Group by a.F_Nom
order by sum(b.C_NbAtomes*c.E_poids) |
Partager