SHARE
TWEET

Untitled

a guest Feb 24th, 2020 72 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. PRAGMA foreign_keys = ON;
  2.  
  3. DROP TABLE IF EXISTS PurchasingOrderItems;
  4. DROP TABLE IF EXISTS CustomerOrderItems;
  5. DROP TABLE IF EXISTS SupplierItems;
  6. DROP TABLE IF EXISTS WarehouseItems;
  7. DROP TABLE IF EXISTS PurchasingOrder;
  8. DROP TABLE IF EXISTS Suppliers;
  9. DROP TABLE IF EXISTS MadeOrders;
  10. DROP TABLE IF EXISTS Items;
  11. DROP TABLE IF EXISTS WarehouseLocation;
  12. DROP TABLE IF EXISTS Customers;
  13. DROP TABLE IF EXISTS CustomerOrders;
  14.  
  15. -- HERE CREATE TABLES DEFINITIONS -- COMPILE ENTIRE SCRIPT ON https://kripken.github.io/sql.js/GUI/
  16.  
  17. CREATE TABLE Suppliers (SuppliersCode INTEGER PRIMARY KEY,
  18. SupplierName        TEXT,
  19. Country         TEXT,
  20. City            TEXT,
  21. Code            INTEGER,
  22. Email           TEXT);
  23.  
  24. CREATE TABLE Items (ItemsCode INTEGER PRIMARY KEY,
  25. Name        TEXT,
  26. Details     TEXT,
  27. ItemCode    FLOAT);
  28.  
  29. CREATE TABLE PurchasingOrder (PONumber INTEGER PRIMARY KEY,
  30. SupplierCode INTEGER,
  31. PaidDate    TEXT,
  32. RecivedDate TEXT
  33. );
  34.  
  35. CREATE TABLE WarehouseLocation (Location INTEGER PRIMARY KEY);
  36.  
  37. CREATE TABLE CustomerOrders (CustomerOrderNumber INTEGER PRIMARY KEY,
  38. PaidDate    TEXT,
  39. OutgoingDate    TEXT);
  40.  
  41. CREATE TABLE Customers(CustomerCode INTEGER PRIMARY KEY,
  42. CustomerName        TEXT,
  43. Country     TEXT,
  44. City        TEXT,
  45. Street      TEXT,
  46. PhoneNumber TEXT,
  47. Email       TEXT);
  48.  
  49.  
  50.  
  51.  
  52.  
  53. CREATE TABLE SupplierItems (SupplierCode INTEGER,
  54. ItemCode INTEGER,
  55. ItemPrice FLOAT,
  56. PRIMARY KEY (SupplierCode, ItemCode),
  57. FOREIGN KEY (SupplierCode) REFERENCES Suppliers (SuppliersCode),
  58. FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
  59. );
  60.  
  61. CREATE TABLE PurchasingOrderItems (PurchasingOrderNumber INTEGER,
  62. ItemCode INTEGER,
  63. ItemQuantity FLOAT,
  64. PRIMARY KEY (PurchasingOrderNumber, ItemCode),
  65. FOREIGN KEY (PurchasingOrderNumber) REFERENCES PurchasingOrder (PONumber),
  66. FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
  67. );
  68.  
  69. CREATE TABLE CustomerOrderItems (CustomerOrderNumber INTEGER,  
  70. ItemCode    TEXT,
  71. ItemQuantity    TEXT,
  72. PRIMARY KEY (CustomerOrderNumber, ItemCode)
  73. FOREIGN KEY (CustomerOrderNumber) REFERENCES CustomerOrders(CustomerOrderNumber)
  74. FOREIGN KEY (ItemCode) REFERENCES Items(ItemsCode)
  75. );
  76.  
  77.  
  78.  
  79. -- HERE CREATE TABLES DEFINITIONS
  80.  
  81. --INPUT DATA FROM SESSION 1
  82. INSERT INTO Suppliers VALUES( 1, "Sandvik", "Norway", "Sandviken", 555666, "sandvik@gazeta.pl" );
  83. INSERT INTO Suppliers VALUES( 2, "CocaCola", "USA", "New York", 888333, "coke@gazeta.pl" );
  84. INSERT INTO Suppliers VALUES( 3, "KFC", "USA", "Chicago", 999222, "kfc@gazeta.pl" );
  85.  
  86. INSERT INTO Items VALUES( 1, "Chicken", "Tasty", 5.5 );
  87. INSERT INTO Items VALUES( 2, "Leg", "Delicious", 4 );
  88. INSERT INTO Items VALUES( 3, "Omelette", "Ordinary", 3 );
  89. INSERT INTO Items VALUES( 4, "Pizza", "Ugly", 12.5 );
  90. INSERT INTO Items VALUES( 5, "Fanta", "When Thirsty", 2.4 );
  91. INSERT INTO Items VALUES( 6, "Cola", "Refreshing", 3 );
  92. INSERT INTO Items VALUES( 7, "Screw", "Decent", 5.5 );
  93. INSERT INTO Items VALUES( 8, "Scissors", "Indecent", 7.5 );
  94. INSERT INTO Items VALUES( 9, "Nail", "Firm", 8.5 );
  95. INSERT INTO Items VALUES(10, "Decanter", "Spacious", 10 );
  96.  
  97.  
  98.  
  99.  
  100. INSERT INTO Customers VALUES ( 1, 'Mitch Ducanon', 'USA', 'Miami', 'Ocean Street', '+1 8376456', 'mitch@hotmail.com');
  101. INSERT INTO Customers VALUES ( 2, 'Deynn', 'Poland', 'Sosnowiec', 'Na Ostatnim Groszu', '+48 398723', 'deynn@onet.pl');
  102. INSERT INTO Customers VALUES ( 3, 'Angela Merkel', 'Germany', 'Berlin', 'Bullowstrasse', '+49 2134908', 'angie@spiegel.com');
  103. INSERT INTO Customers VALUES ( 4, 'Patrick Bruel', 'France', 'Pari', 'Frog Street', '+33 8376456', 'bruel@hotmail.com');
  104. INSERT INTO Customers VALUES ( 5, 'Gianni Versace', 'Italy', 'Rome', 'Macaroni Street', '+39 8376456', 'gianni@hotmail.com');
  105.  
  106.  
  107. INSERT INTO CustomerOrders VALUES ( 1, '4.09.2017', '12.09.2017');
  108. INSERT INTO CustomerOrders VALUES ( 2, '1.09.2017', '14.09.2017');
  109. INSERT INTO CustomerOrders VALUES ( 3, '5.09.2017', '18.09.2017');
  110. INSERT INTO CustomerOrders VALUES ( 4, NULL, NULL);
  111. INSERT INTO CustomerOrders VALUES ( 5, '10.09.2017', '15.09.2017');
  112. INSERT INTO CustomerOrders VALUES ( 6, '8.09.2017', NULL);
  113. INSERT INTO CustomerOrders VALUES ( 7, '4.09.2017', '14.09.2017');
  114. INSERT INTO CustomerOrders VALUES ( 8, '2.09.2017', '13.09.2017');
  115.  
  116.  
  117. INSERT INTO WarehouseLocation VALUES( 1 );
  118. INSERT INTO WarehouseLocation VALUES( 2 );
  119. INSERT INTO WarehouseLocation VALUES( 3 );
  120. INSERT INTO WarehouseLocation VALUES( 4 );
  121. INSERT INTO WarehouseLocation VALUES( 5 );
  122.  
  123.  
  124. INSERT INTO PurchasingOrder VALUES( 1, 1, NULL, "01.01.2017");
  125. INSERT INTO PurchasingOrder VALUES( 2, 1, "02.01.2017", "02.01.2017");
  126. INSERT INTO PurchasingOrder VALUES( 3, 1, "02.01.2017", "03.01.2017");
  127. INSERT INTO PurchasingOrder VALUES( 4, 2, "02.01.2017", "04.01.2017");
  128. INSERT INTO PurchasingOrder VALUES( 5, 2, "02.01.2017", "05.01.2017");
  129. INSERT INTO PurchasingOrder VALUES( 6, 3, "02.01.2017", "06.01.2017");
  130. INSERT INTO PurchasingOrder VALUES( 7, 3, "02.01.2017", "07.01.2017");
  131. INSERT INTO PurchasingOrder VALUES( 8, 3, "02.01.2017", "08.01.2017");
  132. INSERT INTO PurchasingOrder VALUES( 9, 3, "09.01.2017", NULL);
  133.  
  134.  
  135.  
  136. INSERT INTO SupplierItems VALUES( 1, 7, 3 );
  137. INSERT INTO SupplierItems VALUES( 1, 8, 5 );
  138. INSERT INTO SupplierItems VALUES( 1, 9, 6.5 );
  139. INSERT INTO SupplierItems VALUES( 1,10, 7 );
  140. INSERT INTO SupplierItems VALUES( 2,10, 8 );
  141. INSERT INTO SupplierItems VALUES( 2, 5, 2 );
  142. INSERT INTO SupplierItems VALUES( 2, 6, 2.5 );
  143. INSERT INTO SupplierItems VALUES( 3, 7, 2 );
  144. INSERT INTO SupplierItems VALUES( 3, 8, 4 );
  145. INSERT INTO SupplierItems VALUES( 3, 9, 6 );
  146. INSERT INTO SupplierItems VALUES( 3,10, 5 );
  147. INSERT INTO SupplierItems VALUES( 3, 5, 1 );
  148. INSERT INTO SupplierItems VALUES( 3, 6, 2 );
  149. INSERT INTO SupplierItems VALUES( 3, 1, 3 );
  150. INSERT INTO SupplierItems VALUES( 3, 2, 2 );
  151. INSERT INTO SupplierItems VALUES( 3, 3, 2.2 );
  152. INSERT INTO SupplierItems VALUES( 3, 4, 8 );
  153.  
  154. INSERT INTO PurchasingOrderItems VALUES(1, 7,  20);
  155. INSERT INTO PurchasingOrderItems VALUES(1, 8,  50);
  156. INSERT INTO PurchasingOrderItems VALUES(1, 9,  15);
  157. INSERT INTO PurchasingOrderItems VALUES(1, 10, 10);
  158. INSERT INTO PurchasingOrderItems VALUES(2, 8,  35);
  159. INSERT INTO PurchasingOrderItems VALUES(3, 7,  40);
  160. INSERT INTO PurchasingOrderItems VALUES(3, 10, 80);
  161. INSERT INTO PurchasingOrderItems VALUES(4, 5,  10);
  162. INSERT INTO PurchasingOrderItems VALUES(4, 6,  30);
  163. INSERT INTO PurchasingOrderItems VALUES(5, 10,  70);
  164. INSERT INTO PurchasingOrderItems VALUES(6, 2,  20);
  165. INSERT INTO PurchasingOrderItems VALUES(6, 1,  50);
  166. INSERT INTO PurchasingOrderItems VALUES(6, 4,  30);
  167. INSERT INTO PurchasingOrderItems VALUES(7, 3,  15);
  168. INSERT INTO PurchasingOrderItems VALUES(7, 1,  20);
  169. INSERT INTO PurchasingOrderItems VALUES(8, 4,  40);
  170. INSERT INTO PurchasingOrderItems VALUES(9, 3,  30);
  171. INSERT INTO PurchasingOrderItems VALUES(9, 2,  10);
  172. INSERT INTO PurchasingOrderItems VALUES(9, 1,  20);
  173. INSERT INTO PurchasingOrderItems VALUES(9, 4,  25);
  174.  
  175. INSERT INTO CustomerOrderItems VALUES ( 1, 2,  80 );
  176. INSERT INTO CustomerOrderItems VALUES ( 1, 10, 30 );
  177. INSERT INTO CustomerOrderItems VALUES ( 1, 3, 50 );
  178. INSERT INTO CustomerOrderItems VALUES ( 2, 6, 70 );
  179. INSERT INTO CustomerOrderItems VALUES ( 2, 5, 40 );
  180. INSERT INTO CustomerOrderItems VALUES ( 3, 8, 90 );
  181. INSERT INTO CustomerOrderItems VALUES ( 3, 7, 120 );
  182. INSERT INTO CustomerOrderItems VALUES ( 4, 9, 100 );
  183. INSERT INTO CustomerOrderItems VALUES ( 4, 3, 30 );
  184. INSERT INTO CustomerOrderItems VALUES ( 5, 2, 40 );
  185. INSERT INTO CustomerOrderItems VALUES ( 5, 1, 35 );
  186. INSERT INTO CustomerOrderItems VALUES ( 5, 4, 60 );
  187. INSERT INTO CustomerOrderItems VALUES ( 6, 10, 70 );
  188. INSERT INTO CustomerOrderItems VALUES ( 7, 1, 30 );
  189. INSERT INTO CustomerOrderItems VALUES ( 7, 4, 65 );
  190. INSERT INTO CustomerOrderItems VALUES ( 7, 6, 50 );
  191. INSERT INTO CustomerOrderItems VALUES ( 7, 8, 25 );
  192. INSERT INTO CustomerOrderItems VALUES ( 8, 2, 80 );
  193.  
  194.  
  195. --DISPLAY ALL TABLES
  196. SELECT * FROM Suppliers;
  197. SELECT * FROM Items;
  198. SELECT * FROM PurchasingOrder;
  199.  
  200. SELECT * FROM WarehouseLocation;
  201.  
  202. SELECT * FROM Customers;
  203. SELECT * FROM CustomerOrders;
  204. SELECT * FROM SupplierItems;
  205. SELECT * FROM CustomerOrderItems;
RAW Paste Data
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
Top