DBS lab3

lab3

*refer 2 PDFs. ema badhu chhe j ;)

english <-> db
noun <-> entity
verb <-> relation



CREATE TABLE salesman_master
  (
     salesman_no VARCHAR2(6) PRIMARY KEY CHECK(salesman_no LIKE '5%'),
     sal_name    VARCHAR2(20) NOT NULL,
     address     VARCHAR2(30) NOT NULL,
     city        VARCHAR2(20),
     state       VARCHAR2(20),
     pincode     NUMBER(6),
     sal_amt     NUMBER(8, 2) CHECK(sal_amt != 0),
     tgt_to_get  NUMBER(6, 2) CHECK(tgt_to_get != 0),
     ytd_sales   NUMBER(6, 2) CHECK(ytd_sales != 0),
     remarks     VARCHAR2(30)
  );





INSERT INTO salesman_master
            (salesman_no,
             sal_name,
             address,
             city,
             pincode,
             state,
             sal_amt,
             tgt_to_get,
             ytd_sales,
             remarks)
VALUES      ('500001',
             'Kiran',
             'A/14 Worli',
             'Bombay',
             400002,
             'Mah',
             3000,
             100,
             50,
             'Good');

INSERT INTO salesman_master
            (salesman_no,
             sal_name,
             address,
             city,
             pincode,
             state,
             sal_amt,
             tgt_to_get,
             ytd_sales,
             remarks)
VALUES      ('500002',
             'Manish',
             '65 Nariman',
             'Bombay',
             400001,
             'Mah',
             3000,
             200,
             100,
             'Good');

INSERT INTO salesman_master
            (salesman_no,
             sal_name,
             address,
             city,
             pincode,
             state,
             sal_amt,
             tgt_to_get,
             ytd_sales,
             remarks)
VALUES      ('500003',
             'Ravi',
             'P-7 Bandra',
             'Bombay',
             400032,
             'Mah',
             3000,
             200,
             100,
             'Good');

INSERT INTO salesman_master
            (salesman_no,
             sal_name,
             address,
             city,
             pincode,
             state,
             sal_amt,
             tgt_to_get,
             ytd_sales,
             remarks)
VALUES      ('500004',
             'Ashish',
             'A/5 Juhu',
             'Bombay',
             400044,
             'Mah',
             3500,
             200,
             150,
             'Good');




CREATE TABLE sales_order
  (
     s_order_no   VARCHAR2(6) PRIMARY KEY CHECK(s_order_no LIKE '0%'),
     s_order_date DATE,
     client_no    VARCHAR2(25) REFERENCES client_master(client_no),
     dely_add     VARCHAR2(6),
     salesman_no  VARCHAR2(6) REFERENCES salesman_master(salesman_no),
     dely_type    CHAR(1) DEFAULT 'f' CHECK (dely_type IN('p', 'f') ),
     billed_yn    CHAR(1),
     dely_date    DATE,
     order_status VARCHAR2(15) CHECK (order_status IN ('in process', 'fulfilled'
     ,
     'back order', 'canceled')),
     CHECK (s_order_date<dely_date)
  );



insert all
into sales_order(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_status) values('016865','18-feb-1996','0003','f','y','500003','20-feb-1996','fulfilled')

into
sales_order(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_status) values('019003','03-apr-1996','0001','f','y','500001','07-apr-1996','fulfilled')

into
sales_order(s_order_no,s_order_date,client_no,dely_type,billed_yn,salesman_no,dely_date,order_status) values('046866','20-may-1996','0004','p','n','500002','22-may-1996','canceled')




select * from dual;