SQL练习

father表   son表    
fid fname   sid sname fid height money
1 a   100 s1 1 1.7 7000
2 b   101 s2 2 1.6 8000
3 c   102 s3 2 1.8 9000s
create table father(
fid number primary key,
fname varchar(10)
);
create table son(
sid number primary key,
sname varchar(10),
fid number,
height number,
money number
);
select t.*, t.rowid from father t;
select t.*, t.rowid from son t;
1、查询sid、sname、fid  
select sid,sname,fid from son;
     
2、查询各儿子涨价20%以后的新学费,注意,8000块以下的不涨价。
select money*1.2 as new_money from son where money>=8000
union all
select money from son where money<8000;
select '涨了' as 涨价情况,money*1.2 as new_money from son where money>=8000
union all
select '没涨',money from son where money<8000;
select money,money*1.2 as new_money from son where money>=8000
union all
select money,null from son where money<8000;
select son.*,(case when money<8000 then money
               when money>=8000 then money*1.2 end)as new_money
from son;
              
--函数
create or replace function f_get_new_money(p_money son.money%type)
return number
as
begin
  if(p_money>=8000)then
    return p_money*1.2;
  else
    return p_money;
  end if;
end;
select son.*,scott.f_get_new_money(money) as new_money from son;
--一题多解，下面的题目也是按照这种思路（1.用SQL 2.如果做不来，用函数一定可以）
     
3、查询sid、sname、fname。  
--语法  from t1 join t2 on t1.id=t2.id
SELECT s.sid,s.sname,f.fname
from father f JOIN son s ON f.fid=s.fid;