sql习题五--简单plsql

2019独角兽企业重金招聘Python工程师标准>>>

1、写一个plsql计算正方形的面积
SQL> declare
   2  a number ;
   3  s number;
   4  begin
   5  a:=&a;
   6  s:=a*a;
   7  dbms_output.put_line(a||'Square area is'||s);
   8  end;
   9  /
Enter value for a: 5
old   5: a:=&a;
new   5: a:=5;
5Square area is25

2.显示10部门的名字
SQL> declare
  2  vdname varchar2(30);
  3  begin
  4  select dname into vdname from dept where deptno=10;
  5  dbms_output.put_line(vdname);
 6  end;
  7  /
ACCOUNTING
 

3.显示7566员工的姓名，薪水（使用%type,record方式）
SQL> l
  1  declare
  2  vename emp.ename%type;
  3  vsal emp.sal%type;
  4  begin
  5  select ename,sal into vename ,vsal from emp where empno=7788;
  6  dbms_output.put_line(vename||' '||vsal);
  7* end;
SQL> /
SCOTT 3002

SQL> l
  1  declare
  2  type remp is record(vename varchar2(20),vsal number);
  3  vemp remp;
  4  begin
  5  select ename,sal into vemp from emp where empno=7788;
  6  dbms_output.put_line(vemp.vename||vemp.vsal);
  7* end;
SQL> /
SCOTT3002

4.显示7566员工的姓名，职位，薪水（输出结束显示为：姓名：  职位：  薪水：）

SQL> declare
  2  vemp emp%rowtype;
  3  begin
  4  select * into vemp from emp where empno=7566;
  5  dbms_output.put_line('name:'||vemp.ename||' '||'job:'||vemp.job||'
          '||'sal:'||vemp.sal);
  6  end;
  7  /

name:JONES job:dancer sal:2977

5.显示7566员工姓名，部门名，薪水（使用%type,record,输出结束显示为：员工姓名：部>门名字： 薪水：）
SQL> l
  1  declare
  2  vename emp.ename%type;
  3  vdname dept.dname%type;
  4  vsal emp.sal%type;
  5  begin
  6  select ename,dname,sal into vename,vdname,vsal from emp e,dept d where e.deptno=d.deptno and empno=7566;
  7  dbms_output.put_line('name:'||vename||'  '||'deptment:'||vdname||' '||'salary:'||vsal);
  8* end;
SQL> /
name:JONES deptment:RESEARCH salary:2977

6.显示工资最高的员工姓名
SQL> l
  1  declare
  2  vename varchar2(20);
  3  begin
  4  select ename into vename from emp where sal=(select max(sal) from emp);
  5  dbms_output.put_line(vename);
  6* end;
SQL> /
KING

7.通过部门编号计算该部门的总人数，应发给员工的总薪水。
SQL> l
  1  begin
  2  for i in(select deptno ,count(*) c,sum(sal) s from emp group by deptno) loop
  3  dbms_output.put_line('deptnumber:'||i.deptno||' '||'sum_sal:'||' '||i.s||' '||'sum_people:'||i.c);
  4  end loop;
  5* end;
SQL> /
deptnumber:30 sum_sal: 9400 sum_people:6
deptnumber:20 sum_sal: 7875 sum_people:4
deptnumber:10 sum_sal: 8750 sum_people:3

PL/SQL procedure successfully completed.

8.显示部门编号，每个部门的最高薪水
QL> l
  1  begin
  2  for i in(select deptno,max(sal) m from emp group by deptno) loop
  3  dbms_output.put_line('deptnumber:'||i.deptno||' '||'max_sal:'||i.m);
  4  end loop;
  5* end;
SQL> /
deptnumber:30 max_sal:2850
deptnumber:20 max_sal:3000
deptnumber:10 max_sal:5000

PL/SQL procedure successfully completed.

9.显示薪水最高的员工姓名，薪水

SQL> begin
  2  for i in (select ename,sal from emp where sal=(select max(sal) from emp)) loop
  3  dbms_output.put_line('name:'||i.ename||' '||'sal:'||i.sal);
  4  end loop;
  5  end;
  6  /
name:KING sal:5000

PL/SQL procedure successfully completed.

10.显示部门名，员工名，薪水等级
  SQL> l
    1  begin
    2  for i in (select dname,ename,grade from emp join dept using (deptno) join salgrade on(sal between losal and hisal)) loop
    3  dbms_output.put_line(i.dname||'   '||i.ename||'  '||i.grade);
    4  end loop;
    5* end;
SQL> /
RESEARCH   SMITH  1
SALES   JAMES  1
RESEARCH   ADAMS  1
SALES   WARD  2
SALES   MARTIN  2
ACCOUNTING   MILLER  2
SALES   TURNER  3
SALES   ALLEN  3
ACCOUNTING   CLARK  4
SALES   BLAKE  4
RESEARCH   JONES  4
RESEARCH   SCOTT  4
ACCOUNTING   KING  5

PL/SQL procedure successfully completed.

SQL>

 

转载于:https://my.oschina.net/liubaizi/blog/796111