SQL及PLSQL实践练习

编写一个匿名块，把所有员工的姓名、薪水、所在部门名称和工作地点提取到一个INDEX_BY TABLE 变量中输出，要求使用游标。

declare
type emp_record_typ is record(
v_lastname employees.last_name%TYPE,
v_salary employees.salary%TYPE,
v_dpna departments.department_name%TYPE,
v_lona locations.city%TYPE
); emp_rcm emp_record_typ;
cursor emp_cursor1 is select e.last_name,e.salary,d.department_name,l.city
from employees e, departments d, locations l
where e.department_id=d.department_id and d.location_id=l.location_id;
begin
open emp_cursor1;
loop
fetch emp_cursor1 into emp_rcm;
exit when emp_cursor1%NOTFOUND;
dbms_output.put_line('name'emp_rcm.v_lastname);
end loop;
close emp_cursor1;
end;

Test 表存放数据如下：

Sql> Select * From Test
Id Salary
-------- -------
1 1000
2 1500
3 1650
4 1000
5 1500
6 2000
7 3500
8 4800
8 Rows Selected

执行如下语句会产生什么结果：

select Id, Salary, Rank()Over(Order By Salary) From Test;

对于表 A( 有重复数据 ) ，请按要求写出统计 SQL ：

 1）查询出消费排在第二的所有用户的信息。2）查询出前两名的平均费用

（1）查询出消费排在第二的所有用户的信息。
select * from 
(select user_id, fee, row_number() over(order by fee desc) rm_fee from (
select user_id, fee, row_number() over(partition by user_id order by user_id) rm 
from test a) where rm = 1) where rm_fee = 2; 
（2）查询出前两名的平均费用。
select avg(fee) from (
select user_id, fee,row_number() over(order by fee desc) rm_fee from (
select user_id, fee, row_number() over(partition by user_id order by user_id) rm
from test a) where rm = 1)
where rm_fee <= 2;