API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 7th, 2016
97
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.10 KB | None | 0 0
raw download clone embed print report
  1. DROP TABLE order_item CASCADE CONSTRAINTS;
  2. DROP TABLE product CASCADE CONSTRAINTS;
  3. DROP TABLE product_category_link CASCADE CONSTRAINTS;
  4. DROP TABLE product_category CASCADE CONSTRAINTS;
  5. DROP TABLE delivery CASCADE CONSTRAINTS;
  6. DROP TABLE payment_link CASCADE CONSTRAINTS;
  7. DROP TABLE order_table CASCADE CONSTRAINTS;
  8. DROP TABLE payment CASCADE CONSTRAINTS;
  9. DROP TABLE commercial CASCADE CONSTRAINTS;
  10. DROP TABLE customer CASCADE CONSTRAINTS;
  11.  
  12. CREATE TABLE customer (
  13. customer_ID VARCHAR(8),
  14. customer_name VARCHAR(30) CONSTRAINT customer_name_nn not null,
  15. email VARCHAR(60) CONSTRAINT customer_email_constraint check (email like '%_@_%._%'),
  16. phone_number NUMBER(11),
  17. house_number VARCHAR(5) CONSTRAINT customer_houseno_nn not null,
  18. postcode VARCHAR(7)CONSTRAINT customer_postcode_nn not null,
  19. CONSTRAINT customer_PK PRIMARY KEY (customer_ID));
  20.  
  21. CREATE TABLE commercial (
  22. company_registration_number VARCHAR(8),
  23. company_name VARCHAR(30)CONSTRAINT company_name_nn not null,
  24. authentication_code VARCHAR(6)CONSTRAINT company_auth_nn not null,
  25. customer_ID VARCHAR(8),
  26. CONSTRAINT company_PK PRIMARY KEY (company_registration_number),
  27. CONSTRAINT customer_FK_CT FOREIGN KEY (customer_ID) REFERENCES customer(customer_ID));
  28.  
  29. CREATE TABLE payment (
  30. payment_ID VARCHAR(8),
  31. payment_method VARCHAR(25) CONSTRAINT payment_method_nn not null,
  32. date_of_payment DATE CONSTRAINT payment_date_nn not null,
  33. CONSTRAINT payment_PK PRIMARY KEY (payment_ID));
  34.  
  35. CREATE TABLE order_table (
  36. order_ID VARCHAR(8),
  37. customer_ID VARCHAR(8),
  38. order_date DATE CONSTRAINT order_table_date_nn not null,
  39. CONSTRAINT order_PK PRIMARY KEY (order_ID),
  40. CONSTRAINT customer_FK_OT FOREIGN KEY (customer_ID) REFERENCES customer(customer_ID));
  41.  
  42. CREATE TABLE payment_link (
  43. payment_link_ID VARCHAR(8),
  44. order_ID VARCHAR(8),
  45. to_pay NUMBER(19,4) CONSTRAINT to_pay_nn not null,
  46. CONSTRAINT payment_link_CK PRIMARY KEY (payment_link_ID, order_ID),
  47. CONSTRAINT payment_link_FK1 FOREIGN KEY (payment_link_ID) REFERENCES payment(payment_ID),
  48. CONSTRAINT payment_link_FK2 FOREIGN KEY (order_ID) REFERENCES order_table(order_ID));
  49.  
  50. CREATE TABLE delivery (
  51. delivery_ID VARCHAR(8),
  52. postcode VARCHAR(7) CONSTRAINT delivery_postcode_nn not null,
  53. house_number NUMBER(5) CONSTRAINT delivery_houseno_nn not null,
  54. delivered CHAR(1) CONSTRAINT delivery_delivered_nn not null,
  55. delivery_timestamp DATE CONSTRAINT delivery_date_nn not null,
  56. CONSTRAINT delivery_PK PRIMARY KEY (delivery_ID));
  57.  
  58. CREATE TABLE product (
  59. product_ID VARCHAR(8),
  60. product_name VARCHAR(30) CONSTRAINT product_name_nn not null,
  61. product_description VARCHAR(150) CONSTRAINT product_desc_nn not null,
  62. product_price NUMBER(19,4) CONSTRAINT payment_price_nn not null,
  63. CONSTRAINT product_PK PRIMARY KEY (product_ID));
  64.  
  65. CREATE TABLE product_category (
  66. product_category_ID VARCHAR(8),
  67. product_category VARCHAR(20),
  68. CONSTRAINT product_category_PK PRIMARY KEY (product_category_ID));
  69.  
  70. CREATE TABLE product_category_link (
  71. product_ID VARCHAR(8),
  72. product_category_ID VARCHAR(8),
  73. CONSTRAINT product_CK PRIMARY KEY (product_ID, product_category_ID),
  74. CONSTRAINT product_link_FK1 FOREIGN KEY (product_ID) REFERENCES product(product_ID),
  75. CONSTRAINT product_link_FK2 FOREIGN KEY (product_category_ID) REFERENCES product_category(product_category_ID));
  76.  
  77. CREATE TABLE order_item (
  78. order_item_ID VARCHAR(8),
  79. product_ID VARCHAR(8),
  80. delivery_ID VARCHAR(8),
  81. order_ID VARCHAR(8),
  82. CONSTRAINT order_item_PK PRIMARY KEY (order_item_ID),
  83. CONSTRAINT product_FK_OIT FOREIGN KEY (product_ID) REFERENCES product(product_ID),
  84. CONSTRAINT delivery_FK_OIT FOREIGN KEY (delivery_ID) REFERENCES delivery(delivery_ID),
  85. CONSTRAINT order_FK_OIT FOREIGN KEY (order_ID) REFERENCES order_table(order_ID));
  86.  
  87. --customer
  88. INSERT INTO customer VALUES ('01', 'Sean Sparshott', 'sean.sparshott@yahoo.com', '07540700362', '3', 'so456ha');
  89. INSERT INTO customer VALUES ('02', 'John Smith', 'john.smith@yahoo.com', '07540710232', '56', 'sh736sa');
  90. INSERT INTO customer VALUES ('03', 'Kang Kong', 'kang.kong@yahoo.com', '07540756342', '33', 'bg467hg');
  91. INSERT INTO customer VALUES ('04', 'Chaswinder Singh', 'chaz.singh@yahoo.com', '07574709462', '67b', 'sh768ga');
  92. INSERT INTO customer VALUES ('05', 'Martin Smith', 'm.smith@yahoo.com', '07785433453', '67', 'jg655hg');
  93. INSERT INTO customer VALUES ('06', 'Sam Shaw', 'sam.shaw@yahoo.com', '07235463464', '22', 'ji877aj');
  94. INSERT INTO customer VALUES ('07', 'Max Anderson', 'max.anderson@yahoo.com', '07465678996', '108', 'kl344hj');
  95. INSERT INTO customer VALUES ('08', 'James Scott', 'james.scott@yahoo.com', '07357899044', '99', 'gf835lk');
  96. INSERT INTO customer VALUES ('09', 'Lia Tootsie', 'lia.tootsie@yahoo.com', '07345632690', '345', 'lk835gh');
  97. INSERT INTO customer VALUES ('10', 'Sean Sparshott', 'sean.sparshott@yahoo.com', '07964365846', '97c', 'fi635li');
  98. --commercial
  99. INSERT INTO commercial VALUES ('82746378', 'Master Builders', 'A28734', '01');
  100. INSERT INTO commercial VALUES ('88739475', 'Builders Unite', 'A35983', '03');
  101. INSERT INTO commercial VALUES ('90832544', 'Bricks were laid', 'A96833', '04');
  102. INSERT INTO commercial VALUES ('82340987', 'I know a guy', 'A12389', '05');
  103. INSERT INTO commercial VALUES ('82732487', 'Screwen nails', 'A43573', '07');
  104. INSERT INTO commercial VALUES ('81239853', 'Building Company', 'A87365', '08');
  105. INSERT INTO commercial VALUES ('86439083', 'Your local builders', 'A99893', '09');
  106. --payment
  107. INSERT INTO payment VALUES ('01', 'visa', TO_DATE('15/SEP/2015', 'dd/MON/yyyy'));
  108. INSERT INTO payment VALUES ('02', 'visa', TO_DATE('16/SEP/2015', 'dd/MON/yyyy'));
  109. INSERT INTO payment VALUES ('03', 'paypal', TO_DATE('20/OCT/2015', 'dd/MON/yyyy'));
  110. INSERT INTO payment VALUES ('04', 'visa', TO_DATE('22/OCT/2015', 'dd/MON/yyyy'));
  111. INSERT INTO payment VALUES ('05', 'paypal', TO_DATE('12/DEC/2015', 'dd/MON/yyyy'));
  112. INSERT INTO payment VALUES ('06', 'visa', TO_DATE('18/JAN/2016', 'dd/MON/yyyy'));
  113. INSERT INTO payment VALUES ('07', 'visa', TO_DATE('06/MAR/2016', 'dd/MON/yyyy'));
  114. INSERT INTO payment VALUES ('08', 'paypal', TO_DATE('03/MAR/2016', 'dd/MON/yyyy'));
  115. INSERT INTO payment VALUES ('09', 'payment on collection', TO_DATE('19/JUN/2016', 'dd/MON/yyyy'));
  116. INSERT INTO payment VALUES ('10', 'paypal', TO_DATE('11/JUL/2016', 'dd/MON/yyyy'));
  117. INSERT INTO payment VALUES ('11', 'paypal', TO_DATE('14/JUL/2016', 'dd/MON/yyyy'));
  118. INSERT INTO payment VALUES ('12', 'visa', TO_DATE('22/AUG/2016', 'dd/MON/yyyy'));
  119. INSERT INTO payment VALUES ('13', 'payment on collection', TO_DATE('23/AUG/2016', 'dd/MON/yyyy'));
  120. INSERT INTO payment VALUES ('14', 'payment on collection', TO_DATE('28/AUG/2016', 'dd/MON/yyyy'));
  121. INSERT INTO payment VALUES ('15', 'visa', TO_DATE('09/SEP/2016', 'dd/MON/yyyy'));
  122. INSERT INTO payment VALUES ('16', 'visa', TO_DATE('11/SEP/2016', 'dd/MON/yyyy'));
  123. INSERT INTO payment VALUES ('17', 'paypal', TO_DATE('17/SEP/2016', 'dd/MON/yyyy'));
  124. INSERT INTO payment VALUES ('18', 'paypal', TO_DATE('17/OCT/2016', 'dd/MON/yyyy'));
  125. INSERT INTO payment VALUES ('19', 'visa', TO_DATE('11/DEC/2016', 'dd/MON/yyyy'));
  126. INSERT INTO payment VALUES ('20', 'payment on collection', TO_DATE('11/DEC/2016', 'dd/MON/yyyy'));
  127. --order_table
  128. INSERT INTO order_table VALUES ('01', '01', '18/SEP/2015');
  129. INSERT INTO order_table VALUES ('02', '02', '19/SEP/2015');
  130. INSERT INTO order_table VALUES ('03', '03', '25/OCT/2015');
  131. INSERT INTO order_table VALUES ('04', '04', '25/OCT/2015');
  132. INSERT INTO order_table VALUES ('05', '05', '17/DEC/2015');
  133. INSERT INTO order_table VALUES ('06', '06', '21/JAN/2016');
  134. INSERT INTO order_table VALUES ('07', '07', '07/MAR/2016');
  135. INSERT INTO order_table VALUES ('08', '08', '05/MAR/2016');
  136. INSERT INTO order_table VALUES ('09', '09', '20/JUN/2016');
  137. INSERT INTO order_table VALUES ('10', '03', '16/JUL/2016');
  138. INSERT INTO order_table VALUES ('11', '01', '16/JUL/2016');
  139. INSERT INTO order_table VALUES ('12', '07', '16/JUL/2016');
  140. INSERT INTO order_table VALUES ('13', '07', '16/JUL/2016');
  141. INSERT INTO order_table VALUES ('14', '05', '16/JUL/2016');
  142. INSERT INTO order_table VALUES ('15', '07', '16/JUL/2016');
  143. --payment_link
  144. INSERT INTO payment_link VALUES ('01', '01', '23.46');
  145. INSERT INTO payment_link VALUES ('02', '12', '233.26');
  146. INSERT INTO payment_link VALUES ('03', '07', '47.46');
  147. INSERT INTO payment_link VALUES ('04', '14', '9.15');
  148. INSERT INTO payment_link VALUES ('05', '05', '123.45');
  149. INSERT INTO payment_link VALUES ('06', '05', '543.21');
  150. INSERT INTO payment_link VALUES ('07', '07', '11.11');
  151. INSERT INTO payment_link VALUES ('08', '14', '21.34');
  152. INSERT INTO payment_link VALUES ('09', '09', '258.34');
  153. INSERT INTO payment_link VALUES ('10', '02', '982.12');
  154. INSERT INTO payment_link VALUES ('11', '02', '100011.34');
  155. INSERT INTO payment_link VALUES ('12', '12', '1004.23');
  156. INSERT INTO payment_link VALUES ('13', '07', '210.34');
  157. INSERT INTO payment_link VALUES ('14', '13', '9833.78');
  158. INSERT INTO payment_link VALUES ('15', '05', '212.44');
  159. INSERT INTO payment_link VALUES ('16', '07', '21.34');
  160. INSERT INTO payment_link VALUES ('17', '01', '21.34');
  161. INSERT INTO payment_link VALUES ('18', '14', '21.34');
  162. INSERT INTO payment_link VALUES ('19', '11', '21.34');
  163. INSERT INTO payment_link VALUES ('20', '03', '21.34');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!