/*
from 里面写子查询,子查询别名,select 可以lian
where里面写子查询,select 的可以连
where 条件可以用 in
在用条件过滤的时候,一定要看过滤条件,中,如果有检索范围变大,一定要考虑控制条件是否可以应付所有的特殊情况了。
*/
/*CREATE table*/
CREATE table studnet_score(
sid number,
sscore varchar2(20),
constraint studnet_score PRIMARY KEY(sid)
);
/*drop table*/
drop table studnet;
/*drop table with PRIMARY KEY and foreign key*/
CREATE table studnet(
sid number,
stuname varchar(20),
constraint studnet_pk PRIMARY KEY(sid),
constraint fk_studnet_score
foreign key (sid)
references studnet_score(sid)
);
/*查询表*/
select * from studnet_score;
select * from studnet;
select * from dual;
/*创建表*/
create table student2 as select * from studnet;
select * from student2;
/*插入*/
insert into student2 values('1','zlj');
/*创建表*/
create table student3 as select * from student2 stu where stu.sid=1;
select * from student3;
/*备注*/
comment on table student3 is 'stuen comments';
comment on column student3.stuname is '名字';
/*修改表*/
alter table student3 rename to newname_student3;
select * from newname_student3 where sid=1;
alter table newname_student3 add remind varchar(20);
alter table newname_student3 modify remind varchar(21);
alter table newname_student3 drop column remind;
truncate table newname_student3;
--部门与 ACCOUNTING 相同的员工信息
select *
from emp a
where exists
(select 1 from scott.dept b
where a.deptno = b.deptno
and b.dname = 'ACCOUNTING');
select a.*
from emp a, scott.dept b
where a.deptno = b.deptno
and b.dname = 'ACCOUNTING'
---查询部门总工资大于10000的部门 id 和 总工资
select a.deptno,sum(a.sal)
from emp_liwenxiu a
group by a.deptno
having sum(a.sal)>10000;
---各种连接
--连接分类:
select b.deptno,b.dname,a.ename
from emp_liwenxiu a,scott.dept b
where a.deptno(+)=b.deptno;
select b.deptno,b.dname,a.ename
from emp_liwenxiu a,scott.dept b
where b.deptno=a.deptno(+);
select b.deptno,b.dname,a.ename
from emp_liwenxiu a right join scott.dept b
on b.deptno=a.deptno;
select b.deptno,b.dname,a.deptno,a.ename
from scott.dept b full outer join emp_liwenxiu a
on b.deptno=a.deptno;
--建表
CREATE table studnet_score(
sid number,
sscore varchar2(20),
constraint studnet_score PRIMARY KEY(sid)
);
drop table studnet;
CREATE table studnet(
sid number,
stuname varchar(20),
constraint studnet_pk PRIMARY KEY(sid),
constraint fk_studnet_score
foreign key (sid)
references studnet_score(sid)
);
--快速建表--查询列,不查数据 可以快速从不同表查询
create table student2 as select * from studnet;
--插入数据
insert into student2 values('1','zlj');
--建表
create table student3 as select * from student2 stu where stu.sid=1;
--注释
comment on table student3 is 'stuen comments';
comment on column student3.stuname is '名字';
--改表
alter table student3 rename to newname_student3;
--改表 加字段
alter table newname_student3 add remind varchar(20);
--改表 改字段
alter table newname_student3 modify remind varchar(21);
--改表 删字段
alter table newname_student3 drop column remind;
--改表 删除表
truncate table newname_student3;
-- distinct 三字段同时重复
select * from student_zhanglinjiang a where a.rowid not in ( select min (rowid )
from student_zhanglinjiang group by student_zhanglinjiang.sno)
--top * 记录
SELECT top FROM emp_zhanglinjiang;
Select TOP5 from emp_zhanglinjiang a order by a.sal
(select * from emp_zhanglinjiang a order by a.sal)
select a.*,rownum row_numa from(
select a.empno ,rownum as row_num from emp_zhanglinjiang a
where rownum<6
order by sal desc) a
where rownum<5
--雇员表工资前五
SELECT *
FROM emp_zhanglinjiang a
order by a.sal desc
where rownum<5
--差集
select *
from student_zhanglinjiang a
where a.rowid not in
(select min(rowid) from student_zhanglinjiang group by stu_id
)
--工资大于平均工资
select *
from emp_zhanglinjiang emp
where emp.sal >
(select avg(sal) from emp_zhanglinjiang where deptno = emp.deptno)
select * from (select * from emp_zhanglinjiang order by sal) where rownum<=5;
select 'update emp_zhanglinjiang set sal =(emp_zhanglinjiang.sal+1000)where emp_zhanglinjiang.empno =' ||
k.empno
-- UPDATE emp_zhanglinjiang SET SAL = (emp_zhanglinjiang.sal+1000) where emp_zhanglinjiang.empno =
from (select emp.empno
from emp_zhanglinjiang emp
where emp.sal > (select avg(sal)
from emp_zhanglinjiang
where deptno = emp.deptno)) k
select *from emp_zhanglinjiang;
update emp_zhanglinjiang a
set a.sal = a.sal + 1000
where a.empno in (select emp.empno
from emp_zhanglinjiang emp
where emp.sal > (select avg(sal)
from emp_zhanglinjiang
where deptno = emp.deptno));
select *from emp_zhanglinjiang;
----lpad
select level,
emp.empno,
lpad(' ', level * 4, ' ') || emp.ename,
emp.job,
emp.sal
from emp_zhanglinjiang emp
connect by prior emp.empno = mgr
start with emp.empno = 7698;
--1选择在部门30中员工的所有信息
select * from emp_zhanglinjiang e where e.deptno=30;
--2列出职位为(MANAGER)的员工的编号,姓名
select * from emp_zhanglinjiang e where e.job='MANAGER';
--3找出奖金高于工资的员工
select * from emp_zhanglinjiang e where e.comm>e.sal;
--4找出每个员工奖金和工资的总和
select sum(e.sal) sum_sal ,sum(e.comm) sum_comm from emp_zhanglinjiang e;
select a.ename, (sal + nvl(comm,0)) "Sum" from scott.emp a
--5找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
select * from emp_zhanglinjiang e where (e.deptno=10 and e.job='MANAGER') OR (e.deptno=10 and e.job='CLERK');
--6找出部门10中既不是经理也不是普通员工,而且工资大于等于2000的员工
SELECT * FROM emp_zhanglinjiang e where (e.job<>'MANAGER' AND e.job<>'CLERK' AND E.SAL>2000) AND E.DEPTNO=10;
select a.ename
from scott.emp a
where a.deptno = 10
and a.job not in ('MANAGER', 'CLERK')
and a.sal >= 2000;
--7找出有奖金的员工的不同工作
select distinct e.job from emp_zhanglinjiang e where nvl(e.comm,'0')>0;
select distinct a.job from scott.emp a where a.comm is not null;
--8找出没有奖金或者奖金低于500的员工
select e.ename from emp_zhanglinjiang e where e.comm is null or nvl(e.comm,0)>500;
--9显示雇员姓名,根据其服务年限,将最老的雇员排在最前面
select * from emp_zhanglinjiang e order by e.hiredate asc;
--10分别用case和decode函数列出员工所在的部门,deptno=10显示'部门10',
select e.ename,
case
when e.deptno = 10 then
'部门10'
when e.deptno = 20 then
'部门20'
when e.deptno = 30 then
'部门30'
when e.deptno = 40 then
'部门40'
else
'未知部门'
end "部門" --別稱
from emp_zhanglinjiang e;
select a.empno,
a.ename,
a.sal,
decode(a.deptno, 10, '財務部', 20, '研發部' 30, '銷售部', '未知部門')
from scott.emp a;
--11找出每个月倒数第三天受雇的员工
select * from emp_zhanglinjiang e where e.hiredate=(last_day(e.HIREDATE) - 2);
--12找出25年前雇的员工
select * from emp_zhanglinjiang
--13所有员工名字前加上Dear ,并且名字首字母大写e where e.hiredate=add_months(sysdate,-12*25);
select 'Dear'||initcap(a.ename) from scott.emp a
--14找出姓名为5个字母的员工
select a.ename from scott.emp a where length(a.ename)=5
--15找出姓名中不带R这个字母的员工*
select a.ename from scott.emp a where a.ename not like '%R%'
--17显示所有员工的姓名的第一个字母
select substr(a.ename,0,1) from scott.emp a
--18显示所有员工,按名字降序排列,若相同,则按工资升序排序
select a.* from scott.emp a order by a.ename desc ,sal asc
--19假设一个月为30天,找出所有员工的日薪,不计小数
select round(a.sal/30,2) from scott.emp a --第二个参数默认0
--20找到2月份受雇的员工
select a.ename from scott.emp a where to_char(a.hiredate,'MM')='02';
select a.ename from scott.emp a where to_char(hiredate,'fmmm')='2';
--21列出员工加入公司的天数(四舍五入)
select round(sysdate-a.hiredate) from scott.emp a;
--22分组统计*各部门下工资>500的员工的平均工资
select avg(a.sal) from scott.emp a where a.sal>500 group by a.deptno;
--23算出部门30中得到最多奖金的员工奖金
select max(a.comm) from scott.emp a where a.deptno=30;
--24算出部门30中得到最多奖金的员工姓名
select a.ename from scott.emp a where a.comm=(
select max(a.comm) from scott.emp a where a.deptno=30)
and a.deptno=30;
--25算出每个职位的员工数和最低工资
select count(*) "人數" , min(sal) "最低工資", a.job
from scott.emp a group by a.job
--26算出每个部门,每个职位的平均工资和平均奖金(平均值包括没有奖金),
--如果平均奖金大于300,显示“奖金不错”,如果平均奖金100到300,
--显示“奖金一般”,如果平均奖金小于100,显示“基本没有奖金”,按部门编号降序,
--平均工资降序排列
select a.deptno, a.job, avg(a.sal), avg(a.comm), case
when avg(nvl(a.comm,0))>300 then '獎金不錯'
when avg(nvl(a.comm,0))>=100 and avg(nvl(a.comm,0))<=300 then '獎金一般'
when avg(nvl(a.comm,0))<100 then '基本沒有獎金'
end "獎金情況"
from scott.emp a
group by a.deptno, a.job
order by a.deptno desc,avg(a.sal) desc;
--27列出员工表中每个部门的员工数,和部门NO
select count(*),e.deptno from emp_zhanglinjiang e group by e.deptno;
--28得到工资大于自己部门平均工资的员工信息
select e.ename
from emp_zhanglinjiang e
where e.sal > (select avg(sal)
from emp_zhanglinjiang a
where a.deptno = e.deptno
group by a.deptno)
select a.*
from scott.emp a,
(select deptno, avg(sal) as avgsal from scott.emp group by deptno) b
where a.sal > b.avgsal
and b.deptno = a.deptno
-- 29分组统计每个部门下,每种职位的平均奖金(也要算没奖金的人)和总工资(包括奖金)
select d.deptno,e.job,avg(nvl(e.comm,0)) ,sum(e.sal + nvl(e.comm, 0))
from emp_zhanglinjiang e, dept_zhanglinjiang d
group by d.deptno ,e.job;
--30首先建立一张只包含20部门员工信息的表
create table emp_20 as
select * from emp e
where e.deptno=20;
--31 UNION all 和union 的區別,使用union語句中的重複語句不在顯示,union all 显示所有的
select * from emp
union
select * from emp_20;
select * from emp
union all
select * from emp_20;
--32 INTERSECT用來顯示兩者之間的重複語句
SELECT * FROM scott.emp INTERSECT SELECT * FROM emp_20 ;
-- 33 ******any 和 all 和 in*******-----多行子查詢 不是很懂
--any 大于最小值,小于最大值
--in 在集合里
--all 大于最大值,小于最小值
--
/*查询比部门号为10的员工中的任何一个员工工资都要高的员工的姓名和个人工资。
也就是说只要比部门号为10中工资最少的员工高就满足条件。
SELECT ename,sal
FROM emp
WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 10);
这里推荐用any,如果你非要用some也是没有任何问题的,结果是一样的,只是一般来讲some用在“=”的比较从句中。
例如:
SELECT ename,sal
FROM emp
WHERE sal = SOME(SELECT sal FROM emp WHERE deptno = 30) AND deptno NOT IN (SELECT deptno FROM emp WHERE deptno = 30);
含义是找到和30号部门员工的任何一个人工资相同的那些员工。
最后一个关键字all的用法就是要与子查询的每一结果都要匹配。
SELECT ename,sal
FROM emp
WHERE sal > ALL(select sal from emp where deptno = 20);
上面的SQL语句的意义与前面的就完全不一样了,其意义是找到比部门号为20的员
工的所有员工的工资都要高的员工,也就是比那个工资最高的员工的还要高的员工。
总的来说some和any用法意义是一样的,仅在词法上有不同,都表示对子查询结果
集“或”的比较关系,而all则是对子查询结果集总每一个结果“与”的关系。*/
select * from scott.emp where sal > any(select avg(sal) from scott.emp group by deptno);
select * from scott.emp where sal > all(select avg(sal) from scott.emp group by deptno);
select * from scott.emp where job in (select job from scott.emp where ename = 'MARTIN' or ename = 'SMITH');
-- 34 rownum用法
select * from scott.emp where rownum<=5 order by sal desc;
select * from scott.emp where rownum=1 or rownum=2 ;
-- 35 查詢第幾行的內容
select rownum no, t.* from scott.emp t order by sal desc;
select *
-- 36查询emp表,工资最多的第3,4,5 名
from (select rownum no, e.*
from (select * from scott.emp order by sal desc) e
where rownum <= 5)
where no >= 3;
--37查询emp表,工资最多的第3,4,5 名
select *
from (select rownum no, e.* from (select * from scott.emp order by sal desc) e)
where no >= 3
and no <= 5;
--对于in 和 exists的性能区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in,
--反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。
--38找出每个部门的员工个数大于三的部门信息,输出人数。全部部门都要显示,如果人数小于等于三,就不显示人数。
select d.*, ed.cou
from scott.dept d,
(select deptno, count(*) cou
from scott.emp
group by deptno
having count(*) > 3) ed
where d.deptno = ed.deptno(+);
-- 39列出所有员工的姓名和其上级的姓名
select e.ename,empmgr.ename
from emp e ,emp empmgr
where e.mgr=empmgr.empno(+)
select a1.ename ,a2.ename from
(select t1.ename,t1.deptno from scott.emp t1 where job='MANAGER') a1,
(select t2.ename,t2.deptno from scott.emp t2 where job not in ('MANAGER')) a2
where a1.deptno= a2.deptno
order by a1.ename desc;
select e1.ename as lower ,e2.ename as upper from scott.emp e1,scott.emp e2 where e1.mgr = e2.empno;
--40以职位分组,找出平均工资最高的两种职位
select a.job
from (select e.job, avg(e.sal) as v from emp e group by e.job order by v desc) a
where rownum <3;
----rownum 可以用的别名 在where 里面是不可以用别名的
--41--查找出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
select e.ename,e.deptno from emp e where e.deptno<>20 and e.sal>(select max(sal) as max_sal from emp where deptno=20)
select e.*
from emp e
where e.sal>(select max(a.sal) from emp a where a.deptno=20) and e.deptno<>20;
---
select t1.ename, t2.dname
from scott.emp t1, scott.dept t2
where t1.deptno not in (20)
and t1.deptno = t2.deptno
and t1.sal > all (select sal from scott.emp where deptno=20 ) ;
--2
select t1.ename, t2.dname
from scott.emp t1, scott.dept t2
where t1.deptno not in (20)
and t1.deptno = t2.deptno
and t1.sal > (select max(sal) from scott.emp where deptno=20 );
--42得到每个月工资总数最少的那个部门的部门编号,部门名称,部门位置
-----先找到记录,外面一个查询在进行排序,那么就知道有几个子查询了
select d.*
from dept d, (select deptno, avg(sal) as sum_sal
from emp
group by deptno
order by sum_sal asc
) k
where d.deptno =k.deptno
select d.deptno,d.dname,d.loc
from dept d
where d.deptno=(select k.deptno from(select deptno, sum(sal) as aum_sal from emp group by deptno order by aum_sal asc) k where rownum=1)
SELECT DEPTNO, DNAME, LOC
FROM SCOTT.DEPT
WHERE DEPTNO = (SELECT DEPTNO FROM (SELECT DEPTNO, SUM(SAL) AS SUMSAL
FROM SCOTT.EMP
GROUP BY DEPTNO
ORDER BY SUMSAL ASC) T1 WHERE ROWNUM=1)
--43--分部门得到平均工资等级为4级(等级表)的部门编号
--where select 里面的avg的别名 无法识别。。。
select avg(e.sal) over(partition by e.deptno) as avg_sal, s.grade
from emp_zhanglinjiang e , salgrade s
where avg_sal>=s.losal and avg_sal<=s.hisal
group by e.deptno;
select t1.deptno, t2.grade
from (select deptno, avg(sal) as avgsal from scott.emp group by deptno) t1,
scott.salgrade t2
where t1.avgsal between losal and hisal
and t2.grade = 4
--44. decode() 函数类似于 if....elsif...else 语句
select decode(1, 1, '内容是 1', 2, '内容是 2', 3, '内容是 3') from dual;
--45. 内连接
select * from emp e, dept d where e.deptno = d.deptno;
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;
--46. 自然连接
select * from emp natural join dept;
select * from emp e join dept d using(deptno);
--47. 左外连接
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno = d.deptno(+);
select empno, ename, d.deptno, dname, loc from emp e left outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e left join dept d on e.deptno = d.deptno(+);
--48. 右外连接
select empno, ename, d.deptno, dname, loc from emp e, dept d where e.deptno(+) = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right outer join dept d on e.deptno = d.deptno;
select empno, ename, d.deptno, dname, loc from emp e right join dept d on e.deptno = d.deptno;
--49.
select * from emp e, dept d where e.deptno = d.deptno and d.deptno = 30;
select * from emp e inner join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e join dept d on e.deptno = d.deptno where d.deptno = 30;
select * from emp e natural join dept d where deptno = 30;
select * from emp e join dept d using(deptno) where deptno = 30;
--50. 要求显示出平均工资大于 2000 的部门编号和平均工资
select deptno, avg(sal) from emp group by deptno having avg(sal) > 2000;
--51. 求出每个部门的最低工资的雇员的信息
select * from emp where sal in(select min(sal) from emp group by deptno);
select * from emp where sal =any(select min(sal) from emp group by deptno);
select * from
(select min(sal) m from emp group by deptno) temp,
emp e
where e.sal = temp.m;
--52找出姓名中不带'张'这个字的员工
select * from emp where ename not like '%张%';
--53显示所有员工的姓名的第一个字
select substr(ename,0,1) from emp
--54显示所有员工的姓
select distinct(substr(ename,0,1)) from emp
--55算出每个职位的员工数和最低工资
select job,count(*),min(sal) from emp group by job
---56分组统计各部门下工资>5000 的员工的平均工资
select round(avg(t.sal)) from emp t where t.sal>5000 group by t.deptno
--57占比排列分析函数运用例子
SELECT rownum,a.ename,a.sal,a.deptno,
SUM(a.sal)over(ORDER BY sal)AS nsal,
a.sal,
sal-lag(sal,1) over(ORDER BY sal)AS "差值",--下一行减去上一行得的差值
lead(sal,0,0) over(ORDER BY sal) AS odate,--0没偏移量就是自己数据
lag(sal,1,0) over(ORDER BY sal) AS lagdate1,--向上1偏移量
lag(sal,2,0) over(ORDER BY sal) AS lagdate2,
lead(sal,1,0) over(ORDER BY sal) AS ledate1,--向下1偏移量
lead(sal,2,0) over(ORDER BY sal) AS ledate2,--2-----
SUM(a.sal)over(PARTITION BY a.deptno) AS deptsal,
round(a.sal/SUM(a.sal)over(PARTITION BY a.deptno)*100,2)||'%' AS "雇员部门占比",--edpercent
SUM(a.sal)over(PARTITION BY deptno ORDER BY ename DESC) AS "分部门求和",--ndsal
SUM(a.sal)over() AS totaldeptsal,--总工资
round(SUM(a.sal)over(PARTITION BY a.deptno)/SUM(a.sal)over()*100,2)||'%' AS "部门公司占比",--dcpercent
MAX(sal)over()AS maxsal,MIN(sal) over() AS minsal,
MAX(sal)over(PARTITION BY deptno)AS pmaxsal,MIN(sal) over(PARTITION BY deptno) AS pminsal,
--rownum,row_number,dense_rank,rank
row_number()over(ORDER BY a.sal) AS rnsal,--数据一样排列不重复
dense_rank()over(ORDER BY a.sal) AS drsal,--
rank() over(ORDER BY a.sal) AS ranksal
FROM emp_fzx a
--
GROUP BY a.ename,a.sal,a.deptno
--58占比排列分析函数运用例子
---------常见分析函数
--查询员工信息与其中工资最高最低员工
select ename,sal,max(sal) over(), min(sal) over() from emp;
--连续求和
select deptno,ename,sal,
sum(sal) over(),
sum(sal) over(partition by deptno order by ename) sum_sal from emp;
--sum(sal) over(order by ename)指的是连续求和.是以ename来排序的。
--分部门连续求和
select deptno,sal ,ename,
sum(sal) over (partition by deptno order by ename) as 分部门连续求和 ,
sum(sal) over (partition by deptno) 分部门求和
from emp;
--得到当前行上一行或者下一行的数据
--语法结构:lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)
--参数说明:
--value_expr 值表达式,通常是字段,也可是是表达式。value_expr本身不支持分析函数,也就是lead不支持多层调用。
--offset 偏移,应该是很熟悉的数学概念了,或者是相对偏移,表格来开当前行的第offset行,如果offset是整数就表示是顺序下的前第n行。 如果不提供这个参数,就是默认为1.
--default 默认值,如果没有找到,应该返回什么值的意思,有点类似nvl(col,value)。如果没有设置,且找不到,那么就返回Null
--over 理解成在一个结果集范围内,如果后面的partition by为空,那么就是当前的结果集范围内。
--query_partition_clause 分区语句,对结果集合分区的语句,是可选的,如果没有就是所有的一个分区。
--Order_by_clause 排序语句 必须需要 ,形如order by xxx desc/asc
select ename,
sal,
lead(sal,0) over(order by sal) aaa ,
lag(sal,0) over(order by sal) bbb
from emp;
select ename,
sal,
lead(sal,1,0) over(order by sal) leadsal ,
lag(sal,1,0) over(order by sal) lagsal from emp;
select ename,
sal,
lead(sal, 2, 0) over(order by sal) lead_sal2,
lag(sal, 2, 0) over(order by sal) lag_sal2
from emp;
--查询EMP员工表下每个部门工资前二名的员工信息
--ROW_NUMBER:
--Row_number函数返回一个唯一的值,当碰到相同数据时,排名按照记录集中记录的顺序依次递增。
--DENSE_RANK:
--Dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都是一样的。
--RANK:
--Rank函数返回一个唯一的值,除非遇到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
select * from emp_zhanglinjiang order by sal ;
select deptno, ename, sal
from emp_zhanglinjiang e1
where (select count(1) from emp_zhanglinjiang e2
where e2.deptno = e1.deptno
and e2.ename != e1.ename
and e2.sal > e1.sal) < 2
order by deptno, sal desc;
select * from
(select deptno,ename,sal,row_number() over (partition by deptno
order by sal asc) rn
from emp_zhanglinjiang)
where rn<6;
select *
from (select deptno,
ename,
sal,
rank() over( partition by deptno order by sal asc) rn
from emp_zhanglinjiang)
where rn < 6;
select *
from (select deptno,
ename,
sal,
dense_rank() over( partition by deptno order by sal asc) rn
from emp_zhanglinjiang)
where rn < 6;
---58常见分析函数
--59分部门得到平均工资等级为2级(等级表)的部门编号
select a.deptno, s.grade
from (select deptno, avg(sal) as avg_sal
from emp_zhanglinjiang
group by deptno) a,
salgrade_zhanglinjiang s
where a.avg_sal between losal and hisal
and grade = 2;
--60查找出工资等级不为4级的员工的员工名字,部门名字,部门位置
select e.ename as 员工名字, d.dname as 部门名字, e.job as 部门位置
from emp_zhanglinjiang e, dept_zhanglinjiang d,salgrade_zhanglinjiang s
where d.deptno = e.deptno
/* and (select salgrade.grade
from salgrade_zhanglinjiang salgrade
where e.sal >= salgrade.losal
and e.sal <= salgrade.hisal) <> 4;*/
and e.sal between losal and hisal
and s.grade<>4;
---
select avg(sal)
from emp_zhanglinjiang
where emp_zhanglinjiang.job in
(select emp_zhanglinjiang.job
from emp_zhanglinjiang
where emp_zhanglinjiang.ename in('MARTIN','SMITH'))
/* OR emp_zhanglinjiang.job in
(select emp_zhanglinjiang.job
from emp_zhanglinjiang
where emp_zhanglinjiang.ename = 'SMITH')*/
-----------------------------------------------61游标 循环
declare
v_num number :=1;
v_sum number :=0;
begin
loop
exit when v_num>10;
dbms_output.put_line('循环第'||v_num||'次');
v_sum :=v_sum+v_num;
v_num :=1+v_num;
end loop;
dbms_output.put_line('求和为'||v_sum||'');
end;
declare
v_num number :=1;
v_sum number :=0;
begin
while v_num <11 loop
dbms_output.put_line('循环第'||v_num||'次');
v_sum :=v_sum+v_num;
v_num :=1+v_num;
end loop;
dbms_output.put_line('求和为'||v_sum||'');
end;
declare
--v_num number:=1;
v_sum number :=0;
begin
for v_num in 1..10 loop
dbms_output.put_line('循环第'||v_num||'次');
v_sum :=v_sum+v_num;
end loop;
dbms_output.put_line('求和为'||v_sum||'');
end;
declare
--v_num number:=1;
v_sum number :=0;
begin
for v_num in 1..10 loop
exit when v_num=5;
dbms_output.put_line('循环第'||v_num||'次');
v_sum :=v_sum+v_num;
end loop;
dbms_output.put_line('求和为'||v_sum||'');
end;
declare
--v_num number:=1;
v_sum number :=0;
begin
for v_num in 1..10 loop
if v_num=5 then
exit;
end if;
dbms_output.put_line('循环第'||v_num||'次');
v_sum :=v_sum+v_num;
end loop;
dbms_output.put_line('求和为'||v_sum||'');
exception
when others then null;
end;
--select * from dept_zhanglinjiang;---62遍历表数据
declare
v_rowdept dept_zhanglinjiang%rowtype;
v_num number:=10;
v_maxnum number:=0;
begin
select max(deptno) into v_maxnum from dept_zhanglinjiang ;
select dept_zhanglinjiang.* into v_rowdept from dept_zhanglinjiang where deptno=v_num;
while v_rowdept.deptno<=v_maxnum loop
select dept_zhanglinjiang.* into v_rowdept from dept_zhanglinjiang where deptno=v_num;
dbms_output.put_line('dept数据为:'||v_rowdept.deptno||' '||v_rowdept.dname ||' '||v_rowdept.loc ||' '||v_num);
v_num:=v_num+10;
end loop;
exception
when others then null;
end;
-------遍历表数据
declare
cursor c_dept /*(d_dno number)*/is
select d.deptno,d.dname,d.loc from dept_zhanglinjiang d ;
/*where d.deptno=d_dno;*/
v_deptno dept_zhanglinjiang.deptno%type;
v_dname dept_zhanglinjiang.dname%type;
v_loc dept_zhanglinjiang.loc%type;
begin
open c_dept/*(&d_dno)*/;
loop
fetch c_dept into v_deptno, v_dname,v_loc;
exit when c_dept%notfound;
dbms_output.put_line('数据为:'||v_deptno||' '||v_dname||' '||v_loc);
end loop;
close c_dept;
end;
---63输入部门编号 对应涨工资
declare
cursor c_emp(b_deptno number) is
select d.deptno,d.ename,d.sal ,d.empno
from emp_lisiya d
where d.deptno=b_deptno;
v_deptno emp_lisiya.deptno%type;
v_sal emp_lisiya.sal%type;
begin
select sal,deptno
into v_sal,v_deptno
from emp_lisiya em
where empno = &eno_empno;
if v_deptno = 10 then
for empno_row in c_emp(v_deptno) loop
if empno_row.sal*(1.1)<5000 then
update emp_lisiya a set sal=empno_row.sal*(1.1) where empno=empno_row.empno;
else
update emp_lisiya a set sal=5000 where empno=empno_row.empno ;
end if;
end loop;
elsif v_deptno = 20 then
for empno_row in c_emp(v_deptno) loop
if empno_row.sal*(1.2)<5000 then
update emp_lisiya a set sal=empno_row.sal*(1.2) where empno=empno_row.empno;
else
update emp_lisiya a set sal=5000 where empno=empno_row.empno ;
end if;
end loop;
elsif v_deptno = 30 then
for empno_row in c_emp(v_deptno) loop
if empno_row.sal*(1.3)<5000 then
update emp_lisiya a set sal=empno_row.sal*(1.3) where empno=empno_row.empno;
else
update emp_lisiya a set sal=5000 where empno=empno_row.empno ;
end if;
end loop;
end if;
end;
------64输入工作,输出同工作emp的全部信息
declare
cursor c_emp(p_job varchar2) is
select * from emp_zhanglinjiang d where d.job = p_job;
begin
for emp_zhanglinjiang in c_emp('&_job') loop
dbms_output.put_line('编号:' || emp_zhanglinjiang.empno || ' 名字:' ||
emp_zhanglinjiang.ename || ' job:' ||
emp_zhanglinjiang.job || ' mgr:' || emp_zhanglinjiang.mgr ||
' hiredate:' || emp_zhanglinjiang.hiredate ||
' sal:' || emp_zhanglinjiang.sal || ' comm:' ||
emp_zhanglinjiang.comm || ' deptno:' ||
emp_zhanglinjiang.deptno || '');
end loop;
exception
when others then
null;
end;
---------------------------------65 姓名以A 或者 S开头的员工加10%sal
select * from emp_zhanglinjiang;
declare
cursor c_emp is
select d.deptno, d.ename, d.sal, d.empno
from emp_zhanglinjiang d
where d.ename like 'A%'
or d.ename like 'S%';
begin
for empno_row in c_emp loop
update emp_zhanglinjiang a
set sal = empno_row.sal * (1.1)
where empno = empno_row.empno;
commit;
end loop;
--Select * From suppliers Where supplier_name Like 'H%!_' escape '!';
exception
when others then
dbms_output.put_line('unknow');
end;
--BI-冯镇兴
declare
begin
for c_temp in (select *
from emp
where ename like 'A%'or ename like 'S%'
) loop
c_temp.sal := c_temp.sal * 1.1;
update emp e set e.sal = c_temp.sal where e.empno = c_temp.empno;
end loop;
end;
--1胡洪杰
---对名字以 A或S开头的员工加薪 10%
select * from emp_huhongjie;
declare
--申明‘查询名字以 A或S开头的员工 ’的游标
cursor emp_cursor is select A.* from emp_huhongjie A
where A.ename like 'A%' or A.ename like 'S%';
begin
--循环游标
for emp_row in emp_cursor loop
dbms_output.put_line(emp_row.ename||'--'||emp_row.sal||'--'||emp_row.sal*1.1);
--修改工资
update emp_huhongjie B set B.sal=(B.sal*1.1) where B.empno=emp_row.empno;
commit;
end loop;
exception
when others then
dbms_output.put_line('系统出现未知错误!!!');
end;
------66 异常处理 exception
declare
a_exception exception;
b_exception exception;
begin
begin
raise a_exception;
exception
when a_exception then
raise b_exception;
when b_exception then
raise b_exception;
dbms_output.put_line(' b_exception; 内');
--异常中的异常,抛给父块
end;
exception
when b_exception then
dbms_output.put_line(' b_exception; 外');
end;
-------67过程传入部门名称,插入部门表数据
create or replace procedure p_insert_dept(p_dept_name varchar2) is
v_deptartment_id dept_zhanglinjiang.deptno%type;
begin
select s_app.nextval into v_deptartment_id from dual;
insert into dept_zhanglinjiang (deptno,dname)
values
(v_deptartment_id,p_dept_name);
commit;
dbms_output.put_line('新建部门id为'||v_deptartment_id);
end;
begin
p_insert_dept('黑暗钢铁','北京');
end;
--------68索引 sequence
create sequence seq_deptno
minvalue 1 --最小值为 1
maxvalue 100 --最大值为 100
start with 1 --从 1 开始增长
increment by 1 --每次增长长度为 1*/
-----------------------69输入部门名称返回,id
declare
v_deptno number;
begin
v_deptno:= p_returndeptbyid('&dname');
dbms_output.put_line(v_deptno||' &dname');
end;
---------------------------------------70 包的建立 包头 包体 PACKAGE 返回结果集
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;-------REF CURSOR?
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/
---------------------------------------
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
----存储函数:71包的建立 包头 包体 PACKAGE 返回结果集
CREATE OR REPLACE
package pkg_test as
type myrctype is ref cursor;
function get(intID number) return myrctype;
end pkg_test;
CREATE OR REPLACE
package body pkg_test as
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量?
sqlstr varchar2(500);
begin
if intID=0 then
--静态测试直接用select语句直接返回结
open rc for select id,name,sex,address,postcode,birthday from student;
else
--动态sql赋值用:w_id来申明该变量从外部获得
sqlstr := 'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试用sqlstr字符串返回结用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
end pkg_test;
/
--72创建一个过程,传入员工名称,
--输出该员工的工资、入职日期、上级领导、所在部门名称。
create or replace procedure p_insert_dept(p_ename in varchar2,
v_ename out varchar2,
v_sal out number,
v_hiredate out date,
v_emgrename out varchar2,
v_dname out varchar2) is
begin
select e.ename, e.sal, e.hiredate, emgr.ename, d.dname
into v_ename, v_sal, v_hiredate, v_emgrename, v_dname
from emp_zhanglinjiang e, dept_zhanglinjiang d, emp_zhanglinjiang emgr
where e.ename = p_ename
and e.mgr = emgr.empno
and e.deptno = d.deptno;
exception
when no_data_found then
dbms_output.put_line('error');
end;
declare
v_ename emp_zhanglinjiang.ename%type;
v_sal emp_zhanglinjiang.sal%type;
v_hiredate emp_zhanglinjiang.hiredate%type;
v_emgrename emp_zhanglinjiang.ename%type;
v_dname dept_zhanglinjiang.dname%type;
begin
p_insert_dept('&dname',v_ename,v_sal,v_hiredate,v_emgrename,v_dname);
dbms_output.put_line('员工名字:'||' &dname'||'工资:'||v_sal||'入职日期:'||v_hiredate||'上级领导:'||v_emgrename||'所在部门名称:'||v_dname);
end;
--73创建一个过程,
--输出工资成本最高的部门的部门号和部门名称。
create or replace procedure p_select_deptno_dname(p_sum out number,
p_dname out varchar2,
p_deptno out varchar2) is
begin
select sum(e.sal) over(partition by e.deptno) d_sum, d.dname, d.deptno into p_sum ,p_dname,p_deptno
from emp_zhanglinjiang e, dept_zhanglinjiang d
where e.deptno = d.deptno
and rownum = 1
order by d_sum desc;
exception
when no_data_found then
dbms_output.put_line('error');
end;
declare
v_sum emp_zhanglinjiang.sal%type;
v_dname dept_zhanglinjiang.dname%type;
v_deptno dept_zhanglinjiang.deptno%type;
begin
p_select_deptno_dname(v_sum,v_dname,v_deptno);
dbms_output.put_line('最高成本部门名字:'||v_dname||' 部门编号:'||v_deptno );
end;
---74创建一个函数,传入员工编号,
--返回其所在部门名称。
create or replace function p_ReturnDeptNameBYEmpID(p_empno varchar2) return varchar
is
v_deptartment_name dept_zhanglinjiang.dname%type;
begin
select d.dname
from emp_zhanglinjiang e,dept_zhanglinjiang d
where e.deptno=d.deptno and
e.empno='p_empno'
dbms_output.put_line('dname为'||v_deptartment_name);
return v_deptartment_name;
end;
declare
v_DeptName dept_zhanglinjiang.dname%type;
begin
v_DeptName:= p_ReturnDeptNameBYEmpID(&p_empno);
dbms_output.put_line(v_DeptName);
end;
--75创建一个函数,传入时间,
--返回入职时间比这个时间早的所有员工的平均工资。
create or replace function p_ReturnEAvgSal(Datelike20070907 varchar2) return varchar
is
v_deptartment_name dept_zhanglinjiang.dname%type;
begin
select avg(e.sal) over() as 平均工资
from emp_zhanglinjiang e
where e.hiredate< to_date('Datelike20070907 ','yyyymmdd ')
and rownum=1
group by e.sal ;
dbms_output.put_line('平均工资为'||平均工资);
return 平均工资;
end;
declare
v_EAvgSal number;
begin
v_EAvgSal:= p_ReturnEAvgSal('&Datelike19810928');
dbms_output.put_line('平均工资为' || v_EAvgSal);
end;
select distinct avg(e.sal) over() as 平均工资
from emp_zhanglinjiang e
where e.hiredate < to_date('19810928', 'yyyymmdd')
group by e.sal;
select * FROM EMP_ZHANGLINJIANG;
--------------------------------- 76 根据名字返回部门id 包头包体 ReturnDeptIDBYDName
declare
v_deptno dept_zhanglinjiang.deptno%type;
begin
v_deptno:= p_ReturnDeptIDBYDName('&dname');
dbms_output.put_line(v_deptno);
end;
--------------------------------- 77 根据雇员名字返回部门id 包头包体 p_ReturnDeptIDBYEmpName
create or replace package pkg_emp_dept is
procedure print_emp;
function get_dno(p_ename VARCHAR2) return number;
end pkg_emp_dept;
CREATE OR REPLACE PACKAGE BODY pkg_emp_dept is
PROCEDURE print_emp IS
declare cursor c_emp is
select e.* from emp_zhanglinjiang e;
begin
for empno_row in c_emp loop dbms_output.put_line(empno_row.ename);
end loop;
end print_emp;
function get_dno(p_ename VARCHAR2) return number is
v_deptartment_id dept_zhanglinjiang.deptno%type;
begin
select d.deptno into v_deptartment_id from emp_zhanglinjiang e, dept_zhanglinjiang d where e.deptno = d.deptno and e.ename = p_ename; dbms_output.put_line('dID为' || v_deptartment_id); return v_deptartment_id;
end get_dno;
END pkg_emp_dept;
-----测试
begin
pkg_emp_dept.print_emp;
end;
declare
v_deptno dept_zhanglinjiang.deptno%type;
begin
v_deptno:= p_ReturnDeptIDBYEmpName('&p_ename');
end;
------78 遍历emp的游标
declare
cursor c_emp /*(d_dno number)*/is
select e.* from emp_zhanglinjiang e ;
begin
for empno_row in c_emp loop
dbms_output.put_line(empno_row.ename);
end loop;
end;
--------------------79 触发器
create or replace trigger trig_ins_dept21
before INSERT on emp_zhanglinjiang
begin
dbms_output.put_line('a');
END;
create table dept_zhanglinjiang
as
select * from dept_zg;
begin
insert into dept_zhanglinjiang values(2,'wudi','asdf') ;
end;
select * from dept_zg;
select * from dept_zhanglinjiang;
--80创建一个包,包含一个存储过程和一个函数。
--1存储过程
-- 对所有员工按他们工资的20%为他们加薪,如果增加的工资大于600 就取消加薪。
--函数
-- 输入员工姓名,返回该员工所在的部门工作年限最大的员工名字。
-- 作业提交方式:存储过程&函数调用代码、测试结果截图,及包文件。
create or replace package pkg_emp_s is
procedure changeSal_emp;
function get_OldestEinD(p_ename VARCHAR2) return VARCHAR2;
end pkg_emp_s;
declare
v_ename emp_zhanglinjiang.ename%type;
begin
v_ename:= pkg_emp_s.get_OldestEinD('&ename');
end;
begin
pkg_emp_s.changeSal_emp;
end;
--82 触发器 raise_application_error(-20001,'can not modify');
create table emp_zhanglinjiang
as select * from emp;
-----触发器
create or replace trigger trig_ins_emp
before update on emp_zhanglinjiang
begin
--for each row
if(:old.sal>:new.sal) then
raise_application_error(-20001,'can not modify');
end if;
END;
---更新
declare
begin
update emp_zhanglinjiang a set a.sal=1 where a.empno=7369;
end;
---触发器
create or replace trigger tri_name
before inser on table_a
for each row
insert into table_b(:new.col1,..,:new.coln);
end;
create table emp_zhanglinjiang as
select * from emp
--83.接收部门编号,显示部门名称和地理位置。
declare
v_deptno dept_zhanglinjiang.deptno%type;
v_loc dept_zhanglinjiang.loc%type := '';
begin
select loc into v_loc from dept_zhanglinjiang where deptno = &v_deptno;
dbms_output.put_line(v_loc);
exception
when others then
dbms_output.put_line('未知');
end;
--84.接收员工编号,显示该员工的所有信息,没有提成的用0替代。(用%rowtype实现)select * from emp_zhanglinjiang;
declare
v_empno emp_zhanglinjiang.empno%type;
v_emp_zhanglinjiang emp_zhanglinjiang%ROWTYPE;
begin
select emp_zhanglinjiang.* into v_emp_zhanglinjiang from emp_zhanglinjiang where empno = &v_empno;
dbms_output.put_line( v_emp_zhanglinjiang.ename);
dbms_output.put_line( v_emp_zhanglinjiang.empno);
dbms_output.put_line( v_emp_zhanglinjiang.job);
dbms_output.put_line( v_emp_zhanglinjiang.mgr);
dbms_output.put_line( v_emp_zhanglinjiang.hiredate);
dbms_output.put_line( v_emp_zhanglinjiang.sal);
dbms_output.put_line( v_emp_zhanglinjiang.comm);
dbms_output.put_line( v_emp_zhanglinjiang.sex);
dbms_output.put_line( v_emp_zhanglinjiang.age);
dbms_output.put_line( v_emp_zhanglinjiang.deptno);
exception
when others then
dbms_output.put_line('未知');
end;
--85.输入一个员工的编号,如果其工资高于3500,则显示“高工资”,如果工资大于2000,则显示“中等工资”,如果工资小于2000的则显示“低等工资”。
declare
v_empno emp_zhanglinjiang.empno%type;
v_sal emp_zhanglinjiang.sal%type := 0;
begin
select sal into v_sal from emp_zhanglinjiang where empno = '&v_empno';
case TRUE
when v_sal<= 2000 then
dbms_output.put_line('低等工资');
when v_sal>= 2001 and v_sal<=3500 then
dbms_output.put_line('中等工资');
when v_sal>= 3500 then
dbms_output.put_line('高工资');
else
dbms_output.put_line('unknow');
end case;
exception
when others then
dbms_output.put_line('未知');
end;
--86.接收一个员工名称,判断他的job,根据job不同,为他增加相应的工资,
--“ANALYST”增加10%,“CLERK”增加15%,“MANAGER”增加20%,其他job增加5%。
select * from emp_zhanglinjiang for update
declare
v_ename emp_zhanglinjiang.ename%type;
v_sal emp_zhanglinjiang.sal%type := 0;
v_job emp_zhanglinjiang.job%type :='';
begin
select sal, job
into v_sal, v_job
from emp_zhanglinjiang
where ename = '&v_ename';
if v_job = 'ANALYST' then
update emp_zhanglinjiang
set sal = v_sal * (1.1)
where ename = '&v_ename';
COMMIT;
elsif v_job = 'CLERK' then
update emp_zhanglinjiang
set sal = v_sal * (1.15)
where ename = '&v_ename';
COMMIT;
elsif v_job = 'MANAGER' then
update emp_zhanglinjiang
set sal = v_sal * (1.20)
where ename = '&v_ename';
COMMIT;
else
update emp_zhanglinjiang
set sal = v_sal * (1.05)
where ename = '&v_ename';
COMMIT;
end if;
exception
when others then
dbms_output.put_line('未知');
end;
--87.输入一个雇员编号,根据它所在的部门涨工资,规则:
--? 10部门上涨10%
--? 20部门上涨20%
--? 30部门上涨30%
--注意:所有部门的员工工资上涨后,都不能超过5000,如果超过5000,则工资就为5000。
--select * from emp_zhanglinjiang for update;
declare
v_empno emp_zhanglinjiang.empno%type;
v_sal emp_zhanglinjiang.sal%type := 0;
v_deptno emp_zhanglinjiang.deptno%type := 0;
begin
select sal, deptno
into v_sal, v_deptno
from emp_zhanglinjiang
where empno = &v_empno;
if v_deptno = 10 and v_sal * (1.1) < 5000 then
update emp_zhanglinjiang
set sal = v_sal * (1.1)
where deptno = v_deptno
and ;
COMMIT;
elsif v_deptno = 10 and v_sal * (1.1) >= 5000 then
update emp_zhanglinjiang set sal = 5000 where deptno = v_deptno;
COMMIT;
elsif v_deptno = 20 and v_sal * (1.2) < 5000 then
update emp_zhanglinjiang
set sal = v_sal * (1.2)
where deptno = v_deptno;
COMMIT;
elsif v_deptno = 20 and v_sal * (1.2) >= 5000 then
update emp_zhanglinjiang set sal = 5000 where deptno = v_deptno;
COMMIT;
elsif v_deptno = 30 and v_sal * (1.3) < 5000 then
update emp_zhanglinjiang
set sal = v_sal * (1.3)
where deptno = v_deptno;
COMMIT;
elsif v_deptno = 30 and v_sal * (1.3) >= 5000 then
update emp_zhanglinjiang set sal = 5000 where deptno = v_deptno;
COMMIT;
else
dbms_output.put_line('');
end if;
exception
when others then
dbms_output.put_line('未知');
end;
------------------88 输入 ename 输出工资等级
declare
v_ename emp_zhanglinjiang.ename%type;
v_sal emp_zhanglinjiang.sal%type := 0;
begin
select sal into v_sal from emp_zhanglinjiang where ename = '&ename';
if v_sal between 700 and 1200 then
dbms_output.put_line(1);
elsif v_sal between 1201 and 1400 then
dbms_output.put_line(2);
elsif v_sal between 1401 and 2000 then
dbms_output.put_line(3);
elsif v_sal between 2001 and 3000 then
dbms_output.put_line(4);
elsif v_sal between 3001 and 9999 then
dbms_output.put_line(5);
else
dbms_output.put_line('unknow');
end if;
exception
when others then
dbms_output.put_line('未知');
end;
------------------89 创建过程 根据id修改名字 输入 ename,id
create or replace procedure p_modifyName(v_Name in varchar2,v_id char)
/*
v_Name:输入参数,为修改后的学生姓名
v_id:输入参数,为被修改学生的学好
*/
begin
update emp_zhanglinjiang emp set emp.ename=&v_name where id=v_id;
commit;
end p_modifyName;
--90.为各部门雇员工资等级情况创建一个视图,
--包含部门名称,雇员姓名,工作职位,工资等级4个信息,视图命名规则dept_salgrade_v_xxx。
create view dept_salgrade_v_zhanglinjiang as
select e.ename,e.job,d.deptno,
(select salgrade.grade
from salgrade_zhanglinjiang salgrade
where e.sal>salgrade.losal
and e.sal<salgrade.hisal) as s
from emp_zhanglinjiang e,dept_zhanglinjiang d
where d.deptno=e.deptno(+);
--91.在BIQUSR下为视图dept_salgrade_v_xxx创建公共同义词dept_salgrade_v_xxx。
create public synonym dept_salgrade_v_zhanglinjiang01 for dept_salgrade_v_zhanglinjiang;
--drop public synonym dept_salgrade_v_zhanglinjiang;
--92.在BIQUSR下用同义词dept_salgrade_v_xxx查询视图数据。
select * from dept_salgrade_v_zhanglinjiang;
--93.将dept_salgrade_v_xxx的数据更新权限赋予BIQUSR用户。
grant update on dept_salgrade_v_zhanglinjiang to biqusr
--94.收回dept_salgrade_v_xxx赋予BIQUSR用户的数据更新权限。
revoke update on dept_salgrade_v_zhanglinjiang from biqusr
--95.得到工资大于自己部门平均工资的雇员信息。
select *
from emp_zhanglinjiang emp
where emp.sal >
(select avg(sal) from emp_zhanglinjiang where deptno = emp.deptno)
--96.列出所有雇员的姓名和其上级的姓名。
select emp.ename as emp, empmgr.ename as mgr_emp
from emp_zhanglinjiang emp ,emp_zhanglinjiang empmgr
where emp.mgr=empmgr.mgr(+)
--97.以职位分组,找出平均工资排在第3、4的两种职位。 --还没有写出3、4
select x.*
from (select rownum row_num, v.*
from (select a.job, avg(a.sal) avg_sal
from emp_zhanglinjiang a
group by a.job
order by avg_sal) v) x
where x.row_num = 3
or x.row_num = 4
--98.查找出收入(工资加上奖金),下级比自己上级还高的雇员编号,雇员姓名,雇员收入。
select e.ename,e.sal,e.empno
from emp_zhanglinjiang e ,(select emp_zhanglinjiang.*
from emp_zhanglinjiang )
where
e.mgr=a.empo and (a.sal+nvl(a.comm,0))>(e.sal+nvl(e.comm,0))
select e.empno, e.ename, e.sal
from emp_zhanglinjiang e,
(select empno, ename, (sal + nvl(comm, 0)) income
from emp_zhanglinjiang) m
where e.mgr = m.empno
and e.sal > m.income
--99.更改部门30的位置LOC为“广州”。
UPDATE dept_zhanglinjiang SET dept_zhanglinjiang.loc ='广州'
WHERE dept_zhanglinjiang.deptno = 30;
--100.在部门表中插入如下数据:
/*DEPTNO DNAME LOC
50 营运 行政区
60 BI交付 %ODC2
70 ORACLE交付 %ODC2
80 JAVA交付 %ODC6*/
INSERT INTO dept_zhanglinjiang
(dept_zhanglinjiang.deptno,
dept_zhanglinjiang.dname,
dept_zhanglinjiang.loc)
VALUES
(50, '营运', '行政区');
INSERT INTO dept_zhanglinjiang
(dept_zhanglinjiang.deptno,
dept_zhanglinjiang.dname,
dept_zhanglinjiang.loc)
VALUES
(60, 'BI交付', '%ODC2');
INSERT INTO dept_zhanglinjiang
(dept_zhanglinjiang.deptno,
dept_zhanglinjiang.dname,
dept_zhanglinjiang.loc)
VALUES
(70, 'ORACLE交付', '%ODC2');
INSERT INTO dept_zhanglinjiang
(dept_zhanglinjiang.deptno,
dept_zhanglinjiang.dname,
dept_zhanglinjiang.loc)
VALUES
(80, 'JAVA交付', '%ODC6');
--101.修改部门位置中含有%的数据,去掉%。
update dept_zhanglinjiang
set dept_zhanglinjiang.loc = replace(dept_zhanglinjiang.loc, '%', ''); --可以更新到数据库
--where dept_zhanglinjiang.loc is like '-%-' escape '-' --加条件
select * from dept_zhanglinjiang
--102.删除部门编号为50的部门。
delete dept.*
from dept_zhanglinjiang dept
where dept.deptno=50
select 'delete from dept d where deptno=' || v.deptno || ';' delete_sql,
v.dname,
v.loc
from dept_zhanglinjiang v
where v.deptno = 50;
--103.找出dept_xxx中非dept的数据。(3种实现方式)。
select *
from dept_zhanglinjiang
minus
select * from scott.dept;
select distinct *from dept_zhanglinjiang
where not exists (select *
from scott.dept
where dept_zhanglinjiang.deptno = scott.dept.deptno
and dept_zhanglinjiang.dname = scott.dept.dname
and dept_zhanglinjiang.loc = scott.dept.loc);
--104.查询职位为“PRESIDENT”及其下属菜单,按树形结构展现,使用lpad函数辅助缩进,展示结果如下。
--(用select 生产删除的语句)
select *
from emp_zhanglinjiang.mgr ,(select * from emp_zhanglinjiang) v
where
emp_zhanglinjiang.empno is not null
connect by prior emp_zhanglinjiang.mgr=v.empno
start with emp.job='PRESIDENT'
select level, emp.empno, lpad(' ', level * 4, ' ') || emp.ename, emp.job, emp.sal
from emp_zhanglinjiang emp
connect by prior emp.empno = mgr
start with emp.job = 'PRESIDENT';
select level,lpad(' ',level*2,' ')|| e.ename ename,e.job,e.sal
from emp_zhanglinjiang e
connect by prior e.empno = mgr
start with e.ename='KING';
---- 一次作业
create table SUPPLIERS_zlj
(
SUPPLIER_ID NUMBER(10) not null,
SUPPLIER_NAME VARCHAR2(50),
CITY VARCHAR2(50)
);
select a.supplier_id from SUPPLIERS_zlj a
INSERT INTO SUPPLIERS_zlj a
(a.supplier_id,
a.supplier_name,
a.city)
VALUES
(20004, 'Smath', 'Detroit');
create table ORDERS_zlj
(
ORDER_ID NUMBER(10) not null,
ORDER_DATE DATE,
SUPPLIER_ID NUMBER(10),
EMPLOYEE_NUMBER NUMBER(20)
);
create table DEPARTMENT_zlj
(
DEPARTMENT_ID NUMBER(10) not null,
DEPARTMENT_NAME VARCHAR2(50)
);
SELECT *FROM DEPARTMENT_zlj for update
create table EMPLOYEES_zlj
(
EMPLOYEE_NUMBER NUMBER(10) not null,
EMPLOYEE_NAME VARCHAR2(50),
DEPARTMENT_ID NUMBER(10),
SALARY NUMBER(7,2)
);
select * from EMPLOYEES_zlj for update;
--105.查询没有下过采购订单供应商名称及其所属城市,并按城市名称降序排列
Select sup.Supplier_Name, sup.city
from SUPPLIERS_zlj sup
where
sup.supplier_id not in
(Select sup.supplier_id
from SUPPLIERS_zlj sup, ORDERS_zlj ord
where sup.supplier_id = ord.supplier_id)
--106.查询所有部门名称及其员工个数和平均工资
select distinct dept.department_name,
count(emp.employee_number) over(partition by emp.department_id) sum_emp_num,
avg(emp.salary) over(partition by emp.department_id) avg_emp_sal
from EMPLOYEES_zlj emp, DEPARTMENT_zlj dept
where emp.department_id(+) = dept.department_id
--107.查询供应商数量大于3的供应商所属城市
select sup.city
from SUPPLIERS_zlj sup
group by sup.city
having count(sup.city)>3;
--108.查询所有员工,及其相关采购订单日期、采购订单相关供应商
select EMPLOYEES_zlj.employee_name, ORDERS_ZLJ.order_date, SUPPLIERS_zlj.supplier_name
from EMPLOYEES_zlj ,
ORDERS_ZLJ ,
SUPPLIERS_zlj
where ORDERS_ZLJ.EMPLOYEE_NUMBER(+)=EMPLOYEES_zlj.Employee_Number
and
ORDERS_ZLJ.SUPPLIER_ID=SUPPLIERS_zlj.Supplier_Id(+);
--109.查询所有供应商名称及其所属城市,并按城市名称降序排列,分页显示,每页2条记录,取出第2页数据
select EMPLOYEES_zlj.city,EMPLOYEES_zlj.supplier_name from EMPLOYEES_zlj ;
select * from EMPLOYEES_zlj;
select x.*
from (select rownum row_num, v.*
from (select * from SUPPLIERS_zlj a
order by a.city) v) x
where x.row_num between 3 and 4
--210查询每个员工占部门总工资的百分比,及部门总工资占全公司总工资的百分比,要求按部门编码升序,雇员工资降序排列。
select b.sal /100*(sum(sal) over(partition by b.deptno)) ,sum(sal)/(100*sum(sal) over())
from dept_zhanglinjiang a, emp_zhanglinjiang b
where b.deptno=a.deptno
--group by a.deptno,
order by a.dep2 tno asc,b.sal desc
select (sum(sal) over(partition by b.deptno) )as dept_sal ,(100*sum(sal) over()) as all_sal
from dept_zhanglinjiang a, emp_zhanglinjiang b
where b.deptno=a.deptno
--group by a.deptno,
order by a.deptno asc,b.sal desc
---211 查询 工资占部门总工资,部门占工资总工资百分比
select a.ename as 雇员姓名,
a.sal as 雇员工资,
a.deptno as 部门编码,
a.d_sum as 部门总工资,
round((a.sal / a.d_sum) * 100, 2) || '%' avg_e,
a.e_sum as 公司总工资,
round((a.d_sum / a.e_sum) * 100, 2) || '%' avg_d
from (select e.*,
sum(e.sal) over(partition by e.deptno order by e.deptno) d_sum,
sum(e.sal) over() e_sum
from emp e) a
order by a.deptno asc, sal desc;
--212查询每个部门最早入职的两名员工。
----1
select a.ename as 雇员名字, a.empno as 雇员编号, a.hiredate as 入职时间
from (select * from EMP_zhanglinjiang a order by a.hiredate asc) a
where rownum < 3
select select a.ename as 雇员名字, a.empno as 雇员编号, a.hiredate as 入职时间
from
----2
select *
from (select deptno,
ename,
sal,
row_number() over(partition by deptno order by sal desc) rn
from emp_lijia)
where rn < 3;
/*
select rownum row_id, EMP_a.Hiredate,min(Hiredate) over(),lead(row_id,1) over(order by Hiredate) leadsal
from EMP_zhanglinjiang EMP_a */
--偏移量
--213分部门得到平均工资等级为2级(等级表)的部门编号
select d.deptno , avg(sal) over(partition by d.deptno order by sal) as a
from emp_zhanglinjiang e ,dept_zhanglinjiang d
where e.deptno=d.deptno
and a between(select salgrade.losal from salgrade_zhanglinjiang salgrade where salgrade.grade=2) and
(select salgrade.hisal from salgrade_zhanglinjiang salgrade where salgrade.grade=2)
select a.deptno, s.grade
from (select deptno, avg(sal) as avg_sal
from emp_zhanglinjiang
group by deptno) a,
salgrade_zhanglinjiang s
where a.avg_sal between losal and hisal
and grade = 2;
--214部门名称,雇员姓名,工作职位,工资等级(直接放到查询字句)
select e.ename,e.job,d.deptno, e.sal,
(select salgrade.grade
from salgrade_zhanglinjiang salgrade
where e.sal>=salgrade.losal
and e.sal<=salgrade.hisal) as s
from emp_zhanglinjiang e,dept_zhanglinjiang d
where d.deptno=e.deptno(+);
--215查找出不在部门20,且比部门20中任何一个人工资都高的员工姓名、部门名称
select e.ename as 员工姓名, d.dname as 部门名称
from emp_zhanglinjiang e, dept_zhanglinjiang d
where d.deptno = e.deptno
and e.deptno <> 20
and e.sal >
(select max(sal) from emp_zhanglinjiang a where a.deptno = 20)
--216查找出工资等级不为4级的员工的员工名字,部门名字,部门位置
select e.ename as 员工名字, d.dname as 部门名字, e.job as 部门位置
from emp_zhanglinjiang e, dept_zhanglinjiang d
where d.deptno = e.deptno
and (select salgrade.grade
from salgrade_zhanglinjiang salgrade
where e.sal >= salgrade.losal
and e.sal <= salgrade.hisal) <> 4
--217查找出职位和'MARTIN' 或者'SMITH'一样的员工的平均工资
select avg(sal)
from emp_zhanglinjiang
where emp_zhanglinjiang.job in
(select emp_zhanglinjiang.job
from emp_zhanglinjiang
where emp_zhanglinjiang.ename = 'MARTIN')
OR emp_zhanglinjiang.job in
(select emp_zhanglinjiang.job
from emp_zhanglinjiang
where emp_zhanglinjiang.ename = 'SMITH')
--218查询出king所在部门的工作年限最大的员工名字
select ename
from emp_zhanglinjiang
where hiredate in
(select min(hiredate)
from emp_zhanglinjiang
where deptno in (select deptno from emp_zhanglinjiang where ename = 'KING'));
----219 查询上司工资比下属工资奖金和都要大的
select e.empno, e.ename, e.sal
from emp_zhanglinjiang e, emp_zhanglinjiang a
Where e.mgr = a.empno and
e.sal >(a.sal + nvl(a.comm, 0))
----220查询上司工资奖金比下属工资奖金和都要大的
select e.empno,e.ename,(e.sal+nvl(e.comm,0))"sum"
from emp e,emp m
where e.mgr=m.empno
and (e.sal+nvl(e.comm,0))>(m.sal+nvl(m.comm,0));
PL_SQL 学记总结代码
最新推荐文章于 2022-11-17 23:09:03 发布