API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Mar 25th, 2019
83
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.65 KB | None | 0 0
raw download clone embed print report
  1. create table classroom
  2. (building varchar(15),
  3. room_number varchar(7),
  4. capacity numeric(4,0),
  5. primary key (building, room_number)
  6. );
  7.  
  8. create table department
  9. (dept_name varchar(20),
  10. building varchar(15),
  11. budget numeric(12,2) check (budget > 0),
  12. primary key (dept_name)
  13. );
  14.  
  15. create table course
  16. (course_id varchar(8),
  17. title varchar(50),
  18. dept_name varchar(20),
  19. credits numeric(2,0) check (credits > 0),
  20. primary key (course_id),
  21. foreign key (dept_name) references department(dept_name)
  22. on delete set null
  23. );
  24.  
  25. create table instructor
  26. (ID varchar(5),
  27. name varchar(20) not null,
  28. dept_name varchar(20),
  29. salary numeric(8,2) check (salary > 29000),
  30. primary key (ID),
  31. foreign key (dept_name) references department(dept_name)
  32. on delete set null
  33. );
  34.  
  35. create table section
  36. (course_id varchar(8),
  37. sec_id varchar(8),
  38. semester varchar(6)
  39. check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
  40. year numeric(4,0) check (year > 1701 and year < 2100),
  41. building varchar(15),
  42. room_number varchar(7),
  43. time_slot_id varchar(4),
  44. primary key (course_id, sec_id, semester, year),
  45. foreign key (course_id) references course(course_id)
  46. on delete cascade,
  47. foreign key (building, room_number) references classroom(building, room_number)
  48. on delete set null
  49. );
  50.  
  51. create table teaches
  52. (ID varchar(5),
  53. course_id varchar(8),
  54. sec_id varchar(8),
  55. semester varchar(6),
  56. year numeric(4,0),
  57. primary key (ID, course_id, sec_id, semester, year),
  58. foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
  59. on delete cascade,
  60. foreign key (ID) references instructor(ID)
  61. on delete cascade
  62. );
  63.  
  64. create table student
  65. (ID varchar(5),
  66. name varchar(20) not null,
  67. dept_name varchar(20),
  68. tot_cred numeric(3,0) check (tot_cred >= 0),
  69. primary key (ID),
  70. foreign key (dept_name) references department(dept_name)
  71. on delete set null
  72. );
  73.  
  74. create table takes
  75. (ID varchar(5),
  76. course_id varchar(8),
  77. sec_id varchar(8),
  78. semester varchar(6),
  79. year numeric(4,0),
  80. grade varchar(2),
  81. primary key (ID, course_id, sec_id, semester, year),
  82. foreign key (course_id,sec_id, semester, year) references section(course_id,sec_id, semester, year)
  83. on delete cascade,
  84. foreign key (ID) references student(ID)
  85. on delete cascade
  86. );
  87.  
  88. create table advisor
  89. (s_ID varchar(5),
  90. i_ID varchar(5),
  91. primary key (s_ID),
  92. foreign key (i_ID) references instructor (ID)
  93. on delete set null,
  94. foreign key (s_ID) references student (ID)
  95. on delete cascade
  96. );
  97.  
  98. create table time_slot
  99. (time_slot_id varchar(4),
  100. day varchar(1),
  101. start_hr numeric(2) check (start_hr >= 0 and start_hr < 24),
  102. start_min numeric(2) check (start_min >= 0 and start_min < 60),
  103. end_hr numeric(2) check (end_hr >= 0 and end_hr < 24),
  104. end_min numeric(2) check (end_min >= 0 and end_min < 60),
  105. primary key (time_slot_id, day, start_hr, start_min)
  106. );
  107.  
  108. create table prereq
  109. (course_id varchar(8),
  110. prereq_id varchar(8),
  111. primary key (course_id, prereq_id),
  112. foreign key (course_id) references course(course_id)
  113. on delete cascade,
  114. foreign key (prereq_id) references course(course_id)
  115. );
  116.  
  117.  
  118.  
  119.  
  120.  
  121.  
  122.  
  123. insert into classroom values ('Packard', '101', '500');
  124. insert into classroom values ('Painter', '514', '10');
  125. insert into classroom values ('Taylor', '3128', '70');
  126. insert into classroom values ('Watson', '100', '30');
  127. insert into classroom values ('Watson', '120', '50');
  128. insert into department values ('Biology', 'Watson', '90000');
  129. insert into department values ('Comp. Sci.', 'Taylor', '100000');
  130. insert into department values ('Elec. Eng.', 'Taylor', '85000');
  131. insert into department values ('Finance', 'Painter', '120000');
  132. insert into department values ('History', 'Painter', '50000');
  133. insert into department values ('Music', 'Packard', '80000');
  134. insert into department values ('Physics', 'Watson', '70000');
  135. insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
  136. insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
  137. insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
  138. insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
  139. insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
  140. insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
  141. insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
  142. insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
  143. insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
  144. insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
  145. insert into course values ('HIS-351', 'World History', 'History', '3');
  146. insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
  147. insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
  148. insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
  149. insert into instructor values ('12121', 'Wu', 'Finance', '90000');
  150. insert into instructor values ('15151', 'Mozart', 'Music', '40000');
  151. insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
  152. insert into instructor values ('32343', 'El Said', 'History', '60000');
  153. insert into instructor values ('33456', 'Gold', 'Physics', '87000');
  154. insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
  155. insert into instructor values ('58583', 'Califieri', 'History', '62000');
  156. insert into instructor values ('76543', 'Singh', 'Finance', '80000');
  157. insert into instructor values ('76766', 'Crick', 'Biology', '72000');
  158. insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
  159. insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
  160. insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
  161. insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
  162. insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
  163. insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
  164. insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
  165. insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
  166. insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
  167. insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
  168. insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
  169. insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
  170. insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
  171. insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
  172. insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
  173. insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
  174. insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
  175. insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
  176. insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
  177. insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
  178. insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
  179. insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
  180. insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
  181. insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
  182. insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
  183. insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
  184. insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
  185. insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
  186. insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
  187. insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
  188. insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
  189. insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
  190. insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
  191. insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
  192. insert into student values ('19991', 'Brandt', 'History', '80');
  193. insert into student values ('23121', 'Chavez', 'Finance', '110');
  194. insert into student values ('44553', 'Peltier', 'Physics', '56');
  195. insert into student values ('45678', 'Levy', 'Physics', '46');
  196. insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
  197. insert into student values ('55739', 'Sanchez', 'Music', '38');
  198. insert into student values ('70557', 'Snow', 'Physics', '0');
  199. insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
  200. insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
  201. insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
  202. insert into student values ('98988', 'Tanaka', 'Biology', '120');
  203. insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
  204. insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
  205. insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
  206. insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
  207. insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
  208. insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
  209. insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
  210. insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
  211. insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
  212. insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
  213. insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
  214. insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
  215. insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
  216. insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
  217. insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
  218. insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
  219. insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
  220. insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
  221. insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
  222. insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
  223. insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
  224. insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
  225. insert into advisor values ('00128', '45565');
  226. insert into advisor values ('12345', '10101');
  227. insert into advisor values ('23121', '76543');
  228. insert into advisor values ('44553', '22222');
  229. insert into advisor values ('45678', '22222');
  230. insert into advisor values ('76543', '45565');
  231. insert into advisor values ('76653', '98345');
  232. insert into advisor values ('98765', '98345');
  233. insert into advisor values ('98988', '76766');
  234. insert into time_slot values ('A', 'M', '8', '0', '8', '50');
  235. insert into time_slot values ('A', 'W', '8', '0', '8', '50');
  236. insert into time_slot values ('A', 'F', '8', '0', '8', '50');
  237. insert into time_slot values ('B', 'M', '9', '0', '9', '50');
  238. insert into time_slot values ('B', 'W', '9', '0', '9', '50');
  239. insert into time_slot values ('B', 'F', '9', '0', '9', '50');
  240. insert into time_slot values ('C', 'M', '11', '0', '11', '50');
  241. insert into time_slot values ('C', 'W', '11', '0', '11', '50');
  242. insert into time_slot values ('C', 'F', '11', '0', '11', '50');
  243. insert into time_slot values ('D', 'M', '13', '0', '13', '50');
  244. insert into time_slot values ('D', 'W', '13', '0', '13', '50');
  245. insert into time_slot values ('D', 'F', '13', '0', '13', '50');
  246. insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
  247. insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
  248. insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
  249. insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
  250. insert into time_slot values ('G', 'M', '16', '0', '16', '50');
  251. insert into time_slot values ('G', 'W', '16', '0', '16', '50');
  252. insert into time_slot values ('G', 'F', '16', '0', '16', '50');
  253. insert into time_slot values ('H', 'W', '10', '0', '12', '30');
  254. insert into prereq values ('BIO-301', 'BIO-101');
  255. insert into prereq values ('BIO-399', 'BIO-101');
  256. insert into prereq values ('CS-190', 'CS-101');
  257. insert into prereq values ('CS-315', 'CS-101');
  258. insert into prereq values ('CS-319', 'CS-101');
  259. insert into prereq values ('CS-347', 'CS-101');
  260. insert into prereq values ('EE-181', 'PHY-101');
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!