Chap1-练习
Chap1 简单查询
key point:
简单select语句的书写
order by 数据排序
where条件判断
练习
--1.查询员工表所有数据
select * from EMPLOYEES;
--2.打印公司里所有的manager_id
select manager_id from EMPLOYEES;
---3.查询所员工的email全名,公司email 统一以 "@zpark.cn" 结尾.
select email||'@zpark.cn' "email全名" from EMPLOYEES;
--4.按照入职日期由新到旧排列员工信息
select * from EMPLOYEES order by hire_date desc;
--5.查询80号部门的所有员工
select * from EMPLOYEES where department_id=80;
--6.查询50号部门的员工姓名以及全年工资.
select first_name "姓名",salary*12 "全年工资" from EMPLOYEES where department_id=50;
--7.查询50号部门每人增长1000元工资之后的人员姓名及工资.
select first_name "姓名",salary+1000 "增长后工资" from EMPLOYEES where department_id=50;
--8.查询80号部门工资大于7000的员工的全名与工资.
select first_name "姓名" from EMPLOYEES where department_id=80 and salary>7000;
--9.查询80号部门工资大于8000并且提成高于0.3的员工姓名,工资以及提成
select first_name "姓名",salary "工资",commission_pct "提成" from EMPLOYEES where department_id=80 and salary>8000 and commission_pct >0.3;
--10.查询职位(job_id)为'AD_PRES'的员工的工资
select salary "工资" from EMPLOYEES where job_id='AD_PRES';
--11.查询工资高于7000但是没有提成的所有员工.
select * from EMPLOYEES where salary>7000 and commission_pct is null;
--12.查询佣金(commission_pct)为0或为NULL的员工信息
select * from EMPLOYEES where commission_pct is null;
--13.查询入职日期在2002-5-1到2002-12-31之间的所有员工信息
select * from employees where hire_date>to_date('2002-5-1','yyyy-MM-dd') and hire_date<to_date('2002-12-31','yyyy-MM-dd');
--14.显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from EMPLOYEES where first_name not like '%L%' or first_name like '%SM';
--15.查询电话号码以8开头的所有员工信息.
select * from EMPLOYEES where phone_number like '8%';
--16.查询80号部门中last_name以n结尾的所有员工信息
select * from EMPLOYEES where department_id=80 and last_name like '%n';
--17.查询所有last_name 由四个以上字母组成的员工信息
select * from EMPLOYEES where length(last_name)>4;
--18.查询first_name 中包含"na"的员工信息.
select * from EMPLOYEES where first_name like '%na%';