经典数据库查询例题(更新)

[size=large]有两个表，分别为father表和son表。表结构如下：

father表
fid    fname
1       a
2       b
3       c

son表
sid   sname     fid   height     money
100     s1       1      1.7      7000
101     s2       2      1.7      8000
102     s3       3      1.8      9000 

建表代码：
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;
  

问题用sqlserver或者Oracle查询：
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;

 

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) 
方法3：以father表为准进行连接
SELECT f.fid,f.fname,s.sname
FROM father f LEFT JOIN son s ON f.fid=s.fid；

 

方法4：

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；

 

方法5：

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；

 

方法6：
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;

 

方法7：
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;

 

方法8：
--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;

方法2：函数思路
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

 

方法2：函数
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;

 

方法3：

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;