API tools faq
paste
prateeksharma

DBMS_Assignment_7

prateeksharma
Nov 18th, 2019
52
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.80 KB | None | 0 0
raw download clone embed print report
  1.  
  2.  
  3. drop table Apply;
  4.  
  5. drop table Student;
  6.  
  7. drop table College;
  8.  
  9. create table College(cName varchar2(10) primary key, state
  10. varchar2(10), enrollment int);
  11.  
  12. create table Student(sID int primary key, sName varchar2(10), GPA
  13. real, sizeHS int, DoB date);
  14.  
  15. create table Apply(sID int, cName varchar2(10), major
  16. varchar2(20), decision char(1), primary key(sID, major, cName),
  17. constraint sID_fk Foreign key(sID) references Student, constraint
  18. cName_fk Foreign key(cName) references College);
  19.  
  20. delete from Student;
  21.  
  22. delete from College;
  23.  
  24. delete from Apply;
  25.  
  26. insert into Student values (123, 'Amy', 3.9, 1000, '26-JUN-96');
  27.  
  28. insert into Student values (234, 'Bob', 3.6, 1500, '7-Apr-95');
  29.  
  30. insert into Student values (345, 'Craig', 3.5, 500, '4-Feb-95');
  31.  
  32. insert into Student values (456, 'Doris', 3.9, 1000, '24-Jul-97');
  33.  
  34. insert into Student values (567, 'Edward', 2.9, 2000, '21-Dec-96');
  35.  
  36. insert into Student values (678, 'Fay', 3.8, 200, '27-Aug-96');
  37.  
  38. insert into Student values (789, 'Gary', 3.4, 800, '8-Oct-96');
  39.  
  40. insert into Student values (987, 'Helen', 3.7, 800, '27-Mar-97');
  41.  
  42. insert into Student values (876, 'Irene', 3.9, 400, '7-Mar-96');
  43.  
  44. insert into Student values (765, 'Jay', 2.9, 1500, '8-Aug-98');
  45.  
  46. insert into Student values (654, 'Amy', 3.9, 1000, '26-May-96');
  47.  
  48. insert into Student values (543, 'Craig', 3.4, 2000, '27-Aug-05');
  49.  
  50. insert into College values ('Stanford', 'CA', 15000);
  51.  
  52. insert into College values ('Berkeley', 'CA', 36000);
  53.  
  54. insert into College values ('MIT', 'MA', 10000);
  55.  
  56. insert into College values ('Cornell', 'NY', 21000);
  57.  
  58. insert into College values ('Harvard', 'MA', 50040);
  59.  
  60. insert into Apply values (123, 'Stanford', 'CS', 'Y');
  61.  
  62. insert into Apply values (123, 'Stanford', 'EE', 'N');
  63.  
  64. insert into Apply values (123, 'Berkeley', 'CS', 'Y');
  65.  
  66. insert into Apply values (123, 'Cornell', 'EE', 'Y');
  67.  
  68. insert into Apply values (234, 'Berkeley', 'biology', 'N');
  69.  
  70. insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
  71.  
  72. insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
  73.  
  74. insert into Apply values (345, 'Cornell', 'CS', 'Y');
  75.  
  76. insert into Apply values (345, 'Cornell', 'EE', 'N');
  77.  
  78. insert into Apply values (678, 'Stanford', 'history', 'Y');
  79.  
  80. insert into Apply values (987, 'Stanford', 'CS', 'Y');
  81.  
  82. insert into Apply values (987, 'Berkeley', 'CS', 'Y');
  83.  
  84. insert into Apply values (876, 'Stanford', 'CS', 'N');
  85.  
  86. insert into Apply values (876, 'MIT', 'biology', 'Y');
  87.  
  88. insert into Apply values (876, 'MIT', 'marine biology', 'N');
  89.  
  90. insert into Apply values (765, 'Stanford', 'history', 'Y');
  91.  
  92. insert into Apply values (765, 'Cornell', 'history', 'N');
  93.  
  94. insert into Apply values (765, 'Cornell', 'psychology', 'Y');
  95.  
  96. insert into Apply values (543, 'MIT', 'CS', 'N');
  97.  
  98. create view WeakStudent as select sid,sName from student where gpa<3.7;
  99.  
  100. create view WeakStudent as select sid,sName from student where gpa<3.7;
  101.  
  102. create or replace view WeakStudent as select sid, sName from student where gpa<3.7;
  103.  
  104. select * from WeakStudent;
  105.  
  106. create view cView(collegeName, seats) as select cname, enrollment from college;
  107.  
  108. select * from cview;
  109.  
  110. create view CSaccept as select sid, cname where major='CS' and decision='Y';
  111.  
  112. create view CSaccept as select sid, cname where major='CS' and decision='Y' from apply;
  113.  
  114. create view CSaccept as select sid, cname from apply where major='CS' and decision='Y';
  115.  
  116. select * from CSaccept;
  117.  
  118. drop table Apply;
  119.  
  120. drop table Student;
  121.  
  122. drop table College;
  123.  
  124. create table College(cName varchar2(10) primary key, state
  125. varchar2(10), enrollment int);
  126.  
  127. create table Student(sID int primary key, sName varchar2(10), GPA
  128. real, sizeHS int, DoB date);
  129.  
  130. create table Apply(sID int, cName varchar2(10), major
  131. varchar2(20), decision char(1), primary key(sID, major, cName),
  132. constraint sID_fk Foreign key(sID) references Student, constraint
  133. cName_fk Foreign key(cName) references College);
  134.  
  135. delete from Student;
  136.  
  137. delete from College;
  138.  
  139. delete from Apply;
  140.  
  141. insert into Student values (123, 'Amy', 3.9, 1000, '26-JUN-96');
  142.  
  143. insert into Student values (234, 'Bob', 3.6, 1500, '7-Apr-95');
  144.  
  145. insert into Student values (345, 'Craig', 3.5, 500, '4-Feb-95');
  146.  
  147. insert into Student values (456, 'Doris', 3.9, 1000, '24-Jul-97');
  148.  
  149. insert into Student values (567, 'Edward', 2.9, 2000, '21-Dec-96');
  150.  
  151. insert into Student values (678, 'Fay', 3.8, 200, '27-Aug-96');
  152.  
  153. insert into Student values (789, 'Gary', 3.4, 800, '8-Oct-96');
  154.  
  155. insert into Student values (987, 'Helen', 3.7, 800, '27-Mar-97');
  156.  
  157. insert into Student values (876, 'Irene', 3.9, 400, '7-Mar-96');
  158.  
  159. insert into Student values (765, 'Jay', 2.9, 1500, '8-Aug-98');
  160.  
  161. insert into Student values (654, 'Amy', 3.9, 1000, '26-May-96');
  162.  
  163. insert into Student values (543, 'Craig', 3.4, 2000, '27-Aug-05');
  164.  
  165. insert into College values ('Stanford', 'CA', 15000);
  166.  
  167. insert into College values ('Berkeley', 'CA', 36000);
  168.  
  169. insert into College values ('MIT', 'MA', 10000);
  170.  
  171. insert into College values ('Cornell', 'NY', 21000);
  172.  
  173. insert into College values ('Harvard', 'MA', 50040);
  174.  
  175. insert into Apply values (123, 'Stanford', 'CS', 'Y');
  176.  
  177. insert into Apply values (123, 'Stanford', 'EE', 'N');
  178.  
  179. insert into Apply values (123, 'Berkeley', 'CS', 'Y');
  180.  
  181. insert into Apply values (123, 'Cornell', 'EE', 'Y');
  182.  
  183. insert into Apply values (234, 'Berkeley', 'biology', 'N');
  184.  
  185. insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
  186.  
  187. insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
  188.  
  189. insert into Apply values (345, 'Cornell', 'CS', 'Y');
  190.  
  191. insert into Apply values (345, 'Cornell', 'EE', 'N');
  192.  
  193. insert into Apply values (678, 'Stanford', 'history', 'Y');
  194.  
  195. insert into Apply values (987, 'Stanford', 'CS', 'Y');
  196.  
  197. insert into Apply values (987, 'Berkeley', 'CS', 'Y');
  198.  
  199. insert into Apply values (876, 'Stanford', 'CS', 'N');
  200.  
  201. insert into Apply values (876, 'MIT', 'biology', 'Y');
  202.  
  203. insert into Apply values (876, 'MIT', 'marine biology', 'N');
  204.  
  205. insert into Apply values (765, 'Stanford', 'history', 'Y');
  206.  
  207. insert into Apply values (765, 'Cornell', 'history', 'N');
  208.  
  209. insert into Apply values (765, 'Cornell', 'psychology', 'Y');
  210.  
  211. insert into Apply values (543, 'MIT', 'CS', 'N');
  212.  
  213. create or replace view WeakStudent as select sid, sName from student where gpa<3.7;
  214.  
  215. select * from WeakStudent;
  216.  
  217. create view cView(collegeName, seats) as select cname, enrollment from college;
  218.  
  219. select * from cview;
  220.  
  221. create view CSaccept as select sid, cname from apply where major='CS' and decision='Y';
  222.  
  223. select * from CSaccept;
  224.  
  225. select * from csaccept;
  226.  
  227. select * from csaccept;
  228.  
  229. select * from cview;
  230.  
  231. select * from weakstudent;
  232.  
  233. create view CSaccept as select sid, sname, gpa, sizehs, from college natural join apply where major='CS' and decision='Y';
  234.  
  235. create view CSberkelry as select sid, sname, gpa, sizehs, from college natural join apply where major='CS' and decision='Y' and cname='berkeley' and sizehs>500;
  236.  
  237. select * from CSaccept;
  238.  
  239. create view CSberkelry as select sid, sname, gpa, sizehs, from college natural join csaccept where cname='berkeley' and sizehs>500;
  240.  
  241. create view CSberkelry as select sid, sname, gpa, sizehs, from college natural join csaccept where cname='Berkeley' and sizehs>500;
  242.  
  243. create view CSberkelry as select sid, sname, gpa, sizehs, from student natural join csaccept where cname='Berkeley' and sizehs>500;
  244.  
  245. create view CSberkeley as select sid, sname, gpa, sizehs from student natural join csaccept where cname='Berkeley' and sizehs>500;
  246.  
  247. select * from csberkeley;
  248.  
  249. create or replace view CSBerkeley as select sid, sname, gpa, sizehs from student,apply where student.sid=apply.sid and major='CS' and decision='Y' and cname='Berkeley';
  250.  
  251. create or replace view CSBerkeley as select sid, sname, gpa, sizehs from student,apply where student.sid=apply.sid and sizehs>500 and major='CS' and decision='Y' and cname='Berkeley';
  252.  
  253. create or replace view CSBerkeley as select student.sid, sname, gpa, sizehs from student,apply where student.sid=apply.sid and sizehs>500 and major='CS' and decision='Y' and cname='Berkeley';
  254.  
  255. select * from csberkeley;
  256.  
  257. select * from csberkeley where gpa>3.8;
  258.  
  259. drop view csaccept;
  260.  
  261. drop table Apply;
  262.  
  263. drop table Student;
  264.  
  265. drop table College;
  266.  
  267. create table College(cName varchar2(10) primary key, state
  268. varchar2(10), enrollment int);
  269.  
  270. create table Student(sID int primary key, sName varchar2(10), GPA
  271. real, sizeHS int, DoB date);
  272.  
  273. create table Apply(sID int, cName varchar2(10), major
  274. varchar2(20), decision char(1), primary key(sID, major, cName),
  275. constraint sID_fk Foreign key(sID) references Student, constraint
  276. cName_fk Foreign key(cName) references College);
  277.  
  278. delete from Student;
  279.  
  280. delete from College;
  281.  
  282. delete from Apply;
  283.  
  284. insert into Student values (123, 'Amy', 3.9, 1000, '26-JUN-96');
  285.  
  286. insert into Student values (234, 'Bob', 3.6, 1500, '7-Apr-95');
  287.  
  288. insert into Student values (345, 'Craig', 3.5, 500, '4-Feb-95');
  289.  
  290. insert into Student values (456, 'Doris', 3.9, 1000, '24-Jul-97');
  291.  
  292. insert into Student values (567, 'Edward', 2.9, 2000, '21-Dec-96');
  293.  
  294. insert into Student values (678, 'Fay', 3.8, 200, '27-Aug-96');
  295.  
  296. insert into Student values (789, 'Gary', 3.4, 800, '8-Oct-96');
  297.  
  298. insert into Student values (987, 'Helen', 3.7, 800, '27-Mar-97');
  299.  
  300. insert into Student values (876, 'Irene', 3.9, 400, '7-Mar-96');
  301.  
  302. insert into Student values (765, 'Jay', 2.9, 1500, '8-Aug-98');
  303.  
  304. insert into Student values (654, 'Amy', 3.9, 1000, '26-May-96');
  305.  
  306. insert into Student values (543, 'Craig', 3.4, 2000, '27-Aug-05');
  307.  
  308. insert into College values ('Stanford', 'CA', 15000);
  309.  
  310. insert into College values ('Berkeley', 'CA', 36000);
  311.  
  312. insert into College values ('MIT', 'MA', 10000);
  313.  
  314. insert into College values ('Cornell', 'NY', 21000);
  315.  
  316. insert into College values ('Harvard', 'MA', 50040);
  317.  
  318. insert into Apply values (123, 'Stanford', 'CS', 'Y');
  319.  
  320. insert into Apply values (123, 'Stanford', 'EE', 'N');
  321.  
  322. insert into Apply values (123, 'Berkeley', 'CS', 'Y');
  323.  
  324. insert into Apply values (123, 'Cornell', 'EE', 'Y');
  325.  
  326. insert into Apply values (234, 'Berkeley', 'biology', 'N');
  327.  
  328. insert into Apply values (345, 'MIT', 'bioengineering', 'Y');
  329.  
  330. insert into Apply values (345, 'Cornell', 'bioengineering', 'N');
  331.  
  332. insert into Apply values (345, 'Cornell', 'CS', 'Y');
  333.  
  334. insert into Apply values (345, 'Cornell', 'EE', 'N');
  335.  
  336. insert into Apply values (678, 'Stanford', 'history', 'Y');
  337.  
  338. insert into Apply values (987, 'Stanford', 'CS', 'Y');
  339.  
  340. insert into Apply values (987, 'Berkeley', 'CS', 'Y');
  341.  
  342. insert into Apply values (876, 'Stanford', 'CS', 'N');
  343.  
  344. insert into Apply values (876, 'MIT', 'biology', 'Y');
  345.  
  346. insert into Apply values (876, 'MIT', 'marine biology', 'N');
  347.  
  348. insert into Apply values (765, 'Stanford', 'history', 'Y');
  349.  
  350. insert into Apply values (765, 'Cornell', 'history', 'N');
  351.  
  352. insert into Apply values (765, 'Cornell', 'psychology', 'Y');
  353.  
  354. insert into Apply values (543, 'MIT', 'CS', 'N');
  355.  
  356. create or replace view WeakStudent as select sid, sName from student where gpa<3.7;
  357.  
  358. select * from WeakStudent;
  359.  
  360. create view cView(collegeName, seats) as select cname, enrollment from college;
  361.  
  362. select * from cview;
  363.  
  364. create view CSaccept as select sid, cname from apply where major='CS' and decision='Y';
  365.  
  366. select * from CSaccept;
  367.  
  368. select * from csaccept;
  369.  
  370. select * from cview;
  371.  
  372. select * from weakstudent;
  373.  
  374. create view CSberkeley as select sid, sname, gpa, sizehs from student natural join csaccept where cname='Berkeley' and sizehs>500;
  375.  
  376. create or replace view CSBerkeley as select student.sid, sname, gpa, sizehs from student,apply where student.sid=apply.sid and sizehs>500 and major='CS' and decision='Y' and cname='Berkeley';
  377.  
  378. select * from csberkeley;
  379.  
  380. select * from csberkeley where gpa>3.8;
  381.  
  382. drop view csaccept;
  383.  
  384. select sname from csberkeley;
  385.  
  386. create or replace view WeakStudent as select sid, sName, gpa, sizehs, dob from student where gpa<3.7;
Add Comment
Please, Sign In to add comment
Public Pastes
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!