1、创建数据表emp,dept,salgrade;
create table emp (
empno NUMBER(11) not null,
deptno NUMBER(11),
ename VARCHAR2(15) not null,
sal NUMBER(11) not null,
job VARCHAR2(15) not null,
mgr NUMBER(11) not null,
hirdate DATE not null,
comm NUMBER(11) not null,
constraint PK_EMP primary key (empno)
);
create table dept(
deptno NUMBER(11) not null,
dname VARCHAR2(15) not null,
loc VARCHAR2(15) not null,
constraint PK_DEPT primary key (deptno)
);
create table salgrade(
grade NUMBER(11) not null,
losal NUMBER(11) not null,
hisal NUMBER(11) not null,
constraint PK_SALGRADE primary key (grade)
)
2、创建关系外键;
alter table emp
add constraint FK_EMP_REFERENCE_DEPT foreign key (deptno)
references dept (deptno);
3、分别对emp、dept、salgrade表插入数据
insert int dept(deptno,dname,loc) values(10,'软件设计部','广州');
insert int dept(deptno,dname,loc) values(11,'财务部','广州');
insert int dept(deptno,dname,loc) values(12,'人力资资源部','广州');
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (101,10,'张一',3500,'软件工程师',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (102,10,'张二',9000,'软件工程师',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (103,10,'张三',6900,'软件工程师',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (104,11,'张四',3500,'会计',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (105,11,'张五',4500,'会计',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (106,11,'张六,5500,'会计',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (107,12,'张七',3500,'HR',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (108,12,'张八',6800,'HR',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (109,12,'张九',7800,'HR',0,sysdate,0);
insert into emp(empno,deptno,ename,sal,job,mgr,hirdate,comm) values (110,12,'张十',6500,'HR',0,sysdate,0);
insert into salgrade(grade,losal,hisal) values(1,3000,4999);
insert into salgrade(grade,losal,hisal) values(2,5000,6999);
insert into salgrade(grade,losal,hisal) values(3,7000,9999);
4、测试简单的SQL语句;
公司工资最高的员工列表 子查询
select t.ename,t.sal from emp t where t.sal =
(
select max(sal) from emp
)
查询每一个员工的经理人及自己的名字
select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno)
查询公司平均薪水的等级
select s.grade from salgrade s where
(select avg(t.sal) from emp t) between s.losal and s.hisal
求部门中那些人的工资最高
select d.dname,ename,sal from
(select t.deptno,ename,sal from
(select deptno,max(sal) as max_sal from emp group by deptno) e
join emp t on (e.deptno = t.deptno and t.sal = max_sal)
) et
join dept d on (d.deptno = et.deptno)
查询部门平均薪水的等级
select d.dname,avg_sal,grade from
(select deptno,avg_sal,grade from
(select deptno,avg(sal) as avg_sal from emp group by deptno) e
join salgrade s on (e.avg_sal between s.losal and s.hisal )
) es
join dept d on (es.deptno = d.deptno)
求部门的平均薪水等级
select deptno,avg(grade) from
(select deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)) t
group by t.deptno
求那些人是经理人
select ename from emp e
where empno in (select distinct mgr from emp)
求薪水的最高值
select ename from emp
where empno not in
(select distinct e1.empno from emp e1
join emp e2 on (e1.sal<e2.sal)
)
平均薪水最高的部门编号与名称
select d.deptno,dname from
(select deptno,avg(sal) avg_sal from emp group by deptno) t1
join dept d on (d.deptno = t1.deptno)
where avg_sal =
(select max(avg_sal) from
(select deptno,avg(sal) avg_sal from emp group by deptno) t2
)
求平均薪水的等级最低的部门名称
select dname from dept d
where d.deptno in (
select deptno from
(
select deptno,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t1
join salgrade g on (avg_sal between g.losal and g.hisal)
) t2
where t2.grade =
(
select min(grade) from
(
select deptno,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t1
join salgrade g on (avg_sal between g.losal and g.hisal)
) t3
)
)
求部门经理人中平均薪水最低的部门名称
select d.dname,t1.avg_sal from dept d
join
(
select deptno,avg(sal) avg_sal from
(
select e2.deptno,e2.ename,e2.sal from emp e1
join emp e2 on (e1.mgr = e2.empno)
) t
group by deptno
) t1
on (d.deptno = t1.deptno)
where avg_sal =
(
select min(avg_sal) from
(
select deptno,avg(sal) avg_sal from
(
select e2.deptno,e2.ename,e2.sal from emp e1
join emp e2 on (e1.mgr = e2.empno)
) t
group by deptno
)
)
求必普通员工的最高薪水还要高的经理人名称
select ename from
(
select e2.ename,e2.empno,e2.sal from emp e1
join emp e2 on (e1.mgr = e2.empno)
) t
where t.sal >
(
select max(e.sal) from emp e
where e.empno not in
(
select e1.mgr from emp e1
join emp e2 on (e1.mgr = e2.empno)
)
)
求薪水最高的第6名到10名雇员
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM (select e1.ename,e1.sal from emp e1 order by e1.sal desc)) A
WHERE ROWNUM <= 10
)
WHERE RN >= 6
Oracle sql 入门
最新推荐文章于 2024-05-31 16:00:00 发布