API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 9th, 2019
211
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.37 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE CUSTOMER(
  2. CID int PRIMARY KEY,
  3. isCompany BIT,
  4. "name" varchar(30) NOT NULL,
  5. surname varchar(30) NOT NULL,
  6. country varchar(30),
  7. City varchar(30),
  8. Street varchar(30),
  9. BNR int,
  10. postalcode CHAR(5),
  11. email varchar(30),
  12. phone varchar(15),
  13. );
  14.  
  15.  
  16. CREATE TABLE EMPLOYEE(
  17. EID varchar(10) PRIMARY KEY,
  18. "name" varchar(30) NOT NULL,
  19. surname varchar(30) NOT NULL,
  20. hireDate date,
  21. position varchar(30 )NOT NULL,
  22. salary float,
  23. bankacc numeric(26),
  24. phone char(10),
  25. email varchar(50)
  26. );
  27.  
  28. CREATE TABLE "ORDER"(
  29. OrderID varchar(10) PRIMARY KEY,
  30. "date" DATETIME,
  31. totalprice float NOT NULL,
  32. CID varchar(10),
  33. FOREIGN KEY (CID) REFERENCES CUSTOMER,
  34. EID varchar(10),
  35. FOREIGN KEY (EID) REFERENCES EMPLOYEE,
  36. );
  37.  
  38. CREATE TABLE SHIPMENT(
  39. trackingnumber varchar(30) PRIMARY KEY,
  40. shipmentdate datetime NOT NULL,
  41. deliverydate datetime,
  42. CID varchar(10),
  43. FOREIGN KEY (CID) REFERENCES CUSTOMER,
  44. EID varchar(10),
  45. FOREIGN KEY (EID) REFERENCES EMPLOYEE,
  46. );
  47. CREATE TABLE CARPART(
  48. PartSerialID varchar(30) PRIMARY KEY,
  49. "OEM" varchar(30) UNIQUE,
  50. "name" varchar(30),
  51. "brandname" varchar(30),
  52. carmanufacturer varchar(30),
  53. model varchar(30),
  54. "version" varchar(30),
  55. "year" int,
  56. engineversion varchar(30),
  57. gastype varchar(30),
  58. price float ,
  59. );
  60.  
  61. CREATE TABLE PARTSTORAGE(
  62. partid varchar(30) PRIMARY KEY,
  63. place varchar(5),
  64. quantity int,
  65. sellprice float NOT NULL,
  66. PartSerialID varchar(30),
  67. FOREIGN KEY (PartSerialID) REFERENCES CARPART,
  68. );
  69.  
  70. CREATE TABLE OrderDetails(
  71. QuantityOrdered int,
  72. QuantitySent int,
  73. UnitPrice float NOT NULL,
  74. OrderID varchar(10),
  75. partid varchar(30),
  76. FOREIGN KEY (OrderID) REFERENCES "ORDER",
  77. FOREIGN KEY (partid) REFERENCES "PARTSTORAGE",
  78. );
  79.  
  80. CREATE TABLE SupplierOrder(
  81. SupOrderID varchar(30) PRIMARY KEY,
  82. "date" DATETIME,
  83. totalprice float NOT NULL,
  84. EID varchar(10),
  85. CID varchar(10),
  86. FOREIGN KEY (EID) REFERENCES EMPLOYEE,
  87. FOREIGN KEY (CID) REFERENCES CUSTOMER,
  88.  
  89. );
  90.  
  91. CREATE TABLE SupOrderDetails(
  92. quantitysent int,
  93. quantityordered int,
  94. price float,
  95. SupOrderID varchar(30),
  96. FOREIGN KEY (SUpOrderid) REFERENCES SupplierOrder,
  97. PartSerialID varchar(30),
  98. FOREIGN KEY(Partserialid) REFERENCES CarPArt,
  99.  
  100. );
  101.  
  102. CREATE TABLE DELIVERY(
  103. deliveryID varchar(30) PRIMARY KEY,
  104. shipmentdate date,
  105. deliverydate date,
  106. SupoOrderID varchar(30),
  107. FOREIGN KEY (SupoOrderID) REFERENCES SupplierOrder,
  108. );
  109.  
  110. CREATE TABLE DELIVERYDETAILS(
  111. quantity int,
  112. PartSerialNumber varchar(30),
  113. FOREIGN KEY (PartSerialNumber) REFERENCES CARPART,
  114. deliveryid varchar(30),
  115. FOREIGN KEY (deliveryid) REFERENCES DELIVERY,
  116. );
  117.  
  118. CREATE TABLE SUPPLIER(
  119. SUpplierid varchar(30) PRIMARY KEY,
  120. "name" varchar(30),
  121. "country" varchar(30),
  122. city varchar(30),
  123. street varchar(30),
  124. buildingnumber int,
  125. phone varchar(30),
  126. email varchar(30),
  127. bankacc varchar(30),
  128. );
  129.  
  130.  
  131.  
  132. INSERT INTO EMPLOYEE VALUES
  133. (1, 'John', 'Jonas', '20120615', 'cleaner', '35000',44301472416862092162328111, 685948374, 'sample@sample'),
  134. (2, 'Misty', 'Arch', '20130611', 'maintenance', '20000',10118314889321318945481154, 685948374, 'sample@sample'),
  135. (3, 'Ash', 'Kosciuszko', '20140619 10:34:09 AM', 'cleaner', '15000',19870412473572669509858093, 685948374, 'sample@sample'),
  136. (4, 'Brock', 'Mike', '20150615 10:34:09 AM', 'manager', '54023',61812968420401374236699952, 685948374, 'sample@sample'),
  137. (5, 'Kevin', 'Bright', '20160604 10:34:09 AM', 'maintenance', '10000',38607982756179957645670713, 685948374, 'sample@sample'),
  138. (6, 'Jimmy', 'Williams', '20170628 10:34:09 AM', 'driver', '58395',11417503821088821824295614, 685948374, 'sample@sample'),
  139. (7, 'William', 'Robinson', '20180628 10:34:09 AM', 'secretary', '75754',36255174334747489852086018, 685948374, 'sample@sample'),
  140. (8, 'Tyrone', 'Tyrone', '20120620 10:34:09 AM', 'cleaner', '25000',80212250304779709215038811, 685948374, 'sample@sample'),
  141. (9, 'Ann', 'Zimmermann', '20130130 10:34:09 AM', 'maintenance', '29000',59049247823094306595671072, 685948374, 'sample@sample'),
  142. (10, 'Brian', 'Goldberg', '20140228 10:34:09 AM', 'cleaner', '200000',51614072255400933847064322, 685948374, 'sample@sample'),
  143. (11, 'Eric', 'Adm', '20150318 10:34:09 AM', 'manager', '43000',98055786164102959491790161, 685948374, 'sample@sample'),
  144. (12, 'Kyle', 'Bor', '20160418 04:34:09 AM', 'maintenance', '43775',93522711780910716698983336, 685948374, 'sample@sample'),
  145. (13, 'Joshua', 'Dyka', '20170518 10:34:09 AM', 'driver', '96949',17021559721356994484196573, 685948374, 'sample@sample'),
  146. (14, 'Carl', 'Carlson', '20180718 06:34:09 AM', 'secretary', '20000',16811501186826177132057991, 685948374, 'sample@sample');
  147.  
  148.  
  149. INSERT into CUSTOMER values
  150. (1, 0, 'Seba', 'Sebski', 'Poland', 'Gdynia', 'Buraczana', 12, '81310', 'samplesample@smaple,.com', '505505505'),
  151. (2, 1, 'Monex', NULL, 'Germany', 'Berlin', 'IhreStrasse', 60, '74938', 'samplesample@smaple,.com', '505505505'),
  152. (3, 0, 'Abraham', 'Morgenstern', 'Israel', 'Tel Aviv', 'Shalom', 15, '09029', 'abraham.morgenstern@smaple.is', '505505505'),
  153. (4, 0, 'Icek', 'Goofman', 'Israel', 'Haifa', 'Molahs', 1, '81123', 'icek.goofman@israel.is', '505505505'),
  154. (5, 0, 'Mateusz', 'Kraj', 'Poland', 'Gdynia', 'Słowackiego', 39, '81390', 'mkr@smaple.com', '505505505'),
  155. (6, 1, 'Krkr', NULL, 'Poland', 'Gdynia', '', 12, '81162', 'momkrkr@smaple.pl', '505505505'),
  156. (7, 1, 'Bado', NULL, 'Poland', 'Gdynia', 'Polska', 12, '81472', 'bado@bado.com', '505505505'),
  157. (8, 0, 'Janusz', 'Karol', 'Poland', 'Toruń', 'Rzemieślnicza', 12, '81540', 'jankkaro@gmail.com', '505505505'),
  158. (9, 0, 'Michał', 'Monikowski', 'Poland', 'Gdańsk', 'Starowiejska', 12, '84310', 'm.mon@gmail.com', '505505505'),
  159. (10, 0, 'Mateusz', 'Bright', 'England', 'Birmingham', 'Pepper Street', 64, '72046', 'mati.b@gmail.com', '505505505');
  160.  
  161. INSERT INTO ORDER values
  162. (0, '20170605', 9694.7,4, 9)
  163.  
  164.  
  165.  
  166. insert into CARPART values
  167. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mercedes', 'G-class', 'G63', '2017', '5.0', 'Diesel', 525.00),
  168. ('M748381', '1857278124', 'Brakes', 'Zonix', 'Fiat', 'Punto', 'Turbo', '1999', '1.4', 'Gasoline', 250.00),
  169. ('M094192', '78263', 'Mirror', 'Mercedes', 'Opel', 'G-Astra', 'G63', '2018', '3.0', 'Diesel', 1000.00),
  170. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mercedes', 'A-class', 'G63', '2019', '4.5', 'Gasoline', 2.00),
  171. ('M094192', '78263', 'Mirror', 'Mercedes', 'Kia', 'Sportage', 'G63', '1992', '2.0', 'Diesel', 491.10),
  172. ('M094192', '78263', 'Mirror', 'Mercedes', 'Toyota', 'Hilux', 'G63', '1998', '2.3', 'Diesel', 525.10),
  173. ('M094192', '78263', 'Mirror', 'Mercedes', 'Toyota', 'CH-R', 'G63', '2017', '1.8', 'Gasoline', 49.50),
  174. ('M094192', '78263', 'Mirror', 'Mercedes', 'Toyota', 'Corolla', 'G63', '1990', '5.0', 'Gasoline', 18967.00),
  175. ('M094192', '78263', 'Mirror', 'Mercedes', 'Fiat', 'Panda', 'G63', '1994', '4.0', 'Gasoline', 157.00),
  176. ('M094192', '78263', 'Mirror', 'Mercedes', 'Merceds', 'G-class', 'G63', '2052', '3.5', 'Diesel', 5468.00),
  177. ('M094192', '78263', 'Mirror', 'Mercedes', 'BMW', 'G-class', 'G63', '2020', '2.0', 'Diesel', 132.00),
  178. ('M094192', '78263', 'Mirror', 'Mercedes', 'BWM', 'G-class', 'G63', '2017', '3.5', 'Gasoline', 754.00),
  179. ('M094192', '78263', 'Mirror', 'Mercedes', 'Audi', 'G-class', 'G63', '2012', '2.0', 'Gasoline', 254.00),
  180. ('M094192', '78263', 'Mirror', 'Mercedes', 'Peugeot', 'G-class', 'G63', '2010', '1.4', 'Gasoline', 156.00),
  181. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mercedes', 'G-class', 'G63', '2016', '7.2', 'Gasoline', 102.00),
  182. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mercedes', 'G-class', 'G63', '2001', '4.0', 'Diesel', 56783.00),
  183. ('M094192', '78263', 'Mirror', 'Mercedes', 'Toyota', 'G-class', 'G63', '2011', '3.8', 'Diesel', 5.00),
  184. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mercedes', 'G-class', 'G63', '2011', '2.0', 'Gasoline', 57.30),
  185. ('M094192', '78263', 'Mirror', 'Mercedes', 'Dacia', 'G-class', 'G63', '2012', '3.9', 'Gasoline', 6230.50),
  186. ('M094192', '78263', 'Mirror', 'Mercedes', 'Mitsubishi', 'G-class', 'G63', '2001', '1.0', 'Gasoline', 150.00),
  187.  
  188.  
  189.  
  190.  
  191.  
  192. INSERT into "ORDER" values (0, '20190618 10:34:09 PM', 2139, 1, 1);
  193. select EID, name, surname from EMPLOYEE
  194.  
  195. );
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!