oracle习题练习

1. 查询所员工的email全名,公司email 统一以 "@zpark.cn" 结尾
   select email || ‘@zpark.cn’ from employees;
2. 按照入职日期由新到旧排列员工信息
   select employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id from employees order by hire_date;
3. 查询80号部门工资大于7000的员工的全名与工资.
   select first_name || ’ ‘ || last_name as name, salary from employees where department_id = 80 and salary > 7000;
4. 查询所有last_name 由四个以上字母组成的员工信息
   select * from employees where last_name like’____%’;

单行函数练习

1. 把hiredate列看做是员工的生日,查询本月过生日的员工(考察知识点:单行函数)
   select * 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 hire_date between to_date(‘2002-1-1’, ‘yyyy-mm-dd’) and to_date(‘2002-12-30’, ‘yyyy-mm-dd’);
   ③select * from employees where hire_date >= to_date(‘2002-1-1’, ‘yyyy-mm-dd’) and hire_date <= to_date(‘2002-12-30’, ‘yyyy-mm-dd’);
3. 查询2002年下半年入职的员工(考察知识点:单行函数)
   select * from employees where hire_date between to_date(‘2002-7-1’, ‘yyyy-mm-dd’) and to_date(‘2002-12-30’, ‘yyyy-mm-dd’);
4. 打印自己出生了多少天
   select to_date(to_char(sysdate, 'yyyy-mm-dd'), 'yyyy-mm-dd') - to_date('1996-3-1', 'yyyy-mm-dd') from dual;

组函数练习

1. 求1997年各个月入职的的员工个数(考察知识点:组函数)
   select count(*), to_char(hire_date, 'mm') from employees where to_char(hire_date, 'yyyy') = '2002' group by to_char(hire_date, 'mm');
2. 查询50号部门,60号部门,70号部门的平均工资
   select avg(salary), department_id from employees where department_id in(50, 60, 70) group by department_id;
3. 查询平均工资高于8000元的部门的最高工资.
   select max(salary), department_id from employees group by department_id having avg(salary) > 8000;
4. 统计公司里经理的人数
   ①select count(count(manager_id)), manager_id from employees where manager_id is not null group by manager_id;
   ②select count(distinct manager_id) from employees;

分页查询练习

查询工资排名第5到第10的员工信息
select * from (select employee_id, first_name, rownum as rn from (select * from employees order by salary desc)) where rn >= 5 and rn <= 10;

子查询练习

1. 查询工资大于本部门平均工资的员工基本信息
   select * from (select * from employees group by department_id having salary > avg(salary));
2. 显示与30号部门first_name为’Guy’员工工资相同的员工姓名和工资
   select first_name, salary from employees where salary = (select salary from employees where department_id = 30 and first_name = ‘Guy’);
3. 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
   select count(*), job_id from employees where salary > (select avg(salary) from employees) group by job_id;

表连接查询练习

1. 显示所有职员的姓名及其所在部门的名称和工资
   select employees.first_name, department_name, salary from employees left outer join departments on employees.department_id = departments.department_id;
2. 查询在研发部('IT')工作员工的编号，姓名，工作部门，工作所在地
   select employees.employee_id, employees.first_name ,employees.department_id from (employees left outer join departments on employees.department_id = departments.department_id ) left outer join locations on departments.location_id = locations.location_id
3. 查询各个部门的名称和员工人数
   select departments.department_name, count(*) from employees left join departments on employees.department_id = departments.department_id group by departments.department_name;
4. 查询员工的基本信息，附加其上级的姓名
   select e1.employee_id, e1.first_name, e1.salary, e2.first_name from employees e1 left join employees e2 on e1.manager_id = e2.employee_id
5. 求入职日期相同（年月日相同）的员工(考察知识点:自连接)(有重复)
   select e1.first_name, e2.first_name from employees e1 inner join employees e2 on e1.hire_date = e2.hire_date and e1.first_name != e2.first_name
   where e1.employee_id < e2.employee_id; --(去重)
6. 显示各个部门经理的基本工资
   select employees.employee_id, salary from departments left outer join employees on departments.manager_id = employees.employee_id