drop TABLE orderp;
drop TABLE orderItem;
drop TABLE customer;
drop TABLE item;

CREATE TABLE customer(
  custid INT PRIMARY KEY,
  cNAME varchar(20),
  city varchar(20)
);

CREATE TABLE orderp(
  orderid INT PRIMARY KEY,
  oDate DATE,
  custid INT,
  oAmount INT,
  FOREIGN KEY (custid) REFERENCES customer(custid)
);

CREATE TABLE item(
  itemid INT PRIMARY KEY,
  unitPrice INT
);

CREATE TABLE orderItem(
  orderid INT,
  itemid INT,
  quant INT,
  FOREIGN KEY (itemid) REFERENCES item(itemid)
);

Insert into customer(custid,cNAME,city) values(11,'shobha','Dhaka');
Insert into customer(custid,cNAME,city) values(12,'shilpa','Jessore');
Insert into customer(custid,cNAME,city) values(13,'sameera','Khulna');
Insert into customer(custid,cNAME,city) values(14,'Shuvo','Bagerhat');
Insert into customer(custid,cNAME,city) values(15,'salam','Valuka');

Insert into item(itemid,unitPrice) values(33,100);
Insert into item(itemid,unitPrice) values(10,150);
Insert into item(itemid,unitPrice) values(3,140);
Insert into item(itemid,unitPrice) values(11,100);
Insert into item(itemid,unitPrice) values(22,101);

Insert into orderp(orderid,oDate,custid,oAmount) values(21,'12-JAN-02',11,1000);
Insert into orderp(orderid,oDate,custid,oAmount) values(22,'12-JAN-02',12,2000);
Insert into orderp(orderid,oDate,custid,oAmount) values(23,'02-JAN-02',11,3000);
Insert into orderp(orderid,oDate,custid,oAmount) values(24,'12-FEB-02',13,4000);
Insert into orderp(orderid,oDate,custid,oAmount) values(25,'12-JAN-02',13,5000);

select * from orderp;

Insert into orderItem(orderid,itemid,quant) values(21,33,3);
Insert into orderItem(orderid,itemid,quant) values(22,10,2);
Insert into orderItem(orderid,itemid,quant) values(23,3,5);
Insert into orderItem(orderid,itemid,quant) values(24,11,4);
Insert into orderItem(orderid,itemid,quant) values(25,22,7);

select * from orderItem;

alter table item add color varchar(20);

describe item;

alter table item modify unitPrice int default 1;