-- create
CREATE TABLE PRODUCTS (
  pId INTEGER PRIMARY KEY NOT NULL,
  item TEXT ,
  price MONEY 
);
INSERT INTO PRODUCTS VALUES(1,"PEN",1.20);
INSERT INTO PRODUCTS VALUES(2,"PENCIL",2.20);
INSERT INTO PRODUCTS VALUES(3,"BOOK",5.20);

--alter TABLE
ALTER TABLE PRODUCTS 
ADD cId INTEGER;

--fetch again
UPDATE PRODUCTS
SET cId = 0001
WHERE pId = 1;

UPDATE PRODUCTS
SET cId = 0002
WHERE pId = 2;

UPDATE PRODUCTS
SET cId = 0003
WHERE pId = 3;

INSERT INTO PRODUCTS VALUES(4,"","",004);

--delete
DELETE FROM PRODUCTS WHERE pId = 4;


--fetch
SELECT cId FROM PRODUCTS;

SELECT item FROM PRODUCTS;

--count 
SELECT COUNT(pId)
FROM PRODUCTS;

--avg
SELECT AVG(price)
FROM PRODUCTS;

INSERT INTO PRODUCTS VALUES(4,"BOX","10.20",0004);

--count 
SELECT COUNT(pId)
FROM PRODUCTS;

--Search
SELECT item
FROM PRODUCTS
WHERE item LIKE 'P%';

CREATE TABLE CUSTOMERS(
cId INTEGER PRIMARY KEY NOT NULL,
product TEXT,
price MONEY
);

INSERT INTO CUSTOMERS (cId, product, price)
SELECT cId,item,price
FROM PRODUCTS;

SELECT price
FROM CUSTOMERS
ORDER BY price DESC;

-- fetch 
SELECT * FROM PRODUCTS;

--fetch
SELECT * FROM CUSTOMERS;