SQL DDL & DML from Oracle JDBC toturial

闲来无事，写写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);