oracle练习及我的答案

select * from employees

--1.选择部门110中的所有员工.

select * from employees where department_id = 110;

 

--2.列出所有办事员(PU_CLERK)的姓名，编号和部门编号.

select last_name,employee_id,department_id 

from employees 

where job_id = 'PU_CLERK';

 

--3.找出提成高于10%的员工.

select * from employees where commission_pct>0.1;

 

--4.找出没有佣金的员工. (is null)

select * from employees where commission_pct is null;

 

--5.找出部门110中所有FI_ACCOUNT和部门30中所有PU_CLERK的详细资料.

select * from employees

where 

(department_id = 110 and job_id = 'AC_ACCOUNT') 

or 

(department_id=30 and job_id='PU_CLERK' );

 

--6.找出部门110中所有AC_ACCOUNT,部门30中薪金大于4000的所有PU_CLERK详细资料.

select * from employees

where 

(department_id = 110 and job_id = 'AC_ACCOUNT') 

or 

(department_id=30 and job_id='PU_CLERK' and salary>4000 );

 

--7.找出收取佣金的员工的不同工作.

select job_id from employees where commission_pct is not null;

 

--8.找出不收取佣金或收取的佣金低于5%的员工.

select * from employees where commission_pct is null or commission_pct<5%;

 

--9.找出各月倒数第3天受雇的所有员工.    --找出现在距离月底还有多少天。

select * from employees where (last_day(hire_date)-hire_date)=2;

 

--10.找出早于12年前受雇的员工.

select * from employees where months_between(sysdate,hire_date) > 144;

 

--11.以首字母大写的方式显示所有员工的姓名.

select initcap(first_name)||' '||initcap(last_name) 姓名 from employees;

 

--12.显示正好为5个字符的员工的姓名.

select * from employees where length(first_name)=5

 

--13.显示不带有"R"的员工的姓名.

select * from employees where first_name like '%R%'

 

--14.显示所有员工姓名的前三个字符.

select substr(first_name,0,3) from employees;

 

--15.显示所有员工的姓名,用a替换所有"A"

select replace(first_name,'a','A') from employees

 

--16.显示满10年服务年限的员工的姓名和受雇日期.

select first_name||' '||last_name 姓名,hire_date from employees where months_between(sysdate,hire_date) > 120;

 

--17.显示员工的详细资料,按姓名排序.

select * from employees order by first_name;

 

--18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.

select first_name||' '||last_name 姓名 ,hire_date from employees order by hire_date;

 

--19.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.

select first_name||' '||last_name 姓名,department_id,salary from employees order by department_id desc, salary

 

--20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.

select first_name||' '||last_name 姓名,to_date(hire_date,'dd/mon/yy') 入职时间 from employees order by to_char(round(hire_date, 'mon')) desc, hire_date

 

--21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.

select first_name||' '||last_name 姓名,round(salary/30,0) 日薪资 from employees

 

--22.找出在(任何年份的)2月受聘的所有员工。

select * from employees where lower(hire_date) like '%-2月%'

select * from employees where to_char(hire_date,'mon') like '2%'

 

--23.对于每个员工,显示其加入公司的天数.

select first_name,trunc(sysdate-hire_date) from employees

 

--24.显示姓名字段的任何位置包含"A"的所有员工的姓名.

select * from employees where first_name like '%A%' and last_name like '%A%'

 

--25.以年月日的方式显示所有员工的服务年限. (大概)

select first_name||' '||last_name 姓名,hire_date, trunc(months_between(sysdate, hire_date)/12)||'年'||

trunc((months_between(sysdate, hire_date)/12-trunc(months_between(sysdate, hire_date)/12))*12)||'月'

||trunc((months_between(sysdate, hire_date)-trunc(months_between(sysdate, hire_date)))*30)||'天' 服务年限

from employees