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; 

--子查询
SELECT s.sid,s.sname,s.fid,(SELECT fname FROM father WHERE father.fid=s.fid) AS fname
FROM son s  
         
4、fid、fname、儿子数(没有儿子的不显示)
SELECT f.fid,f.fname,s.sname
FROM father f JOIN son s ON f.fid=s.fid

SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
     
5、fid、fname、儿子数(没有儿子的个数显示为0) 
--为father表为准进行连接
SELECT f.fid,f.fname,s.sname
FROM father f LEFT JOIN son s ON f.fid=s.fid

SELECT f.fid,f.fname,COUNT(*) x
FROM father f LEFT JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname

SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f LEFT JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname

SELECT f.fid,f.fname,COUNT(s.sid) x
FROM son s  RIGHT JOIN father f ON f.fid=s.fid
GROUP BY f.fid,f.fname

SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f , son s
WHERE f.fid=s.fid
GROUP BY f.fid,f.fname
--Oracle的特有方式
SELECT f.fid,f.fname,COUNT(s.sid) x
FROM father f , son s
WHERE f.fid=s.fid(+)
GROUP BY f.fid,f.fname

--不是什么地方都 count(*)
      

6、找出不止有1个儿子的father信息：fid、fname、儿子数    
--sql思路：统计出所有父亲的儿子数，统计以后过滤，用having
SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
HAVING COUNT(*)>1;

--函数思路
CREATE OR REPLACE FUNCTION f_get_child_count(
p_fid father.fid%TYPE
)
RETURN NUMBER
AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM son WHERE son.fid=p_fid;
  RETURN v_count;
END;

SELECT f.fid,f.fname,f_get_child_count(f.fid) AS x
FROM father f
WHERE f_get_child_count(f.fid)>1;
 
7、找出儿子最多的father信息：fid、fname、儿子数    
--非相关子查询
SELECT f.fid,f.fname,COUNT(*) x
FROM father f JOIN son s ON f.fid=s.fid
GROUP BY f.fid,f.fname
HAVING COUNT(*)=(
    SELECT max(COUNT(*))
    FROM father f JOIN son s ON f.fid=s.fid
    GROUP BY f.fid,f.fname
);
   
8、找出所有father中身高最高的儿子。 
SELECT * FROM son
WHERE height = (SELECT MAX(height) FROM son)

9、找出各father中身高最高的儿子。
--容易错
SELECT * FROM son
WHERE height IN(
  SELECT MAX(height) FROM son GROUP BY fid
) 

--相关子查询
SELECT s1.*
FROM son s1
WHERE s1.height = (SELECT MAX(height) FROM son s2 WHERE s2.fid=s1.fid);

SELECT s1.* FROM son s1
SID SNAME FID HEIGHT MONEY
100 s1 1 1.7 7000   SELECT MAX(height) FROM son s2 WHERE s2.fid=1  ->  1.7=第一条记录的height
101 s2 2 1.7 8000   SELECT MAX(height) FROM son s2 WHERE s2.fid=2  ->  1.8!=第二条记录的height
102 s3 2 1.8 9000   SELECT MAX(height) FROM son s2 WHERE s2.fid=2  ->  1.8=第三条记录的height

--函数
CREATE OR REPLACE FUNCTION f_get_max_height(
p_fid father.fid%TYPE
)
RETURN NUMBER
AS
  v_height NUMBER;
BEGIN
  SELECT MAX(height) INTO v_height FROM son WHERE son.fid=p_fid;
  RETURN v_height;
END;

SELECT f.*,f_get_max_height(f.fid) ROM father f

SELECT s.sid,s.sname,s.height,f_get_max_height(s.fid)
FROM son s 
WHERE s.height=f_get_max_height(s.fid);
        
10、找出身高在1.8到1.65之间的所有儿子及父亲信息。
SELECT f.fid,f.fname,s.sname,height
FROM father f LEFT JOIN son s ON f.fid=s.fid
WHERE height BETWEEN 1.65 AND 1.8;