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
     
      =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后面要加 ;----执行结果为：的名字是 : SMITHPL/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 empALTER 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_deptafter update on deptfor each rowbeginupdate 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