-- SQLアンチパターン Chapter17「スパゲッティクエリ」 写経用INSERTデータ

-- DDLは書籍の"はじめに"に掲載されているSQLを使用してください。
-- 以下の書籍サイトに公開されているサンプルコードにも setup.sql が提供されています。
-- http://www.oreilly.co.jp/books/9784873115894/

-- Product Table Insert
insert into Products (product_name) values('Open RoundFile');
insert into Products (product_name) values('Visual TurboBuilder');
insert into Products (product_name) values('ReConsider');


-- BugStatus Table Insert
insert into BugStatus (status) values ('FIXED');
insert into BugStatus (status) values ('OPEN');
insert into BugStatus (status) values ('NEW');


-- Accounts Table Insert
insert into Accounts (account_name, first_name, last_name, email)
  values('sqlap01', 'taro', 'hitachi', 'abc@mail.co.jp');
insert into Accounts (account_name, first_name, last_name, email)
  values('sqlap02', 'hanako', 'hitachi', 'def@example.com');


-- Bugs Table Insert
-- status = FIXED
-- 01
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2009-12-19', 1, 1, 'FIXED', 10.5);
-- 02
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-06-01', 2, 2, 'FIXED', 10.5);
-- 03
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-16', 1, 1, 'FIXED', 10.5);
-- 04
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-10', 2, 2, 'FIXED', 10.5);
-- 05
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-16', 1, 1, 'FIXED', 10.5);
-- 06
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-01-01', 2, 2, 'FIXED', 10.5);
-- 07
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2009-11-09', 1, 1, 'FIXED', 10.5);
-- 08
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2009-12-19', 1, 1, 'FIXED', 10.5);
-- 09
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-06-01', 2, 2, 'FIXED', 10.5);
-- 10
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-16', 1, 1, 'FIXED', 10.5);
-- 11
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-10', 2, 2, 'FIXED', 10.5);
--  status = OPEN
-- 12
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2009-12-19', 1, 1, 'OPEN', 10.5);
-- 13
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-06-01', 2, 2, 'OPEN', 10.5);
-- 14
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-16', 1, 1, 'OPEN', 10.5);
-- 15
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-10', 2, 2, 'OPEN', 10.5);
-- 16
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-02-16', 1, 1, 'OPEN', 10.5);
-- 17
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2010-01-01', 2, 2, 'OPEN', 10.5);
-- 18
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)  values('2009-11-09', 1, 1, 'OPEN', 10.5);  

-- Others
-- 19
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)
  values('2010-02-16', 1, 1, 'NEW', 10.5);
-- 20
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)
  values('2010-01-01', 2, 2, 'OPEN', 10.5);
-- 21
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)
  values('2009-11-09', 1, 1, 'FIXED', 10.5);  
-- 22
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)
  values('2010-01-01', 2, 2, 'NEW', 10.5);
-- 23
insert into Bugs (date_reported, reported_by, assigned_to, status, hours)
  values('2009-11-09', 1, 1, 'OPEN', 10.5);  

  
-- Bugs Table PrimaryKey Update
-- status = FIXED
update Bugs set bug_id =  1234 where bug_id =  1;
update Bugs set bug_id =  3456 where bug_id =  2;
update Bugs set bug_id =  4567 where bug_id =  3;
update Bugs set bug_id =  5678 where bug_id =  4;
update Bugs set bug_id =  6789 where bug_id =  5;
update Bugs set bug_id =  7890 where bug_id =  6;
update Bugs set bug_id =  8901 where bug_id =  7;
update Bugs set bug_id =  9012 where bug_id =  8;
update Bugs set bug_id = 10123 where bug_id =  9;
update Bugs set bug_id = 11234 where bug_id = 10;
update Bugs set bug_id = 12345 where bug_id = 11;
-- status = OPEN
update Bugs set bug_id =  4077 where bug_id = 12;
update Bugs set bug_id =  8063 where bug_id = 13;
update Bugs set bug_id =  5150 where bug_id = 14;
update Bugs set bug_id =   867 where bug_id = 15;
update Bugs set bug_id =  5309 where bug_id = 16;
update Bugs set bug_id =  6060 where bug_id = 17;
update Bugs set bug_id =   842 where bug_id = 18;

-- Others
update Bugs set bug_id = 20001 where bug_id = 19;
update Bugs set bug_id = 20002 where bug_id = 20;
update Bugs set bug_id = 20003 where bug_id = 21;
update Bugs set bug_id = 20004 where bug_id = 22;
update Bugs set bug_id = 20005 where bug_id = 23;


-- BugsProducts Table Insert
-- status = FIXED
insert into BugsProducts (bug_id, product_id) values( 1234, 1);
insert into BugsProducts (bug_id, product_id) values( 3456, 1);
insert into BugsProducts (bug_id, product_id) values( 4567, 1);
insert into BugsProducts (bug_id, product_id) values( 5678, 1);
insert into BugsProducts (bug_id, product_id) values( 6789, 1);
insert into BugsProducts (bug_id, product_id) values( 7890, 1);
insert into BugsProducts (bug_id, product_id) values( 8901, 1);
insert into BugsProducts (bug_id, product_id) values( 9012, 1);
insert into BugsProducts (bug_id, product_id) values(10123, 1);
insert into BugsProducts (bug_id, product_id) values(11234, 1);
insert into BugsProducts (bug_id, product_id) values(12345, 1);
-- status = OPEN
insert into BugsProducts (bug_id, product_id) values( 4077, 1);
insert into BugsProducts (bug_id, product_id) values( 8063, 1);
insert into BugsProducts (bug_id, product_id) values( 5150, 1);
insert into BugsProducts (bug_id, product_id) values(  867, 1);
insert into BugsProducts (bug_id, product_id) values( 5309, 1);
insert into BugsProducts (bug_id, product_id) values( 6060, 1);
insert into BugsProducts (bug_id, product_id) values(  842, 1);
-- Others
insert into BugsProducts (bug_id, product_id) values( 20001, 2);
insert into BugsProducts (bug_id, product_id) values( 20002, 3);
insert into BugsProducts (bug_id, product_id) values( 20003, 2);
insert into BugsProducts (bug_id, product_id) values( 20004, 3);
insert into BugsProducts (bug_id, product_id) values( 20005, 2);