CREATE TABLE t1 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1');
INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2');
 
CREATE TABLE t2 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t2 (key, name) VALUES ('t2_key1', 't2_name1');
INSERT INTO t2 (key, name) VALUES ('t2_key2', 't2_name2');
 
CREATE TABLE t3 (
key TEXT PRIMARY KEY NOT NULL,
name TEXT NOT NULL UNIQUE
);
INSERT INTO t3 (key, name) VALUES ('t3_key1', 't3_name1');
INSERT INTO t3 (key, name) VALUES ('t3_key2', 't3_name2');
 
CREATE TABLE t4 (
year TEXT NOT NULL,
t1_key TEXT NOT NULL,
t3_key TEXT NOT NULL,
percent INTEGER NOT NULL,
UNIQUE(year, t1_key, t3_key),
FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE,
FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50);
INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100);
 
CREATE TABLE t5 (
no INTEGER PRIMARY KEY AUTOINCREMENT, 
date TEXT NOT NULL,
t2_key TEXT NOT NULL,
t3_key TEXT NOT NULL,
amount INTEGER NOT NULL,
FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE,
FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE
);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-01-01', 't2_key1', 't3_key1', 3000);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2011-02-02', 't2_key1', 't3_key2', 2000);
INSERT INTO t5 (date, t2_key, t3_key, amount) VALUES ('2012-01-01', 't2_key1', 't3_key1', 5000);
 

Q1JFQVRFIFRBQkxFIHQxICgKa2V5IFRFWFQgUFJJTUFSWSBLRVkgTk9UIE5VTEwsCm5hbWUgVEVYVCBOT1QgTlVMTCBVTklRVUUKKTsKSU5TRVJUIElOVE8gdDEgKGtleSwgbmFtZSkgVkFMVUVTICgndDFfa2V5MScsICd0MV9uYW1lMScpOwpJTlNFUlQgSU5UTyB0MSAoa2V5LCBuYW1lKSBWQUxVRVMgKCd0MV9rZXkyJywgJ3QxX25hbWUyJyk7CgpDUkVBVEUgVEFCTEUgdDIgKAprZXkgVEVYVCBQUklNQVJZIEtFWSBOT1QgTlVMTCwKbmFtZSBURVhUIE5PVCBOVUxMIFVOSVFVRQopOwpJTlNFUlQgSU5UTyB0MiAoa2V5LCBuYW1lKSBWQUxVRVMgKCd0Ml9rZXkxJywgJ3QyX25hbWUxJyk7CklOU0VSVCBJTlRPIHQyIChrZXksIG5hbWUpIFZBTFVFUyAoJ3QyX2tleTInLCAndDJfbmFtZTInKTsKCkNSRUFURSBUQUJMRSB0MyAoCmtleSBURVhUIFBSSU1BUlkgS0VZIE5PVCBOVUxMLApuYW1lIFRFWFQgTk9UIE5VTEwgVU5JUVVFCik7CklOU0VSVCBJTlRPIHQzIChrZXksIG5hbWUpIFZBTFVFUyAoJ3QzX2tleTEnLCAndDNfbmFtZTEnKTsKSU5TRVJUIElOVE8gdDMgKGtleSwgbmFtZSkgVkFMVUVTICgndDNfa2V5MicsICd0M19uYW1lMicpOwoKQ1JFQVRFIFRBQkxFIHQ0ICgKeWVhciBURVhUIE5PVCBOVUxMLAp0MV9rZXkgVEVYVCBOT1QgTlVMTCwKdDNfa2V5IFRFWFQgTk9UIE5VTEwsCnBlcmNlbnQgSU5URUdFUiBOT1QgTlVMTCwKVU5JUVVFKHllYXIsIHQxX2tleSwgdDNfa2V5KSwKRk9SRUlHTiBLRVkodDFfa2V5KSBSRUZFUkVOQ0VTIHQxKGtleSkgT04gREVMRVRFIENBU0NBREUsCkZPUkVJR04gS0VZKHQzX2tleSkgUkVGRVJFTkNFUyB0MyhrZXkpIE9OIERFTEVURSBDQVNDQURFCik7CklOU0VSVCBJTlRPIHQ0ICh5ZWFyLCB0MV9rZXksIHQzX2tleSwgcGVyY2VudCkgVkFMVUVTICgnMjAxMScsICd0MV9rZXkxJywgJ3QzX2tleTEnLCAxMCk7CklOU0VSVCBJTlRPIHQ0ICh5ZWFyLCB0MV9rZXksIHQzX2tleSwgcGVyY2VudCkgVkFMVUVTICgnMjAxMicsICd0MV9rZXkyJywgJ3QzX2tleTInLCA1MCk7CklOU0VSVCBJTlRPIHQ0ICh5ZWFyLCB0MV9rZXksIHQzX2tleSwgcGVyY2VudCkgVkFMVUVTICgnMjAxMicsICd0MV9rZXkyJywgJ3QzX2tleTEnLCAxMDApOwoKQ1JFQVRFIFRBQkxFIHQ1ICgKbm8gSU5URUdFUiBQUklNQVJZIEtFWSBBVVRPSU5DUkVNRU5ULCAKZGF0ZSBURVhUIE5PVCBOVUxMLAp0Ml9rZXkgVEVYVCBOT1QgTlVMTCwKdDNfa2V5IFRFWFQgTk9UIE5VTEwsCmFtb3VudCBJTlRFR0VSIE5PVCBOVUxMLApGT1JFSUdOIEtFWSh0Ml9rZXkpIFJFRkVSRU5DRVMgdDIoa2V5KSBPTiBERUxFVEUgQ0FTQ0FERSwKRk9SRUlHTiBLRVkodDNfa2V5KSBSRUZFUkVOQ0VTIHQzKGtleSkgT04gREVMRVRFIENBU0NBREUKKTsKSU5TRVJUIElOVE8gdDUgKGRhdGUsIHQyX2tleSwgdDNfa2V5LCBhbW91bnQpIFZBTFVFUyAoJzIwMTEtMDEtMDEnLCAndDJfa2V5MScsICd0M19rZXkxJywgMzAwMCk7CklOU0VSVCBJTlRPIHQ1IChkYXRlLCB0Ml9rZXksIHQzX2tleSwgYW1vdW50KSBWQUxVRVMgKCcyMDExLTAyLTAyJywgJ3QyX2tleTEnLCAndDNfa2V5MicsIDIwMDApOwpJTlNFUlQgSU5UTyB0NSAoZGF0ZSwgdDJfa2V5LCB0M19rZXksIGFtb3VudCkgVkFMVUVTICgnMjAxMi0wMS0wMScsICd0Ml9rZXkxJywgJ3QzX2tleTEnLCA1MDAwKTsK

c2VsZWN0ICctLSB0MSAtLSc7CnNlbGVjdCAqIGZyb20gdDE7CgpzZWxlY3QgJy0tIHQyIC0tJzsKc2VsZWN0ICogZnJvbSB0MjsKCnNlbGVjdCAnLS0gdDMgLS0nOwpzZWxlY3QgKiBmcm9tIHQzOwoKc2VsZWN0ICctLSB0NCAtLSc7CnNlbGVjdCAqIGZyb20gdDQ7CgpzZWxlY3QgJy0tIHQ1LS0nOwpzZWxlY3QgKiBmcm9tIHQ1OwoKc2VsZWN0ICctLSDntZDmnpwgLS0nOwpzZWxlY3Qgc3VtKHQ1LmFtb3VudCAqICh0NC5wZXJjZW50IC8gMTAwLjApKSByZXN1bHQgZnJvbSAKKAogKAogIHNlbGVjdCBzdHJmdGltZSgnJVknLCBkYXRlKSB5ZWFyLCB0Ml9rZXksIHQzX2tleSwgYW1vdW50IAogIGZyb20gdDUKICB3aGVyZSBzdHJmdGltZSgnJVknLCBkYXRlKSA9ICcyMDExJwogKSB0NQogaW5uZXIgam9pbiB0MiBvbiAodDIua2V5ID0gdDUudDJfa2V5KQogaW5uZXIgam9pbgogICgKICAgc2VsZWN0IHQ0LiogZnJvbSB0NCAKICAgaW5uZXIgam9pbiB0MSBvbiAodDQudDFfa2V5ID0gdDEua2V5IGFuZCB0MS5uYW1lID0gJ3QxX25hbWUxJykKICApIHQ0CiBvbiAodDUudDNfa2V5ID0gdDQudDNfa2V5IGFuZCB0NS55ZWFyID0gdDQueWVhcikKKTsK

select '-- t1 --';
select * from t1;

select '-- t2 --';
select * from t2;

select '-- t3 --';
select * from t3;

select '-- t4 --';
select * from t4;

select '-- t5--';
select * from t5;

select '-- 結果 --';
select sum(t5.amount * (t4.percent / 100.0)) result from 
(
 (
  select strftime('%Y', date) year, t2_key, t3_key, amount 
  from t5
  where strftime('%Y', date) = '2011'
 ) t5
 inner join t2 on (t2.key = t5.t2_key)
 inner join
  (
   select t4.* from t4 
   inner join t1 on (t4.t1_key = t1.key and t1.name = 't1_name1')
  ) t4
 on (t5.t3_key = t4.t3_key and t5.year = t4.year)
);