Oct/Nov - 2018 Practical-3

--select :-
-----------------------------------------------

CREATE TABLE category
(
    categoryid NUMBER(10) primary key,
    categoryname VARCHAR(40),
    CHECK(categoryname IN('electronics','sports','fashion and lifestyle','home and furniture'))
);


CREATE TABLE product1
(
    productid NUMBER(10) primary key,
    categoryid NUMBER(10) references category(categoryid) ON DELETE cascade,
    productname VARCHAR(30),
    price NUMBER(10) CHECK(price>0),
    discount NUMBER(10)
);

CREATE TABLE auditoffer
(
    offerid NUMBER(10) primary key,
    categoryid NUMBER(10) references category(categoryid) ON DELETE cascade,
    productid NUMBER(10) references product1(productid) ON DELETE cascade,
    discount NUMBER(10)
);

--insert data:-
-----------------------------------------------

1.) INSERT INTO category VALUES(101,'electronics');
    INSERT INTO category VALUES(102,'sports');
    INSERT INTO category VALUES(103,'fashion and lifestyle');
    INSERT INTO category VALUES(104,'home and furniture');

2.) INSERT INTO product1 VALUES(1,101,'bulbs',50,0);
    INSERT INTO product1 VALUES(2,102,'basket ball',300,0);
    INSERT INTO product1 VALUES(3,103,'tshirt',500,0);
    INSERT INTO product1 VALUES(4,104,'table',600,0);
    INSERT INTO product1 VALUES(5,101,'fairy lights',200,0);
    INSERT INTO product1 VALUES(6,103,'shoes',4000,0);
    INSERT INTO product1 VALUES(7,104,'chair',1700,0);

3.) INSERT INTO auditoffer VALUES(201,101,1,10);
    INSERT INTO auditoffer VALUES(202,102,2,20);
    INSERT INTO auditoffer VALUES(203,103,3,40);
    INSERT INTO auditoffer VALUES(204,104,4,50);
    INSERT INTO auditoffer VALUES(205,101,5,30);
    INSERT INTO auditoffer VALUES(206,103,6,20);
    INSERT INTO auditoffer VALUES(207,104,7,60);