一个查询转换

 题目：There are two table:  

T_A (name VARCHAR2(100)) 

T_B (name VARCHAR2(100), name_a VARCHAR2(100)); 

The values are:

 

(1) Please write a SQL to output below result: 

Name         Apple       Orange       Banana 

-----------  ----------  -----------  ----------- 

Jane         Yes         Yes          Yes 

Leo          Yes         Yes          No 

David        No          No           Yes

select name,
       case when ap>=1 then 'YES' else 'NO' end ap,
       case when og>=1 then 'YES' else 'NO' end og,
       case when ba>=1 then 'YES' else 'NO' end ba
  from (
select name, sum(ap) ap, sum(og) og, sum(ba) ba
  from (select name_a name,
               decode(b.name, 'Apple', 1, 0) ap,
               decode(b.name, 'Orange', 1, 0) og,
               decode(b.name, 'Banana', 1, 0) ba
          from t_b b)
 group by name)

(3)  Please write a SQL to output below result: 

(Find rows between 2 and 4, using ROWNUM) 

Name        Name_a 

---------   ------------- 

Orange       Jane 

Banana       Jane 

Apple        Leo 

select *
  from t_b
 where rownum < 5
minus
select * 
  from t_b
 where rownum  < 2

(4) Please write a Function: 

This function has a parameter which has the same data type as 

name in T_A and return the second fruit name in T_B.  

if one person has no the second fruit, the function return the 

first fruit. 

Example: pass „Jane‟ as parameter, return „Orange‟; pass 

„David‟, return „Banana‟.

create or replace function fun1(fname in varchar2) return varchar2 is
fruit varchar2(10);
n varchar2(10);
ap number;
og number;
ba number;
begin
  select name, ap, og, ba into n,ap,og,ba
    from (select name, sum(ap) ap, sum(og) og, sum(ba) ba
            from (select name_a name,
                         decode(b.name, 'Apple', 1, 0) ap,
                         decode(b.name, 'Orange', 1, 0) og,
                         decode(b.name, 'Banana', 1, 0) ba
                    from t_b b)
           group by name) 
   where name = fname;
   
   if ap = 1 and og = 1 then select 'Orange' into fruit from dual;
   elsif ap = 1 and og = 0 and ba = 1 then select 'Banana' into fruit from dual;
   elsif ap = 0 and og = 1 and ba = 1 then select 'Banana' into fruit from dual;
   elsif ap = 1 and og = 0 and ba = 0 then select 'Apple' into fruit from dual;
   elsif ap = 0 and og = 1 and ba = 0 then select 'Orange' into fruit from dual;
   elsif ap = 0 and og = 0 and ba = 1 then select 'Banana' into fruit from dual;
   end if;
   

   return (fruit);
end fun1;

这里暂时还没想出更好的办法。。。