oracle查询数据实例,Oracle数据库查询的使用实例记录-Oracle

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