切换到 oracle的 hr用户下面练习

1.  查询工资大于12000的员工姓名和工资

Select initcap(concat(last_name,first_name)) "姓名",salary from employees where salary>12000;

2.  查询员工号为176的员工的姓名和部门号

select initcap(concat(last_name,first_name)) "姓名",department_id from employees where employee_id = 176;

3.  选择工资不在5000到12000的员工的姓名和工资

select initcap(concat(last_name,first_name)) "姓名", salary from employees where salary<5000 or salary>12000;

4.  选择雇用时间在1908-02-01到1908-05-01之间的员工姓名，job_id和雇用时间

写法一：

select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between '01-2月 -08' and  '01-5月 -08';

写法二：

select initcap(concat(last_name,first_name)) "姓名",job_id,hire_date from employees where hire_date between to_date('1908-02-01','YYYY-MM-DD') and to_date('1908-05-01','YYYY-MM-DD');

5.  选择在20或50号部门工作的员工姓名和部门号

写法一：

Select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id=20 or department_id=50;

写法二：

select initcap(concat(last_name,first_name)) "姓名",department_id from employees where department_id in (20,50);

6.  选择在1908年雇用的员工的姓名和雇用时间

写法一：

select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date like '%08';

写法二：

select initcap(concat(last_name,first_name))"姓名",hire_date from employees where hire_date between to_date('1908-1-1','YYYY-MM-DD') and to_date('1908-12-31','YYYY-MM-DD');

7.  选择公司中没有管理者的员工姓名及job_id

写法一：

Select initcap(concat(last_name,first_name)) "姓名",job_id from employees where manager_id is null;

写法二：

select initcap(concat(last_name,first_name)) "姓名",job_id from employees where nvl(manager_id