本来之前是做网络通信这块的,今天面试题,出了个SQL 没做出来,长时间没接触SQL 了。。估计要被BS了。。
题目是这样的。
一张学生表student (sid , name ,sex) 一张课程表course(c_id , name , teachers, required..) 一张课程得分表 course_score(c_sid(关联到student sid), c_id (关联到course c_id), score )
现在从这些表中选出必修课程超过三门不及格的学生信息。必修课程是required = 1 的记录.
请各位指点!!
题目是这样的。
一张学生表student (sid , name ,sex) 一张课程表course(c_id , name , teachers, required..) 一张课程得分表 course_score(c_sid(关联到student sid), c_id (关联到course c_id), score )
现在从这些表中选出必修课程超过三门不及格的学生信息。必修课程是required = 1 的记录.
请各位指点!!
from student, course_score
where course_score.c_sid = student.sid and course.required = 1
group by student.sid, student.name, student.sex
having count(*) > 3
from student, course_score
where course_score.c_sid = student.sid and course.required = 1 and course_score.score < 60
group by student.sid, student.name, student.sex
having count(*) > 3
from student, course, course_score
where course_score.c_sid = student.sid and course_score.c_id = course.c_id and course.required = 1
group by student.sid, student.name, student.sex
having count(*) > 3
from student, course, course_score
where course_score.c_sid = student.sid and course_score.c_id = course.c_id and course.required = 1 and course_score.score > 60
group by student.sid, student.name, student.sex
having count(*) > 3
from student t join course_score s on t.sid=s.c_sid jolin course c on s.c_id=c.c_id
where c.required=1 and s.score<60
order by t.sid,t.name,t.sex
having count(*)>3;