查询练习

--迪卡尔积联接
select *
from s_emp,s_dept;
--等值连接,用等号实现的联接
select *
from s_dept,s_emp
where s_dept.id = s_emp.dept_id;
--非等值联接,不是用等号进行的联接
select * from
--工资等级salgrade
create table salgrade(id int,losal int,hisal int);
insert into salgrade values(1,0,500);
select * from salgrade;
insert into salgrade values(2,500,1000);
insert into salgrade values(3,1000,1500);
insert into salgrade values(4,1500,2000);
insert into salgrade values(5,2000,3000);
insert into salgrade values(6,3000,5000);
insert into salgrade values(7,5000,8000);
insert into salgrade values(8,8000,10000);
--查询工资等级为3的员工信息
select s_emp.last_name,s_emp.salary
from s_emp,salgrade
where  salgrade.id = 4 and s_emp.salary between salgrade.losal and salgrade.hisal

--内联接,左外联接,右外联接语法,区别
--内联接语法
select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_dept,s_emp
where s_dept.id = s_emp.dept_id
order by s_dept.id

select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_dept inner join s_emp
on s_dept.id = s_emp.dept_id
order by s_dept.id;

--左外连接语法
insert into s_dept values(60,'Java',2);
select * from s_dept order by id;

select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_dept,s_emp
where s_dept.id = s_emp.dept_id (+)
order by s_dept.id;

select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_dept left join s_emp
on s_dept.id = s_emp.dept_id
order by s_dept.id

--右外联接语法
select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_emp,s_dept
where s_emp.dept_id (+) = s_dept.id  
order by s_dept.id;

select s_dept.name,s_dept.id,s_emp.last_name,s_emp.dept_id
from s_emp right join  s_dept
on s_emp.dept_id = s_dept.id
order by s_dept.id


--内联接与左联接的区别,内联接以两边的表为准
--左联接以左边的表为准
--示例:查出41部门员工的姓名及员工所在的部门名称
select s_emp.last_name,s_dept.name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id and s_dept.id = 41
--示例:查出工资范围在 1200-2000 之间所有员工姓名及其部门名称
select s_emp.last_name,s_dept.name,s_emp.salary
from s_emp,s_dept
where s_emp.dept_id = s_dept.id and s_emp.salary between 1200 and 2000
--示例:查出 Sales 部所有员工的信息
select s_emp.last_name,s_dept.name,s_emp.salary
from s_emp,s_dept
where s_emp.dept_id = s_dept.id and s_dept.name = 'Sales'
--自联接
--查出s_emp 表中所有员工名，及其领导名(最大的领导没有被查出)
select t1.last_name,t2.last_name leader
from s_emp t1,s_emp t2
where t1.manager_id = t2.id
--查询ID为2领导的员工
select *
from s_emp
where manager_id = 2;

--组函数
--薪水不同的人的个数,distinct的作用
select count(distinct salary) from s_emp;
--平均工资,最高工资,最低工资
select avg(salary),max(salary),min(salary) from s_emp;

--查询各部门的平均工资,最高工资,最低工资
select dept_id,avg(salary),max(salary),min(salary)
from s_emp
group by dept_id
--查询部门平均工资大于1500的各部门的平均工资,最高工资,最低工资
select dept_id,avg(salary),max(salary),min(salary)
from s_emp
group by dept_id
having avg(salary) > 1500

1.查询部门平均工资大于1500的各部门的名称,平均工资,最高工资,最低工资
select s.dept_id,max(d.name),avg(s.salary),max(d.name),max(s.salary),min(s.salary)
from s_emp s,s_dept d
where s.dept_id = d.id
group by s.dept_id
having avg(s.salary) > 1500
2.(1)select avg(salary) avgs from s_emp group by dept_id; temp
  (2)select temp.avgs,d.name from s_dept d ,(select avg(salary) ,id avgs from s_emp group by dept_id) temp
  where d.id=temp.id group by d.id;



--查除41部门以外的部门员工的平均工资,工资总和及部门名称并按照工资总和排序：

--求非“VP”开头职位之外的各个职位的工资总和大于5000的职位及工资总和，并按工资总和排序
select  title,sum(salary)
from s_emp
where title not like 'VP%'
group by title
having sum(salary)> 5000
order by sum(salary);

--子查询,在select子句,from子句,where子句等子句中有select语句的查询语句
--查询最低(最高)工资的员工
select last_name,salary
from s_emp
where salary = (select max(salary) from s_emp);

--查询大于(小于)平均工资的员工
select last_name,salary
from s_emp
where salary >= (select avg(salary) from s_emp)
order by salary
-- 查询平均工资低于 32 部门的平均工资部门编号
select avg(salary)
from s_emp
where dept_id = 32

select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) > (
select avg(salary)
from s_emp
where dept_id = 32
)
order by avg(salary)

-- 查询部门位于区域id为2的员工
select * from s_dept where region_id=2;
select last_name,first_name,dept_id
from s_emp e,s_dept d
where e.dept_id = d.id and d.region_id = 2

select last_name,first_name,dept_id
from s_emp
where dept_id in (select id from s_dept where region_id=2);


--in与=any与=some相同
select last_name,first_name,dept_id
from s_emp
where dept_id =some (select id from s_dept where region_id=2);

-- >any与>some相同,<any与<some相同

select last_name,first_name,dept_id
from s_emp
where dept_id <any (select id from s_dept where region_id=2)
order by dept_id


--not in = <>all
select last_name,first_name,dept_id
from s_emp
where dept_id  <> all (select id from s_dept where region_id=2)
order by dept_id

-->all及<all
select last_name,first_name,dept_id
from s_emp
where dept_id  <all (select id from s_dept where region_id=2)
order by dept_id

--exists及not exists查询的子句总是返回true或false
select e.last_name,e.first_name,e.dept_id
from s_emp e
where exists (select * from s_dept d where e.dept_id = d.id and d.region_id=2);

--查询没学过课程号4的学生id
select * from sc a
where not exists (select * from sc b where a.s_id = b.s_id and b.c_id=4);


--查询工资相同的员工
select a.last_name,a.salary
from s_emp a
where 2 <= (select count(*) from s_emp b where a.salary = b.salary)
order by a.salary

--select子句用在from子句中

--查询课程1的成绩大于课程2的成绩的学生
select a.s_id,a.score,b.score
from (select s_id,score from sc where c_id=1) a,
(select s_id,score from sc where c_id=2) b
where a.s_id =b.s_id and a.score > b.score

--用在update语句中,将成绩改为本门课程的平均分
update sc set score = (
select avg(sc2.score) from sc sc2 where sc.c_id = sc2.c_id);

--rownum表示查询的顺序号
select rownum,last_name,first_name
from s_emp
where rownum <=10
order by id

--oracle分页查询
select * from
(
select rownum rn,last_name,first_name
from s_emp
where rownum <=20
order by id
)
where rn between 11 and 20