assi6

 --
 CREATE TABLE product_master
 (
    prd_no VARCHAR2(6) primary key,
    prd_desc VARCHAR2(15) ,
    quantity_on_hand NUMBER(8),
    sell_price NUMBER(8,2),
    cost_price NUMBER(8,2),
    constraints ck_sp CHECK(sell_price>0),
    constraints ck_cp CHECK(cost_price>0)
 );

 CREATE TABLE sales_order_detail
 (
    order_no VARCHAR2(5) primary key,
    prd_no VARCHAR2(5),
    qty_order NUMBER(8),
    prd_rate NUMBER(10,2),
    order_date DATE,
    constraints fk_pno foreign key (prd_no) references product_master(prd_no) ON DELETE cascade ,
    constraints ck_qty CHECK(qty_order>0),
    constraints ck_prd CHECK(prd_rate>0)
 );


 --insert
 INSERT INTO  product_master VALUES('p01','Pendrive',20,750,700);
 INSERT INTO  product_master VALUES('p02','Headphones',30,1200,1000);
 INSERT INTO  product_master VALUES('p03','Football',25,950,700);
 INSERT INTO  product_master VALUES('p04','Backpack',12,1000,750);
 INSERT INTO  product_master VALUES('p05','Speakers',52,1500,1200);
 INSERT INTO  product_master VALUES('p06','Smart_t.v.',32,12000,10000);
 INSERT INTO  product_master VALUES('p07','trouser',20,250,150);


 --insert
 INSERT INTO sales_order_detail VALUES('O01','p01',2,950,DATE '2020-05-16');
 INSERT INTO sales_order_detail VALUES('O02','p02',3,1300,DATE '2020-06-01');
 INSERT INTO sales_order_detail VALUES('O03','p03',12,1000,DATE '2020-07-11');
 INSERT INTO sales_order_detail VALUES('O04','p04',1,1100,DATE '2020-07-13');
 INSERT INTO sales_order_detail VALUES('O05','p05',14,1300,DATE '2020-07-30');
 INSERT INTO sales_order_detail VALUES('O06','p06',6,15000,DATE '2020-08-03');
 INSERT INTO sales_order_detail VALUES('O07','p01',4,1000,DATE '2020-07-13');
 INSERT INTO sales_order_detail VALUES('O08','p02',14,1100,DATE '2020-07-15');
 INSERT INTO sales_order_detail VALUES('O09','p03',9,830,DATE '2020-11-01');
 INSERT INTO sales_order_detail VALUES('O10','p04',2,1100,DATE '2020-11-11');

--
--Q1>
SELECT order_no,order_date,prd_desc "product_name",qty_order
FROM  sales_order_detail "s"
join  product_master "p"
ON  "s".prd_no="p".prd_no
WHERE  TO_CHAR(order_date,'mm')>=1 AND TO_CHAR(order_date,'mm')<4 ;

--q2>
SELECT "p".prd_no,prd_desc,quantity_on_hand,sell_price,cost_price
FROM  sales_order_detail "s"
join  product_master "p"
ON  "s".prd_no="p".prd_no
WHERE  TO_CHAR(order_date,'mm')= TO_CHAR(SYSDATE,'mm') ;


--select to_char(sysdate,'mm') from dual;

--Q3>
SELECT *
FROM product_master
WHERE prd_no NOT IN(
                                            SELECT prd_no
                                            FROM  sales_order_detail
                                            GROUP BY prd_no
);

--Q4>
DELETE
FROM product_master
WHERE prd_desc LIKE('J%' )OR prd_desc LIKE('T%');

--Q5>
UPDATE product_master
SET cost_price=50 ,sell_price=100
WHERE prd_desc ='trouser';