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