sql语句操作记录
发觉一些sql语句写出来的时候不停忘记，做一个记录。
mySQL
.查看表的创建过程sql语句和注释，注释是在创建表的过程中增加comment，后面跟随注释的内容
SHOW CRATE TABLE TABLENAME # 查看表创建过程
.查询表中的嵌套条件，父类包含子类的结果
SELECT * FROM test WHERE keyword="技术" OR paraent_id IN (SELECT DISTINCT id from 'test' WHERE keyword = "技术")
添加唯一索引或者唯一性约束的时候，字段中必须没有重复的值，不然会创建失败。MySQL不支持text类型的字段创建，会失败。
http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length
.创建表时候给字段增加唯一约束
create table t3 (tid INT NOT NULL AUTO_INCREMENT,title VARCHAR(100)
NOT NULL UNIQUE,datetime DATE,user VARCHHAR(200),PRIMARY KEY(tid));
.给已经建立的表某个字段增加唯一约束
ALTER TABLE tablename add unique(fied)
.创建表时候给字段增加唯一索引
create table t2 (tid INT NOT NULL AUTO_INCREMENT,title VARCHAR(100)
NOT NULL,datetime DATE,user VARCHAR(200),PRIMARY KEY(tid),UNIQUE KEY u1 (user));
.给已经建立的表某个字段增加唯一索引
create unique index ti2 on t2(title);
.查看MySQL字符集
SHOW CHARACTER SET;
# 使用多列的主键成为复合主键
# 主键的值不允许被修改（实质可以修改）
# 字符串：CHAR(20) _ VARCHAR(20)
# 文本：TINYTEXT TEXT MEDIUMTEXT LONGTEXT
# 数值型：boolean,mediumint int bigint
float(p,s) double(p,s)
# 时间数据：date datetime timestamp year time
+++++ 完整的定义一张person表和favorite_food外键约束表流程 +++++
.创建一张person表，并且不给它增加主键自动增长
CREATE TABLE person (person_id SMALLINT UNSIGNED,name VARCHAR(20),gender ENUM('M','F'),
birth_date DATE,city VARCHAR(20),postal_code
VARCHAR(20), CONSTRAINT pk_person PRIMARY KEY (person_id));
.创建一张favorite_food，两个主键，外键引用person表的主键id
CREATE TABLE favorite_food (person_id SMALLINT UNSIGNED, food VARCHAR(20), CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food), CONSTRAINT fk_fav_food_person_id FOREIGN KEY(person_id) REFERENCES person (person_id));
.现在给person表增加主键自增长，但是由于主键person_id被favorite_food引用，修改表增加自增长会失败，必须先删除掉favorite_food的外键引用
锁表： LOCK TABLES favorite_food WRITE,person WRITE; 删除外键引用： ALTER TABLE favorite_food DROP FOREIGN KEY fk_fav_food_person_id;
.修改person表主键为自增长
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
.增加favorite_food外键引用
ALTER TABLE favorite_food ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE;
.释放表：
UNLOCK TABLES;
+++++++ +++++++++++++
.子查询产生的表
SELECT e.emp_id,e.fname FROM (SELECT emp_id,fname,start_date,title FROM employee) AS e;
.创建视图
CREATE VIEW employee_vw AS SELECT emp_id,fname,YEAR(start_date) AS start_year FROM employee;
.使用视图
SELECT emp_id,start_year FROM employee_vw;
.表连接
SELECT e.emp_id,e.fname,department.name AS dept_name FROM employee as e inner join department ON e.dept_id = department.dept_id;
# 创建bank表项目
1 /* begin table creation */ 2 3 create table department 4 (dept_id smallint unsigned not null auto_increment, 5 name varchar(20) not null, 6 constraint pk_department primary key (dept_id) 7 ); 8 9 create table branch 10 (branch_id smallint unsigned not null auto_increment, 11 name varchar(20) not null, 12 address varchar(30), 13 city varchar(20), 14 state varchar(2), 15 zip varchar(12), 16 constraint pk_branch primary key (branch_id) 17 ); 18 19 create table employee 20 (emp_id smallint unsigned not null auto_increment, 21 fname varchar(20) not null, 22 lname varchar(20) not null, 23 start_date date not null, 24 end_date date, 25 superior_emp_id smallint unsigned, 26 dept_id smallint unsigned, 27 title varchar(20), 28 assigned_branch_id smallint unsigned, 29 constraint fk_e_emp_id 30 foreign key (superior_emp_id) references employee (emp_id), 31 constraint fk_dept_id 32 foreign key (dept_id) references department (dept_id), 33 constraint fk_e_branch_id 34 foreign key (assigned_branch_id) references branch (branch_id), 35 constraint pk_employee primary key (emp_id) 36 ); 37 38 create table product_type 39 (product_type_cd varchar(10) not null, 40 name varchar(50) not null, 41 constraint pk_product_type primary key (product_type_cd) 42 ); 43 44 create table product 45 (product_cd varchar(10) not null, 46 name varchar(50) not null, 47 product_type_cd varchar(10) not null, 48 date_offered date, 49 date_retired date, 50 constraint fk_product_type_cd foreign key (product_type_cd) 51 references product_type (product_type_cd), 52 constraint pk_product primary key (product_cd) 53 ); 54 55 create table customer 56 (cust_id integer unsigned not null auto_increment, 57 fed_id varchar(12) not null, 58 cust_type_cd enum('I','B') not null, 59 address varchar(30), 60 city varchar(20), 61 state varchar(20), 62 postal_code varchar(10), 63 constraint pk_customer primary key (cust_id) 64 ); 65 66 create table individual 67 (cust_id integer unsigned not null, 68 fname varchar(30) not null, 69 lname varchar(30) not null, 70 birth_date date, 71 constraint fk_i_cust_id foreign key (cust_id) 72 references customer (cust_id), 73 constraint pk_individual primary key (cust_id) 74 ); 75 76 create table business 77 (cust_id integer unsigned not null, 78 name varchar(40) not null, 79 state_id varchar(10) not null, 80 incorp_date date, 81 constraint fk_b_cust_id foreign key (cust_id) 82 references customer (cust_id), 83 constraint pk_business primary key (cust_id) 84 ); 85 86 create table officer 87 (officer_id smallint unsigned not null auto_increment, 88 cust_id integer unsigned not null, 89 fname varchar(30) not null, 90 lname varchar(30) not null, 91 title varchar(20), 92 start_date date not null, 93 end_date date, 94 constraint fk_o_cust_id foreign key (cust_id) 95 references business (cust_id), 96 constraint pk_officer primary key (officer_id) 97 ); 98 99 create table account 100 (account_id integer unsigned not null auto_increment, 101 product_cd varchar(10) not null, 102 cust_id integer unsigned not null, 103 open_date date not null, 104 close_date date, 105 last_activity_date date, 106 status enum('ACTIVE','CLOSED','FROZEN'), 107 open_branch_id smallint unsigned, 108 open_emp_id smallint unsigned, 109 avail_balance float(10,2), 110 pending_balance float(10,2), 111 constraint fk_product_cd foreign key (product_cd) 112 references product (product_cd), 113 constraint fk_a_cust_id foreign key (cust_id) 114 references customer (cust_id), 115 constraint fk_a_branch_id foreign key (open_branch_id) 116 references branch (branch_id), 117 constraint fk_a_emp_id foreign key (open_emp_id) 118 references employee (emp_id), 119 constraint pk_account primary key (account_id) 120 ); 121 122 create table transaction 123 (txn_id integer unsigned not null auto_increment, 124 txn_date datetime not null, 125 account_id integer unsigned not null, 126 txn_type_cd enum('DBT','CDT'), 127 amount double(10,2) not null, 128 teller_emp_id smallint unsigned, 129 execution_branch_id smallint unsigned, 130 funds_avail_date datetime, 131 constraint fk_t_account_id foreign key (account_id) 132 references account (account_id), 133 constraint fk_teller_emp_id foreign key (teller_emp_id) 134 references employee (emp_id), 135 constraint fk_exec_branch_id foreign key (execution_branch_id) 136 references branch (branch_id), 137 constraint pk_transaction primary key (txn_id) 138 ); 139 140 /* end table creation */ 141 142 /* begin data population */ 143 144 /* department data */ 145 insert into department (dept_id, name) 146 values (null, 'Operations'); 147 insert into department (dept_id, name) 148 values (null, 'Loans'); 149 insert into department (dept_id, name) 150 values (null, 'Administration'); 151 152 /* branch data */ 153 insert into branch (branch_id, name, address, city, state, zip) 154 values (null, 'Headquarters', '3882 Main St.', 'Waltham', 'MA', '02451'); 155 insert into branch (branch_id, name, address, city, state, zip) 156 values (null, 'Woburn Branch', '422 Maple St.', 'Woburn', 'MA', '01801'); 157 insert into branch (branch_id, name, address, city, state, zip) 158 values (null, 'Quincy Branch', '125 Presidential Way', 'Quincy', 'MA', '02169'); 159 insert into branch (branch_id, name, address, city, state, zip) 160 values (null, 'So. NH Branch', '378 Maynard Ln.', 'Salem', 'NH', '03079'); 161 162 /* employee data */ 163 insert into employee (emp_id, fname, lname, start_date, 164 dept_id, title, assigned_branch_id) 165 values (null, 'Michael', 'Smith', '2001-06-22', 166 (select dept_id from department where name = 'Administration'), 167 'President', 168 (select branch_id from branch where name = 'Headquarters')); 169 insert into employee (emp_id, fname, lname, start_date, 170 dept_id, title, assigned_branch_id) 171 values (null, 'Susan', 'Barker', '2002-09-12', 172 (select dept_id from department where name = 'Administration'), 173 'Vice President', 174 (select branch_id from branch where name = 'Headquarters')); 175 insert into employee (emp_id, fname, lname, start_date, 176 dept_id, title, assigned_branch_id) 177 values (null, 'Robert', 'Tyler', '2000-02-09', 178 (select dept_id from department where name = 'Administration'), 179 'Treasurer', 180 (select branch_id from branch where name = 'Headquarters')); 181 insert into employee (emp_id, fname, lname, start_date, 182 dept_id, title, assigned_branch_id) 183 values (null, 'Susan', 'Hawthorne', '2002-04-24', 184 (select dept_id from department where name = 'Operations'), 185 'Operations Manager', 186 (select branch_id from branch where name = 'Headquarters')); 187 insert into employee (emp_id, fname, lname, start_date, 188 dept_id, title, assigned_branch_id) 189 values (null, 'John', 'Gooding', '2003-11-14', 190 (select dept_id from department where name = 'Loans'), 191 'Loan Manager', 192 (select branch_id from branch where name = 'Headquarters')); 193 insert into employee (emp_id, fname, lname, start_date, 194 dept_id, title, assigned_branch_id) 195 values (null, 'Helen', 'Fleming', '2004-03-17', 196 (select dept_id from department where name = 'Operations'), 197 'Head Teller', 198 (select branch_id from branch where name = 'Headquarters')); 199 insert into employee (emp_id, fname, lname, start_date, 200 dept_id, title, assigned_branch_id) 201 values (null, 'Chris', 'Tucker', '2004-09-15', 202 (select dept_id from department where name = 'Operations'), 203 'Teller', 204 (select branch_id from branch where name = 'Headquarters')); 205 insert into employee (emp_id, fname, lname, start_date, 206 dept_id, title, assigned_branch_id) 207 values (null, 'Sarah', 'Parker', '2002-12-02', 208 (select dept_id from department where name = 'Operations'), 209 'Teller', 210 (select branch_id from branch where name = 'Headquarters')); 211 insert into employee (emp_id, fname, lname, start_date, 212 dept_id, title, assigned_branch_id) 213 values (null, 'Jane', 'Grossman', '2002-05-03', 214 (select dept_id from department where name = 'Operations'), 215 'Teller', 216 (select branch_id from branch where name = 'Headquarters')); 217 insert into employee (emp_id, fname, lname, start_date, 218 dept_id, title, assigned_branch_id) 219 values (null, 'Paula', 'Roberts', '2002-07-27', 220 (select dept_id from department where name = 'Operations'), 221 'Head Teller', 222 (select branch_id from branch where name = 'Woburn Branch')); 223 insert into employee (emp_id, fname, lname, start_date, 224 dept_id, title, assigned_branch_id) 225 values (null, 'Thomas', 'Ziegler', '2000-10-23', 226 (select dept_id from department where name = 'Operations'), 227 'Teller', 228 (select branch_id from branch where name = 'Woburn Branch')); 229 insert into employee (emp_id, fname, lname, start_date, 230 dept_id, title, assigned_branch_id) 231 values (null, 'Samantha', 'Jameson', '2003-01-08', 232 (select dept_id from department where name = 'Operations'), 233 'Teller', 234 (select branch_id from branch where name = 'Woburn Branch')); 235 insert into employee (emp_id, fname, lname, start_date, 236 dept_id, title, assigned_branch_id) 237 values (null, 'John', 'Blake', '2000-05-11', 238 (select dept_id from department where name = 'Operations'), 239 'Head Teller', 240 (select branch_id from branch where name = 'Quincy Branch')); 241 insert into employee (emp_id, fname, lname, start_date, 242 dept_id, title, assigned_branch_id) 243 values (null, 'Cindy', 'Mason', '2002-08-09', 244 (select dept_id from department where name = 'Operations'), 245 'Teller', 246 (select branch_id from branch where name = 'Quincy Branch')); 247 insert into employee (emp_id, fname, lname, start_date, 248 dept_id, title, assigned_branch_id) 249 values (null, 'Frank', 'Portman', '2003-04-01', 250 (select dept_id from department where name = 'Operations'), 251 'Teller', 252 (select branch_id from branch where name = 'Quincy Branch')); 253 insert into employee (emp_id, fname, lname, start_date, 254 dept_id, title, assigned_branch_id) 255 values (null, 'Theresa', 'Markham', '2001-03-15', 256 (select dept_id from department where name = 'Operations'), 257 'Head Teller', 258 (select branch_id from branch where name = 'So. NH Branch')); 259 insert into employee (emp_id, fname, lname, start_date, 260 dept_id, title, assigned_branch_id) 261 values (null, 'Beth', 'Fowler', '2002-06-29', 262 (select dept_id from department where name = 'Operations'), 263 'Teller', 264 (select branch_id from branch where name = 'So. NH Branch')); 265 insert into employee (emp_id, fname, lname, start_date, 266 dept_id, title, assigned_branch_id) 267 values (null, 'Rick', 'Tulman', '2002-12-12', 268 (select dept_id from department where name = 'Operations'), 269 'Teller', 270 (select branch_id from branch where name = 'So. NH Branch')); 271 272 /* create data for self-referencing foreign key 'superior_emp_id' */ 273 create temporary table emp_tmp as 274 select emp_id, fname, lname from employee; 275 276 update employee set superior_emp_id = 277 (select emp_id from emp_tmp where lname = 'Smith' and fname = 'Michael') 278 where ((lname = 'Barker' and fname = 'Susan') 279 or (lname = 'Tyler' and fname = 'Robert')); 280 update employee set superior_emp_id = 281 (select emp_id from emp_tmp where lname = 'Tyler' and fname = 'Robert') 282 where lname = 'Hawthorne' and fname = 'Susan'; 283 update employee set superior_emp_id = 284 (select emp_id from emp_tmp where lname = 'Hawthorne' and fname = 'Susan') 285 where ((lname = 'Gooding' and fname = 'John') 286 or (lname = 'Fleming' and fname = 'Helen') 287 or (lname = 'Roberts' and fname = 'Paula') 288 or (lname = 'Blake' and fname = 'John') 289 or (lname = 'Markham' and fname = 'Theresa')); 290 update employee set superior_emp_id = 291 (select emp_id from emp_tmp where lname = 'Fleming' and fname = 'Helen') 292 where ((lname = 'Tucker' and fname = 'Chris') 293 or (lname = 'Parker' and fname = 'Sarah') 294 or (lname = 'Grossman' and fname = 'Jane')); 295 update employee set superior_emp_id = 296 (select emp_id from emp_tmp where lname = 'Roberts' and fname = 'Paula') 297 where ((lname = 'Ziegler' and fname = 'Thomas') 298 or (lname = 'Jameson' and fname = 'Samantha')); 299 update employee set superior_emp_id = 300 (select emp_id from emp_tmp where lname = 'Blake' and fname = 'John') 301 where ((lname = 'Mason' and fname = 'Cindy') 302 or (lname = 'Portman' and fname = 'Frank')); 303 update employee set superior_emp_id = 304 (select emp_id from emp_tmp where lname = 'Markham' and fname = 'Theresa') 305 where ((lname = 'Fowler' and fname = 'Beth') 306 or (lname = 'Tulman' and fname = 'Rick')); 307 308 drop table emp_tmp; 309 310 /* product type data */ 311 insert into product_type (product_type_cd, name) 312 values ('ACCOUNT','Customer Accounts'); 313 insert into product_type (product_type_cd, name) 314 values ('LOAN','Individual and Business Loans'); 315 insert into product_type (product_type_cd, name) 316 values ('INSURANCE','Insurance Offerings'); 317 318 /* product data */ 319 insert into product (product_cd, name, product_type_cd, date_offered) 320 values ('CHK','checking account','ACCOUNT','2000-01-01'); 321 insert into product (product_cd, name, product_type_cd, date_offered) 322 values ('SAV','savings account','ACCOUNT','2000-01-01'); 323 insert into product (product_cd, name, product_type_cd, date_offered) 324 values ('MM','money market account','ACCOUNT','2000-01-01'); 325 insert into product (product_cd, name, product_type_cd, date_offered) 326 values ('CD','certificate of deposit','ACCOUNT','2000-01-01'); 327 insert into product (product_cd, name, product_type_cd, date_offered) 328 values ('MRT','home mortgage','LOAN','2000-01-01'); 329 insert into product (product_cd, name, product_type_cd, date_offered) 330 values ('AUT','auto loan','LOAN','2000-01-01'); 331 insert into product (product_cd, name, product_type_cd, date_offered) 332 values ('BUS','business line of credit','LOAN','2000-01-01'); 333 insert into product (product_cd, name, product_type_cd, date_offered) 334 values ('SBL','small business loan','LOAN','2000-01-01'); 335 336 /* residential customer data */ 337 insert into customer (cust_id, fed_id, cust_type_cd, 338 address, city, state, postal_code) 339 values (null, '111-11-1111', 'I', '47 Mockingbird Ln', 'Lynnfield', 'MA', '01940'); 340 insert into individual (cust_id, fname, lname, birth_date) 341 select cust_id, 'James', 'Hadley', '1972-04-22' from customer 342 where fed_id = '111-11-1111'; 343 insert into customer (cust_id, fed_id, cust_type_cd, 344 address, city, state, postal_code) 345 values (null, '222-22-2222', 'I', '372 Clearwater Blvd', 'Woburn', 'MA', '01801'); 346 insert into individual (cust_id, fname, lname, birth_date) 347 select cust_id, 'Susan', 'Tingley', '1968-08-15' from customer 348 where fed_id = '222-22-2222'; 349 insert into customer (cust_id, fed_id, cust_type_cd, 350 address, city, state, postal_code) 351 values (null, '333-33-3333', 'I', '18 Jessup Rd', 'Quincy', 'MA', '02169'); 352 insert into individual (cust_id, fname, lname, birth_date) 353 select cust_id, 'Frank', 'Tucker', '1958-02-06' from customer 354 where fed_id = '333-33-3333'; 355 insert into customer (cust_id, fed_id, cust_type_cd, 356 address, city, state, postal_code) 357 values (null, '444-44-4444', 'I', '12 Buchanan Ln', 'Waltham', 'MA', '02451'); 358 insert into individual (cust_id, fname, lname, birth_date) 359 select cust_id, 'John', 'Hayward', '1966-12-22' from customer 360 where fed_id = '444-44-4444'; 361 insert into customer (cust_id, fed_id, cust_type_cd, 362 address, city, state, postal_code) 363 values (null, '555-55-5555', 'I', '2341 Main St', 'Salem', 'NH', '03079'); 364 insert into individual (cust_id, fname, lname, birth_date) 365 select cust_id, 'Charles', 'Frasier', '1971-08-25' from customer 366 where fed_id = '555-55-5555'; 367 insert into customer (cust_id, fed_id, cust_type_cd, 368 address, city, state, postal_code) 369 values (null, '666-66-6666', 'I', '12 Blaylock Ln', 'Waltham', 'MA', '02451'); 370 insert into individual (cust_id, fname, lname, birth_date) 371 select cust_id, 'John', 'Spencer', '1962-09-14' from customer 372 where fed_id = '666-66-6666'; 373 insert into customer (cust_id, fed_id, cust_type_cd, 374 address, city, state, postal_code) 375 values (null, '777-77-7777', 'I', '29 Admiral Ln', 'Wilmington', 'MA', '01887'); 376 insert into individual (cust_id, fname, lname, birth_date) 377 select cust_id, 'Margaret', 'Young', '1947-03-19' from customer 378 where fed_id = '777-77-7777'; 379 insert into customer (cust_id, fed_id, cust_type_cd, 380 address, city, state, postal_code) 381 values (null, '888-88-8888', 'I', '472 Freedom Rd', 'Salem', 'NH', '03079'); 382 insert into individual (cust_id, fname, lname, birth_date) 383 select cust_id, 'Louis', 'Blake', '1977-07-01' from customer 384 where fed_id = '888-88-8888'; 385 insert into customer (cust_id, fed_id, cust_type_cd, 386 address, city, state, postal_code) 387 values (null, '999-99-9999', 'I', '29 Maple St', 'Newton', 'MA', '02458'); 388 insert into individual (cust_id, fname, lname, birth_date) 389 select cust_id, 'Richard', 'Farley', '1968-06-16' from customer 390 where fed_id = '999-99-9999'; 391 392 /* corporate customer data */ 393 insert into customer (cust_id, fed_id, cust_type_cd, 394 address, city, state, postal_code) 395 values (null, '04-1111111', 'B', '7 Industrial Way', 'Salem', 'NH', '03079'); 396 insert into business (cust_id, name, state_id, incorp_date) 397 select cust_id, 'Chilton Engineering', '12-345-678', '1995-05-01' from customer 398 where fed_id = '04-1111111'; 399 insert into officer (officer_id, cust_id, fname, lname, 400 title, start_date) 401 select null, cust_id, 'John', 'Chilton', 'President', '1995-05-01' 402 from customer 403 where fed_id = '04-1111111'; 404 insert into customer (cust_id, fed_id, cust_type_cd, 405 address, city, state, postal_code) 406 values (null, '04-2222222', 'B', '287A Corporate Ave', 'Wilmington', 'MA', '01887'); 407 insert into business (cust_id, name, state_id, incorp_date) 408 select cust_id, 'Northeast Cooling Inc.', '23-456-789', '2001-01-01' from customer 409 where fed_id = '04-2222222'; 410 insert into officer (officer_id, cust_id, fname, lname, 411 title, start_date) 412 select null, cust_id, 'Paul', 'Hardy', 'President', '2001-01-01' 413 from customer 414 where fed_id = '04-2222222'; 415 insert into customer (cust_id, fed_id, cust_type_cd, 416 address, city, state, postal_code) 417 values (null, '04-3333333', 'B', '789 Main St', 'Salem', 'NH', '03079'); 418 insert into business (cust_id, name, state_id, incorp_date) 419 select cust_id, 'Superior Auto Body', '34-567-890', '2002-06-30' from customer 420 where fed_id = '04-3333333'; 421 insert into officer (officer_id, cust_id, fname, lname, 422 title, start_date) 423 select null, cust_id, 'Carl', 'Lutz', 'President', '2002-06-30' 424 from customer 425 where fed_id = '04-3333333'; 426 insert into customer (cust_id, fed_id, cust_type_cd, 427 address, city, state, postal_code) 428 values (null, '04-4444444', 'B', '4772 Presidential Way', 'Quincy', 'MA', '02169'); 429 insert into business (cust_id, name, state_id, incorp_date) 430 select cust_id, 'AAA Insurance Inc.', '45-678-901', '1999-05-01' from customer 431 where fed_id = '04-4444444'; 432 insert into officer (officer_id, cust_id, fname, lname, 433 title, start_date) 434 select null, cust_id, 'Stanley', 'Cheswick', 'President', '1999-05-01' 435 from customer 436 where fed_id = '04-4444444'; 437 438 /* residential account data */ 439 insert into account (account_id, product_cd, cust_id, open_date, 440 last_activity_date, status, open_branch_id, 441 open_emp_id, avail_balance, pending_balance) 442 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 443 e.branch_id, e.emp_id, a.avail, a.pend 444 from customer c cross join 445 (select b.branch_id, e.emp_id 446 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 447 where b.city = 'Woburn' limit 1) e 448 cross join 449 (select 'CHK' prod_cd, '2000-01-15' open_date, '2005-01-04' last_date, 450 1057.75 avail, 1057.75 pend union all 451 select 'SAV' prod_cd, '2000-01-15' open_date, '2004-12-19' last_date, 452 500.00 avail, 500.00 pend union all 453 select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date, 454 3000.00 avail, 3000.00 pend) a 455 where c.fed_id = '111-11-1111'; 456 insert into account (account_id, product_cd, cust_id, open_date, 457 last_activity_date, status, open_branch_id, 458 open_emp_id, avail_balance, pending_balance) 459 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 460 e.branch_id, e.emp_id, a.avail, a.pend 461 from customer c cross join 462 (select b.branch_id, e.emp_id 463 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 464 where b.city = 'Woburn' limit 1) e 465 cross join 466 (select 'CHK' prod_cd, '2001-03-12' open_date, '2004-12-27' last_date, 467 2258.02 avail, 2258.02 pend union all 468 select 'SAV' prod_cd, '2001-03-12' open_date, '2004-12-11' last_date, 469 200.00 avail, 200.00 pend) a 470 where c.fed_id = '222-22-2222'; 471 insert into account (account_id, product_cd, cust_id, open_date, 472 last_activity_date, status, open_branch_id, 473 open_emp_id, avail_balance, pending_balance) 474 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 475 e.branch_id, e.emp_id, a.avail, a.pend 476 from customer c cross join 477 (select b.branch_id, e.emp_id 478 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 479 where b.city = 'Quincy' limit 1) e 480 cross join 481 (select 'CHK' prod_cd, '2002-11-23' open_date, '2004-11-30' last_date, 482 1057.75 avail, 1057.75 pend union all 483 select 'MM' prod_cd, '2002-12-15' open_date, '2004-12-05' last_date, 484 2212.50 avail, 2212.50 pend) a 485 where c.fed_id = '333-33-3333'; 486 insert into account (account_id, product_cd, cust_id, open_date, 487 last_activity_date, status, open_branch_id, 488 open_emp_id, avail_balance, pending_balance) 489 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 490 e.branch_id, e.emp_id, a.avail, a.pend 491 from customer c cross join 492 (select b.branch_id, e.emp_id 493 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 494 where b.city = 'Waltham' limit 1) e 495 cross join 496 (select 'CHK' prod_cd, '2003-09-12' open_date, '2005-01-03' last_date, 497 534.12 avail, 534.12 pend union all 498 select 'SAV' prod_cd, '2000-01-15' open_date, '2004-10-24' last_date, 499 767.77 avail, 767.77 pend union all 500 select 'MM' prod_cd, '2004-09-30' open_date, '2004-11-11' last_date, 501 5487.09 avail, 5487.09 pend) a 502 where c.fed_id = '444-44-4444'; 503 insert into account (account_id, product_cd, cust_id, open_date, 504 last_activity_date, status, open_branch_id, 505 open_emp_id, avail_balance, pending_balance) 506 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 507 e.branch_id, e.emp_id, a.avail, a.pend 508 from customer c cross join 509 (select b.branch_id, e.emp_id 510 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 511 where b.city = 'Salem' limit 1) e 512 cross join 513 (select 'CHK' prod_cd, '2004-01-27' open_date, '2005-01-05' last_date, 514 2237.97 avail, 2897.97 pend) a 515 where c.fed_id = '555-55-5555'; 516 insert into account (account_id, product_cd, cust_id, open_date, 517 last_activity_date, status, open_branch_id, 518 open_emp_id, avail_balance, pending_balance) 519 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 520 e.branch_id, e.emp_id, a.avail, a.pend 521 from customer c cross join 522 (select b.branch_id, e.emp_id 523 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 524 where b.city = 'Waltham' limit 1) e 525 cross join 526 (select 'CHK' prod_cd, '2002-08-24' open_date, '2004-11-29' last_date, 527 122.37 avail, 122.37 pend union all 528 select 'CD' prod_cd, '2004-12-28' open_date, '2004-12-28' last_date, 529 10000.00 avail, 10000.00 pend) a 530 where c.fed_id = '666-66-6666'; 531 insert into account (account_id, product_cd, cust_id, open_date, 532 last_activity_date, status, open_branch_id, 533 open_emp_id, avail_balance, pending_balance) 534 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 535 e.branch_id, e.emp_id, a.avail, a.pend 536 from customer c cross join 537 (select b.branch_id, e.emp_id 538 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 539 where b.city = 'Woburn' limit 1) e 540 cross join 541 (select 'CD' prod_cd, '2004-01-12' open_date, '2004-01-12' last_date, 542 5000.00 avail, 5000.00 pend) a 543 where c.fed_id = '777-77-7777'; 544 insert into account (account_id, product_cd, cust_id, open_date, 545 last_activity_date, status, open_branch_id, 546 open_emp_id, avail_balance, pending_balance) 547 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 548 e.branch_id, e.emp_id, a.avail, a.pend 549 from customer c cross join 550 (select b.branch_id, e.emp_id 551 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 552 where b.city = 'Salem' limit 1) e 553 cross join 554 (select 'CHK' prod_cd, '2001-05-23' open_date, '2005-01-03' last_date, 555 3487.19 avail, 3487.19 pend union all 556 select 'SAV' prod_cd, '2001-05-23' open_date, '2004-10-12' last_date, 557 387.99 avail, 387.99 pend) a 558 where c.fed_id = '888-88-8888'; 559 insert into account (account_id, product_cd, cust_id, open_date, 560 last_activity_date, status, open_branch_id, 561 open_emp_id, avail_balance, pending_balance) 562 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 563 e.branch_id, e.emp_id, a.avail, a.pend 564 from customer c cross join 565 (select b.branch_id, e.emp_id 566 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 567 where b.city = 'Waltham' limit 1) e 568 cross join 569 (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date, 570 125.67 avail, 125.67 pend union all 571 select 'MM' prod_cd, '2004-10-28' open_date, '2004-10-28' last_date, 572 9345.55 avail, 9845.55 pend union all 573 select 'CD' prod_cd, '2004-06-30' open_date, '2004-06-30' last_date, 574 1500.00 avail, 1500.00 pend) a 575 where c.fed_id = '999-99-9999'; 576 577 /* corporate account data */ 578 insert into account (account_id, product_cd, cust_id, open_date, 579 last_activity_date, status, open_branch_id, 580 open_emp_id, avail_balance, pending_balance) 581 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 582 e.branch_id, e.emp_id, a.avail, a.pend 583 from customer c cross join 584 (select b.branch_id, e.emp_id 585 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 586 where b.city = 'Salem' limit 1) e 587 cross join 588 (select 'CHK' prod_cd, '2002-09-30' open_date, '2004-12-15' last_date, 589 23575.12 avail, 23575.12 pend union all 590 select 'BUS' prod_cd, '2002-10-01' open_date, '2004-08-28' last_date, 591 0 avail, 0 pend) a 592 where c.fed_id = '04-1111111'; 593 insert into account (account_id, product_cd, cust_id, open_date, 594 last_activity_date, status, open_branch_id, 595 open_emp_id, avail_balance, pending_balance) 596 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 597 e.branch_id, e.emp_id, a.avail, a.pend 598 from customer c cross join 599 (select b.branch_id, e.emp_id 600 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 601 where b.city = 'Woburn' limit 1) e 602 cross join 603 (select 'BUS' prod_cd, '2004-03-22' open_date, '2004-11-14' last_date, 604 9345.55 avail, 9345.55 pend) a 605 where c.fed_id = '04-2222222'; 606 insert into account (account_id, product_cd, cust_id, open_date, 607 last_activity_date, status, open_branch_id, 608 open_emp_id, avail_balance, pending_balance) 609 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 610 e.branch_id, e.emp_id, a.avail, a.pend 611 from customer c cross join 612 (select b.branch_id, e.emp_id 613 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 614 where b.city = 'Salem' limit 1) e 615 cross join 616 (select 'CHK' prod_cd, '2003-07-30' open_date, '2004-12-15' last_date, 617 38552.05 avail, 38552.05 pend) a 618 where c.fed_id = '04-3333333'; 619 insert into account (account_id, product_cd, cust_id, open_date, 620 last_activity_date, status, open_branch_id, 621 open_emp_id, avail_balance, pending_balance) 622 select null, a.prod_cd, c.cust_id, a.open_date, a.last_date, 'ACTIVE', 623 e.branch_id, e.emp_id, a.avail, a.pend 624 from customer c cross join 625 (select b.branch_id, e.emp_id 626 from branch b inner join employee e on e.assigned_branch_id = b.branch_id 627 where b.city = 'Quincy' limit 1) e 628 cross join 629 (select 'SBL' prod_cd, '2004-02-22' open_date, '2004-12-17' last_date, 630 50000.00 avail, 50000.00 pend) a 631 where c.fed_id = '04-4444444'; 632 633 /* put $100 in all checking/savings accounts on date account opened */ 634 insert into transaction (txn_id, txn_date, account_id, txn_type_cd, 635 amount, funds_avail_date) 636 select null, a.open_date, a.account_id, 'CDT', 100, a.open_date 637 from account a 638 where a.product_cd IN ('CHK','SAV','CD','MM'); 639 640 /* end data population */
