Sh3fBfks

1use mct;
2create table if not exists categories(
3id int auto_increment not null,
4category varchar(255),
5primary key (id)
6);
7
8create table if not exists transactions(
9id int auto_increment not null,
10dt date,
11vendor varchar(255),
12amount decimal(7,2),
13cat_id int default 1,
14primary key(id),
15foreign key(cat_id) references categories(id)
16);
17
18create table if not exists budgets_2018(
19id int not null,
20January decimal(7,2) default 0.00,
21February decimal(7,2) default 0.00,
22March decimal(7,2) default 0.00,
23April decimal(7,2) default 0.00,
24May decimal(7,2) default 0.00,
25June decimal(7,2) default 0.00,
26July decimal(7,2) default 0.00,
27August decimal(7,2) default 0.00,
28September decimal(7,2) default 0.00,
29October decimal(7,2) default 0.00,
30November decimal(7,2) default 0.00,
31December decimal(7,2) default 0.00,
32primary key(id),
33foreign key(id) references categories(id)
34);
35insert into categories (category) values ('Uncategorized');
36insert into budgets_2018(id) values ((select id from categories where category = 'Uncategorized'));
37	
38insert into transactions (dt, vendor, amount) values (
39(curdate(), 'DummyVendor1', 3.45),
40(curdate(), 'DummyVendor2', 11.47),
41(curdate(), 'DummyVendor3', 73.22),
42(curdate(), 'DummyVendor4', 199.27),
43(curdate(), 'DummyVendor5', 34.56),
44(curdate(), 'DummyVendor6', 18.99),
45(curdate(), 'DummyVendor7', 1.45),
46(curdate(), 'DummyVendor8', 2.41)
47);