fork download
  1. create table employee(Fname varchar2(15) not null,
  2. Minit varchar2(1) not null,
  3. Lname varchar2(15) not null,
  4. Ssn number(10) not null,
  5. Bdate date not null,
  6. Address varchar2(50) not null,
  7. Sex varchar2(1) not null,
  8. Salary number(5) not null,
  9. Super_ssn number(10),
  10. primary key(ssn),
  11. foreign key(Super_ssn) references employee(Ssn)
  12. );
  13.  
  14. create table department
  15. (Dname varchar2(10) not null,
  16. Dnumber number(2) primary key,
  17. Mgr_ssn number(10),
  18. Mgr_start_date date not null,
  19. foreign key(Mgr_ssn) references employee(Ssn)
  20. );
  21.  
  22.  
  23. alter table employee
  24. add Dno number(2) add foreign key (Dno) references Department(Dnumber));
  25.  
  26.  
  27.  
  28.  
  29. create table dept_locations
  30. (Dnumber number(2) references department(Dnumber),
  31. Dlocation varchar2(15),
  32. primary key (Dnumber,Dlocation)
  33. );
  34.  
  35. create table project
  36. (Pname varchar2(15) not null,
  37. Pnumber number(2) primary key,
  38. Plocation varchar2(15) not null,
  39. Dnum number(2) references department(Dnumber)
  40. );
  41.  
  42. create table works_on
  43. (Essn number(10),
  44. Pno number(2) references project(Pnumber),
  45. hours number(3,1),
  46. primary key(Essn,Pno),
  47. foreign key(Essn) references employee(ssn)
  48. );
  49.  
  50. create table dependent
  51. (Essn number(10),
  52. Dependent_name varchar2(10),
  53. sex varchar2(1) not null,
  54. Bdate date not null,
  55. Releationship varchar2(10) not null,
  56. primary key(Essn,Dependent_name),
  57. foreign key(Essn) references employee(Ssn)
  58. );
  59.  
  60.  
  61.  
  62.  
  63.  
  64. //Insert statement for table employee
  65.  
  66. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  67. values('John','B','Smith',123456789,to_date('1965-01-09','yyyy-mm-dd'),'731 Fondren, Mouston, TX','M',30000);
  68.  
  69. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  70. values('Franklin','T','Wonk',333445555,to_date('1955-12-08','yyyy-mm-dd'),'638 Vost,Houston,TX','M',40000);
  71.  
  72. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary,super_ssn)
  73. values('Alicia','','Wallace',987654321,to_date('1941-06-20','yyyy-mm-dd'),'291 Castle, Spring, TX','F',25000,333445555);
  74.  
  75.  
  76.  
  77. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  78. values('Zennifer','S','Wallace',987654321,to_date('1941-06-20','yyyy-mm-dd'),'291 Berry, Bollare, TX','F',43000);
  79.  
  80. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  81. values('Ramesh','K','Narayan',666884444,to_date('1962-09-15','yyyy-mm-dd'),'975 Fire Oak,Humble, TX','M',38000);
  82.  
  83.  
  84. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  85. values('Ahmad','v','Jabbar',987987987,to_date('1969-03-29','yyyy-mm-dd'),'980 Dallas,Houstion, TX','M',25000);
  86.  
  87. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  88. values('James','E','Borg',888665555,to_date('1937-11-10','yyyy-mm-dd'),'450 Stone,Houstion, TX','M',55000);
  89.  
  90.  
  91. insert into employee(Fname,minit,lname,ssn,bdate,address,sex,salary)
  92. values('Joyce','A','English',453453453,to_date('1972-07-31','yyyy-mm-dd'),'5631 Rice,Houstion, TX','F',25000);
  93.  
  94. //Insert Statement for Department table
  95.  
  96. insert into Department
  97. values('Research',5,333445555,to_date('1988-05-22','yyyy-mm-dd'));
  98.  
  99.  
  100.  
  101. insert into Department
  102. values('Administration',4,987654321,to_date('1995-01-01','yyyy-mm-dd'));
  103.  
  104.  
  105.  
  106. insert into Department
  107. values('Headquarters',1,888665555,to_date('1981-06-19','yyyy-mm-dd'));
  108.  
  109.  
  110.  
  111.  
  112.  
  113. update employee
  114. set super_ssn=333445555
  115. where super_ssn is null and minit='B';
  116.  
  117.  
  118.  
  119. update employee
  120. set super_ssn=888565555
  121. where ssn=333445555;
  122.  
  123. update employee
  124. set super_ssn=333445555
  125. where ssn=999887777;
  126.  
  127. update employee
  128. set super_ssn=987654321
  129. where ssn=999887777;
  130.  
  131. update employee
  132. set super_ssn=888665555
  133. where ssn=987654321;
  134.  
  135.  
  136. update employee
  137. set super_ssn=333445555
  138. where ssn=666884444;
  139.  
  140. update employee
  141. set super_ssn=333445555
  142. where ssn=453453453;
  143.  
  144.  
  145. update employee
  146. set super_ssn=987654321
  147. where ssn=987987987;
  148.  
  149.  
  150. update employee
  151. set super_ssn=null
  152. where ssn=888665555;
  153.  
  154.  
  155. insert into dept_locations values(1,'Houston');
  156.  
  157. insert into dept_locations values(4,'Stafford');
  158.  
  159. insert into dept_locations values(5,'Bellarre');
  160.  
  161. insert into dept_locations values(5,'Sugarland');
  162.  
  163. insert into dept_locations values(5,'Houston');
  164.  
  165.  
  166. insert into project values('ProductsX',1,'Bellaire',5);
  167.  
  168. insert into project values('ProductsY',2,'Sugarland',5);
  169.  
  170. insert into project values('ProductsZ',3,'Houston',5);
  171.  
  172. insert into project values('Computerization',10,'Stafford',4);
  173.  
  174. insert into project values('Reorganization',20,'Houston',1);
  175.  
  176. insert into project values('Newbenefits',30,'Stafford',4);
  177.  
  178.  
  179. insert into works_on values(123456789,1,3.2);
  180. insert into works_on values(123456789,2,7.5);
  181. insert into works_on values(666884444,3,40.0);
  182. insert into works_on values(453453453,1,20.0);
  183. insert into works_on values(453453453,2,20.0);
  184. insert into works_on values(333445555,2,10.0);
  185. insert into works_on values(333445555,3,10.0);
  186. insert into works_on values(333445555,10,10.0);
  187. insert into works_on values(333445555,20,10.0);
  188. insert into works_on values(999887777,30,30.0);
  189. insert into works_on values(999887777,10,10.0);
  190. insert into works_on values(987987987,10,35.0);
  191. insert into works_on values(987987987,30,5.0);
  192. insert into works_on values(987654321,30,20.0);
  193. insert into works_on values(987654321,20,75.0);
  194. insert into works_on values(888665555,20,null);
  195.  
  196.  
  197.  
  198. insert into dependent values(333445555,'Alice','F',TO_DATE('1986-04-05','yyyy-mm-dd'),'daughter');
  199.  
  200. insert into dependent values(333445555,'Theodore','M',TO_DATE('1983-10-25','yyyy-mm-dd'),'son');
  201.  
  202. insert into dependent values(333445555,'Joy','F',TO_DATE('1958-05-03','yyyy-mm-dd'),'spouse');
  203.  
  204. insert into dependent values(987654321,'Abner','M',TO_DATE('1942-02-28','yyyy-mm-dd'),'spouse');
  205.  
  206. insert into dependent values(123456789,'Michael','M',TO_DATE('1988-01-04','yyyy-mm-dd'),'son');
  207.  
  208. insert into dependent values(123456789,'Alice','F',TO_DATE('1988-12-30','yyyy-mm-dd'),'daughter');
  209.  
  210. insert into dependent values(123456789,'Elizabeth','F',TO_DATE('1967-05-05','yyyy-mm-dd'),'spouse');
  211.  
  212.  
  213.  
  214.  
  215. Alter table employee add(Dno number(1));
  216.  
  217. update employee
  218. set Dno=5
  219. where minit='B';
  220.  
  221.  
  222. update employee
  223. set Dno=5
  224. where minit='T';
  225.  
  226.  
  227. update employee
  228. set Dno=4
  229. where minit='J';
  230.  
  231.  
  232. update employee
  233. set Dno=4
  234. where minit='S';
  235.  
  236.  
  237. update employee
  238. set Dno=5
  239. where minit='K';
  240.  
  241.  
  242. update employee
  243. set Dno=5
  244. where minit='A';
  245.  
  246.  
  247. update employee
  248. set Dno=4
  249. where minit='V';
  250.  
  251.  
  252. update employee
  253. set Dno=1
  254. where minit='E';
Success #stdin #stdout #stderr 0.01s 5356KB
stdin
Standard input is empty
stdout
Standard output is empty
stderr
Error: near line 23: near "add": syntax error
Error: near line 64: near "/": syntax error
Error: near line 69: no such function: to_date
Error: near line 72: no such function: to_date
Error: near line 77: no such function: to_date
Error: near line 80: no such function: to_date
Error: near line 84: no such function: to_date
Error: near line 87: no such function: to_date
Error: near line 91: no such function: to_date
Error: near line 94: near "/": syntax error
Error: near line 101: no such function: to_date
Error: near line 106: no such function: to_date
Error: near line 198: no such function: TO_DATE
Error: near line 200: no such function: TO_DATE
Error: near line 202: no such function: TO_DATE
Error: near line 204: no such function: TO_DATE
Error: near line 206: no such function: TO_DATE
Error: near line 208: no such function: TO_DATE
Error: near line 210: no such function: TO_DATE
Error: near line 215: near "(": syntax error
Error: near line 217: no such column: Dno
Error: near line 222: no such column: Dno
Error: near line 227: no such column: Dno
Error: near line 232: no such column: Dno
Error: near line 237: no such column: Dno
Error: near line 242: no such column: Dno
Error: near line 247: no such column: Dno
Error: near line 252: no such column: Dno