mysql: query

USE fitbit_new;

insert into product (product_id, code, name, color, class, msrp) values
(5, 'np1', 'new product 1', 'PINK','EVERYDAY', 49),
(6, 'np2', 'new product 2', 'PINK','EVERYDAY', 29),
(7, 'np3', 'new product 3', 'RED','ACTIVE', 99);

INSERT INTO sales
(tran_id, date, product_id, client_id, price, quantity) 
VALUES ('10', '2016-08-10', '1', '1', '500', '100');

select * from product;
select * from client;
/*********************
 *       Query       *
 *********************/
-- list all product name in the product table;
select name
from product;

-- practice: list all color in the product table;


-- show me all the distinct status in shipping table
select distinct status
from shipping;

-- practice: show me all the distinct type in client table



/*********************
 * conditional query *
 *********************/
-- list all sales with product_id=2;
select *
from sales
where product_id=2;

-- what's the msrp of Flex and Blaze, respectively;
select name, msrp
from product
where name='Flex' or  name = 'Blaze';

-- practice: what's the client type of Amazon;


-- find trans_id which occured after 06/08/2016
select *
from sales
where date > '2016-06-08';

-- find trans_id which occured between 06/01/2016 and 06/08/2016

-- practice: find trans_id which occured before after 06/20/2016


-- find product names which color is black and msrp is 99.95


-- find product names which color is black or msrp is 99.95


--  practice: find product names which color is Green or msrp is 249.95




-- find product names which msrp is 99.95 or 199.95


--  practice: find product names which color is Green or Black use in function


/*********************
 *        like       *
 *********************/
-- find product code start with E;
-- /* % can substitue one or more characters */
select * from product where code like 'E%';

-- find product code end with Z;

-- find product code contains B;


-- practice: find product name start with F;


-- practice: find all product name not containing "product";


-- find product name with 4 characters;
-- /* _ can substitue single character */
select * from product where length(name) = 4;

/*********************
 *        As         *
 *********************/
-- Calculated columns
select *, msrp*0.9 as adjusted_msrp from product;