Advertisement
Guest User

Untitled

a guest
Feb 24th, 2020
87
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.73 KB | None | 0 0
  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.  
  16.  
  17.  
  18.  
  19.  
  20.  
  21.  
  22.  
  23. -- HERE CREATE TABLES DEFINITIONS -- COMPILE ENTIRE SCRIPT ON https://kripken.github.io/sql.js/GUI/
  24.  
  25. CREATE TABLE Suppliers (SuppliersCode INTEGER PRIMARY KEY,
  26. SupplierName TEXT,
  27. Country TEXT,
  28. City TEXT,
  29. Code INTEGER,
  30. Email TEXT);
  31.  
  32. CREATE TABLE Items (ItemsCode INTEGER PRIMARY KEY,
  33. Name TEXT,
  34. Details TEXT,
  35. ItemCode FLOAT);
  36.  
  37. CREATE TABLE PurchasingOrder (PONumber INTEGER PRIMARY KEY,
  38. SupplierCode INTEGER,
  39. PaidDate TEXT,
  40. RecivedDate TEXT
  41. );
  42.  
  43. CREATE TABLE WarehouseLocation (Location INTEGER PRIMARY KEY);
  44.  
  45. CREATE TABLE CustomerOrders (CustomerOrderNumber INTEGER PRIMARY KEY,
  46. PaidDate TEXT,
  47. OutgoingDate TEXT);
  48.  
  49. CREATE TABLE Customers(CustomerCode INTEGER PRIMARY KEY,
  50. CustomerName TEXT,
  51. Country TEXT,
  52. City TEXT,
  53. Street TEXT,
  54. PhoneNumber TEXT,
  55. Email TEXT);
  56.  
  57.  
  58.  
  59.  
  60.  
  61. CREATE TABLE SupplierItems (SupplierCode INTEGER,
  62. ItemCode INTEGER,
  63. ItemPrice FLOAT,
  64. PRIMARY KEY (SupplierCode, ItemCode),
  65. FOREIGN KEY (SupplierCode) REFERENCES Suppliers (SuppliersCode),
  66. FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
  67. );
  68.  
  69. CREATE TABLE PurchasingOrderItems (PurchasingOrderNumber INTEGER,
  70. ItemCode INTEGER,
  71. ItemQuantity FLOAT,
  72. PRIMARY KEY (PurchasingOrderNumber, ItemCode),
  73. FOREIGN KEY (PurchasingOrderNumber) REFERENCES PurchasingOrder (PONumber),
  74. FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
  75. );
  76.  
  77. CREATE TABLE WarehouseItems (Location INTEGER,
  78. ItemCode INTEGER,
  79. ItemQuantity FLOAT,
  80. SupplierCode INTEGER,
  81. PRIMARY KEY (Location, ItemCode),
  82. FOREIGN KEY (Location) REFERENCES WarehouseLocation (Location),
  83. FOREIGN KEY (ItemCode) REFERENCES Items (ItemsCode)
  84. );
  85.  
  86. CREATE TABLE CustomerOrderItems (CustomerOrderNumber INTEGER,
  87. ItemCode TEXT,
  88. ItemQuantity TEXT,
  89. PRIMARY KEY (CustomerOrderNumber, ItemCode)
  90. FOREIGN KEY (CustomerOrderNumber) REFERENCES CustomerOrders(CustomerOrderNumber)
  91. FOREIGN KEY (ItemCode) REFERENCES Items(ItemsCode)
  92. );
  93.  
  94. CREATE TABLE MadeOrders (CustomerCode INTEGER,
  95. CustomerOrderNumber INTEGER,
  96. PRIMARY KEY (CustomerCode, CustomerOrderNumber)
  97. FOREIGN KEY (CustomerCode) REFERENCES Customers(CustomerCode)
  98. FOREIGN KEY (CustomerOrderNumber) REFERENCES CustomerOrders(CustomerOrderNumber)
  99. );
  100.  
  101.  
  102.  
  103.  
  104.  
  105.  
  106.  
  107.  
  108.  
  109.  
  110.  
  111.  
  112.  
  113.  
  114.  
  115.  
  116. -- HERE CREATE TABLES DEFINITIONS
  117.  
  118. --INPUT DATA FROM SESSION 1
  119. INSERT INTO Suppliers VALUES( 1, "Sandvik", "Norway", "Sandviken", 555666, "sandvik@gazeta.pl" );
  120. INSERT INTO Suppliers VALUES( 2, "CocaCola", "USA", "New York", 888333, "coke@gazeta.pl" );
  121. INSERT INTO Suppliers VALUES( 3, "KFC", "USA", "Chicago", 999222, "kfc@gazeta.pl" );
  122.  
  123. INSERT INTO Items VALUES( 1, "Chicken", "Tasty", 5.5 );
  124. INSERT INTO Items VALUES( 2, "Leg", "Delicious", 4 );
  125. INSERT INTO Items VALUES( 3, "Omelette", "Ordinary", 3 );
  126. INSERT INTO Items VALUES( 4, "Pizza", "Ugly", 12.5 );
  127. INSERT INTO Items VALUES( 5, "Fanta", "When Thirsty", 2.4 );
  128. INSERT INTO Items VALUES( 6, "Cola", "Refreshing", 3 );
  129. INSERT INTO Items VALUES( 7, "Screw", "Decent", 5.5 );
  130. INSERT INTO Items VALUES( 8, "Scissors", "Indecent", 7.5 );
  131. INSERT INTO Items VALUES( 9, "Nail", "Firm", 8.5 );
  132. INSERT INTO Items VALUES(10, "Decanter", "Spacious", 10 );
  133.  
  134. INSERT INTO PurchasingOrder VALUES( 1, 1, NULL, "01.01.2017");
  135. INSERT INTO PurchasingOrder VALUES( 2, 1, "02.01.2017", "02.01.2017");
  136. INSERT INTO PurchasingOrder VALUES( 3, 1, "02.01.2017", "03.01.2017");
  137. INSERT INTO PurchasingOrder VALUES( 4, 2, "02.01.2017", "04.01.2017");
  138. INSERT INTO PurchasingOrder VALUES( 5, 2, "02.01.2017", "05.01.2017");
  139. INSERT INTO PurchasingOrder VALUES( 6, 3, "02.01.2017", "06.01.2017");
  140. INSERT INTO PurchasingOrder VALUES( 7, 3, "02.01.2017", "07.01.2017");
  141. INSERT INTO PurchasingOrder VALUES( 8, 3, "02.01.2017", "08.01.2017");
  142. INSERT INTO PurchasingOrder VALUES( 9, 3, "09.01.2017", NULL);
  143.  
  144. --INPUT DATA FROM SESSION 2
  145. INSERT INTO SupplierItems VALUES( 1, 7, 3 );
  146. INSERT INTO SupplierItems VALUES( 1, 8, 5 );
  147. INSERT INTO SupplierItems VALUES( 1, 9, 6.5 );
  148. INSERT INTO SupplierItems VALUES( 1,10, 7 );
  149. INSERT INTO SupplierItems VALUES( 2,10, 8 );
  150. INSERT INTO SupplierItems VALUES( 2, 5, 2 );
  151. INSERT INTO SupplierItems VALUES( 2, 6, 2.5 );
  152. INSERT INTO SupplierItems VALUES( 3, 7, 2 );
  153. INSERT INTO SupplierItems VALUES( 3, 8, 4 );
  154. INSERT INTO SupplierItems VALUES( 3, 9, 6 );
  155. INSERT INTO SupplierItems VALUES( 3,10, 5 );
  156. INSERT INTO SupplierItems VALUES( 3, 5, 1 );
  157. INSERT INTO SupplierItems VALUES( 3, 6, 2 );
  158. INSERT INTO SupplierItems VALUES( 3, 1, 3 );
  159. INSERT INTO SupplierItems VALUES( 3, 2, 2 );
  160. INSERT INTO SupplierItems VALUES( 3, 3, 2.2 );
  161. INSERT INTO SupplierItems VALUES( 3, 4, 8 );
  162.  
  163. INSERT INTO PurchasingOrderItems VALUES(1, 7, 20);
  164. INSERT INTO PurchasingOrderItems VALUES(1, 8, 50);
  165. INSERT INTO PurchasingOrderItems VALUES(1, 9, 15);
  166. INSERT INTO PurchasingOrderItems VALUES(1, 10, 10);
  167. INSERT INTO PurchasingOrderItems VALUES(2, 8, 35);
  168. INSERT INTO PurchasingOrderItems VALUES(3, 7, 40);
  169. INSERT INTO PurchasingOrderItems VALUES(3, 10, 80);
  170. INSERT INTO PurchasingOrderItems VALUES(4, 5, 10);
  171. INSERT INTO PurchasingOrderItems VALUES(4, 6, 30);
  172. INSERT INTO PurchasingOrderItems VALUES(5, 10, 70);
  173. INSERT INTO PurchasingOrderItems VALUES(6, 2, 20);
  174. INSERT INTO PurchasingOrderItems VALUES(6, 1, 50);
  175. INSERT INTO PurchasingOrderItems VALUES(6, 4, 30);
  176. INSERT INTO PurchasingOrderItems VALUES(7, 3, 15);
  177. INSERT INTO PurchasingOrderItems VALUES(7, 1, 20);
  178. INSERT INTO PurchasingOrderItems VALUES(8, 4, 40);
  179. INSERT INTO PurchasingOrderItems VALUES(9, 3, 30);
  180. INSERT INTO PurchasingOrderItems VALUES(9, 2, 10);
  181. INSERT INTO PurchasingOrderItems VALUES(9, 1, 20);
  182. INSERT INTO PurchasingOrderItems VALUES(9, 4, 25);
  183.  
  184. --INPUT DATA FROM SESSION 3
  185. INSERT INTO WarehouseLocation VALUES( 1 );
  186. INSERT INTO WarehouseLocation VALUES( 2 );
  187. INSERT INTO WarehouseLocation VALUES( 3 );
  188. INSERT INTO WarehouseLocation VALUES( 4 );
  189. INSERT INTO WarehouseLocation VALUES( 5 );
  190.  
  191. INSERT INTO WarehouseItems VALUES( 1, 1, 20, 1);
  192. INSERT INTO WarehouseItems VALUES( 1, 2, 30, 1);
  193. INSERT INTO WarehouseItems VALUES( 1, 3, 60, 1);
  194. INSERT INTO WarehouseItems VALUES( 1, 4, 20, 1);
  195. INSERT INTO WarehouseItems VALUES( 1, 5, 10, 1);
  196. INSERT INTO WarehouseItems VALUES( 1, 6, 0, 1);
  197. INSERT INTO WarehouseItems VALUES( 1, 7, 0, 1);
  198. INSERT INTO WarehouseItems VALUES( 1, 8, 40, 1);
  199. INSERT INTO WarehouseItems VALUES( 1, 9, 20, 1);
  200. INSERT INTO WarehouseItems VALUES( 1, 10, 50, 1);
  201. INSERT INTO WarehouseItems VALUES( 2, 1, 15, 1);
  202. INSERT INTO WarehouseItems VALUES( 2, 2, 30, 1);
  203. INSERT INTO WarehouseItems VALUES( 2, 3, 25, 1);
  204. INSERT INTO WarehouseItems VALUES( 2, 4, 55, 1);
  205. INSERT INTO WarehouseItems VALUES( 2, 5, 75, 1);
  206. INSERT INTO WarehouseItems VALUES( 2, 6, 10, 1);
  207. INSERT INTO WarehouseItems VALUES( 2, 7, 80, 1);
  208. INSERT INTO WarehouseItems VALUES( 2, 8, 30, 1);
  209. INSERT INTO WarehouseItems VALUES( 2, 9, 45, 1);
  210. INSERT INTO WarehouseItems VALUES( 2, 10, 25, 1);
  211. INSERT INTO WarehouseItems VALUES( 3, 1, 5, 1);
  212. INSERT INTO WarehouseItems VALUES( 3, 2, 0, 1);
  213. INSERT INTO WarehouseItems VALUES( 3, 3, 0, 1);
  214. INSERT INTO WarehouseItems VALUES( 3, 4, 0, 1);
  215. INSERT INTO WarehouseItems VALUES( 3, 5, 30, 1);
  216. INSERT INTO WarehouseItems VALUES( 3, 6, 65, 1);
  217. INSERT INTO WarehouseItems VALUES( 3, 7, 10, 1);
  218. INSERT INTO WarehouseItems VALUES( 3, 8, 45, 1);
  219. INSERT INTO WarehouseItems VALUES( 3, 9, 50, 1);
  220. INSERT INTO WarehouseItems VALUES( 3, 10, 5, 1);
  221. INSERT INTO WarehouseItems VALUES( 4, 1, 35, 1);
  222. INSERT INTO WarehouseItems VALUES( 4, 2, 20, 1);
  223. INSERT INTO WarehouseItems VALUES( 4, 3, 45, 1);
  224. INSERT INTO WarehouseItems VALUES( 4, 4, 75, 1);
  225. INSERT INTO WarehouseItems VALUES( 4, 5, 80, 1);
  226. INSERT INTO WarehouseItems VALUES( 4, 6, 0, 1);
  227. INSERT INTO WarehouseItems VALUES( 4, 7, 0, 1);
  228. INSERT INTO WarehouseItems VALUES( 4, 8, 0, 1);
  229. INSERT INTO WarehouseItems VALUES( 4, 9, 15, 1);
  230. INSERT INTO WarehouseItems VALUES( 4, 10, 20, 1);
  231. INSERT INTO WarehouseItems VALUES( 5, 1, 10, 1);
  232. INSERT INTO WarehouseItems VALUES( 5, 2, 65, 1);
  233. INSERT INTO WarehouseItems VALUES( 5, 3, 20, 1);
  234. INSERT INTO WarehouseItems VALUES( 5, 4, 65, 1);
  235. INSERT INTO WarehouseItems VALUES( 5, 5, 75, 1);
  236. INSERT INTO WarehouseItems VALUES( 5, 6, 0, 1);
  237. INSERT INTO WarehouseItems VALUES( 5, 7, 45, 1);
  238. INSERT INTO WarehouseItems VALUES( 5, 8, 35, 1);
  239. INSERT INTO WarehouseItems VALUES( 5, 9, 25, 1);
  240. INSERT INTO WarehouseItems VALUES( 5, 10, 10, 1);
  241.  
  242. --INPUT DATA FROM SESSION 4
  243. INSERT INTO Customers VALUES ( 1, 'Mitch Ducanon', 'USA', 'Miami', 'Ocean Street', '+1 8376456', 'mitch@hotmail.com');
  244. INSERT INTO Customers VALUES ( 2, 'Deynn', 'Poland', 'Sosnowiec', 'Na Ostatnim Groszu', '+48 398723', 'deynn@onet.pl');
  245. INSERT INTO Customers VALUES ( 3, 'Angela Merkel', 'Germany', 'Berlin', 'Bullowstrasse', '+49 2134908', 'angie@spiegel.com');
  246. INSERT INTO Customers VALUES ( 4, 'Patrick Bruel', 'France', 'Pari', 'Frog Street', '+33 8376456', 'bruel@hotmail.com');
  247. INSERT INTO Customers VALUES ( 5, 'Gianni Versace', 'Italy', 'Rome', 'Macaroni Street', '+39 8376456', 'gianni@hotmail.com');
  248.  
  249. INSERT INTO CustomerOrders VALUES ( 1, '4.09.2017', '12.09.2017');
  250. INSERT INTO CustomerOrders VALUES ( 2, '1.09.2017', '14.09.2017');
  251. INSERT INTO CustomerOrders VALUES ( 3, '5.09.2017', '18.09.2017');
  252. INSERT INTO CustomerOrders VALUES ( 4, NULL, NULL);
  253. INSERT INTO CustomerOrders VALUES ( 5, '10.09.2017', '15.09.2017');
  254. INSERT INTO CustomerOrders VALUES ( 6, '8.09.2017', NULL);
  255. INSERT INTO CustomerOrders VALUES ( 7, '4.09.2017', '14.09.2017');
  256. INSERT INTO CustomerOrders VALUES ( 8, '2.09.2017', '13.09.2017');
  257.  
  258. --INPUT DATA FROM SESSION 5
  259. INSERT INTO CustomerOrderItems VALUES ( 1, 2, 80 );
  260. INSERT INTO CustomerOrderItems VALUES ( 1, 10, 30 );
  261. INSERT INTO CustomerOrderItems VALUES ( 1, 3, 50 );
  262. INSERT INTO CustomerOrderItems VALUES ( 2, 6, 70 );
  263. INSERT INTO CustomerOrderItems VALUES ( 2, 5, 40 );
  264. INSERT INTO CustomerOrderItems VALUES ( 3, 8, 90 );
  265. INSERT INTO CustomerOrderItems VALUES ( 3, 7, 120 );
  266. INSERT INTO CustomerOrderItems VALUES ( 4, 9, 100 );
  267. INSERT INTO CustomerOrderItems VALUES ( 4, 3, 30 );
  268. INSERT INTO CustomerOrderItems VALUES ( 5, 2, 40 );
  269. INSERT INTO CustomerOrderItems VALUES ( 5, 1, 35 );
  270. INSERT INTO CustomerOrderItems VALUES ( 5, 4, 60 );
  271. INSERT INTO CustomerOrderItems VALUES ( 6, 10, 70 );
  272. INSERT INTO CustomerOrderItems VALUES ( 7, 1, 30 );
  273. INSERT INTO CustomerOrderItems VALUES ( 7, 4, 65 );
  274. INSERT INTO CustomerOrderItems VALUES ( 7, 6, 50 );
  275. INSERT INTO CustomerOrderItems VALUES ( 7, 8, 25 );
  276. INSERT INTO CustomerOrderItems VALUES ( 8, 2, 80 );
  277.  
  278.  
  279. INSERT INTO MadeOrders VALUES (1, 8);
  280. INSERT INTO MadeOrders VALUES (1, 7);
  281. INSERT INTO MadeOrders VALUES (3, 1);
  282. INSERT INTO MadeOrders VALUES (3, 6);
  283. INSERT INTO MadeOrders VALUES (2, 3);
  284. INSERT INTO MadeOrders VALUES (5, 2);
  285. INSERT INTO MadeOrders VALUES (4, 4);
  286. INSERT INTO MadeOrders VALUES (1, 5);
  287.  
  288.  
  289. --DISPLAY ALL TABLES
  290. SELECT * FROM Suppliers;
  291. SELECT * FROM Items;
  292. SELECT * FROM PurchasingOrder;
  293. SELECT * FROM SupplierItems;
  294. SELECT * FROM PurchasingOrderItems;
  295. SELECT * FROM WarehouseLocation;
  296. SELECT * FROM WarehouseItems;
  297. SELECT * FROM Customers;
  298. SELECT * FROM CustomerOrders;
  299. SELECT * FROM CustomerOrderItems;
  300. SELECT * FROM MadeOrders;
  301.  
  302.  
  303.  
  304.  
  305.  
  306.  
  307.  
  308.  
  309.  
  310.  
  311.  
  312.  
  313.  
  314. -- DISPLAY PURCHASE ORDERS NUMBERS, RELATED ITEM NAMES WITH PROCES AND QUANTITY
  315. -- DISPLAY PURCHASE ORDERS NUMBERS AND RELATED ITEM NAMES WITH THEIR VALUES
  316. -- WHAT IS SELLING VALUE OF EVERY ITEM IN ALL WAREHOUSE(ITEM NAME - PRICE)
  317. -- DISPLAY SELLING PRICE FOR EVERY CUSTOMERORDER
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement