API tools faq
paste
Advertisement
Guest User

SQL

a guest
Dec 8th, 2016
203
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.76 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 VARCHAR(5) CONSTRAINT delivery_houseno_nn not null,
  54. delivered CHAR(1) CONSTRAINT delivery_delivered_nn not null,
  55. delivery_timestamp DATE,
  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', '25/OCT/2015');
  138. INSERT INTO order_table VALUES ('11', '01', '18/SEP/2015');
  139. INSERT INTO order_table VALUES ('12', '07', '07/MAR/2016');
  140. INSERT INTO order_table VALUES ('13', '07', '07/MAR/2016');
  141. INSERT INTO order_table VALUES ('14', '05', '17/MAR/2015');
  142. INSERT INTO order_table VALUES ('15', '10', '16/AUG/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', '15', '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', '56.84');
  161. INSERT INTO payment_link VALUES ('18', '14', '1.23');
  162. INSERT INTO payment_link VALUES ('19', '11', '9820.34');
  163. INSERT INTO payment_link VALUES ('20', '03', '55551.65');
  164. --delivery
  165. INSERT INTO delivery VALUES('01','so456ha','3','Y',TO_DATE('16/SEP/2015 11:30:32', 'dd/MON/yyyy hh24:mi:ss'));
  166. INSERT INTO delivery VALUES('02','sh736sa','56','N',NULL);
  167. INSERT INTO delivery VALUES('03','bg467hg','33','Y',TO_DATE('23/OCT/2015 14:25:32', 'dd/MON/yyyy hh24:mi:ss'));
  168. INSERT INTO delivery VALUES('04','sh768ga','67b','N',NULL);
  169. INSERT INTO delivery VALUES('05','jg655hg','67','Y',TO_DATE('07/MAR/2016 15:12:32', 'dd/MON/yyyy hh24:mi:ss'));
  170. INSERT INTO delivery VALUES('06','ji877aj','22','N',NULL);
  171. INSERT INTO delivery VALUES('07','kl344hj','108','Y',TO_DATE('13/JUL/2016 15:35:32', 'dd/MON/yyyy hh24:mi:ss'));
  172. INSERT INTO delivery VALUES('08','gf835lk','99','N',NULL);
  173. INSERT INTO delivery VALUES('09','lk835gh','345','Y',TO_DATE('20/JUL/2016 17:09:32', 'dd/MON/yyyy hh24:mi:ss'));
  174. INSERT INTO delivery VALUES('10','fi635li','97c','N',NULL);
  175. INSERT INTO delivery VALUES('11','gf835lk','99','Y',TO_DATE('22/AUG/2016 18:56:32', 'dd/MON/yyyy hh24:mi:ss'));
  176. INSERT INTO delivery VALUES('12','so456ha','3','N',NULL);
  177. INSERT INTO delivery VALUES('13','jg655hg','67','Y',TO_DATE('17/OCT/2016 19:22:32', 'dd/MON/yyyy hh24:mi:ss'));
  178. --product
  179. INSERT INTO product VALUES ('01', 'Blue Paint', 'Paint that is blue for your walls', '22.50');
  180. INSERT INTO product VALUES ('02', 'Batteries', 'Pack of 4x AAA Batteries', '8.99');
  181. INSERT INTO product VALUES ('03', 'Wood Nails', 'Box of 200 flat headed wood nails', '20.25');
  182. INSERT INTO product VALUES ('04', 'Cement Mixture', 'A packet of cement mixture', '75.00');
  183. INSERT INTO product VALUES ('05', 'Hammer Set', 'A set of your everyday building hammers', '180.00');
  184. INSERT INTO product VALUES ('06', 'Electric Drill Set', 'A set of your everyday building electric drills with bits', '220.99');
  185. INSERT INTO product VALUES ('07', 'Crane', 'A lifting vehicle that is suitable for building structures', '110000.20');
  186. INSERT INTO product VALUES ('08', 'Digger', 'A digging vehicale for all your big digging needs', '75000.99');
  187. --product_catagory
  188. INSERT INTO product_category VALUES ('01', 'Household materials');
  189. INSERT INTO product_category VALUES ('02', 'Buidling Materials');
  190. INSERT INTO product_category VALUES ('03', 'Building Tools');
  191. INSERT INTO product_category VALUES ('04', 'Vehicles');
  192. --product_category_link
  193. INSERT INTO product_category_link VALUES ('01', '01');
  194. INSERT INTO product_category_link VALUES ('02', '01');
  195. INSERT INTO product_category_link VALUES ('03', '02');
  196. INSERT INTO product_category_link VALUES ('04', '02');
  197. INSERT INTO product_category_link VALUES ('05', '03');
  198. INSERT INTO product_category_link VALUES ('06', '03');
  199. INSERT INTO product_category_link VALUES ('07', '04');
  200. INSERT INTO product_category_link VALUES ('08', '04');
  201. --order_item, some items may not add up to total value in payment link, this is due to items not being fully paid for. E.g. a product being paid on finance.
  202. INSERT INTO order_item VALUES ('01', '01', '01', '01');
  203. INSERT INTO order_item VALUES ('02', '01', '01', '11');
  204. INSERT INTO order_item VALUES ('03', '02', '02', '07');
  205. INSERT INTO order_item VALUES ('04', '07', '09', '02');
  206. INSERT INTO order_item VALUES ('06', '08', '13', '03');
  207. INSERT INTO order_item VALUES ('07', '03', '11', '07');
  208. INSERT INTO order_item VALUES ('08', '03', '11', '15');
  209. INSERT INTO order_item VALUES ('09', '04', '11', '12');
  210. INSERT INTO order_item VALUES ('10', '04', '11', '07');
  211. INSERT INTO order_item VALUES ('11', '05', '03', '12');
  212. INSERT INTO order_item VALUES ('12', '05', '04', '01');
  213. INSERT INTO order_item VALUES ('13', '05', '04', '11');
  214. INSERT INTO order_item VALUES ('14', '06', '03', '12');
  215. INSERT INTO order_item VALUES ('15', '04', '05', '08');
  216. INSERT INTO order_item VALUES ('16', '06', '06', '09');
  217. INSERT INTO order_item VALUES ('17', '04', '06', '12');
  218. INSERT INTO order_item VALUES ('18', '06', '07', '14');
  219. INSERT INTO order_item VALUES ('19', '04', '08', '15');
  220. INSERT INTO order_item VALUES ('20', '05', '10', '13');
  221. INSERT INTO order_item VALUES ('21', '03', '12', '13');
  222. INSERT INTO order_item VALUES ('22', '04', '12', '11');
  223.  
  224. SELECT *
  225. FROM customer order by customer_ID;
  226.  
  227. SELECT * FROM customer, commercial
  228. where customer.customer_id = commercial.customer_id
  229. order by customer_name;
  230.  
  231. SELECT * FROM customer, commercial, order_table
  232. where customer.customer_id = commercial.customer_id and customer.customer_id = order_table.customer_id
  233. order by customer.customer_id;
  234.  
  235. SELECT customer.customer_name,
  236. delivery.postcode,
  237. delivery.house_number
  238. FROM customer, order_table, order_item, delivery
  239. where customer.customer_id = order_table.customer_id and
  240. order_table.order_id = order_item.order_id and
  241. order_item.delivery_id = delivery.delivery_id
  242. order by customer.customer_name;
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!