Oracle练习(一)
Oracle
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 | 1.检索部门编号、部门名称、部门所在地及其每个部门的员工总数。 select d.deptno,d.dname,d.loc, count (*) from emp e,dept d where e.deptno=d.deptno group by d.deptno,d.dname,d.loc; 2.检索员工和所属经理的姓名。 select e.ename 雇员,m.ename 经理 from emp e,emp m where e.mgr=m.empno; 3. 检索工资等级处于第四级别的员工的姓名。 select e.ename from emp e inner join salgrade s on (e.sal between s.losal and s.hisal) where s.grade=4; 4.检索工资等级高于smith的员工信息。 select * from emp e join salgrade s on (e.sal between s.losal and s.hisal) where s.grade>( select distinct s.grade from emp e join salgrade s on (( select sal from emp where ename= 'SMITH' ) between s.losal and s.hisal)); 5.检索30部门中1980年1月份之前入职的员工信息。显示:员工号、员工名、部门号,部门名、入职日期。 select empno,ename,d.deptno,d.dname,hiredate from emp,dept d where emp.deptno=d.deptno and d.deptno=30 and hiredate<to_date( '1980-01-01' , 'yyyy-mm-dd' ); 6. 检索雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。 select e.ename 雇员,m.ename 经理,e.HIREDATE 雇员的雇佣日期,m.hiredate 经理雇佣日期 from emp e,emp m where e.mgr=m.empno and e.hiredate<m.hiredate; 7.按部门号(deptno) 及 工资(sal)排序检索公司的员工信息(要求部门号从大到小 ,部门号相同的按工资由低到高),显示的字段为:员工号、员工名、工资、部门号。 select empno,ename,sal,deptno from emp order by deptno desc ,sal; 8.检索不同部门经理的最低工资。 select m.ename,e.empno,e.sal from emp e,emp m where e.mgr=m.empno and e.deptno in ( select deptno from emp group by deptno) and e.sal in ( select min (sal) from emp group by deptno); 9.检索部门号及其本部门的最低工资。 select deptno, min (sal) from emp group by deptno; 10.检索从事clerk工作的员工姓名和所在部门名称。 select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno where e.job= 'CLERK' ; 11.检索和名叫SCOTT的员工相同工资的员工信息,但不显示SCOTT。显示字段:员工号、员工名、工资。 select empno,ename,sal from emp where sal =( select sal from emp where ename= 'SCOTT' ) and ename!= 'SCOTT' 12. 检索与SCOTT从事相同工作的员工信息。 select * from emp where job=( select job from emp where ename= 'SCOTT' ); 13.检索出员工表emp中的第3条到第8条记录。显示字段:emp表中的全部字段。 select * from ( select rownum num,e.* from emp e) where num>=3 and num<=8; 14. 检索与30部门员工工资等级相同的员工姓名与工资。 select * from emp e join salgrade s on (e.sal between s.LOSAL and s.HISAL) where s.grade=( select distinct s.grade from emp e join salgrade s on (( select avg (sal) from emp where deptno=30) between s.LOSAL and s.HISAL)); 15. 求分段显示薪水的个数。显示结果如下: DEPTNO 800-2000 2001-5000 ------ ---------- ---------- 5 1 2 3 1 2 select t1.deptno, "800-2000" , "2001-5000" from ( select deptno , count (*) as "800-2000" from emp where sal between 800 and 2000 group by deptno ) t1 join ( select deptno , count (*) as "2001-5000" from emp where sal between 2001 and 5000 group by deptno) t2 on t1.deptno = t2.deptno; 16. 用PL/sql实现输出7369号员工的姓名。 ---用PL/sql实现输出7369员工的姓名; set serveroutput on ; -- 将sql*plus的输出功能开启 declare --声明部分 var_empno number(4) := 7369 ; --声明一个变量var_empno ,并给赋值,用:= 赋值var_name varchar2(10); --声明一个变量var_name var_sal number(9,2); begin --执行部分的开始 select ename ,sal into var_name ,var_sal from emp where empno = var_empno; dbms_output.put_line(var_empno || ' 的名字是 :' || var_name); --输出语句 dbms_output.put_line(var_empno || ' 的工资是 :' || var_ sal ); ----输出语句 end ; --执行部分的结束,end后面要加 ; ----执行结果为: 的名字是 : SMITH PL/SQL 过程已成功完成。 17.编写一sql语句,将7369号员工的入职日期改为1997/7/1,工资提高800 ,其他信息不变。 update emp set hiredate=to_date( '1997/7/1' , 'yyyy-mm-dd' ),sal=sal+800 where empno=7369; 18.假定现在有一张表,表名为T_emp ,其表结构和现有的emp表结构完全相同,但是T_emp中没有记录,请编写一sql语句,实现将emp中部门编号为20的员工信息添加到T_emp中。 insert into T_emp select * from emp where deptno=20; 19. 对所有员工,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%; 如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%;其他情况不作处理。要求:使用游标或函数编程实现。 set serveroutput on ; declare cursor c1 is select * from emp; c1rec c1%rowtype; v_loc varchar2(20); begin for c1rec in c1 loop select loc into v_loc from dept where deptno = c1rec.deptno; if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then update emp set sal = sal * 1.15 where empno = c1rec.empno; elsif c1rec.job= 'CLERK' and v_loc = 'NEW YORK' then update emp set sal = sal * 0.95 where empno = c1rec.empno; else null ; end if; end loop; end ; 20. 对直接上级是 'BLAKE' 的所有员工,按照参加工作的时间加薪:1981年6月以前入职的员工加薪10%、1981年6月以后入职的员工加薪5%。 要求:使用游标或函数编程实现。 declare cursor c1 is select * from emp where mgr = ( select empno from emp where ename= 'BLAKE' ); --直接上级是'BLAKE'的所有员工 c1rec c1%rowtype; begin for c1rec in c1 loop if c1rec.hiredate < '01-6月-81' then update emp set sal = sal * 1.1 where empno = c1rec.empno; else update emp set sal = sal * 1.05 where empno = c1rec.empno; end if; end loop; end ; 创建一个角色MYROLE,此角色具有建表,建视图的系统权限和查找SCOTT用户EMP,DEPT表的权限,将此角色授权给TEST用户。 CREATE ROLE MYROLE; GRANT CREATE TABLE , CREATE VIEW TO MYROLE; GRANT SELECT ON SCOTT.EMP TO MYROLE; GRANT SELECT ON SCOTT.DEPT TO MYROLE; CREATE USER TEST IDENTIFIED BY TEST; GRANT MYROLE TO TEST; 创建EMP,DEPT两张表的副表(包括它们之间的约束) CREATE TABLE mydept AS SELECT * FROM dept; ALTER TABLE mydept ADD CONSTRAINT pk_mydept_deptno PRIMARY KEY (deptno); CREATE TABLE myemp AS SELECT * FROM emp ALTER TABLE myemp ADD CONSTRAINT pk_myemp_empno PAIMARY KEY (empno); ALTER TABLE myemp ADD CONSTRAINT fk_myemp_mydept FOREIGN KEY (deptno) REFERENCES mydept(deptno); 找出部门10中所有经理、部门20中所有办事员,既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料 select * from emp where (deptno=10 and job= upper ( 'manager' )) or (deptno=20 and job= upper ( 'clerk ' )) or (job<> upper (‘manager’) and job<> upper (‘clerk’) and sal>=2000); 找出不收取佣金或收取的佣金低于100的雇员 select * from emp where nvl(comm,0)<100; 显示不带有 'R' 的雇员姓名 select ename from emp where ename not like ‘%R%’; 显示所有雇员的姓名、工作和薪金,按工作的降序顺序排序,而工作相同时按薪金升序 select ename,job,sal from emp order by job desc ,sal asc 求哪些人工资在平均工资之上,列出姓名和工资。 select ename,sal from emp where sal > ( select avg (sal) from emp); 显示除20部门外,部门平均工资在2500以上的部门,列出部门编号和平均工资。 select avg (sal) , deptno from emp where deptno <>20 group by deptno having avg (sal) > 2500; 列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。 select * from emp where sal in ( select sal from emp where deptno=30); 列出所有部门的详细信息和部门人数。 select a.*,( select count (*) from emp where deptno=a.deptno) tot from dept a ; 列出各种工作的最低月收入。 select job, min (sal+nvl(comm,0)) from emp group by job; 返回工资水平(等级)高于平均工资水平的员工详细信息。 select * from emp e join salgrade s on (e.sal between s.losal and s.hisal) where s.grade>( select distinct s.grade from emp e join salgrade s on (( select avg (sal) from emp) between s.losal and s.hisal)); 列出至少有两个员工的所有部门信息 select * from dept where deptno in ( select deptno from emp group by deptno having count (*)>1); 查询emp表的第6行到第10行内容 select * from ( select rownum r,e.* from emp e) where r between 6 and 10; 查询入职最早的前5到10名员工的姓名和入职日期(按年月日格式显示日期) Select ename,to_char(hiredate,’yyyy-mm-dd’) from ( select ename,hiredate,rownum r from ( select ename,hiredate from emp order by hiredate ) where rownum<=10) where r>=5; 编写一sql语句,将7369号员工的入职日期改为1997/7/1,工资提高800 ,其他信息不变。 update emp set hiredate=to_date( '1997/7/1' , 'yyyy-mm-dd' ),sal=sal+800 where empno=7369; 查询雇员和其直接领导姓名及他们所在部门名称,没有领导的雇员也要列出. select e.ename,ed.dname,m.ename,md.dname from emp e,emp m,dept ed,dept md where e.deptno=ed.deptno and e.mgr=m.empno(+) and m.deptno=md.deptno(+); 列出部门编号,部门名称,部门的员工数量,部门的平均工资. select d.deptno,d.dname,t.cou,t. avg from dept d,( select deptno, count (empno) cou, avg (sal) avg from emp group by deptno) t where d.deptno=t.deptno; 定义游标update_cur,根据职务调整雇员的工资,如果职务为“SALESMAN”或者“ANALYST”工资上调100元,如果职务为“MANAGER”工资上调200元,其它职务工资上调50元。 declare v_job emp.job%type; cursor update_cur is select job from emp for update ; begin open emp_cur; loop fetch update_cur into v_job; exit when emp_cur%notfound; case when v_job= 'SALESMAN' or v_job= 'ANALYST' then update emp set sal=sal+100 where current of emp_cur; when v_job= ' MANAGER ' then update emp set sal=sal+200 where current of emp_cur; else update emp set sal=sal+50 where current of emp_cur; end case ; end loop; end ; 创建一个触发器,使其可以修改DEPT表的DEPTNO。 create or replace trigger update_dept after update on dept for each row begin update emp set deptno=:new.deptno where deptno=:old.deptno; end ; 1. 对所有员工,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%;如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%;其他情况不作处理。要求:使用游标或函数编程实现。 set serveroutput on ;1. declare cursor c1 is select * from emp; c1rec c1%rowtype; v_loc varchar2(20); begin for c1rec in c1 loop select loc into v_loc from dept where deptno = c1rec.deptno; if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then update emp set sal = sal * 1.15 where empno = c1rec.empno; elsif c1rec.job= 'CLERK' and v_loc = 'NEW YORK' then update emp set sal = sal * 0.95 where empno = c1rec.empno; else null ; end if; end loop; end ; 2. 用PL/sql实现输出7369号员工的姓名。 set serveroutput on ; -- 将sql*plus的输出功能开启 declare --声明部分 var_empno number(4) := 7369 ; --声明一个变量var_empno ,并给赋值,用:= 赋值var_name varchar2(10); --声明一个变量var_name var_sal number(9,2); begin --执行部分的开始 select ename ,sal into var_name ,var_sal from emp where empno = var_empno; dbms_output.put_line(var_empno || ' 的名字是 :' || var_name); --输出语句 dbms_output.put_line(var_empno || ' 的工资是 :' || var_ sal ); --输出语句 end ; --执行部分的结束,end后面要加 ; ----执行结果为: 的名字是 : SMITH |
人怂胆子小,手拿大刀跑。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
· 手把手教你更优雅的享受 DeepSeek
· 腾讯元宝接入 DeepSeek R1 模型,支持深度思考 + 联网搜索,好用不卡机!
· AI工具推荐:领先的开源 AI 代码助手——Continue
· 探秘Transformer系列之(2)---总体架构
· V-Control:一个基于 .NET MAUI 的开箱即用的UI组件库
2016-08-13 事件
2016-08-13 DOM中对象的获得
2016-08-13 DOM
2016-08-13 临界区和事件
2016-08-13 printf,sprintf,fprintf的比较
2016-08-13 线程的临界区