Oracle常用查询（一）

蔚落 2022-03-29 03:37 423阅读 0赞

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

*------ ---------- ----------*

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

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

Oracle常用查询（一）

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关常用Oracle DBA 查询

相关 oracle中查询SQL常用函数

相关 JPA(一) 常用查询方法

相关 Oracle 常用SQL查询列表

相关 oracle 中子查询的常用法

相关 oracle常用经典sql查询

相关 Oracle数据字典常用查询

相关 Oracle常用查询（一）

相关 oracle常用查询语句(更新中..)

相关 Oracle的基本操作（一：子查询与常用函数）