SHARE
TWEET

LO SQL Query Tutorial Database

eeperry Jul 12th, 2014 439 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE "Skills" (
  2. "ID" INT GENERATED BY DEFAULT AS IDENTITY,
  3. "Title" VARCHAR(50) NOT NULL,
  4. "Description" VARCHAR(256),
  5. CONSTRAINT TITLE_NOT_BLANK
  6. CHECK(TRIM(BOTH FROM "Title") <> '')
  7. );
  8.  
  9. CREATE TABLE "TeamMembers" (
  10. "ID" INT GENERATED BY DEFAULT AS IDENTITY,
  11. "FirstName" VARCHAR(50) NOT NULL,
  12. "LastName" VARCHAR(50) NOT NULL,
  13. "DateOfHire" DATE NOT NULL,
  14. "SkillID" INT,
  15. "City" VARCHAR(50),
  16. "Married" BOOLEAN DEFAULT FALSE,
  17. CONSTRAINT FNAME_NOT_BLANK
  18. CHECK(TRIM(BOTH FROM "FirstName") <> ''),
  19. CONSTRAINT LNAME_NOT_BLANK
  20. CHECK(TRIM(BOTH FROM "LastName") <> ''),
  21. FOREIGN KEY ("SkillID") REFERENCES "Skills"("ID")
  22. );
  23.  
  24. CREATE TABLE "Projects" (
  25. "ID" INT GENERATED BY DEFAULT AS IDENTITY,
  26. "Title" VARCHAR(50) NOT NULL,
  27. "Description" VARCHAR(256),
  28. "Start" DATE,
  29. "Due" DATE,
  30. CONSTRAINT PTITLE_NOT_BLANK
  31. CHECK(TRIM(BOTH FROM "Title") <> '')
  32. );
  33.  
  34. CREATE TABLE "TeamMember2Project" (
  35. "TeamMemberID" INT,
  36. "ProjectID" INT,
  37. FOREIGN KEY ("TeamMemberID") REFERENCES "TeamMembers"("ID"),
  38. FOREIGN KEY ("ProjectID") REFERENCES "Projects"("ID"),
  39. PRIMARY KEY ("TeamMemberID", "ProjectID")
  40. );
  41.  
  42. INSERT INTO "Skills"
  43. ("Title", "Description")
  44. VALUES
  45. ('Programmer', 'The one who codes');
  46.  
  47. INSERT INTO "Skills"
  48. ("Title", "Description")
  49. VALUES
  50. ('Artist', 'Images and graphics');
  51.  
  52. INSERT INTO "Skills"
  53. ("Title", "Description")
  54. VALUES
  55. ('Desinger', 'The one who puts it all together');
  56.  
  57. INSERT INTO "TeamMembers"
  58. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  59. VALUES
  60. ('Johnny', 'Walker', '2010-05-01', 0,  'New York', TRUE);
  61.  
  62. INSERT INTO "TeamMembers"
  63. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  64. VALUES
  65. ('Sandy', 'Walker', '2012-04-15', 2, 'New York', TRUE);
  66.  
  67. INSERT INTO "TeamMembers"
  68. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  69. VALUES
  70. ('Johnny', 'Smith', '2011-08-05', 1, 'New Town', TRUE);
  71.  
  72. INSERT INTO "TeamMembers"
  73. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  74. VALUES
  75. ('Sandy', 'Newsome', '2007-10-23', 2, 'New Town', FALSE);
  76.  
  77. INSERT INTO "TeamMembers"
  78. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  79. VALUES
  80. ('Frank', 'Jenson', '1999-01-01', 1, 'Franklin', FALSE);
  81.  
  82. INSERT INTO "TeamMembers"
  83. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  84. VALUES
  85. ('Harvey', 'Benson', '2003-03-12', 0, 'Franklin', TRUE);
  86.  
  87. INSERT INTO "TeamMembers"
  88. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  89. VALUES
  90. ('Michael', 'Benton', '2012-04-28', 2, 'New Town', TRUE);
  91.  
  92. INSERT INTO "TeamMembers"
  93. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  94. VALUES
  95. ('George', 'Hamelton', '1997-02-21', 0, 'New York', FALSE);
  96.  
  97. INSERT INTO "TeamMembers"
  98. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  99. VALUES
  100. ('Nadia', 'Donovan', '2013-11-11', 1, 'New York', TRUE);
  101.  
  102. INSERT INTO "TeamMembers"
  103. ("FirstName", "LastName", "DateOfHire", "SkillID", "City", "Married")
  104. VALUES
  105. ('Patty', 'Donovan', '2014-02-11', 0, 'New York', FALSE);
  106.  
  107.  
  108.  
  109. INSERT INTO "Projects"
  110. ("Title", "Description", "Start", "Due")
  111. VALUES
  112. ('ACME Web Site', 'To showcase their products', '2014-08-05', '2014-10-01');
  113.  
  114. INSERT INTO "Projects"
  115. ("Title", "Description", "Start", "Due")
  116. VALUES
  117. ('Bugs Bunny Ad', 'Advertise new show', '2014-06-05', '2014-12-01');
  118.  
  119. INSERT INTO "Projects"
  120. ("Title", "Description", "Start", "Due")
  121. VALUES
  122. ('Giant Sling Shot', 'New redesign', '2014-11-01', '2015-01-01');
  123.  
  124. INSERT INTO "Projects"
  125. ("Title", "Description", "Start", "Due")
  126. VALUES
  127. ('ACME Mobile App', 'Buy stuff from your phone.', '2014-07-15', '2014-08-31');
  128.  
  129.  
  130. INSERT INTO "TeamMember2Project"
  131. ("TeamMemberID", "ProjectID")
  132. VALUES
  133. (0,0);
  134.  
  135. INSERT INTO "TeamMember2Project"
  136. ("TeamMemberID", "ProjectID")
  137. VALUES
  138. (4,0);
  139.  
  140. INSERT INTO "TeamMember2Project"
  141. ("TeamMemberID", "ProjectID")
  142. VALUES
  143. (3,0);
  144.  
  145. INSERT INTO "TeamMember2Project"
  146. ("TeamMemberID", "ProjectID")
  147. VALUES
  148. (1,1);
  149.  
  150. INSERT INTO "TeamMember2Project"
  151. ("TeamMemberID", "ProjectID")
  152. VALUES
  153. (2,1);
  154.  
  155. INSERT INTO "TeamMember2Project"
  156. ("TeamMemberID", "ProjectID")
  157. VALUES
  158. (8,2);
  159.  
  160. INSERT INTO "TeamMember2Project"
  161. ("TeamMemberID", "ProjectID")
  162. VALUES
  163. (6,2);
  164.  
  165. INSERT INTO "TeamMember2Project"
  166. ("TeamMemberID", "ProjectID")
  167. VALUES
  168. (7,3);
  169.  
  170. INSERT INTO "TeamMember2Project"
  171. ("TeamMemberID", "ProjectID")
  172. VALUES
  173. (3,3);
RAW Paste Data
Top