HSQL之初级训练

本次训练数据集介绍如下：

本次HSQL训练为初级训练，有四个表（course_info，score_info，student_info，teacher_info）

数据如下：

查询同姓（假设每个学生姓名的第一个字为姓）的学生名单并统计同姓人数大于等于2的姓

select t1.sub,count(*) from
(select stu_id,stu_name,substr(stu_name,0,1) sub from student_info ) t1
group by t1.sub
having count(1)>=2;

查询数学成绩不及格的学生和其对应的成绩，按照学号升序排序

select
    s.stu_id,
    s.stu_name,
    t1.score
        from student_info s
        join
(select * from score_info where
course_id=(select course_id from course_info where course_name='数学')
and score<60) t1
on s.stu_id = t1.stu_id
order by s.stu_id;

按照如下格式显示学生的语文、数学、英语三科成绩，没有成绩的输出为0，按照学生的有效平均成绩降序显示

学生id 语文 数学 英语 有效课程数 有效平均成绩

（在写这道题的时候采用的是多表进行全外联，但是在后边表进行全外联的时候on的条件应该怎么进行连接那?

这里就用到了一个函数nvl（）函数，与MySQL中的ifnull()函数  相似 nvl(id1,id2)----如果id1不为空返回id1，如果id1为空则返回id2

还用到了nvl函数的加强版，coalesce（）函数，该函数可以填写无数个参数

）

因为刚开始学，只能写的很复杂（四表联合查询）

代码如下：

select t4.stu_id, nvl(t1.score,0)  ,nvl(t2.score,0),nvl(t3.score,0),cnt,avg
from
(
    select
       stu_id,
       score
  from score_info
   where course_id='01'
    ) t1
full join
(select
       stu_id,
       score
  from score_info
   where course_id='02') t2
on t1.stu_id=t2.stu_id
full outer join

(select
       stu_id,
       score
  from score_info
   where course_id='03') t3
on nvl(t1.stu_id,t2.stu_id)=t3.stu_id
right join
(select
       stu_id,
       count(1) cnt,
avg(score) avg
  from score_info
   group by stu_id) t4
on coalesce(t1.stu_id,t2.stu_id,t3.stu_id)=t4.stu_id
order by avg desc ;