Oracle的SQL练习Day3

/* 
1. 显示所有在欧洲区域工作的员工，显示他们的部门，姓名，岗位，薪资，国籍，结果按照国籍升序，薪资降序排列 
*/ 
select department_name, last_name, job_id, salary, country_name 
from employees, departments, locations, regions, countries 
where employees.department_id = departments.department_id 
and departments.location_id = locations.location_id 
and locations.country_id = countries.country_id 
and countries.region_id = regions.region_id 
and regions.region_name = ‘Europe’ 
order by country_name, salary desc;

/* 
2. 用尽可能多的方式显示姓名以小写’s’结尾的人员总数；只是2中方法 
*/ 
/first/ 
select count(*) from employees 
where last_name like ‘%s’; 
/second/ 
select count(*) from employees 
where substr(last_name,-1)=’s’;

/* 
3. 查询所有薪资大于‘IT_PROG’部门任何一人的薪资员工信息，显示姓名、薪资、岗位；用2种方法实现。 
*/ 
/first/ 
select employee_id last_name,salary,job_id 
from employees 
where salary > any(select salary from employees where job_id=’IT_PROG’) 
order by salary; 
/seconde/ 
select employee_id last_name,salary,job_id 
from employees 
where salary > (select min(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 index_name ,index_type ,owner FROM dba_indexes WHERE owner=’HR’;