fork download 
  1. CREATE DATABASE Maintenance
  2. ON 
  3. (NAME = 'Maintenance',
  4. FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Maintenance.mdf',
  5. SIZE = 20,
  6. MAXSIZE = 100,
  7. FILEGROWTH = 5)
  8. LOG ON
  9. (NAME = 'Maintenance_Log',
  10. FILENAME = 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Maintenance_Log.ldf',
  11. SIZE = 15MB,
  12. MAXSIZE = 45MB,
  13. FILEGROWTH = 5MB
  14. );
  15.  
  16. USE Maintenance
  17. GO
  18.  
  19. CREATE TABLE Vehicle_Type
  20. (
  21. TypeID char (3) not null PRIMARY KEY,
  22. [Description] varchar (100) not null
  23. );
  24. GO
  25.  
  26. INSERT into Vehicle_Type VALUES
  27. ('TT','Tracter Trailor'),
  28. ('ST','Semi-Tracter Trailor');
  29. GO
  30.  
  31. SELECT* from Vehicle_Type
  32. GO
  33.  
  34. CREATE TABLE Maintenance_Code
  35. (
  36. Maintenance_Type_ID varchar (5) not null PRIMARY KEY,
  37. [Description] varchar (100) not null
  38. );
  39. GO
  40.  
  41. INSERT into Maintenance_Code VALUES
  42. ('MOC4','Oil Change'),
  43. ('MHR9','Hose Replacement'),
  44. ('MTR5','Tire Ratation'),
  45. ('MAB8','Alignment & Balancing Tires');
  46. GO
  47.  
  48. SELECT * from Maintenance_Code
  49. GO
  50.  
  51. CREATE TABLE Maintenance_Descriptions
  52. (
  53. Maintenance_Type_ID varchar (5) not null PRIMARY KEY
  54. FOREIGN KEY REFERENCES Maintenance_Code(Maintenance_Type_ID),
  55. Level_Code varchar (10) not null,
  56. Average_Hours_Required varchar (5) not null,
  57. Days_Between_Recommended_Maintenance int not null,
  58. Maximum_Days_Between_Maintenance int not null
  59. );
  60. GO
  61.  
  62. INSERT into Maintenance_Descriptions VALUES
  63. ('MOC4','LvL1','1.25','90','120'),
  64. ('MHR9','LvL2','3.5','180','200'),
  65. ('MTR5','LvL3','2.0','180','210'),
  66. ('MAB8','LvL4','5.30','180','210');
  67. GO
  68.  
  69. SELECT* from Maintenance_Descriptions
  70. GO
  71.  
  72. CREATE TABLE Vehicles
  73. (VIN_Number varchar (17) not null PRIMARY KEY,
  74. TypeID char (3) not null 
  75. FOREIGN KEY REFERENCES Vehicle_Type(TypeID),
  76. Class_Code varchar (6) not null,
  77. Put_Into_Service_Date date not null,
  78. Gross_Weight varchar (7) not null,
  79. Milleage varchar (9) not null,
  80. Purchase_Price varchar (9) not null,
  81. Accumulated_Depreciation varchar (9) not null,
  82. Taken_Out_Of_Service date,
  83. Capacity varchar (20) not null);
  84. GO
  85.  
  86. INSERT into Vehicles VALUES
  87. ('2HNYD28478H501589','ST','CL7GVW','2000-04-02','58,510','30,004','$65,350','$23,314','2010-04-30','45,000 53X13.6X102'),
  88. ('2T1FF28PX1C539447','TT','CL5GVW','2001-05-29','47,850','89,347','$57,950','$17,964','2012-06-01','97,000 55X15.25X102');
  89. GO
  90.  
  91. SELECT * from Vehicles
  92. GO
  93.  
  94. CREATE TABLE Vehicle_Maintenance
  95. (
  96. VIN_Number varchar (17) not null,
  97. PartsID varchar (10) not null,
  98. Maintenance_Type_ID varchar (5) not null,
  99. Put_Into_Service_Date date not null,
  100. Last_Maintenance_Date date not null,
  101. Next_Scheduled_Maintenance date not null,
  102. Under_Warranty_Flag char (1) not null
  103. );
  104. GO
  105.  
  106. INSERT into Vehicle_Maintenance VALUES
  107. ('2HNYD28478H501589','QH1300AS','MOC4','2011-06-15','2012-01-31','2013-01-31','Y'),
  108. ('2T1FF28PX1C539447','1590002','MHR9','2011-02-22','2012-03-25','2012-12-01','Y');
  109. GO
  110.  
  111. select * from Vehicle_Maintenance;
  112. GO
  113.  
  114. CREATE TABLE Tire_Maintenance
  115. (
  116. Barcode varchar (17) not null PRIMARY KEY,
  117. Maintenance_Type_ID varchar (5) not null 
  118. FOREIGN KEY REFERENCES Maintenance_Code(Maintenance_Type_ID),
  119. TypeID char (3) not null 
  120. FOREIGN KEY REFERENCES Vehicle_Type(TypeID),
  121. Manufacture_ID varchar (15) not null,
  122. Put_Into_Service_Date date not null,
  123. Rotated_Scheduled_Date date not null,
  124. Last_Rotated_Date date not null,
  125. Disposal_Date date not null
  126. );
  127. GO
  128.  
  129. INSERT into Tire_Maintenance VALUES
  130. ('79008 40228','MTR5','ST','GoodYr2002','2006-10-06','2012-08-10','2012-05-12','2012-05-17'),
  131. ('96018 13820','MAB8','TT','Yukon2010','2009-07-02','2012-05-31','2012-02-28','2012-02-10');
  132. GO
  133.  
  134. SELECT * from Tire_Maintenance
  135. GO
  136.  
  137. CREATE TABLE Maintenance_Work_Order
  138. (
  139. Work_Order_ID varchar (5) not null PRIMARY KEY,
  140. VIN_Number varchar (17) not null 
  141. FOREIGN KEY REFERENCES Vehicles (VIN_Number),
  142. Maintenance_Type_ID varchar (5) not null 
  143. FOREIGN KEY REFERENCES Maintenance_Code(Maintenance_Type_ID),
  144. Assignment_To varchar (9) not null,
  145. Date_Started date not null,
  146. Date_Completed date not null,
  147. Hour_s varchar (7) not null
  148. );
  149. GO
  150.  
  151.  
  152. INSERT into Maintenance_Work_Order VALUES
  153. ('H1000','2HNYD28478H501589','MOC4','Blue Team','2012-05-09','2012-05-11','3.0'),
  154. ('H1001','2T1FF28PX1C539447','MHR9','Black Team','2012-04-28','2012-04-30','2.75');
  155. GO
  156.  
  157. select * from Maintenance_Work_Order
  158. GO
  159.  
  160. CREATE TABLE Vendors
  161. (
  162. Vendor_ID varchar (7) not null PRIMARY KEY 
  163. FOREIGN KEY REFERENCES Vendors (Vendor_ID),
  164. Name varchar (10)not null,
  165. Order_Address varchar (5) not null,
  166. Order_Street varchar (10) not null,
  167. Order_City varchar (15) not null,
  168. Order_State char (2) not null,
  169. Order_Zip int not null,
  170. Order_Contact char (15) not null,
  171. Order_Phone_Number varchar (15) not null,
  172. Order_Fax_Number varchar (15), 
  173. Billing_Address varchar (5) not null,
  174. Billing_Street varchar (10) not null,
  175. Billing_City varchar (15) not null,
  176. Billing_State char (2) not null,
  177. Billing_Zip int not null,
  178. Billing_Contact char (15) not null,
  179. Billing_Phone_Number varchar(15) not null,
  180. Billing_Fax_Number varchar (15),
  181. GO
  182.  
  183.  
  184. INSERT into Vendors VALUES
  185. ('SAP8','Sears','5178','Pearl Road','Cleveland','OH','44129','Mary Kay','(216)351-3279','(216)351-7500','6600','Bessemer Avenue','Cleveland','OH','44127','John Smith','(216)341-7140','(216)429-3523'),
  186. ('NA00','NAPA','3141','Superior Avenue','Cleveland','OH','44114','Andrew Linder','(216)771-1515','(216)398-9800','6600','Bessemer Avenue','Cleveland','OH','44127','John Smith','(216)341-7140','(216)429-3523')
  187. GO
  188.  
  189. select*from Vendors
  190. GO
  191.  
Runtime error #stdin #stdout 0s 3008KB
comments (?)
stdin 
select * from Names WHERE Fname = 'John' AND Lname = 'Doe';
stdout 
Standard output is empty
https://ideone.com/byAYI
language:
SQLite (sqlite 3.27.2)
created:
14 years ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!