create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)Table created.
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)
)Table created.
insert into DEPT (DEPTNO, DNAME, LOC)
values(10, 'ACCOUNTING', 'NEW YORK')1 row(s) inserted.
insert into dept
values(20, 'RESEARCH', 'DALLAS')1 row(s) inserted.
insert into dept
values(30, 'SALES', 'CHICAGO')1 row(s) inserted.
insert into dept
values(40, 'OPERATIONS', 'BOSTON')1 row(s) inserted.
insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
)1 row(s) inserted.
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'),
2850, null, 30
)1 row(s) inserted.
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10
)1 row(s) inserted.
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'),
2975, null, 20
)1 row(s) inserted.
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
)1 row(s) inserted.
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'),
3000, null, 20
)1 row(s) inserted.
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
to_date('17-12-1980','dd-mm-yyyy'),
800, null, 20
)1 row(s) inserted.
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
to_date('20-2-1981','dd-mm-yyyy'),
1600, 300, 30
)1 row(s) inserted.
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
to_date('22-2-1981','dd-mm-yyyy'),
1250, 500, 30
)1 row(s) inserted.
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
to_date('28-9-1981','dd-mm-yyyy'),
1250, 1400, 30
)1 row(s) inserted.
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
to_date('8-9-1981','dd-mm-yyyy'),
1500, 0, 30
)1 row(s) inserted.
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
to_date('13-JUL-87', 'dd-mm-rr') - 51,
1100, null, 20
)1 row(s) inserted.
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
to_date('3-12-1981','dd-mm-yyyy'),
950, null, 30
)1 row(s) inserted.
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
to_date('23-1-1982','dd-mm-yyyy'),
1300, null, 10
)1 row(s) inserted.
select ename, dname, job, empno, hiredate, loc
from emp, dept
where emp.deptno = dept.deptno
order by ename| ENAME | DNAME | JOB | EMPNO | HIREDATE | LOC | ADAMS | RESEARCH | CLERK | 7876 | 23-MAY-87 | DALLAS | ALLEN | SALES | SALESMAN | 7499 | 20-FEB-81 | CHICAGO | BLAKE | SALES | MANAGER | 7698 | 01-MAY-81 | CHICAGO | CLARK | ACCOUNTING | MANAGER | 7782 | 09-JUN-81 | NEW YORK | FORD | RESEARCH | ANALYST | 7902 | 03-DEC-81 | DALLAS | JAMES | SALES | CLERK | 7900 | 03-DEC-81 | CHICAGO | JONES | RESEARCH | MANAGER | 7566 | 02-APR-81 | DALLAS | KING | ACCOUNTING | PRESIDENT | 7839 | 17-NOV-81 | NEW YORK | MARTIN | SALES | SALESMAN | 7654 | 28-SEP-81 | CHICAGO | MILLER | ACCOUNTING | CLERK | 7934 | 23-JAN-82 | NEW YORK | SCOTT | RESEARCH | ANALYST | 7788 | 19-APR-87 | DALLAS | SMITH | RESEARCH | CLERK | 7369 | 17-DEC-80 | DALLAS | TURNER | SALES | SALESMAN | 7844 | 08-SEP-81 | CHICAGO | WARD | SALES | SALESMAN | 7521 | 22-FEB-81 | CHICAGO |
|---|
select dname, count(*) count_of_employees
from dept, emp
where dept.deptno = emp.deptno
group by DNAME
order by 2 desc| DNAME | COUNT_OF_EMPLOYEES | SALES | 6 | RESEARCH | 5 | ACCOUNTING | 3 |
|---|
select empno, ename, XMLELEMENT("Employee", XMLForest(EMPNO, ENAME, job, deptno, sal, comm)) emp_xml from EMP| EMPNO | ENAME | EMP_XML | 7839 | KING | <Employee><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><DEPTNO>10</DEPTNO><SAL>5000</SAL></Employee> | 7698 | BLAKE | <Employee><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><DEPTNO>30</DEPTNO><SAL>2850</SAL></Employee> | 7782 | CLARK | <Employee><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><DEPTNO>10</DEPTNO><SAL>2450</SAL></Employee> | 7566 | JONES | <Employee><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><DEPTNO>20</DEPTNO><SAL>2975</SAL></Employee> | 7788 | SCOTT | <Employee><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><DEPTNO>20</DEPTNO><SAL>3000</SAL></Employee> | 7902 | FORD | <Employee><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><DEPTNO>20</DEPTNO><SAL>3000</SAL></Employee> | 7369 | SMITH | <Employee><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><DEPTNO>20</DEPTNO><SAL>800</SAL></Employee> | 7499 | ALLEN | <Employee><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><DEPTNO>30</DEPTNO><SAL>1600</SAL><COMM>300</COMM></Employee> | 7521 | WARD | <Employee><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><DEPTNO>30</DEPTNO><SAL>1250</SAL><COMM>500</COMM></Employee> | 7654 | MARTIN | <Employee><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><DEPTNO>30</DEPTNO><SAL>1250</SAL><COMM>1400</COMM></Employee> | 7844 | TURNER | <Employee><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><DEPTNO>30</DEPTNO><SAL>1500</SAL><COMM>0</COMM></Employee> | 7876 | ADAMS | <Employee><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><DEPTNO>20</DEPTNO><SAL>1100</SAL></Employee> | 7900 | JAMES | <Employee><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><DEPTNO>30</DEPTNO><SAL>950</SAL></Employee> | 7934 | MILLER | <Employee><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><DEPTNO>10</DEPTNO><SAL>1300</SAL></Employee> |
|---|