练习
- 查询员工表所有数据
- 打印公司里所有的manager_id(不能重复)
- 查询所员工的email全名,公司email 统一以 "@zpark.cn" 结尾.
- 按照入职日期由新到旧排列员工信息
- 查询80号部门的所有员工
- 查询50号部门的员工姓名以及全年工资.
- 查询50号部门每人增长1000元工资之后的人员姓名及工资.
- 查询80号部门工资大于7000的员工的全名与工资.
- 查询80号部门工资大于8000并且提成(commission_pct)高于0.3的员工姓名,工资以及提成
- 查询职位(job_id)为'AD_PRES'的员工的工资
- 查询工资高于7000但是没有提成的所有员工.
- 查询佣金(commission_pct)为0或为NULL的员工信息
- *显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
- *查询电话号码以5开头的所有员工信息.
- *查询80号部门中last_name以n结尾的所有员工信息
- *查询所有last_name 由四个以上字母组成的员工信息
- *查询first_name 中包含"na"的员工信息.
- *显示公司里所有员工的工资级别case when
A | <=5000 |
B | >=5001 and <=8000 |
C | >=8001 and <=15000 |
D | >15000 |
19.*根据入职时间(hire_date)打印出员工级别,获取日期中的年份的后两位:to_char(日期列,’yy’)
资深员工 | 95前(包含95) |
普通员工 | 95 -- 99(包含99) |
新员工 | 99年后 |
答案:
1.
select * from employees
2.
select distinct manager_id from employees
3.
select email || '@zpark.cn' from employees
4.
select * from employees order by Hire_date desc
5.
select * from employees where department_id = 80
6.
select first_name||last_name 姓名,salary,department_id from employees where department_id = 50
7.
select first_name||last_name 姓名,salary+1000 工资,department_id from employees where department_id = 50
8.
select first_name||last_name 姓名,salary 工资,department_id from employees where department_id = 80 and salary>7000
9.
select first_name||last_name 姓名,salary 工资,department_id,commission_pct 提成 from employees where commission_pct>0.3 and salary>8000
10.
select first_name||last_name 姓名,salary 工资,job_id 职位 from employees where job_id = 'AD_PRES'
11.
select * from employees where salary>7000 and commission_pct is null
12.
select * from employees where commission_pct = 0 or commission_pct is null
13.
select * from employees where first_name not like 'L%' or first_name like 'SM%'
14.
select * from employees where phone_number like '5%'
15.
select * from employees where last_name like '%n' and department_id=80
16.
select * from employees where last_name like '____'
17.
select * from employees where first_name like '%na%'
18.
select first_name||last_name 姓名,salary 工资,
(case when salary<=5000 then 'A'
when salary>=5001 and salary<=8000 then 'B'
when salary>=8001 and salary<=15000 then 'C'
when salary>15000 then 'D'
end)工资级别
from employees
19.
select first_name||last_name 姓名,hire_date 入职时间,to_char(hire_date,'yy') YY数值,(case
when to_char(hire_date,'yy')<=95 and to_char(hire_date,'yy')>20 then '资深员工'
when to_char(hire_date,'yy')<=99 and to_char(hire_date,'yy')>95 then '普通员工'
else '新员工'
end)员工级别 from employees