使用HR用户登录 默认密码是HR
-- 【1. 查询工资大于12000的员工姓名和工资】
select first_name ||' '|| last_name as 全名 ,salary from employees where salary>12000 ;
-- 【2. 查询员工号为176的员工的姓名和部门号】
select first_name ||' '|| last_name as 全名,d.department_id from employees
join departments d on employees.department_id = d.department_id and employee_id=176;
-- 【3. 选择工资不在5000到12000的员工的姓名和工资】
select first_name ||''|| last_name as 全名,salary from employees where
salary<5000 or salary>12000;
select first_name ||''|| last_name as 全名,salary from employees where salary
not between 5000 and 12000;
-- 【4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间】
select first_name ||''|| last_name as 全名,e.job_id,e.hire_date from employees e
where e.hire_date between to_date('1998-02-01','yyyy-MM-dd')
and to_date('1998-05-01','yyyy-MM-dd') ;
-- 【5. 选择在20或50号部门工作的员工姓名和部门号】
select first_name ||''|| last_name as 全名,e.department_id from employees e
where e.department_id=20 or e.department_id=50;
-- 【6. 选择在1994年雇用的员工的姓名和雇用时间】
select e.first_name,e.hire_date from employees e where e.hire_date
between to_date('1994-01-01','yyyy-MM-dd') and to_date('1994-12-31','yyyy-MM-dd') ;
-- 【7. 选择公司中没有管理者的员工姓名及job_id】
select first_name ||''|| last_name as 全名,job_id from employees e
where e.manager_id is null ;
-- 【8. 选择公司中有奖金的员工姓名,工资和奖金级别--有待考察】
Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where commission_pct is not null;
Select initcap(concat(last_name,first_name)) "姓名",salary,commission_pct from employees where nvl2(commission_pct, commission_pct,0)>0;
select initcap(concat(last_name,first_name)) "姓名", commission_pct from employees where nvl(commission_pct,0)<>0;
-- 【9. 选择员工姓名的第三个字母是a的员工姓名】
select first_name ||''|| last_name as 全名 from employees e
where e.first_name like '__a%' and e.last_name like '__a%';
-- 【10. 选择姓名中有字母a和e的员工姓名】
select first_name ||' '|| last_name as 全名 from employees e where e.first_name
like '%a%' and e.first_name like '%e%' and e.last_name like'%a%'
and e.last_name like'%e%';
-- 【11. 显示系统时间】
select sysdate from dual;
-- 【12. 查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)】
select e.employee_id,first_name ||''|| last_name as 全名,e.salary ,
e.salary*1.2 newSalary from employees e;
-- 【13. 将员工的姓名按首字母排序,并写出姓名的长度(length)】
select e.employee_id,first_name ||''|| last_name as 全名,
length(e.last_name||e.first_name) from employees e order by e.first_name;
-- 【14. 查询各员工的姓名,并显示出各员工在公司工作的月份数】
select e.employee_id,first_name ||' '|| last_name as
全名,round(months_between(sysdate,e.hire_date)) from employees e;
-- 【15. 查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列】
select e.employee_id,first_name ||' '|| last_name as
全名,round(months_between(sysdate,e.hire_date)) as sda
from employees e order by sda desc;