第二次作业

Execise02

1.查询85年以后出生的学生姓名、性别和出生日期
2.列表显示所有可能的学生选课组合(学号、课程号)
3.查询1、2、4班中陈姓同学的信息
4.查询所有及格的学生姓名、所选课程名及所得分数
5.统计各门课程的及格人数(课程编号、课程名、及格人数)
6.统计各门课程的总人数、及格人数和不及格人数(课程编号、课程名、总人数、及格人数、不及格人数)(*较难*)
7.统计各门课程选课的人数，若课程无人选择则选课人数为0(课程编号、课程名、选课人数)
8.查询未选课程的学生名单(学号、姓名)，查询选择所有课程的学生名单(*较难*)

1.
  SELECT f_name as "姓名",f_sex as "性别 ",f_birth as "出生日期"
  from t_student
  where f_birth>'31-12月-85';

2.
  select t_student.f_id as"学号",t_course.f_id as "课程号"
  from t_student cross join t_course;

3.
  SELECT * from t_student
  where f_name like '陈%' and f_class in(1,2,4);

4.
  SELECT t_student.f_name,t_course.f_name,f_grade
  FROM  t_grade
  join t_student on(t_grade.f_stuid=t_student.f_id)
  join t_course on (t_grade.f_courseid=t_course.f_id)
  WHERE f_grade>=60 ;

5.
  select t_course.f_id as "课程编号",f_name as "课程名称",count(f_stuid) as "及格人数"
  from t_grade , t_course
  where  t_grade.f_courseid=t_course.f_id  and f_grade>=60
  group by t_course.f_id,t_course.f_name;

6.
  select t_course.f_id as "课程编号",f_name as "课程名称",count(f_stuid) as "总人数", sum(case when f_grade>=60 then 1 else 0 end)as "及格人数",sum(case when f_grade<60 then 1 else 0 end) as "不及格人数"
  from t_grade right join t_course on t_grade.f_courseid=t_course.f_id
  group by t_course.f_id,t_course.f_name;

7.
  select t_course.f_id as "课程编号",f_name as "课程名称",count(f_stuid) as "选课人数"
  from t_grade right join t_course on t_grade.f_courseid=t_course.f_id
  group by t_course.f_id,t_course.f_name;

8.
  select  t_student.f_id as "学号",t_student.f_name as "姓名"
  from  t_student
  where not exists(select t_student.f_id from t_grade where t_student.f_id=t_grade.f_stuid);

8-2
   select t_student.f_id , t_student.f_name,tab1.count1
 from (select t_student.f_id as p, count(f_courseid) as count1
              from t_student left join t_grade on(t_student.f_id=t_grade.f_stuid)
              group by t_student.f_id) tab1 right join t_student on(t_student.f_id =tab1.p)
 where    tab1.count1  not in( select count(*) as 课程总数 from t_course);