oracle-web考试题.docVIP

oracle-web考试题 oracle: 1.查询工资高于7000但是没有提成的所有员工. select * from employees where salary7000 and commission_pct is null 2.查询所有last_name 由四个以上字母组成的员工信息 select * from employees where last_name like %____% 3.查询50号部门每人增长1000元工资之后的人员姓名及工资. select first_name,last_name,(salary+1000) salary from employees where department_id=50 4. 查询所员工的email全名,公司email 统一以 @ 结尾. select (email || @) email from employees 5.查询佣金(COMM)为0或为NULL的员工信息 select * from employees where commission_pct=0 or commission_pct is null 6.显示公司里所有员工的工资级别case when =5000 A 5000-8000 B 8000-15000 C 15000以上 D select first_name, case when salary=5000 then A when salary5000 and salary=8000 then B when salary8000 and salary=15000 then C else D end 工资级别 from employees 7.把hiredate列看做是员工的生日,求下月过生日的员工(考察知识点:单行函数) select first_name from employees where to_char(hire_date,mm)=to_char(add_months(sysdate,1),mm) 8.求1981年下半年入职的员工(考察知识点:单行函数) select * from employees where hire_date between01-7月-1981 and 01-1月-1982 9.判断今年是不是闰年 select sysdate-217 from dual 10.打印下半月入职的员工信息(单行函数) select * from employees where to_char(hire_date,mm)06 11.求1981年各个月入职的的员工个数(考察知识点:组函数) select count(*) 入职人数,to_char(hire_date,mm) 月份 from employees where to_char(hire_date,yyyy)=1981 group by hire_date 12.查询50号部门,60号部门,70号部门的平均工资 select avg(salary) 平均工资, department_id 部门编号 from employees where department_id in(50,60,70) group by department_id 13.查询各岗位的员工总数. select count(*) 员工数,job_id 岗位 from employees group by job_id 14.查询平均工资高于8000元的部门的最高工资 select max(max) 最高工资 from (select max(salary) max, department_id from employees group by department_id having avg(salary)8000) 15.统计公司里经理人数 select count(distinct manager_id) from employees 16.打印所有员工的姓名,工资,入职时间以及所在部门的名称 select first_name ||-|| last_name name,salary,hire_date,department_id from employees 17.打印出公司里最高工资排名在第五到第十位的员工信息 select * from (se