oracle使用实例记录
显示所有在欧洲区域工作的员工,显示他们的部门,姓名,岗位,薪资,国籍,结果按照国籍升序,薪资降序排列 select d.department_name, e.last_name, e.job_id, e.salary, c.country_name from employees e, departments d, locations l, regions r, countries c where e.department_id = d.department_id and d.location_id = l.location_id and l.country_id = c.country_id and c.region_id = r.region_id and r.region_name = 'europe' order by country_name, salary desc; 用尽可能多的方式显示姓名以小写’s’结尾的人员总数;至少2种方法 第一种方法 select count(*) from employees where last_name like '%s'; 第二种方法 select count(*) from employees where substr(last_name,-1)='s'; 正则表达式 select count(*) from employees where regexp_like(last_name,'(*)s$'); 查询所有薪资大于‘it_prog’部门任何一人的薪资员工信息,显示姓名、薪资、岗位;用2种方法实现 第一种方法 select employee_id,first_name,last_name,salary,job_id from employees where salary > any(select salary from employees where job_id='it_prog') order by salary; 第二种方法 select employee_id,first_name,last_name,salary,job_id from employees where salary > (select min(salary) from employees where job_id='it_prog') order by salary; 创建一张表与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%'); 查询hr下所有的约束/索引 select index_name ,index_type from dba_indexes where owner='hr';