Advertisement
tsnaik

DBS lab 4 (incomplete)

Aug 4th, 2015
1,184
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  CREATE TABLE challan_header
  2.   (
  3.      challan_no   VARCHAR2(6) PRIMARY KEY,
  4.      s_order_no   VARCHAR2(6) REFERENCES sales_order(s_order_no),
  5.      challan_date DATE NOT NULL,
  6.      billed_yn    CHAR(1) DEFAULT 'N' CHECK (billed_yn IN ('Y', 'N'))
  7.   );
  8.  
  9. CREATE TABLE challan_details
  10.   (
  11.      challan_no VARCHAR2(6) REFERENCES challan_header(challan_no),
  12.      product_no VARCHAR2(10) REFERENCES product_master(product_no),
  13.      qty_disp   NUMBER(4, 2) NOT NULL,
  14.      PRIMARY KEY(challan_no, product_no)
  15.   );
  16.  
  17.  
  18. INSERT INTO sales_order
  19.             (s_order_no,
  20.              s_order_date,
  21.              client_no,
  22.              dely_type,
  23.              billed_yn,
  24.              salesman_no,
  25.              dely_date,
  26.              order_status)
  27. VALUES     ('046865',
  28.             '20-may-1996',
  29.             '0004',
  30.             'f',
  31.             'y',
  32.             '500002',
  33.             '22-may-1996',
  34.             'canceled');
  35.  
  36. INSERT INTO sales_order
  37.             (s_order_no,
  38.              s_order_date,
  39.              client_no,
  40.              dely_type,
  41.              billed_yn,
  42.              salesman_no,
  43.              dely_date,
  44.              order_status)
  45. VALUES     ('010008',
  46.             '24-may-1996',
  47.             '0005',
  48.             'f',
  49.             'n',
  50.             '500004',
  51.             '26-may-1996',
  52.             'in process');
  53.            
  54. INSERT INTO challan_header
  55. VALUES     ('CH9001',
  56.             '019001',
  57.             '12-dec-1995',
  58.             'Y');
  59.  
  60.  
  61. INSERT INTO challan_header
  62. VALUES     ('CH865',
  63.             '046865',
  64.             '12-nov-1995',
  65.             'Y');
  66.  
  67. INSERT INTO challan_header
  68. VALUES     ('CH3965',
  69.             '010008',
  70.             '12-oct-1995',
  71.             'Y');
  72.  
  73.  
  74. INSERT ALL
  75.  
  76. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH9001', 'P00001', '4')
  77. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH9001' ,'P07965' ,'1')
  78. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH9001', 'P07885', '1')
  79. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH865', 'P07868' ,'3')
  80. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH865', 'P03453', '4')
  81. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH865', 'P00001', '10')
  82. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH3965', 'P00001' ,'5')
  83. INTO challan_details(challan_no,product_no,qty_disp) VALUES('CH3965', 'P07975' ,'2')
  84. SELECT * FROM dual;
Advertisement
RAW Paste Data Copied
Advertisement