题目：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
1 select name, 2 case when ap>=1 then 'YES' else 'NO' end ap, 3 case when og>=1 then 'YES' else 'NO' end og, 4 case when ba>=1 then 'YES' else 'NO' end ba 5 from ( 6 select name, sum(ap) ap, sum(og) og, sum(ba) ba 7 from (select name_a name, 8 decode(b.name, 'Apple', 1, 0) ap, 9 decode(b.name, 'Orange', 1, 0) og, 10 decode(b.name, 'Banana', 1, 0) ba 11 from t_b b) 12 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
1 select * 2 from t_b 3 where rownum < 5 4 minus 5 select * 6 from t_b 7 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‟.
1 create or replace function fun1(fname in varchar2) return varchar2 is 2 fruit varchar2(10); 3 n varchar2(10); 4 ap number; 5 og number; 6 ba number; 7 begin 8 select name, ap, og, ba into n,ap,og,ba 9 from (select name, sum(ap) ap, sum(og) og, sum(ba) ba 10 from (select name_a name, 11 decode(b.name, 'Apple', 1, 0) ap, 12 decode(b.name, 'Orange', 1, 0) og, 13 decode(b.name, 'Banana', 1, 0) ba 14 from t_b b) 15 group by name) 16 where name = fname; 17 18 if ap = 1 and og = 1 then select 'Orange' into fruit from dual; 19 elsif ap = 1 and og = 0 and ba = 1 then select 'Banana' into fruit from dual; 20 elsif ap = 0 and og = 1 and ba = 1 then select 'Banana' into fruit from dual; 21 elsif ap = 1 and og = 0 and ba = 0 then select 'Apple' into fruit from dual; 22 elsif ap = 0 and og = 1 and ba = 0 then select 'Orange' into fruit from dual; 23 elsif ap = 0 and og = 0 and ba = 1 then select 'Banana' into fruit from dual; 24 end if; 25 26 27 return (fruit); 28 end fun1;
这里暂时还没想出更好的办法。。。
