数据库

Oracle数据库语句练习:

--表employee
--1.创建这种表,并输入相关数据
--2.查询员工的first_name,department_id,salary,其中员工的department_id,salary
--与有奖金的任何一位员工的department_id,salary相同即可
--3.选择(工资大于job_id="23"的员工的工资) 的员工的first_name和job_id以及salary
select * from employee

create table employee(
employee number(10),
first_name varchar(100),
job_id number(10),
manager_id number(10),
department_id number(10),
hire_date date,
salary number(10),
commission number(10)
);
alter table employee rename column employee to employee_id;
insert into employee values(15,'wance1',23,113,1014,null,900,1);
insert into employee values(12,'wance2',20,1010,1011,null,2800,1);
insert into employee values(13,'wance3',21,111,1012,null,2800,1);
insert into employee values(14,'wance4',26,161,1012,null,2400,0);
insert into employee values(16,'wance14',216,161,1012,null,2400,null);
insert into employee values(17,'wance14',216,null,112,null,2400,null);
--all和any
--all:表示和返回的所有值比较
--any:表示和返回的任意一个值比较
select first_name,job_id,salary from employee where 
salary>all(select salary from employee where job_id = 23);

select first_name,department_id,salary from employee where 
(department_id,salary) in
(select department_id,salary from employee where commission is not null);
--正确表达
select count(*) from employee where(department_id,salary) in 
(select department_id,salary from employee where commission is not null);
--笛卡尔积的错误表达
select first_name,department_id,salary from employee where
department_id in (select department_id from employee where commission is not null)
and salary in (select salary from employee where commission is not null);

--1 
--选择所有没管理者的员工的first_name(使用not exists)
select first_name from employee where not exists(
select * from employee where 
manager_id is null);
--not exists
--配合子查询使用,当子查询返回的结果为空,表示where条件成立,执行外查询
--exists:和上面相反

--2
--查询与1号或2号员工的manager_id或deparment_id相同的其他员工的
--employee_id,manager_id,department_id
select employee,manager_id,department_id from employee where
manager_id in (
select manager_id from employee where manager_id in(113,161)
)or
department_id in(
select department_id from employee where department_id in(1012,1011)
)

--3
--返回比本部门平均工资高的员工的first_name,department_id,salary以及平均工资
--计算出每个部门的平均工资
select employee_id,manager_id,department_id from employee where 
(manager_id,department_id) in 
(select manager_id,department_id from employee where 
employee_id in (2,3)) and employee_id not in (2,3)

--返回比本部门平均工资高的员工的first_name,department_id,salary以及平均工资
--计算出每个部门的平均工资
--第一步 求出每个部门的平均工资
select avg(salary) from employee group by department_id
--第二步 筛选出比这个平均高的员工高的员工
select employee_id from employee where salary>()
--第三步 在from子句中使用查询
select first_name,e1.department_id,salary,e2.avg from 
employee e1,(select department_id,avg(salary) avg from 
employee group by department_id) e2
where e1.department_id=e2.department_id 
and e1.salary>e2.avg

--单列子查询
--单列子查询表达式是在一行中只返回一列的子查询
--单列子查询可以在任何一个地方使用  比如:
--select,from,where,order by..
--要求按照员工department_name进行排序

select department_name from departments where(
select manager_id from departments group by locattion_id
);

--查询员工中工资大于本部门平均工资的员工的first_name,salary,department_name

--单列子查询(相关子查询)
--按照一行接一行的顺序执行,主查询的每一行都会执行一次子查询

--with子句
--使用该子句可以避免在select语句中重复书写相同的语句块
--with子句将该子句中的语句块一次执行并存储到用户的临时表空间
--使用with 子句可以题号查询效率
--索引,缓存,with子句(本质上就是缓存)

--查询公司中工资比smith高的员工的信息
select * from employee where salary>(select salary from employee where first_name = 'smith')

with smith_sal as(select salary from employee where first_name = 'smith')
select * from employee where salary>(select salary from smith_sal)
--当题目很庞大的时候,可以使用with语句拆分成很多个模块
--查询公司中各部门的总工资大于公司中各部门平均总工资的部门的信息

with dept_sumsal as(
select sum(salary) sum_sal01,department_name from departments d,employee e 
where d.department_id = e.department_id group by department_name)
,depart_avgsal as(
select sum(sum_sal01)/count(*) sum_sum_sal02 from dept_sumsal
)
select * from dept_sumsal where sum_sal01>(select sum_sum_sal02 from depart_avgsal)

create table department(
department_id number(10),
department_name varchar2(20)
)