Emp-Dept With Joins

create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
);
create table emp(

empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
);
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');

insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');
insert into dept values(50, 'HR', 'AUSTIN');
insert into emp

values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51,
1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
1300, null, 10
);
insert into emp

values(
9999, 'Suhas', 'CLERK', 7782,
1300, null, null
);
insert into emp

values(
8888, 'Ashok', 'CLERK', 7782,
1300, null, null
);
insert into emp

values(
7777, 'Sapna', 'CLERK', null,
5000, null, NULL
);
----------
-- inner join
select empno, ename ,sal, job, d.deptno , dname,loc
from emp e join dept d on e.deptno=d.deptno;
-- natural join ( inner join without using on )

-- using common column name from both tables
from emp join dept ;
-- left join
from emp e left join dept d on e.deptno=d.deptno;
-- right join
from emp e right join dept d on e.deptno=d.deptno;
-- full join = left+ righ

from emp e full join dept d on e.deptno=d.deptno;
-- cortesion product/join
select empno, ename , job dname ,loc
from emp e , dept d order by 1;
=======================
create table country( code varchar2(100));
insert INTO country values ( 'IND');

insert into country values ( 'USA');
insert into country values ( 'SPA');
INSERT into country values ( 'GER');
insert into country values ( 'AGR');
SELECT * FROM country c , country d;
IND V/S USA

IND V/S SPA
IND V/S GER
IND V/S AGR
USA V/S IND
SELECT * FROM country c , country d
WHERE c.code > d.code
order by 1;
A < B < C < D .............Z
-- self join
=======================
select loc, ename , job , dname
from dept d right join emp e on d.deptno = e.deptno
minus
select loc, ename , job , dname
from emp e left join dept d on d.deptno = e.deptno;
==================
complex joins
create table emp

(
empno number(3,0) primary key,
ename varchar2(100) not null,
mgr varchar2(100),
deptno number(3)
);
Insert into emp values (1,'vinay', null ,Null);

Insert into emp values (2,'Nasir',1,10);
Insert into emp values (3,'Yamuna',2,10);
Insert into emp values (4,'Rachna',2,20);
Insert into emp values (5,'Shariq',4,50);
Insert into emp values (6,'VinayTL',4,50);
insert into emp values(7, 'Rajiv', 3, 40);
insert into emp values(8, 'Ashok', 3, 400);
create table dept

(
deptno number(3,0) primary key,
dname varchar2(100),
location_id number(3)
);
Insert into dept values (10,'IT',1);

Insert into dept values (20,'HR',1);
Insert into dept values (30,'Transport',2);
Insert into dept values (40,'Housekeeping',Null);
Insert into dept values (50,'Logistics',3);
create table loc

(
location_id number(3) primary key,
LocName varchar2(100),
country varchar2(3),
Pincode number(6)
);
Insert into loc values (1,'Banglore','IND', 56789);

Insert into loc values (2,'New Dehli','IND',678877);
Insert into loc values (3,'Lahore','PAK' ,null);
Insert into loc values (4,'Karachi','PAK',null);
Insert into loc values (5,'New Jersy','USA',null);
=========================
-- inner join
select ename , dname , country
from emp e join dept d on e.deptno = d.deptno
join loc l on d.location_id = l.location_id;
--left join
select ename , dname , country
from emp e left join dept d on e.deptno = d.deptno
left join loc l on d.location_id = l.location_id;
--left join
select ename , dname ,LocName, country
from loc l left join dept d on l.location_id = d.location_id
left join emp e on e.deptno= d.deptno;
-right join
from emp e right join dept d on e.deptno= d.deptno
right join loc l on l.location_id = d.location_id
-------------- both ways

from loc l left join dept d on l.location_id = d.location_id
left join emp e on e.deptno= d.deptno
minus
from emp e right join dept d on e.deptno= d.deptno
right join loc l on l.location_id = d.location_id

Emp-Dept With Joins

Uploaded by

Copyright:

Available Formats

Emp-Dept With Joins

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Emp-Dept With Joins

Uploaded by

Copyright:

Available Formats

create table dept(

create table emp(

insert into dept values(10, 'ACCOUNTING', 'NEW YORK');

insert into emp

insert into emp

insert into emp

insert into emp

-- natural join ( inner join without using on )

-- full join = left+ righ

insert INTO country values ( 'IND');

SELECT * FROM country c , country d;

IND V/S USA

A < B < C < D .............Z

create table emp

Insert into emp values (1,'vinay', null ,Null);

insert into emp values(8, 'Ashok', 3, 400);

create table dept

Insert into dept values (10,'IT',1);

create table loc

Insert into loc values (1,'Banglore','IND', 56789);

-------------- both ways

select ename , dname ,LocName, country

You might also like