postgresql 练习题（二）

1. 显示部门编号、部门名字、该部门的员工数、每个部门的平均工资，部门负责人信息，包括姓名、薪水、职业；平均工资保留2位小数，千分位分隔符显示；结果按部门升序

   select employee.department_id,employee.employees,
   to_char(employee.avg_sal,'99,999.00') as avg_sal,
   department_name,manager.job_id,manager.last_name,manager.salary
   
   from (select department_id,count(*) as employees,avg(salary) as avg_sal,
   manager_id as manager
   from employees group by department_id,manager_id) employee,
   employees manager, departments
   
   where employee.manager = manager.employee_id
   and employee.department_id = departments.department_id;

2. 显示员工数最多的部门信息，显示部门ID、名称、部门员工数，部门的主管经理姓名

   select distinct d.department_id, d.department_name,a.counts,m.first_name
   
   from employees e,departments d,employees m,
   (select  department_id did, count(department_id) counts
   
   from employees
       group by department_id
       having count(department_id) >= all (select count(department_id)
                                                from employees
                                                group by department_id)) a
   
    where d.department_id = a.did
    and d.manager_id=m.employee_id

3. 显示工号、姓名、薪水、部门编号、薪资，薪资与部门平均工资的差异情况；按照部门ID排序

   select employee_id, first_name,last_name, employees.department_id, salary,
   salary - a.avg_salary as salary_chayi
   
   from (select avg(salary) as avg_salary, department_id 
           from employees
           group by department_id) a, employees
   where employees.department_id = a.department_id
   order by employees.department_id

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

   select e.employee_id,e.first_name,e.last_name,to_char(e.hire_date, 'day'),a.counts
   from employees e,
   
   (select to_char(hire_date, 'day') days,
   count(employee_id) counts
   from employees
   group by to_char(hire_date, 'day')
   
   having count(employee_id) <= all
   (select count(employee_id)from employees group by to_char(hire_date, 'day'))
   ) a
   
   where to_char(e.hire_date, 'day') = a.days

5. 自己做练习，验证 between .. and  的外链接。

   --查询部门编号在1-50之间的员工姓名、电话、部门名称和部门编号
   
   select first_name || last_name as full_name,
   phone_number,department_name, departments.department_id
   
   from departments, employees
   where departments.manager_id = employees.manager_id
   and departments.department_id between 1 and 50;

6. 验证rollback ；并提供例子

创建 COMPANY 表（菜鸟教程）

开始一个事务，并从表中删除 age = 25 的记录，使用 ROLLBACK 命令撤消所有的更改

检查 COMPANY 表，仍然有以下记录：

开始另一个事务，从表中删除 age = 25 的记录，使用 COMMIT 命令提交所有的更改，检查 COMPANY 表，记录已被删除：

ROLLBACK 命令是用于撤消尚未保存到数据库的事务命令，即回滚事务。

        7.查询所有hr用户下的索引

由于没有hr用户，可以查所有索引或者查一下用户自己创建的表的索引

-- 查所有索引

select * from pg_indexes

--查询用户创建的表的索引信息

select * from pg_indexes where schemaname='public'

或者直接使用\di命令直接查看