Experiment-04
Object: To create the simple relationship
between two table using(.) dot operator.
Requirement: windows XP, Oracle 9i.
Program Code And Output:
SQL>
create table avneesh1
2
(emp_id number(20),
3
emp_name char(20),
4
emp_address varchar2(30),
5
dept_code number(20));
Table
created.
SQL>
create table avneesh2
2
(dept_name char(20),
3
dept_location varchar2(30),
4
dept_code number(20));
Table
created.
SQL>
insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code');
Enter
value for emp_id: 1031030014
Enter
value for emp_name: avneesh
Enter
value for emp_address: delhi
Enter
value for dept_code: 007
old 1: insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code')
new 1: insert into avneesh1
values('1031030014','avneesh','delhi','007')
1
row created.
SQL> insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code');
Enter
value for emp_id: 103103015
Enter
value for emp_name: avneesh k
Enter
value for emp_address: varanasi
Enter
value for dept_code: 008
old 1:
insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code')
new 1:
insert into avneesh1 values('103103015','avneesh k','varanasi','008')
1
row created.
SQL> insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code');
Enter
value for emp_id: 1031030016
Enter
value for emp_name: karan jindal
Enter
value for emp_address: delhi
Enter
value for dept_code: 009
old 1:
insert into avneesh1 values('&emp_id','&emp_name','&emp_address','&dept_code')
new 1:
insert into avneesh1 values('1031030016','karan jindal','delhi','009')
1
row created.
SQL> insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code');
Enter
value for emp_id: 1031030017
Enter
value for emp_name: akhil gupta
Enter
value for emp_address: delhi
Enter
value for dept_code: 010
old 1:
insert into avneesh1
values('&emp_id','&emp_name','&emp_address','&dept_code')
new 1:
insert into avneesh1 values('1031030017','akhil gupta','delhi','010')
1
row created.
SQL>
insert into avneesh2 values('cse','srm','007');
1
row created.
SQL>
insert into avneesh2 values('cse','srm','008');
1
row created.
SQL>
insert into avneesh2 values('cse','srm','009');
1
row created
SQL>
insert into avneesh2 values('cse','srm','010');
1
row created.
SQL>
desc avneesh1;
Name
Null? Type
-----------------------------------------------------
-------- ------------------------------------
EMP_ID NUMBER(20)
EMP_NAME
CHAR(20)
EMP_ADDRESS
VARCHAR2(30)
DEPT_CODE NUMBER(20)
SQL>
desc avneesh2;
Name
Null? Type
-----------------------------------------------------
-------- ------------------------------------
DEPT_NAME CHAR(20)
DEPT_LOCATION
VARCHAR2(30)
DEPT_CODE
NUMBER(20)
SQL>
select * from avneesh1;
EMP_ID
EMP_NAME EMP_ADDRESS DEPT_CODE
---------
-------------------- ------------------------------ ---------
1.031E+09
avneesh delhi 7
103103015
avneesh k varanasi 8
1.031E+09
karan jindal delhi 9
1.031E+09
akhil gupta delhi 10
SQL>
alter table avneesh2 modify dept_location char(20);
Table
altered.
SQL>
desc avneesh2;
Name Null? Type
-----------------------------------------------------
-------- ------------------------------------
DEPT_NAME
CHAR(20)
DEPT_LOCATION
CHAR(20)
DEPT_CODE
NUMBER(20)
SQL>
select * from avneesh2;
DEPT_NAME DEPT_LOCATION DEPT_CODE
--------------------
-------------------- ---------
cse srm 7
cse srm 8
cse srm 9
cse srm 10
SQL>
select * from avneesh1,avneesh2;
EMP_ID EMP_NAME EMP_ADDRESS DEPT_CODE DEPT_NAME
---------
-------------------- ------------------------------ ---------
--------------------
DEPT_LOCATION DEPT_CODE
--------------------
---------
1.031E+09
avneesh delhi 7 cse
srm 7
103103015
avneesh k varanasi 8 cse
srm 7
1.031E+09
karan jindal delhi 9 cse
srm 7
1.031E+09
akhil gupta delhi 10 cse
srm 7
1.031E+09
avneesh delhi 7 cse
srm 8
103103015
avneesh k varanasi 8 cse
srm 8
1.031E+09
karan jindal delhi 9 cse
EMP_ID EMP_NAME EMP_ADDRESS DEPT_CODE DEPT_NAME
---------
-------------------- ------------------------------ ---------
--------------------
DEPT_LOCATION DEPT_CODE
--------------------
---------
srm 8
1.031E+09
akhil gupta delhi 10 cse
srm 8
1.031E+09
avneesh delhi 7 cse
srm 9
103103015
avneesh k varanasi 8 cse
srm 9
1.031E+09
karan jindal delhi 9 cse
srm 9
1.031E+09
akhil gupta delhi 10 cse
srm 9
1.031E+09
avneesh delhi 7 cse
srm 10
EMP_ID EMP_NAME EMP_ADDRESS DEPT_CODE DEPT_NAME
---------
-------------------- ------------------------------ ---------
--------------------
DEPT_LOCATION DEPT_CODE
--------------------
---------
103103015
avneesh k varanasi 8 cse
srm 10
1.031E+09
karan jindal delhi 9 cse
srm 10
1.031E+09
akhil gupta delhi 10 cse
srm 10
16
rows selected.
SQL>
select * from avneesh1,avneesh2 where avneesh1.dept_code=avneesh2.dept_code;
EMP_ID
EMP_NAME EMP_ADDRESS DEPT_CODE
DEPT_NAME--------- -------------------- ------------------------------
--------- --------------------DEPT_LOCATION DEPT_CODE
--------------------
---------
1.031E+09
avneesh delhi 7 cse
srm 7
103103015
avneesh k varanasi 8 cse
srm 8
1.031E+09
karan jindal delhi 9 cse
srm 9
1.031E+09
akhil gupta delhi 10 cse
srm 10
SQL> select
e.emp_id,e.emp_name,e.emp_address,d.dept_location from avneesh1 e,avneesh2 d
where e.dept_code=d.dept_code;
EMP_ID EMP_NAME EMP_ADDRESS DEPT_LOCATION
---------
-------------------- ------------------------------ --------------------
1.031E+09
avneesh delhi srm
103103015
avneesh k varanasi srm
1.031E+09
karan jindal delhi srm
1.031E+09
akhil gupta delhi srm
SQL>
select avneesh1.emp_name,avneesh2.dept_location from avneesh1,avneesh2;
EMP_NAME DEPT_LOCATION
--------------------
--------------------
avneesh srm
avneesh
k srm
karan
jindal srm
akhil
gupta srm
avneesh srm
avneesh
k srm
karan
jindal srm
akhil
gupta srm
avneesh srm
avneesh
k srm
karan
jindal srm
akhil
gupta srm
avneesh srm
avneesh
k srm
karan
jindal srm
akhil
gupta srm
16
rows selected.
Result:The above command to create
relationship between two table using (.) operator has been executed
successfully.
0 comments:
Post a Comment