6 Table Creations
1. create table department(dname varchar2(30),dnumber int
primary key,mgrssn int,mgrstartdate date)
2. create table employee(fname varchar(10),mname
varchar(10),lname varchar(10),ssn number(10)primary
key,bdate date,address varchar(30),sex varchar(10),salary
number(10),superssn number(10),foreign key(superssn)
references employee(ssn),dno int,foreign key(dno)
references department(dnumber))
3. create table dept_locations(dnumber int,dlocation
varchar(50) primary key,foreign key(dnumber) references
department(dnumber))
4. create table project(pname varchar(30),pnumber
number(15)primary key,plocation varchar(20),dnum
number(20),foreign key(dnum) references
department(dnumber))
5. create table works_on(essn number(20),pno number(20)
primary key,hours float(5),foreign key(essn) references
employee(ssn),foreign key(pno) references
project(pnumber))
6. create table dependent(essn number(20),dependentname
varchar(30)primary key,sex varchar(10),bdate
date,relationship varchar(20),foreign key(essn) references
employee(ssn))
Insert values:
insert into department values('research',5,333445555,'22-May-
1988')
insert into department
values('administration',4,987654321,'01-jan-1995')
insert into department values('headquarters',1,888665555,'19-
Jun-1981')
insert into employee values('John','B','Smith',123456789,'09-
jan-1965','731 Fondren,Houston,TX','M',30000,123456789,5);
insert into employee
values('Franklin','T','Wong',333445555,'08-dec-1955','638
voss,Houston,TX','M',40000,333445555,5);
insert into employee values('Alicia','J','Zetaya',999887777,'19-
jan-1968','3321 Castle,Spring,TX','F',25000,999887777,4);
insert into employee
values('Jennifer','S','Wallace',987654321,'20-jun-1941','291
Berry,Bellaire,TX','F',43000,987654321,4);
insert into employee
values('Ramesh','K','Naraayan',666884444,'15-sep-1962','975
Fire oak,Humble,TX','M',38000,666884444,5);
insert into employee values('Joyce','A','English',453453453,'31-
jul-1972','5631 Rice,Houston,TX','F',25000,453453453,5);
insert into employee
values('Ahmad','V','Jabbar',987987987,'29-mar-1969','980
Dallas,Houston,TX','M',25000,987987987,5);
insert into employee values('James','E','Borg',888665555,'10-
oct-1937','450 Stone,Houston,TX','M',55000,888665555,1);
insert into dept_locations values(1,'Houston')
insert into dept_locations values(4,'Stafford')
insert into dept_locations values(5,'Bellaire')
insert into dept_locations values(5,'Sugarland')
insert into dept_locations values(5,'Houston')
insert into project values('ProductX',1,'Bellaire',5)
insert into project values('ProductY',2,'Sugarland',5)
insert into project values('ProductZ',3,'Houston',5)
insert into project values('Computerization',10,'Stafford',4)
insert into project values('Reorganisation',20,'Houston',1)
insert into project values('Newbenefits',30,'Stafford',4)
insert into works_on values(123456789,1,32.5)
insert into works_on values(123456789,2,7.5)
insert into works_on values(666884444,3,40.0)
insert into works_on values(453453453,1,20.0)
insert into works_on values(453453453,2,20.0)
insert into works_on values(333445555,2,10.0)
insert into works_on values(333445555,10,10.0)
insert into works_on values(333445555,20,10.0)
insert into works_on values(999887777,30,30.0)
insert into works_on values(999887777,10,10.0)
insert into works_on values(987987987,10,35.0)
insert into works_on values(3987987987,30,5.0)
insert into works_on values(987654321,30,20.0)
insert into works_on values(987654321,20,15.0)
insert into works_on values(888665555,20,NULL)
insert into dependent values(333445555,'Alice','F','05-Apr-
1986','daughter')
insert into dependent values(333445555,'Theodore','M','25-
oct-1983','Son')
insert into dependent values(333445555,'Joy','F','03-may-
1958','spouse')
insert into dependent values(987654321,'Abner','M','28-feb-
1942','spouse')
insert into dependent values(123456789,'Michael','M','04-jan-
1988','Son')
insert into dependent values(123456789,'Alice','F','30-dec-
1988','daughter')
insert into dependent values(123456789,'Elizabeth','F','05-may-
1967','spouse')
