add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Dtable_department(
department_id int,
department_name varchar2(100),
loc varchar2(100),
constraint pk_dept primary key (department_id)
);

create table ETable_employee(
empno int,
first_name varchar2(100),
job varchar2(100),
mgr int,
hire_date date,
salary number(7,2),
comm number(7,2),
department_id int,
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (department_id) references Dtable_department(department_id)
);

begin
insert into Dtable_department(department_id, department_name, loc)
values(10, 'ACCOUNTING', 'NEW YORK');
insert into Dtable_department
values(20, 'RESEARCH', 'DALLAS');
insert into Dtable_department
values(30, 'SALES', 'CHICAGO');
insert into Dtable_department
values(40, 'OPERATIONS', 'BOSTON');

insert into ETable_employee
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
);
insert into ETable_employee
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'),
2850, null, 30
);
insert into ETable_employee
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10
);
insert into ETable_employee
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'),
2975, null, 20
);
insert into ETable_employee
values( 
1 rows affected
select department_name, department_id, first_name, hire_date, salary
from
(
select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary,
dense_rank() over ( order by hire_date ) as e_rank_hire
from Dtable_department d
join Etable_employee e
on e.department_id = d.department_id
)
where e_rank_hire <= 10
order by e_rank_hire
DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME HIRE_DATE SALARY
RESEARCH 20 SMITH 17-DEC-80 800
SALES 30 ALLEN 20-FEB-81 1600
SALES 30 WARD 22-FEB-81 1250
RESEARCH 20 JONES 02-APR-81 2975
SALES 30 BLAKE 01-MAY-81 2850
ACCOUNTING 10 CLARK 09-JUN-81 2450
SALES 30 TURNER 08-SEP-81 1500
SALES 30 MARTIN 28-SEP-81 1250
ACCOUNTING 10 KING 17-NOV-81 5000
RESEARCH 20 FORD 03-DEC-81 3000
SALES 30 JAMES 03-DEC-81 950
select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary
from Dtable_department d
join Etable_employee e
on e.department_id = d.department_id
order by hire_date
fetch first 10 rows with ties
DEPARTMENT_NAME DEPARTMENT_ID FIRST_NAME HIRE_DATE SALARY
RESEARCH 20 SMITH 17-DEC-80 800
SALES 30 ALLEN 20-FEB-81 1600
SALES 30 WARD 22-FEB-81 1250
RESEARCH 20 JONES 02-APR-81 2975
SALES 30 BLAKE 01-MAY-81 2850
ACCOUNTING 10 CLARK 09-JUN-81 2450
SALES 30 TURNER 08-SEP-81 1500
SALES 30 MARTIN 28-SEP-81 1250
ACCOUNTING 10 KING 17-NOV-81 5000
RESEARCH 20 FORD 03-DEC-81 3000
SALES 30 JAMES 03-DEC-81 950