Oracle_SQL基础考核题目

一、查询没学过“刘阳”老师课的同学，显示（学号、姓名）（5分）

select s.student_no , s.student_name from hand_student s 
left join hand_student_core sc on s.student_no=sc.student_no
left join hand_course c on sc.course_no=c.course_no
left join hand_teacher t on c.teacher_no=t.teacher_no
where t.teacher_name!='刘阳';

二、查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）（5分）

• 方法一：

select s.student_no, s.student_name
  from hand_student s,
       (
       select hsc.*,
               rank() over(partition by student_no order by core) as rank
          from hand_student_core hsc
         where hsc.course_no = 'c001'
            or hsc.course_no = 'c002'
            ) temp
 where 1 = 1
 and s.student_no = temp.student_no
 and temp.course_no = 'c001'
 and temp.rank <> 1

• 方法二：

select s.student_no,s.student_name from hand_student s,
hand_student_core sc1,hand_student_core sc2 
where s.student_no =