postgresql 练习题（三）

1. 显示所有在欧洲区域工作的员工，显示他们的部门，姓名，岗位，薪资，国籍，结果按照国籍升序，薪资降序排列

   select department_name, last_name, job_id, salary, country_name,region_id
   from employees e, departments d, locations l, countries co
   where e.department_id = d.department_id
   and l.country_id = co.country_id
   --Germany和UK属于欧洲，region_id=1
   and co.region_id = '1'
   order by country_name ASC, salary desc;

2. 用尽可能多的方式显示姓名以小写’s’结尾的人员总数；

   --方法一：
   
   select count(*) from employees where last_name like '%s';
   
   --方法二：
   
   select count(*) from employees
   where substr (reverse(last_name),1,1)='s';
   
   --方法三：
   
   select count(*) from employees
   where last_name ~'s$';

3. 查询所有薪资大于‘IT_PROG’部门任何一人的薪资员工信息，显示姓名、薪资、岗位；用2种方法实现。

   --方法一：
   
   select last_name,salary,job_id
   from employees
   where salary > (select min(salary)from employees where job_id='IT_PROG')
   order by salary;
   
   --方法二：
   
   select last_name,salary,job_id
   from employees
   where salary > any(select salary from employees where job_id='IT_PROG')
   order by salary;

4. 创建一张表与employees表相同的表，表名：employees_工号;将’Marketing’，’IT’ 两个部门下员工导入该表。提供脚本。

   create table employees_工号 as select * from employees where 1=2;
   
   
   insert into employees_工号 (select * from employees
   where job_id like 'IT%' or job_id like 'MK%');

5. 查询HR下所有的约束/索引。

   select * from pg_indexes where schemaname='public'