6-1
--
创建数据库
CREATE table salary(
  sid number(8) not null,
  sname varchar2(50),
  s_salary number(8),
  dep varchar2(50),
constraint salary_pk primary key(sid)
)
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'刘德华',15000,'曲艺部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
张学友',11000,'曲艺部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
赵志强',80,'学术部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
孔子',88088,'学术部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
庄子',78088,'学术部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
二傻子',-100,'学术部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
姚明',30000,'健身部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
施瓦辛格',31000,'健身部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
史泰龙',11000,'健身部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
芙蓉姐姐',-500,'恶搞部');
insert into salary(sid,sname,s_salary,dep) values (salary_seq.nextval,'
周星驰',8500,'恶搞部');
select * from salary;
6-2
--
一共有多少人啊?
select count(*) from salary
--
工资总和是多少啊?
select sum(s_salary) from salary
--
平均工资是多少啊?
select avg(s_salary) from salary
--
最高工资是多少啊?(遗留问题:只知道最高工资,不知道名字)
--select max(s_salary) from salary
--
最低工资是多少啊?(遗留问题:只知道最低工资,不知道名字)
select min(s_salary) from salary
--
每个部门的工资总和是多少啊?
6-3
--
看看都有哪些部门啊?
select dep from salary group by dep
--select dep,sum(s_salary) from salary group by dep
select dep,sum(s_salary) from salary group by dep
--
每个部门的平均工资是多少啊?
select dep,avg(s_salary) from salary group by dep
--
每个部门的最高工资是多少啊?
select dep,max(s_salary) from salary group by dep
--
每个部门的最低工资是多少啊?
select dep,min(s_salary) from salary group by dep
6-4
--
看看部门中最高工资,不过30000以下的我就不看了
select max(s_salary),dep from salary group by dep having max(s_salary) >= 30000
在一个表中查询工资表中的最高工资的人的相关情况
select * from salary where s_salary in (select max(s_salary) from salary)
为子查询创建第二个表
create table dep(
  depid number(8)  not null,
  depName varchar2(50),
  constraint dep_pk primary key(depid)
)

insert into dep (depid,depname) values (dep_seq.nextval,'
曲艺部');
insert into dep (depid,depname) values (dep_seq.nextval,'
健身部');
insert into dep (depid,depname) values (dep_seq.nextval,'
学术部');
insert into dep (depid,depname) values (dep_seq.nextval,'
恶搞部');
--卸载字段
alter table salary
drop column dep;
--增加字段
alter table salary
add depid number(8);
--设置外键
alter table salary add constraint salary_fk
  foreign key (depid)
  references  dep(depid)
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'刘德华',15000,1);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
张学友',11000,1);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
赵志强',80,3);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
孔子',88088,3);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
庄子',78088,3);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
二傻子',-100,3);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
姚明',30000,2);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
施瓦辛格',31000,2);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
史泰龙',11000,2);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
芙蓉姐姐',-500,4);
insert into salary (sid,sname,s_salary,depid) values (salary_seq.nextval,'
周星驰',8500,4);
6-5
--
通过内链接查询员工姓名和部门
select s.sname,d.depName from salary as s,dep as d where s.depid = d.depid
--查找重复姓名
--
先插入一条数据
insert into salary (sname,s_salary,depid) values ('
二傻子',-100,3);
--
查询
select * from salary where sid in (select s1.sid from salary s1,salary s2
where s1.sid <> s2.sid and s1.sname = s2.sname group by s1.sid)
6-6
--
先卸载外键
alter table salary
drop constraint salary_fk
在插入值,有个新部门5.
insert into salary (sname,s_salary,depid) values ('
吴宗宪',8000,5);
--左连接:左边表中左右数据不管条件成立不成立都出现
select s.sname,s.s_salary,d.depName from salary  s,dep  d where s.depid = d.depid(+)
--右连接等同左连接.
子查询
查询最高的工资人的部门和名字
select s.sname,d.depName from salary as s,dep d where s.depid = d.depid and s.s_salary = (select max(s_salary) from salary)
查询最高的各个部门工资最高人和部门名字
select s.sname,d.depName from salary as s,dep d where s.depid = d.depid and s.s_salary in (select max(s_salary) from salary group by depid)
6-7
select * into salary1 from salary
子查询
select (select max(s_salary) from salary where depid=1)
,(select max(s_salary) from salary where depid=2)
,(select max(s_salary) from salary where depid=3)
,(select max(s_salary) from salary where depid=4)
from dual
select * from (select sname,s_salary from salary order by s_salary desc) where rownum < 4