我要评论显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪水、职业;平均工资保留2位小数，千分位分隔符显示;结果按部门升序
select d.department_id, d.department_name,
count(e1.employee_id) employees,
nvl(to_char(avg(e1.salary), '99,999,999.99'),
'no average' ) avg_sal,
e2.last_name, e2.salary, e2.job_id
from departments d, employees e1, employees e2
where d.department_id = e1.department_id(+)
and d.department_id = e2.department_id(+)
group by d.department_id, d.department_name,
e2.last_name, e2.salary, e2.job_id
order by d.department_id, employees
显示员工数最多的部门信息，显示部门id、名称、部门员工数，部门的主管经理姓名
select d.department_id,d.department_name,count(*),m.first_name||m.last_name manager_name
from departments d,employees e,employees m
where d.department_id = e.department_id(+)
and d.manager_id = m.manager_id(+)
group by d.department_id, d.department_name,m.first_name||m.last_name
having count(*) = (select max(count(*))
from employees
group by department_id)
显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况;按照部门id排序
select e.employee_id, e.last_name,
e.department_id,e.salary, (e.salary-avg(s.salary)) salary_avg
from employees e, employees s
where e.department_id = s.department_id
group by e.employee_id, e.last_name, e.department_id,e.salary
order by department_id
周几录取的人数最少，显示人名和日期
select employee_id,first_name,last_name, to_char(hire_date, 'day') day
from employees
where to_char(hire_date, 'day') =
(select to_char(hire_date, 'day')
from employees
group by to_char(hire_date, 'day')
having count(*) = (select min(count(*))
from employees
group by to_char(hire_date, 'day')))
自己做练习，验证 between .. and 的外链接 select job_id
from employees
where to_char(hire_date,'yyyy-mm-dd')
between '1990-01-01' and '1990-01-31'
intersect
select job_id
from employees
where to_char(hire_date,'yyyy-mm-dd')
between '1991-01-01'and '1991-01-31' 验证rollback ;并提供例子
create table testtab4
(pk1 number, field1 varchar2(200));
insert into testtab4 values(1,'aaa');
commit;
select * from testtab4;
delete from testtab4;
查询所有hr用户下的索引 select index_name from all_indexes where owner = 'hr'
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！
网友评论