mysql: create database

/*************************************** 
 *        DDL: Create Database         * 
 ***************************************/

-- drop existing database if exists;
DROP DATABASE IF EXISTS fitbit_new;

-- create database;
CREATE DATABASE fitbit_new;

-- use database;
USE fitbit_new;
show tables;

/*************************************** 
 *           DDL: Create Table         * 
 ***************************************/

/*        product          */
-- create table product;
DROP TABLE IF EXISTS product, client, sales;




describe sales;
-- alter table;



/*       client      */
-- create table client;



describe client;


/*        sales         */
-- create table sales;


-- create foreign key seperately;
alter table sales add
constraint fk_client_id_wanmen
	foreign key (client_id)
    references client (client_id)
    on delete cascade;

-- drop foreign key;
alter table sales drop
foreign key fk_client_id_wanmen;

-- add update cascade;
alter table sales drop
foreign key fk_client_id;
alter table sales drop
foreign key fk_product_id;
alter table sales add
constraint fk_product_id
	foreign key (product_id)
    references product (product_id)
    on delete cascade
    on update cascade;
alter table sales add
constraint fk_client_id
	foreign key (client_id)
    references client (client_id)
    on delete cascade
    on update cascade;

-- create view;


  
/********************************************** 
 *        DML: Insert, Update, Delete         * 
 **********************************************/

INSERT INTO product 
VALUES
(1,'E-ZIP','Zip','EVERYDAY','GREEN','Y',59.95),
(2,'E-FLX','Flex','EVERYDAY','BLACK','N',99.95),
(3,'A-BLZ','Blaze','ACTIVE','PURPLE','Y',199.95),
(4,'P-SUG','Surge','PERFORMANCE','BLACK','Y',249.95);
select * from product;

-- update product;
update product
set product_id = 200
where product_id = 2;

-- cascade delete
delete from product
where product_id = 200;



INSERT INTO sales 
(tran_id, date, product_id, client_id, price, quantity)
VALUES
(1,'2016-6-1', 1, 1, 40, 10),
(2,'2016-6-5', 1, 2, 30, 5),
(3,'2016-6-8', 2, 1, 80, 8),
(4,'2016-6-8', 2, 2, 70, 7),
(5,'2016-6-13', 3, 2, 150, 5),
(6,'2016-6-18', 3, 4, 150, 10),
(7,'2016-6-20', 2, 4, 40, 15); 
select * from sales;
-- update sales;
update sales
set date = '2016-06-21',price = 200
where date = '2016-06-20' and tran_id = 7;



INSERT INTO client 
VALUES
(1,'FITBIT','ONLINE'),
(2,'AMAZON','ONLINE'),
(3,'BESTBUY','OFFLINE'),
(4,'WALMART','OFFLINE')
;
select * from client;
-- create table SHIPPING;
DROP TABLE IF EXISTS shipping;

create table if not exists shipping(
	shipping_id int not null auto_increment,
    tran_id int not null,
    tracking_no int,
    status enum('preparing', 'shipped', 'arrived'),
    arrive_date date,
    eta	date,
    primary key (shipping_id),
    constraint fk_tran_id
	foreign key (tran_id)
    references sales (tran_id)
    on delete cascade
    on update cascade
);

insert into shipping
(tran_id, tracking_no, status, arrive_date, eta)
values
	(3,103,'arrived','2016-06-02','2016-06-02'),
    (4,104,'arrived','2016-06-30','2016-06-25'),
    (5,105,'shipped',null,'2016-03-04'),
    (6,106,'preparing',null,null);

select * from shipping;