and
constraints.
EMPLOYEE
(Fname, Mname, Lname, SSN, Bdate, Address, Gender, Salary,
SuperSSN, Dno)
DEPARTMENT(Dnumber,
Dname, MgrSSN, Mgrstartdate)
DEPENDENT (ESSN,
Dependent_Name, Gender, Bdate, Relationship)
SQL> create table employee_18 (fname varchar2(20),minit
varchar2(1),lname varchar2(20),ssn number(10) primary key,bdate date,address
varchar2(40),sex varchar2(1),salary number(5),super_ssn number(9),dno
number(1));
SQL> Insert into employee
values('&fname','&minit','&lname',&ssn,'&bdate','&address','&sex',&salary,&super_ssn,&dno);
Enter value for fname:john
Enter value for minit:B
Enter value for lname:Smith
Enter value for ssn:123456789
Enter value for bdate:09-jan-1965
Enter value for address:731Fondren,Houston,TX
Enter value for sex:M
Enter value for salary:30000
Enter value for super_ssn:333445555
Enter value for dno:5
1 row created.
SQL> /
Enter value for fname:Franklin
Enter value for minit:T
Enter value for lname:Wong
Enter value for for: ssn:333445555
Enter value for bdate:08-dec-1955
Enter value for address:638Voss,Houston,TX
Enter value for sex:M
Enter value for salary:40000
Enter value for super_ssn:888665555
Enter value for dno:5
1 row created.
SQL> /
Enter value for fname:Alica
Enter value for minit:J
Enter value for lname:Zelaya
Enter value for for: ssn:999887777
Enter value for bdate:19-jan-1968
Enter value for address:3321Castle,Spring,TX
Enter value for sex:F
Enter value for salary:25000
Enter value for super_ssn:987654321
Enter value for dno:4
1 row created.
SQL> /
Enter value for fname:Jennifer
Enter value for minit:S
Enter value for lname:Wallace
Enter value for for: ssn:987654321
Enter value for bdate:20-jun-1941
Enter value for address:291Berry,Bellaire,TX
Enter value for sex:F
Enter value for salary:43000
Enter value for super_ssn:888665555
Enter value for dno:4
1 row created.
SQL> /
Enter value for fname:Ramesh
Enter value for minit:K
Enter value for lname:Narayan
Enter value for for: ssn:666884444
Enter value for bdate:15-sep-1962
Enter value for address:975 Fire Oak,Humble,TX
Enter value for sex:M
Enter value for salary:38000
Enter value for super_ssn:333445555
Enter value for dno:5
1 row created.
SQL> /
Enter value for fname:Joyce
Enter value for minit:A
Enter value for lname:English
Enter value for for: ssn:453453453
Enter value for bdate:31-jul-1972
Enter value for address:5631Rice,Houston,TX
Enter value for sex:F
Enter value for salary:25000
Enter value for super_ssn:333445555
Enter value for dno:5
1 row created.
SQL> /
Enter value for fname:Ahmad
Enter value for minit:V
Enter value for lname:Jabbar
Enter value for for: ssn:987987987
Enter value for bdate:29-mar-1969
Enter value for address:980Dallas,Houston,TX
Enter value for sex:M
Enter value for salary:25000
Enter value for super_ssn:987654321
Enter value for dno:4
1 row created.
SQL> /
Enter value for fname:James
Enter value for minit:E
Enter value for lname:Borg
Enter value for for: ssn:888665555
Enter value for bdate:10-nov-1937
Enter value for address:450Stone,Houston,TX
Enter value for sex:M
Enter value for salary:55000
Enter value for super_ssn:NULL
Enter value for dno:1
1 row created.
SQL> create table department(dname varchar2(10),dnum
number(1) primary key,mgr_ssn number(9),mgr_start_date date );
Table created.
SQL> insert into department
values('&dname',&dnum,&mgr_ssn,'&mgr_start_date');
Enter value for dname:Research
Enter value for dnum:5
Enter value for mgr_ssn:333445555
Enter value for mgr_start_date:22-may-1988
1 row created.
SQL> /
Enter value for dname:Administration
Enter value for dnum:4
Enter value for mgr_ssn:987654321
Enter value for mgr_start_date:01-jan-1995
1 row created.
SQL> /
Enter value for dname:Headquarters
Enter value for dnum:1
Enter value for mgr_ssn:888665555
Enter value for mgr_start_date:19-jun-1981
1 row created.
SQL> Create table Dept_locations(dnum number(1),dlocation
varchar2(10),primary key(dnum,dlocation);
);
Table created.
SQL> insert into dept_locations
values(&dnum,'&dlocation');
Enter value for dnum:1
Enter value for dlocation:Houston
1 row created.
SQL> /
Enter value for dnum:4
Enter value for dlocation:Stafford
1 row created.
SQL> /
Enter value for dnum:5
Enter value for dlocation:Bellaire
1 row created.
SQL> /
Enter value for dnum:5
Enter value for dlocation:SugarLand
1 row created.
SQL> /
Enter value for dnum:5
Enter value for dlocation:Houston
1 row created.
SQL> create table Works_on(essn number(10),pno
number(2),hours number(3,1),primary key(essn,pno));
Table created.
SQL> insert into works_on
values(&essn,&pno,&hours) ;
Enter value for essn:123456789
Enter value for pno:1
Enter value for hours:32.5
1 row created.
SQL> /
Enter value for essn:123456789
Enter value for pno:2
Enter value for hours:7.5
1 row created.
SQL> /
Enter value for essn:666884444
Enter value for pno:3
Enter value for hours:40.0
1 row created.
SQL> /
Enter value for essn:453453453
Enter value for pno:1
Enter value for hours:20.0
1 row created.
SQL> /
Enter value for essn:453453453
Enter value for pno:2
Enter value for hours:20.0
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for pno:2
Enter value for hours:10.0
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for pno:3
Enter value for hours:10.0
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for pno:10
Enter value for hours:10.0
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for pno:20
Enter value for hours:10.0
1 row created.
SQL> /
Enter value for essn:999887777
Enter value for pno:30
Enter value for hours:30.0
1 row created.
SQL> /
Enter value for essn:999887777
Enter value for
pno:10
Enter value for hours:10.0
1 row created.
SQL> /
Enter value for essn:987987987
Enter value for
pno:10
Enter value for hours:35.0
1 row created.
SQL> /
Enter value for essn:987987987
Enter value for pno:10
Enter value for hours:5.0
1 row created.
SQL> /
Enter value for essn:987654321
Enter value for pno:30
Enter value for hours:20.0
1 row created.
SQL> /
Enter value for essn:987654321
Enter value for
pno:20
Enter value for hours:15.0
1 row created.
SQL> /
Enter value for essn:888665555
Enter value for pno:20
Enter value for hours:NULL
1 row created.
SQL> /
Enter value for essn:123456789
Enter value for pno:3
Enter value for hours:10.0
1 row created.
SQL> create table project(pname varchar2(10),pnum
number(2) primary key,plocations varchar2(10),dnum number(1));
Table created.
SQL> insert into project
values('&pname',&pnum,'&ploc',&dnum);
Enter value for pname:ProductX
Enter value for pnum:1
Enter value for ploc:Bellaire
Enter value for dnum:5
1 row created.
SQL> /
Enter value for pname:ProductY
Enter value for pnum:2
Enter value for ploc:SugarLand
Enter value for dnum:5
1 row created.
SQL> /
Enter value for pname:ProductZ
Enter value for pnum:3
Enter value for ploc:Houston
Enter value for dnum:5
1 row created.
SQL> /
Enter value for pname:Computerization
Enter value for pnum:10
Enter value for ploc:Stafford
Enter value for dnum:4
1 row created.
SQL> /
Enter value for pname:Reorganization
Enter value for pnum:20
Enter value for ploc:Houston
Enter value for dnum:1
1 row created.
SQL> /
Enter value for pname:Newbenefits
Enter value for pnum:30
Enter value for ploc:Stafford
Enter value for dnum:4
1 row created.
SQL> create table dependent(essn
number(9),dependent_namevarchar2(10),sex char(1),bdate date,realationship
varchar2(10),primary key(essn,dependent_name) );
Table created.
SQL> insert into dependent
values(&essn,'&dependent_name','&sex','&bdate','&realationship');
Enter value for essn:333445555
Enter value for dependent_name:Alice
Enter value for sex:F
Enter value for bdate:05-apr-1986
Enter value for realationship:Daughter
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for dependent_name:Theodore
Enter value for sex:M
Enter value for bdate:25-nov-1983
Enter value for realationship:Son
1 row created.
SQL> /
Enter value for essn:333445555
Enter value for dependent_name:Joy
Enter value for sex:F
Enter value for bdate:03-may-1958
Enter value for realationship:Spouse
1 row created.
SQL> /
Enter value for essn:987654321
Enter value for dependent_name:Abner
Enter value for sex:M
Enter value for bdate:28-feb-1942
Enter value for realationship:Spouse
1 row created.
SQL> /
Enter value for essn:123456789
Enter value for dependent_name:Michael
Enter value for sex:M
Enter value for bdate:04-jan-1988
Enter value for realationship:Son
1 row created.
SQL> /
Enter value for essn:123456789
Enter value for dependent_name:Alice
Enter value for sex:F
Enter value for bdate:30-dec-1988
Enter value for realationship:Daughter
1 row created.
SQL> /
Enter value for essn:123456789
Enter value for dependent_name:Elizabeth
Enter value for sex:F
Enter value for bdate:05-may-1967
Enter value for realationship:Spouse
1 row created.
SQL> alter table employee add foreign key(super_ssn)
references employee;
Table altered.
SQL> alter table employee add foreign key(dno) references
department;
Table altered.
SQL> alter table department add foreign key(mgr_ssn)
references employee;
Table altered.
SQL> alter table works_on add foreign key(essn)
references employee;
Table altered.
SQL> alter table works_on add foreign key(pno) references
project;
Table altered.
SQL> alter table project add foreign key(dnum) references
department;
Table altered.
SQL> alter table dependent add foreign key(essn)
references employee;
Table altered.
No comments:
Post a Comment