Oracle day2

1. 显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪水、职业；平均工资保留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

2. 显示员工数最多的部门信息，显示部门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)

3. 显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况；按照部门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

4. 周几录取的人数最少，显示人名和日期

   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')))

5. 自己做练习，验证 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'
6. 验证rollback ；并提供例子                                                                                                                                                   

   create table testtab4
   (Pk1 number, field1 varchar2(200));

   insert into testtab4 values(1,'AAA');

   commit;

   select * from testtab4;

   delete from testtab4;

7. 查询所有hr用户下的索引                                                                                                                                                       select index_name from all_indexes where owner = 'hr'