oracle数据库相关练习一(基础、单行函数、组函数)

1.查询员工表所有数据
select * from employees;
2.	打印公司里所有的manager_id
select manager_id from employees where job_id like '%MAN';
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号部门每人增长1000元工资之后的人员姓名及工资.
select first_name||'-'||last_name, salary+1000 "salary" from employees where department_id=50
7.	查询80号部门工资大于7000的员工的全名与工资.
select first_name||'-'||last_name , salary from employees where salary>7000
8.	查询80号部门工资大于8000并且佣金高于0.3的员工姓名,工资以及提成
select last_name,salary,commission_pct from employees where salary>8000 and commission_pct>0.3
9.	查询职位(job_id)为'AD_PRES'的员工的工资
select salary from employees where job_id='AD_PRES'
10.	查询佣金(commission_pct)为0或为NULL的员工信息
select * from employees where commission_pct=0 or commission_pct is null
11.	查询入职日期在1997-5-1到1997-12-31之间的所有员工信息
select * from employees where hire_date between to_date('1997-5-1','yyyy-mm-dd') and 
to_date('1997-12-31','yyyy-mm-dd')
12.	显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
select * from employees where first_name not like 'L%' or first_name like 'SM%'
13.	查询电话号码以5开头的所有员工信息.
select * from employees where phone_number like '5%'
14.	查询80号部门中last_name以n结尾的所有员工信息
select * from employees where last_name like '%n' and department_id=80
15.	查询所有last_name 由四个以上字母组成的员工信息
select * from employees where last_name like '____'


单行函数练习
1.	把hiredate列看做是员工的生日,查询本月过生日的员工(考察知识点:单行函数)
select last_name from employees where to_char(hire_date,'mm')=to_char(sysdate,'mm')
2.	请用三种以上的方式查询2002年入职的员工(考察知识点:单行函数)
select * from employees where to_char(hire_date,'yyyy')=2002
select * from employees where to_char(hire_date,'yyyy') like 2002
select * from employees where to_char(hire_date,'yyyy') in 2002
3.	查询2002年下半年入职的员工(考察知识点:单行函数)
select * from employees where to_char(hire_date,'yyyy')=2002 and to_char(hire_date,'mm')>5
4.	打印自己出生了多少天
select sysdate-to_date('1992-03-27','yyyy-mm-dd') from dual
5.	打印入职时间超过10年的员工信息
select * from employees where to_char(sysdate,'yyyy')-to_char(hire_date,'yyyy')>=10
select * from employees  where months_between(sysdate,hire_date)/12>10;


组函数练习
1.	显示各种职位的最低工资(组函数)
select job_id,min(salary)  from employees group by job_id
2.	求1997年各个月入职的的员工个数(考察知识点:组函数)
select count(*) from employees where to_char(hire_date,'yyyy')=1997 group by to_char(hire_date,'mm')
3.	查询每个部门,每种职位的最高工资(考察知识点:分组)
select department_id,job_id,max(salary) from employees group by department_id,job_id
4.	查询各部门的总工资
select department_id,sum(salary) from employees group by department_id
5.	查询50号部门,60号部门,70号部门的平均工资
select department_id,avg(salary) from employees where department_id in(50,60,70) group by department_id
6.	查询各部门的最高工资,最低工资.
select department_id,max(salary),min(salary) from employees group by department_id
7.	查询各岗位的员工总数.
select job_id,count(*) from employees group by job_id
8.	查询各部门中各个岗位的平均工资.
select department_id,job_id ,avg(salary) from employees group by department_id,job_id
9.	查询平均工资高于8000元的部门的最高工资.
select department_id,min(salary),max(salary)  from employees group by department_id having avg(salary)>8000

本文地址：https://blog.csdn.net/Mr_YXX/article/details/107674443