(1), employee information table EMP
Field name annotation data type is a primary key is the only item index dictionary
EMPNO employee number varchar2 (10) is
ENAME employee name VARHCHAR2 (60)
The JOB post VARHCHAR2 (9)
MGR head number varchar2 (10)
HIREDATE induction time DATE
SAL salary NUMBER (7, 2)
COMM communications NUMBER (7, 2)
DEPTNO department number varchar2 (60)
(2), the department information table DEPT
Field name annotation data type is a primary key is the only item index dictionary
DEPTNO department number varchar2 (60) is
DNAME department name VARHCHAR2 (128)
LOC position VARHCHAR2 (13)
Second, the topic
(1), what are the statistics of the average wage is higher than 3000 department;
(2), statistics from April to September in 2000, how many staff in the;
(3), how many family name "li" employees;
(4), according to the department of statistics departments under the number of employees, salary sum, sum, communications staff, highest pay expensive high staff;
(5), showing each department income ranked the top three employee information, employee number, employee name, department name, total income), wage income=+ communications;
(6), all show no communications employee information (display employee number, employee name);
(7), the statistics of the number of employees is higher than the average number of departments;
(8), the realization of query based on the number of employees from all the above information and all below information;
CodePudding user response:
Homework to doCodePudding user response:
1. Select d.D EPTNO, Max (d.D NAME), round (avg (sal), 2) from emp e, d dept where e.D EPTNO=d.D EPTNO group by d.D EPTNO order by deptno;
2. Select e. * from emp e where to_char (e.H IREDATE, '- dd yyyy - mm) & gt;='2000-04-01' and to_char (e.H IREDATE, '- dd yyyy - mm) & lt;='2000-09-31' order by deptno, ename;
3. Select count (*) from emp where e e.E NAME like '% % lee';
(4), according to the department of statistics departments under the number of employees, salary sum, sum, communications staff, highest pay expensive high staff;
Select e.d eptno, Max (d.D NAME) dname, count (*) number of employees, the sum (m) e.com communications combined, the sum (e.s al) salary sum
The from emp e dept d where e.D EPTNO=d.D EPTNO group by e.D EPTNO
(5), showing each department income ranked the top three employee information, employee number, employee name, department name, total income), wage income=+ communications;
The select EMPNO employee number, ENAME employee name, DNAME department name, NVL (SAL, 0) + NVL (comm, 0) total revenue from
(select e. *, d.D NAME, row_number () over (partition by e.D EPTNO order by (NVL (e.S AL, 0) + NVL (e.com m, 0))) rm
The from emp e dept d
Where e.D EPTNO=d.D EPTNO)
Where rm<=3
(6), all show no communications employee information (display employee number, employee name);
The select e.E MPNO employee number, e.E NAME employee NAME from emp e where NVL (e.com m, 0)=0
(7), the statistics of the number of employees is higher than the average number of departments;
The select d.D EPTNO, Max (d.D NAME) dname, count (e.e NAME)
The from emp e dept d
Where e.D EPTNO=d.D EPTNO
Group by d.D EPTNO
Having a count (e.e name) & gt; (select (count (distinct e1. Ename)/count (distinct d1, deptno)) d_avg from emp e1, dept d1)
The order by d.d eptno
(8), the realization of query based on the number of employees from all the above information and all below information;
- all employees of subordinate information
The select sys_connect_by_path (ename, '/') tree, level from emp connect by the prior empno=MGR;
- all employees superior information
The select sys_connect_by_path (ename, '/') tree, level from emp start with ename='CLARK' connect by the prior MGR=empno;
CodePudding user response: