闲来无事,写写sql,将http://docs.oracle.com/javase/tutorial/jdbc/basics/tables.html里的table用Mysql ddl翻译一遍
use test;
-------------------------suppliers
drop table if exists coffees;
drop table if exists suppliers cascade;
create table suppliers(
sup_id int primary key,
sup_name varchar(32),
street varchar(32),
city varchar(16),
state varchar(10),
zip varchar(5)
);
insert into suppliers(sup_id, sup_name, street, city, state, zip)
values(101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199');
insert into suppliers(sup_id, sup_name, street, city, state, zip)
values(49, 'Superior Coffee', '1 Party Place', 'Mendocino', 'CA', '95460');
insert into suppliers(sup_id, sup_name, street, city, state, zip)
values(150, 'The High Ground', '100 Coffee Lane', 'Meadows', 'CA', '93966');
-------------------------coffees
drop table if exists coffees;
create table coffees(
cof_name varchar(32) primary key,
sup_id int,
price float,
sales int,
total int,
foreign key (sup_id) references suppliers(sup_id)
);
insert into coffees(cof_name, sup_id, price, sales, total)
values('Colombian', 101, 7.99, 0, 0);
insert into coffees(cof_name, sup_id, price, sales, total)
values('French_Roast', 49, 8.99, 0, 0);
insert into coffees(cof_name, sup_id, price, sales, total)
values('Espresso', 150, 9.99, 0, 0);
insert into coffees(cof_name, sup_id, price, sales, total)
values('Colombian_Decaf', 101, 8.99, 0, 0);
insert into coffees(cof_name, sup_id, price, sales, total)
values('French_Roast_Decaf', 49, 9.99, 0, 0);
-------------------------cof_inventory
drop table if exists cof_inventory;
create table cof_inventory(
warehouse_id int,
cof_name varchar(32),
sup_id int,
quan int,
date_val date
);
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'House_Blend', 49, 0, '2006-04-01');
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'House_Blend_Decaf', 49, 0, '2006-04-01');
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'Colombian', 101, 0, '2006-04-01');
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'French_Roast', 49, 0, '2006-04-01');
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'Espresso', 150, 0, '2006-04-01');
insert into cof_inventory(warehouse_id, cof_name, sup_id, quan, date_val)
values(1234, 'Colombian_Decaf', 101, 0, '2006-04-01');
-------------------------merch_inventory
drop table if exists merch_inventory;
create table merch_inventory(
item_id varchar(10) primary key,
item_name varchar(30),
sup_id int,
quan int,
date_val date
);
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00001234', 'Cup_Large', 00456, 28, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00001235', 'Cup_Small', 00456, 36, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00001236', 'Saucer', 00456, 64, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00001287', 'Carafe', 00456, 12, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00006931', 'Carafe', 00927, 3, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00006935', 'PotHolder', 00927, 88, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00006977', 'Napkin', 00927, 108, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00006979', 'Towel', 00927, 24, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00004488', 'CofMaker', 08732, 5, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00004490', 'CofGrinder', 08732, 9, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00004495', 'EspMaker', 08732, 4, '2006-04-01');
insert into merch_inventory(item_id, item_name, sup_id, quan, date_val)
values('00006914', 'Cookbook', 00927, 12, '2006-04-01');
--------------------------coffee_houses
drop table if exists coffee_houses;
create table coffee_houses(
store_id varchar(10) primary key,
city varchar(16),
coffee int,
merch int,
total int
);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10023, 'Mendocino', 3450, 2005, 5455);
insert into coffee_houses(store_id, city, coffee, merch, total) values(33002, 'Seattle', 4699, 3109, 7808);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10040, 'SF', 5386, 2841, 8227);
insert into coffee_houses(store_id, city, coffee, merch, total) values(32001, 'Portland', 3147, 3579, 6726);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10042, 'SF', 2863, 1874, 4710);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10024, 'Sacramento', 1987, 2341, 4328);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10039, 'Carmel', 2691, 1121, 3812);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10041, 'LA', 1533, 1007, 2540);
insert into coffee_houses(store_id, city, coffee, merch, total) values(33005, 'Olympia', 2733, 1550, 4283);
insert into coffee_houses(store_id, city, coffee, merch, total) values(33010, 'Seattle', 3210, 2177, 5387);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10035, 'SF', 1922, 1056, 2978);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10037, 'LA', 2143, 1876, 4019);
insert into coffee_houses(store_id, city, coffee, merch, total) values(10034, 'San_Jose', 1234, 1032, 2266);
insert into coffee_houses(store_id, city, coffee, merch, total) values(32004, 'Eugene', 1356, 1112, 2468);